cannot import too large sql file to mysql

Question

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?

Did you restarted your xampp after making changes to ini file? — Mr. Alien
– Mr. Alien, Commented Aug 14, 2013 at 10:52
can you please past the error message you get when the import fails? — Gnagno
– Gnagno, Commented Aug 14, 2013 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
– Jason Kuah, Commented Aug 14, 2013 at 10:57
This reference link can help you stackoverflow.com/questions/9981098/… stackoverflow.com/questions/8062496/… — Amit Kumar Sharma
– Amit Kumar Sharma, Commented Aug 14, 2013 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
– The Marlboro Man, Commented Aug 14, 2013 at 12:18

Sal00m · Accepted Answer · 2013-08-14 11:19:51Z

3

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

answered Aug 14, 2013 at 11:19

Sal00m

2,9143 gold badges24 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Allen Gingrich Over a year ago

This should be the answer -- only one that worked for me. Thanks!

The Marlboro Man · Accepted Answer · 2013-08-14 12:14:53Z

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.

answered Aug 14, 2013 at 12:14

The Marlboro Man

9819 silver badges22 bronze badges

Comments

user12933 · Accepted Answer · 2013-08-14 10:57:44Z

0

You should increase max_allowed_packet in MySQL.

Just execute this command before importing your file:

set global max_allowed_packet=1000000000;

answered Aug 14, 2013 at 10:57

user12933

91 bronze badge

2 Comments

Jason Kuah Over a year ago

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".

user12933 Over a year ago

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.

Ripa Saha · Accepted Answer · 2013-08-14 11:11:42Z

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

edited Aug 14, 2013 at 11:11

answered Aug 14, 2013 at 11:05

Ripa Saha

2,5406 gold badges29 silver badges54 bronze badges

2 Comments

Mark Baker Over a year ago

Note : Windows only for the benefit of people on other platforms

Ripa Saha Over a year ago

@MarkBaker yes . it's window's based.

Rod · Accepted Answer · 2022-11-09 11:52:45Z

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.

answered Nov 9, 2022 at 11:52

Rod

1011 silver badge2 bronze badges

Comments

Sudam Ranasinghe · Accepted Answer · 2024-10-06 16:41:05Z

0

Go to the php.ini file in the xamp click the config in the apach. Then in the text file type these commands.you may vary these values according to your file size of the database

post_max_size = 900M
memory_limit = 1024M
max_execution_time = 6000
max_input_time = 6000

answered Oct 6, 2024 at 16:41

Sudam Ranasinghe

281 silver badge8 bronze badges

Collectives™ on Stack Overflow

cannot import too large sql file to mysql

6 Answers 6

1 Comment

Comments

2 Comments

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

1 Comment

Comments

2 Comments

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related