Error while importing large .sql file into MySQL

Question

I am trying to import my backup .sql file into MySQL. I've tried using phpmyadmin, but that didn't allow my large sized file.

My file size is 243 MB. I've made changes in php.ini. The problem is out of 1.1M records, its importing only 600k records. I've tried with MySQL console also using command. Same thing happened with that also. I am using MySQL Workbench, and it showed me below error.

ERROR at line 695259: Unknown command '\a'.
Finished executing script
Operation failed with exitcode 1

What does this mean? How do I import my full data?

Let's take a look at line number 695259 and what is your import command? — e4c5
– e4c5, Commented Jul 26, 2016 at 7:23
From where can I check line number 695259? In mysql console, I tried: mysql -u username -p databasename < yoursqlfile.sql , while mysql workbench, I clicked on data import wizard and follow the steps. — iks_in
– iks_in, Commented Jul 26, 2016 at 7:25
mysql console isn't the only tool in your computer. There is something called a text editor. There is cat, less, grep if you are on linux — e4c5
– e4c5, Commented Jul 26, 2016 at 7:26
Don't use the word lac, no one outside of india will understand you. The error is not in your import but in your original export. You have probably exported the data using phpmyadmin and it has timed out halfway through — e4c5
– e4c5, Commented Jul 26, 2016 at 7:49

e4c5 · Accepted Answer · 2016-07-26 08:54:34Z

1

The presence of the <br/> tag at the tail of your dump file indicates that you have probably used phpmyadmin to create it and the the page timed out while the dump was in progress. Thus the error is not in your import process but in your dump process.

How can this be fixed? A global solution is to increase the php script execution time with set_time_limit an alternative is to use the mysql console client to create the dump by passing phpmyadmin.

A somewhat tedius but workable solution is to dump one table at a time. This doesn't always work (for example if one table makes up most of the size of the database it might still cause a timeout)

answered Jul 26, 2016 at 8:54

e4c5

53.9k11 gold badges110 silver badges139 bronze badges

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

1 Comment

e4c5 Over a year ago

Glad to have helped.

Faizan Younus · Accepted Answer · 2016-07-26 08:04:41Z

1

It seems dump was not properly created. There are few errors in you sql file.

answered Jul 26, 2016 at 8:04

Faizan Younus

8031 gold badge9 silver badges14 bronze badges

Collectives™ on Stack Overflow

Error while importing large .sql file into MySQL

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related