Importing a MySQL data file using phpMyAdmin results in incomplete data in MySQL

I tried moving all my databases from MySQL 4.1 to MySQL 5.1 on Windows and since I had some InnoDB tables I could not just copy the data files between MySQL Server versions (from C:\Documents and Settings\All Users\Application Data\MySQL\MySQL 4.1\data\ to  C:\Documents and Settings\All Users\Application Data\MySQL\MySQL 5.1\data\).
So, after I exported the data from the old MySQL Server version I tried importing it to the MySQL 5.1 using phpMyAdmin.
The file to import was 42MB in size, so I had to set some PHP configuration variables (in php.ini file) to greater values:

post_max_size = 64M
upload_max_filesize = 64M

At first it looked like the phpMyAdmin import script entered a loop or something, as it wouldn’t stop even after running for several minutes and, as I was looking into the data files it was producing, no new files appeared. It was like it blocked on a table with a large number of records, which didn’t make any sense. Then I restarted the Apache server and I tried the import all over again. The same thing was happening. I finally decided to let it run for more time and the script finally stopped. But the data imported was incomplete; a part of the records in the large table I thought script blocked on, and every table and database from there on were missing, with no PHP or MySQL error returned.
Then I thought the script needed even more time to run, so I increased the value of the PHP configuration variable max_execution_time, but with the same result.

The solution. It seems that the execution time limit for the import script in phpMyAdmin is defined by the variable $cfg[‘ExecTimeLimit’] in <path_to_phpMyAdmin>/libraries/config.default.php and the value of this variable should be a lot greater than its default value of 300 seconds when importing a great amount of data. In my case it needed about an hour (3600 seconds) to complete. So you have to set this variable depending on the amount of data you are importing.
You might also need to set the MySQL configuration variable max_allowed_packet (in my case, in C:\Program Files\MySQL\MySQL Server 5.1\my.ini) to a greater value, if you have large queries in the data file you want to import, for avoiding MySQL error 2006, ‘mysql server has gone away’.

3 thoughts on “Importing a MySQL data file using phpMyAdmin results in incomplete data in MySQL

  1. Wow. Thank you so much for this. Absolutely invaluable. I had this problem and the max_allowed_packet tweak did the trick. Couldn’t find this info anywhere else. I owe you a pint!

Leave a Reply

Your email address will not be published. Required fields are marked *