Database Upload and Timeout

Database Upload and Timeout

by Steve Hyndman -
Number of replies: 12

Hello Moodlers,

I've exported my moodle database as an sql file from my production server and have been trying to import it to a local copy on my computer that I have set-up for testing using XAMPP.

Exporting the database always works fine, but I have never been able to get the sql file to import on my local machine....it always times out.

I thought the database may be too large (even though it's only about 10meg), so I have even exported the database several tables at a time, resulting in my sql files being between 2 and 4 megs...still no luck. I even exported as a zip and gzip (about a 1.3mb file) and still get a time out when trying to upload in phpmyadmin.

Everytime I try to restore the files through the SQL window in phpmyadmin, it looks like it gets through about 1/3 of the process and then stops and times out.

I've gone into phpmyadmin/config.default.php and changed "ExecTimeLimit" to "0", but I still get an error message that it has timed-out after 300 seconds...which was the default. Anyway, I don't think the time-out is the problem, since it looks like the upload stops after it gets about 1/3 of the way through....judging by the progress indicator.

I'm sure there has to be some setting in either php or mysql, but I haven't found it yet....still searching and exploring.

On a side note, I did export just a couple of tables (user tables) that resulted in a sql file smaller than 1 meg and that one imported fine.

Any suggestions would be appreciated. Thanks.

Average of ratings: -
In reply to Steve Hyndman

Re: Database Upload and Timeout

by Ray Lawrence -
Hi Steve,

Been there, got the T-shirt on this problem. I didn't find a consistent way of importing a large database, although cutting and pasting seemed to work (sometimes).

Interestingly I've been moving some course from a laptop to a PC this weekend, I found that when the exported database file included the name of the database it didn't work. When the exported file was called "localhost.sql" it worked everytime without a hitch. I'm not suggesting the name alone makes a difference but I suspect there is something different in the file which helps the process.

If you've not tried this already, export from the "Export" option on the phpMyAdmin home page rather than the export tab in the database itself.

Ray
Average of ratings: -
In reply to Ray Lawrence

Re: Database Upload and Timeout

by Steve Hyndman -

Thanks Ray. I tried the export from the phpMyAdmin page, but still not able to upload it. I'll keep working on this one...there must be some settings in php and/or mysql to correct this.

Steve

Average of ratings: -
In reply to Steve Hyndman

Re: Database Upload and Timeout

by Bernard Boucher -
Hi Steve,
if I understand correctly your problem, you don't necessary need to use phpMyAdmin export/import functions.

A database for MySql is "only"mixed a directory using the name of your database with a lot of files for tables and indexes.

If you copy that directory from your production server mysql/data/databasename to the same path on your local machine ( xampp/mysql/data/ ) then your database will be copied more fastly and without phpMyAdmin.

I hope it may help,

Bernard

Average of ratings: -
In reply to Bernard Boucher

Re: Database Upload and Timeout

by Steve Hyndman -

Thanks Barnard,

My problem is that I don't have root access to the server for this one...it's on a shared host and I only have access through phpMyAdmin.

Steve

Average of ratings: -
In reply to Steve Hyndman

Re: Database Upload and Timeout

by Dave Ray -

Steve,
           phpMyAdmin has limits set-up by your host. But
your host administrator should set up a shell access with
which you can use a telenet program such as puTTy . There
is a little learning curve, not much.

Dave

Average of ratings: -
In reply to Dave Ray

Re: Database Upload and Timeout

by Ray Lawrence -
Steve,

I'm watching this thread with great interest hoping you find the answer where I failed.

Dave: When I had this problem it was on a local machine without host intervention.

I had a quick look in the phpMyAdmin docs and found this:

[1.16] I cannot upload big dump files (memory, http or timeout problems).

The first things to check (or ask your host provider to check) are the values of upload_max_filesize, memory_limit and post_max_size in the php.ini configuration file.
All of these three settings limit the maximum size of data that can be submitted and handled by PHP. One user also said that post_max_size and memory_limit need to be larger than upload_max_filesize.
If you get a timeout problem, look at the $cfg['UploadDir'] feature.

Where/how would one access the "$cfg['UploadDir'] feature"?


Average of ratings: -
In reply to Steve Hyndman

Re: Database Upload and Timeout

by Bernard Boucher -
Hi Steve,
sorry to don't have decriptedmixed that you din't have root access!

I just made some tests:

with easyphp if the .sql file contain more than 20,000 records then it exit immediatly without erros message and without importing!

15,000 record imported without problem in few seconds.

with xampp standard I get your error after 300 seconds and without record inserted! During that time the cpu run at 100% but the computer was usable.

I forced ExecTimeLimit to 3000 and I get :

Fatal error: Maximum execution time of 3000 seconds exceeded in H:\xampplite\phpmyadmin\libraries\read_dump.lib.php on line 77

no record inserted and 100% cpu usage but the computer usable!

I need more thinking.

Have a good cogitationthoughtful

Bernard

Average of ratings: -
In reply to Bernard Boucher

Re: Database Upload and Timeout

by Steve Hyndman -

Thanks Bernard...at least now I know the problem is not limited to just me smile

I get the same errors you list above...I can upload small files, but no luck with larger ones. I've changed all the settings in php that Ray listed, but still no luck. My "php expert" on campus was out sick all last week...when he returns I'll ask him to explore this problem as well.

Steve

Average of ratings: -
In reply to Steve Hyndman

Re: Database Upload and Timeout

by Tim Allen -
Steve,

I've used a neat little script called Bigdump to solve this problem in the past. 

HTH,
Tim.
Average of ratings: -
In reply to Tim Allen

Re: Database Upload and Timeout

by Steve Hyndman -

Tim,

Thanks for the tip. I downloaded the script and it worked after I eliminated a few tables. Here is what happened.

When I downloaded the entire database from the webserver and tried to upload it to my local computer (I'm using XAMPP) with Bigdump, I got the following error:

"Error at the line 3001: INSERT INTO `mdl_backup_log` VALUES (2086, 18, 1101097807, 1101097807, ' workshop');

Query: INSERT INTO `mdl_backup_log` VALUES (2086, 18, 1101097807, 1101097807, ' workshop');

MySQL: Table 'teacher.mdl_backup_log' doesn't exist

Stopped on error"

So, I went back to my sever and using phpMyAdmin, I selected my moodle database, clicked on "Export" and "Select All" to highlight all 97 tables. Then I deselected the 5 tables dealing with "backup" (hold down ctrl key and click on each one). This resulted in downloading the sql file for my moodle database minus the 5 backup tables. I imported that sql file using Bigdump and it worked great! The I went back to my server and exported the 5 backup tables and imported them using Bigbump...they imported with no problem.

Just out of curosity, I then deleted the table and tried to import the sql (with the 92 tables) without Bigdump and had the same time-out problem.

It seems like the Bigdump script has solved my problem. Thanks for the help....I now have a local copy of Moodle on my USB Drive that mirrors my live site...great for testing and demos without needing an internet connection.

Steve

Average of ratings: -
In reply to Steve Hyndman

Re: Database Upload and Timeout

by Michael Penney -
Hi Steve, you may need to use a desktop based mysql admin tool for this, like navicat or some such.

We've had to do this for uploading a large course to a shared host when the browser based tools can't handle it.

You should be able to use a desktop mysql admin tool in the same basic way you use phpmyadmin, you put in your host name, db name, and password and then you can upload the database.

Alternativly, you could request your server admin load the files for you.
Average of ratings: -
In reply to Michael Penney

Re: Database Upload and Timeout

by Steve Garcia -
If you have shell access (don't need root access) a command line tool should do the job:
mysql -h dbserver -u myaccount -p mydb < myfile.sql
and it will ask you for your password.
There is no practical upper limit to the file size this will handle.

If you don't have shell access, then a desktop tool like you're suggesting should work as long as the mysql port is not blocked. With a "properly" paranoid admin it will be. If so, your best bet is to ask the admin to load the data for you.

The limit on phpmyadmin is the Apache/PHP, not mySQL. mySQL will accept a multi-gigabyte file just fine, but it will take some time to swallow it. This tends to cause problems with PHP.

Also look to see if it stops at exactly the same place every time. If so, look to make sure the data just after that point in your input file is ok. Syntax errors in the SQL, badly escaped characters, etc can stop mySQL in its tracks. All the data up to that point will be correctly updated, but the import will stop at the point of the error (at the last good data before the error.) At the command line you'll see a description of the error -- dunno what phpmyadmin does.
Average of ratings: -