0

I have a 28 MB sql file need to import to mysql. Firstly, i'm using xampp to import, and it fails, and so i change my max_file_uploads, post_size(something like that)in php.ini-development and php.ini-product to 40 MB, but it still show "max:2048kb" and import fail again.

From research, i've learned to import by using mysql.exe, so i open mysql.exe and type the command line(msdos) below:

-u root -p dbname < C:\xxx\xxx\sqlfile.sql

but still failed again and again.....

what the problem is? xampp? or my sql setting?

Jason Kuah
  • 57
  • 1
  • 2
  • 3
  • Did you restarted your xampp after making changes to ini file? – Mr. Alien Aug 14 '13 at 10:52
  • 1
    can you please past the error message you get when the import fails? – Gnagno Aug 14 '13 at 10:53
  • Mr.Alien - yes Gnagno - this is when import sql.zip -"No data was received to import. Either no file name was submitted, or the file size exceeded the maximum size permitted by your PHP configuration." this is when impoty .sql - "You probably tried to upload too large file. Please refer to documentation for ways to workaround this limit." – Jason Kuah Aug 14 '13 at 10:57
  • This reference link can help you http://stackoverflow.com/questions/9981098/increasing-mysql-import-size http://stackoverflow.com/questions/8062496/how-to-change-max-allowed-packet-size – Amit Kumar Sharma Aug 14 '13 at 11:02
  • What does it say when you try to import it from the command line?. The message errors you mention sound like php / phpmyadmin to me. – The Marlboro Man Aug 14 '13 at 12:18

5 Answers5

2

Try this:

mysql -uroot -p --max_allowed_packet=24M dbname

Once you log into the database:

source C:\xxx\xxx\sqlfile.sql

I think that you should be able to load your file

Sal00m
  • 2,938
  • 3
  • 22
  • 33
1

How large is your file?. You might as well do it from a console:

mysql -u##USER## -p ##YOUR_DATABASE## < ##PATH_TO_YOUR_FILE##

Do that without executing your mysql.ext file: just "cd" right into the directory and try the command.

It will ask for your password and start importing right away. Don't forget to create the database or delete all tables if it's already there.

I always found this approach quick, painless and easier that rolling around with php directives, phpmyadmin configuration or external applications. It's right there, built into the mysql core.

The Marlboro Man
  • 971
  • 7
  • 22
0

You should increase max_allowed_packet in MySQL.

Just execute this command before importing your file:

set global max_allowed_packet=1000000000;
user12933
  • 9
  • 1
  • I've executed it, but still fail with error message "You probably tried to upload too large file. Please refer to documentation for ways to workaround this limit". – Jason Kuah Aug 14 '13 at 11:02
  • Within the same database connexion? Have you tried to add it manually at the start of your 28MB file? I know it is not good solution, but we will know if max_allowed_packet is the problem. – user12933 Aug 14 '13 at 11:12
0

I also fetched the similar problem. So after that I also conclude , large sql file will never be imported to mysql. It will always give timeout error.

Then I found a solution.

There is an software Heidisql.

follow below steps:-

1) download the software.
2) then install the software
3) create new session in Heidisql and open the session
4) then go to Tools -> Load SQL File -> Browse.

That's it. This solution works best for me.

check the link here

Ripa Saha
  • 2,532
  • 6
  • 27
  • 51
0

I found the only solution was to log in to MySQL from the command line and use the 'source' command:-

1) cd to the directory containing your SQL file for import, then log into MySQL:

#> mysql -u YOURUSERNAME -p -h localhost

2) use MySQL commands to import the data:

#> use NAMEOFYOURDB;

#> source NAMEOFFILETOIMPORT.sql

This also feeds back info about progress to your terminal, which is reassuring.

Rod
  • 91
  • 1
  • 2