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

Problems with the Windows version of XAMPP, questions, comments, and anything related.

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

Postby carriepr » 08. March 2012 02:06

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.
carriepr
 
Posts: 8
Joined: 08. March 2012 00:26
XAMPP Version: 5.6.3
Operating System: Win7

Re: How do I import large SQL DB using phpmyadmin?

Postby Sharley » 08. March 2012 02:13

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.
User avatar
Sharley
AF Moderator
 
Posts: 3316
Joined: 03. October 2008 05:10
Location: Yeppoon, Australia Time Zone: GMT/UTC+10
XAMPP Version: 5.6.3
Operating System: Win 7 Pro 32bit/XP Pro SP3

Re: How do I import large SQL DB using phpmyadmin?

Postby carriepr » 08. March 2012 06:10

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?
carriepr
 
Posts: 8
Joined: 08. March 2012 00:26
XAMPP Version: 5.6.3
Operating System: Win7

Re: How do I import large SQL DB using phpmyadmin?

Postby Sharley » 08. March 2012 07:33

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. :)
User avatar
Sharley
AF Moderator
 
Posts: 3316
Joined: 03. October 2008 05:10
Location: Yeppoon, Australia Time Zone: GMT/UTC+10
XAMPP Version: 5.6.3
Operating System: Win 7 Pro 32bit/XP Pro SP3

Re: How do I import large SQL DB using phpmyadmin?

Postby carriepr » 08. March 2012 09:02

That did the trick. Thanks so much for your help!
carriepr
 
Posts: 8
Joined: 08. March 2012 00:26
XAMPP Version: 5.6.3
Operating System: Win7

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

Postby Sharley » 08. March 2012 09:16

You're welcome and thanks for the feedback.

Best wishes. :)
User avatar
Sharley
AF Moderator
 
Posts: 3316
Joined: 03. October 2008 05:10
Location: Yeppoon, Australia Time Zone: GMT/UTC+10
XAMPP Version: 5.6.3
Operating System: Win 7 Pro 32bit/XP Pro SP3

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

Postby wocmultimedia » 06. April 2012 18:15

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
 
Posts: 2
Joined: 06. April 2012 18:07
XAMPP Version: 5.6.3
Operating System: xp professional sp3

[solved] Re: How do I import large SQL DB using phpmyadmin?

Postby wocmultimedia » 06. April 2012 19:46

[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
wocmultimedia
 
Posts: 2
Joined: 06. April 2012 18:07
XAMPP Version: 5.6.3
Operating System: xp professional sp3

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

Postby Filip_nikita » 04. November 2012 22:19

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.
Filip_nikita
 
Posts: 7
Joined: 03. November 2012 23:12
XAMPP Version: 5.6.3
Operating System: Vista

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

Postby Altrea » 05. November 2012 06:06

Hi Filip_nikita,

Filip_nikita wrote:[...] 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
We don't provide any support via personal channels like PM, email, Skype, TeamViewer!

It's like porn for programmers 8)
User avatar
Altrea
AF Moderator
 
Posts: 6946
Joined: 17. August 2009 13:05
XAMPP Version: 5.5.19
Operating System: Windows 10 Pro x64

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

Postby Filip_nikita » 05. November 2012 09:23

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

Can You please think of something else?
Filip_nikita
 
Posts: 7
Joined: 03. November 2012 23:12
XAMPP Version: 5.6.3
Operating System: Vista

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

Postby Filip_nikita » 05. November 2012 12:54

-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
 
Posts: 7
Joined: 03. November 2012 23:12
XAMPP Version: 5.6.3
Operating System: Vista

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

Postby Filip_nikita » 05. November 2012 14:05

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.
Filip_nikita
 
Posts: 7
Joined: 03. November 2012 23:12
XAMPP Version: 5.6.3
Operating System: Vista

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

Postby Nobbie » 05. November 2012 16:39

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
Nobbie
 
Posts: 7151
Joined: 09. March 2008 13:04

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

Postby Filip_nikita » 05. November 2012 22:08

Do You need this?

I uploaded file to filedropper.com just download it.

http://www.filedropper.com/phpinfo
Filip_nikita
 
Posts: 7
Joined: 03. November 2012 23:12
XAMPP Version: 5.6.3
Operating System: Vista

Next

Return to XAMPP for Windows

Who is online

Users browsing this forum: No registered users and 58 guests