How to upload large database files to phpmyadmin????

Sergeant Joined: Jun 17, 2003 Posts: 90

As i continue to pick away at building my new site, I continue to run in to problems, but then I search these boards and find the answers.....however I'm now stumped.

I've sucessfully upgraded my current nuke from 6.5 to 7.8. I've now exported the data and want to import it into my new 7.8 site which is hosted else where. The issue is the file is too large for phpmyadmin.

How can I get around this?

Thanks
Dave

Sergeant Joined: Feb 04, 2004 Posts: 100

If you can access the server, ftp all the phpnuke MySQL files from your old to new location. I do this with a 150 meg database. This way it won't time out on you like you are experiencing right now.

Once in there, optimize your database and you should be good to go.

hope that helps,

Sergeant Joined: Jun 17, 2003 Posts: 90

Thanks but I do not have access to the mysql folders.

What are my other options?

Lieutenant Joined: Sep 13, 2003 Posts: 256

Simplest is to use BigDump to import a big database:
http://www.ozerov.de/bigdump.php

MySQLDumper is an alternative too, which also can be used for big backups with time out issues:
http://www.mysqldumper.de/en/

Sergeant Joined: Jun 17, 2003 Posts: 90

Slackervaara wrote:

Simplest is to use BigDump to import a big database:
http://www.ozerov.de/bigdump.php

Alright I've been trying this since yesterday and no luck... I've done lots of reading on how to edit your config to change to different users but yet I still get this error.

MySQL: User 'username' has exceeded the 'max_questions' resource (current value: 50000)

What can I do? I've most recently tried the code change rom the bottom of this page

http://nukecops.com/modules.php?name=Forums&file=modules&name=Forums&file=viewtopic&t=27071&postdays=0&postorder=asc&&start=0

but I still get the same above error for the first user.

There has to be a way to upload larger data.... grrr.

Site Admin Joined: Aug 17, 2003 Posts: 12342

It means that your server has capped the number of SQL queries you can do. You will have to break your file into seperate steps that you can do in periods (depending on how your limit works, I believe its 50000 queries per hour)

Lieutenant Joined: Sep 13, 2003 Posts: 256

If you use MySQLDumper you can choose Multipart backup and choose the size also of the SQL-files in the backup. One can also ask the web host, if they can change this limit for you.

Sergeant Joined: Jun 17, 2003 Posts: 90

Well it's been at least 8 hours of me sitting in front of this damn pc trying to figure all this out...

So now I've managed to get past the upload problem by adding additional users into the Bigdump file. I no longer get errors..... however.. of course there'd be something else.

I've realized that when I exported the file from my old website that it timed out.. so although the file I was trying to import was large and causing errors it wasn't complete. At the bottom of my exported .sql file I get this error.

Fatal error: Maximum execution time of 300 seconds exceeded in

So now I'm trying to find out how I can export the file completely.

Oh and one more note... my eyes hurt. Shocked

Lieutenant Joined: Sep 13, 2003 Posts: 256

You must install MySQLDumper to make a backup, if you have time out issues. I have used it for a year and it works very well. My backup is 370 MB.

Posted: Fri Nov 16, 2007 12:20 am

As stated earlier in this thread
http://www.ozerov.de/bigdump.php
works fine for phpnuke with large database.

Lieutenant Joined: Sep 13, 2003 Posts: 256

BigDump is for import of databases only, whereas MySQLDumper can be used both for import and export. MySQLDumper is a much more advanced program and if you have to export you must use it. phpMyAdmin has a flaw in the respect that it does not report if the backup is incomplete due to time out issues.

Sergeant Joined: Jun 17, 2003 Posts: 90

Alright... a new day. I've gotten so much from this board that I've always tried to contribute back as much as possible when I find stuff out.

Although my intial question was how to upload large files as you've read I've ran into a few other issues along the way.

My solutions have been:

Exporting large database:

I used mysqldumper to download my existing data base from my old server. I don't understand why it shows only 112 megs on my current server but once it was done exporting it was now 378meg? Anyway thats all done, thanks for the pointer on that. Smile

Importing large database:

Next, i've been able to over come the MySQL: User 'username' has exceeded the 'max_questions' resource (current value: 50000) problem I was running into. I used Bigdump for this. I made the following code changes to the bigdump.php file. Folllow the link, it's at the bottom.

Note: there is one small change in that code on that page. You need to change where it says "$dbuname =" to be " $db_username =". Once I did that Bigdump worked without any issues. I just created a bunch of users so I wouldn't run into any problems.

http://www.karakas-online.de/forum/viewtopic.php?t=4987

I will add that I suspect the same code changes will allow Mysqldumper to do the same thing. This is what I plan on doing.

So next up... is to ftp this 378meg file to my new server. My upload blows at home so this is going to suck.

Thanks everyone for your help, I will report back if I can get mysqldumper to work with the max_questions (50000) issue. If not I'll go back to Bigdump as I know it works.

Lieutenant Joined: Sep 13, 2003 Posts: 256

I was also surprized that the size of the backup was so huge, but it is caused by the fact that MySQLDumper uses Complete insertes for the backup and this increases the size. You can change this in the Configuration of MySQLDumper. For me MySQLDumper compresses my backup of 370 MB to about 30 MB as gz-file.

I think that MySQLDumper has the identical problem as BigDump concerning
the import. I have read about it at the MySQLDumper site. By the way it will take about 1 hr to restore a 370 MB backup.

Sergeant Joined: Jun 17, 2003 Posts: 90

Thanks for the note, I can't seem to get the gzip to work. Everytime I try doing an export even on a small file with gzip active I get an error.

Sergeant Joined: Jun 17, 2003 Posts: 90

I started messing around with gzip activated again.

First time just trying one table it completed 100% but when I went to save it it gave me an error say the site couldn't be found. After digging I found that the file was actually sitting in the backup folder so I can get it no problem.

Second attempt, I tried the entire database again. It made it to about 40% and I got the msql error again.

Here it is:

Warning: Failed opening 'c:/phpdev/www/public/html/mysqldumper/dump.php' for inclusion (include_path='.;C:/phpdev/php/includes;C:/phpdev/php/class') in Unknown on line 0

Once I get this error the servers toast and I have to reload apache and all that crap.

Any ideas what's happening here?

Just so everyone knows, the server I'm trying to export from is my own server at home. However I'm moving to a host. That's the reason for all this crap.