How do I import large SQL DB using phpmyadmin? [SOLVED]

[carriepr]

I need to import a database that is larger than the limit in phpmyadmin. Is there a setting to increase this? Please let me know exactly what I need to tweak to get this to work.

[Sharley]

To give you an exact tweak I also need to know exactly what you have tried already and exactly how big the database is that you are trying to import using phpMyAdmin - do you receive any error messages.

[carriepr]

I am able to import SQL files that are smaller than the limit set (Max: 8,192KiB). I have compressed the DB using gzip, and have broken up the import to as small parts as possible, but one section of the database is 11,364KB, so it just won't fit.

Here is the error that I get when I try to import it.

"You probably tried to upload too large file. Please refer to documentation for ways to workaround this limit."

I have tried in the past to tweak the php.ini the way they describe in the documentation, but it didn't seem to make a difference. Perhaps I am not doing it right. Any suggestions?

[Sharley]

Open the \xampp\php\php.ini file and change this line (about 735) to look like this

Code: Select all

post_max_size = 16M

You may need to increase the size depending on the largest import sql file you have but do not increase it greater than the line at about 886 upload_max_filesize currently set at the default of 128M

After editing the php.ini file save it and then restart Apache and MySQL and in the import tab in phpMyAdmin you should now see the maximum file has changed from Max: 8MiB to Max: 16MiB if using my example above.

I just tested this on my 1.7.7 installation and it showed the above increase in maximum file size.

Just to add, if you get a time out when importing a large database then you would need to change these 2 lines to read something like 300 seconds from the defaults of 30 and 60 seconds

Code: Select all

max_execution_time = 300
max_input_time = 300

and as usual save and restart Apache and MySQL.


Let me know back if that helps your case.

Good luck.

[carriepr]

That did the trick. Thanks so much for your help!

[Sharley]

You're welcome and thanks for the feedback.

Best wishes.

[wocmultimedia]

hi Sharley,
first thank you for this precious post I had problem for importing a large database and I changed the size in the php.ini but I get this error and I'm not a good developer. Can you help me? Thanks, Angelo

Error

SQL query:

INSERT INTO `cache_menu` (`cid`, `data`, `expire`, `created`, `serialized`) VALUES ('links:management:tree-data:en:ec99d3452fef1ede622e66c68ba908b1dad455aa71f5e68648aeec6488b89c88', 0x613a323a7b733a343a2274726565223b613a313a7b693a313b613a323a7b733a343a226c696e6b223b613a34323a7b733a393a226d656e755f6e616d65223b733a31303a226d616e6167656d656e74223b733a343a226d6c6964223b733a313a2231223b733a343a22706c6964223b733a313a2230223b733a393a226c696e6b5f70617468223b733a353a2261646d696e223b733a31313a22726f757465725f70617468223b733a353a2261646d696e223b733a31303a226c696e6b5f7469746c65223b733a31343a2241646d696e697374726174696f6e223b733a373a226f7074696f6e73223b733a363a22613a303a7b7d223b733a363a226d6f64756c65223b733a363a2273797374656d223b733a363a2268696464656e223b733a313a2230223b733a383a2265787465726e616c223b733a313a2230223b733a31323a226861735f6368696c6472656e223b733a313a2231223b733a383a22657870616e646564223b733a313a2230223b733a363a22776569676874223b733a313a2239223b733a353a226465707468223b733a313a2231223b7[...]

MySQL said: Documentation
#2006 - MySQL server has gone away

[wocmultimedia]

[solved] Hi Sharley,

I read several posts and thanks to your suggestion to look for the my.ini file and this answer in
http://stackoverflow.com/questions/1980004/2006-mysql-server-has-gone-away-error-in-wamp

I succeeded to import my large database...it was the "max_allowed_packet"

Thanks again
Angelo

[Filip_nikita]

Hello,

Im using xampp 1.8.1 on Vista and have the same problem.
In \xampp\php\php.ini file I changed post_max_size to higher value but there is no upload_max_filesize and max_allowed_packet.
When I saved changes in php.ini file and restart Apache and MySQL in the import tab in phpMyAdmin nothing changed so I googled this post
http://stackoverflow.com/questions/1980004/2006-mysql-server-has-gone-away-error-in-wamp
and in \mysql\bin\my.ini file changed max_allowed_packet saved changes and restart Apache and MySQL and in the import tab in phpMyAdmin again nothing changed.

Can You please give any advice.

[Altrea]

Hi Filip_nikita,

[...] but there is no upload_max_filesize and max_allowed_packet.

In the default XAMPP 1.8.1 Package there is.

~line 922 in \xampp\php\php.ini: upload_max_filesize
~line 36 in \xampp\mysql\bin\my.ini: max_allowed_packet

best wishes,
Altrea

[Filip_nikita]

Thanks for advice I did change that and nothing changes again.

Can You please think of something else?

[Filip_nikita]

-line 770 in \xampp\php\php.ini: post_max_size = (changed to) 100M
-line 922 in \xampp\php\php.ini: upload_max_filesize = (changed to) 200M

~line 36 in \xampp\mysql\bin\my.ini: max_allowed_packet = (changed to) 20M
I tried changing ~line 161 in \xampp\mysql\bin\my.ini: max_allowed_packet = (to) 20M

Saved changes stopped MySQL ,Apache I also closed XAMPP Control Panel and open it again.
and nothing changed,same thing...

Warning: POST Content-Length of 17179580 bytes exceeds the limit of 8388608 bytes in Unknown on line 0

[Filip_nikita]

Ive made a copy of \xampp\php\php.ini and \xampp\mysql\bin\my.ini files (with changes above) ,reinstalled Xampp and paste php.ini and my.ini files to new Xampp installation.
That didnt help either.
Please help anyone.
I need to upload 17MB .sql file.

[Nobbie]

Run a phpinfo() and show us the output.

P.S.: The value of post_max_size MUST BE GREATER then upload_max_filesize anyway

see http://www.php.net/manual/en/ini.core.p ... t-max-size

[Filip_nikita]

Do You need this?

I uploaded file to filedropper.com just download it.

http://www.filedropper.com/phpinfo