import mysql database exported with phpmyadmin into local lamp stack

Question

I exported mydb.sql from phpMyAdmin. It is about 0.9GB in size.

I copied mydb.sql onto an external hard drive, plugged that drive into another computer on which I just installed a LAMP stack.

On that new computer, in Ubuntu 14.04, from terminal, I type

mysql -u -p, and log into mysql.

Then I type

mysql> use mydb
mysql> source mydb.sql

Boom! It takes off. But within five minutes, failure:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '<br />
<b>Fatal error</b>:  Maximum execution time of 300 seconds exceeded in <b' at line 83

It appears from lots of google searches that this has to do with my php.ini file and its max_execution_time and other limits. I have changed that file to up those limits, but I still get the same error.

Any ideas? I thought that bringing mydb.sql in via the mysql command line would not involve php, but apparently it does.

In command line there is no max_execution, only in web servers, I think the backup didn't finish correctly, can you check the last lines of that file: mydb.sql?, you can use tail for that. — Cyrus
– Cyrus, Commented Oct 7, 2015 at 14:36
No, this is not a PHP error. This is an SQL error - some text you were inserting caused a syntax error. The fact that the text happens to be a php error message is irrelevant - your sql is bad. — Marc B
– Marc B, Commented Oct 7, 2015 at 14:36
@Marc B, thanks I wondered about that. Could it be something phpMyAdmin put in when I exported? — prokaryote
– prokaryote, Commented Oct 7, 2015 at 14:44
Save your sql file into your root folder, let's say /var/www/htdocs/ then run this, change the credentials of course. mysql -u <mysql username> -p <mysql database name> /var/www/htdocs/sql_import.sql — Dgloria
– Dgloria, Commented Jan 28, 2022 at 10:54

e4c5 · Accepted Answer · 2016-08-16 06:23:12Z

1

ERROR 1064 (42000): You have an error in your SQL syntax;
check the manual that corresponds to your MySQL server version for the right syntax to use near 
'<br /><b>Fatal error</b>:  Maximum execution time of 300 seconds exceeded in <b' at line 83

This line should not be in your mydb.sql. This question has been tagged phpmyadmin so it's easy to guess that the data was exported with it but the script was terminated when it reached 300 seconds which is the max allowed on your server.

You have two options:

1) change your php.ini to increase the max execution time

2) dump the database using mysqldump

edited Aug 16, 2016 at 6:23

answered Oct 7, 2015 at 14:39

e4c5

53.9k11 gold badges110 silver badges139 bronze badges

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

6 Comments

prokaryote Over a year ago

yes, dumping with mysqldump seems the right option. Just figuring out how to do THAT... I'm running xampp and it's proving extremely difficult to interact with mysql via command line. Note to self -- never use xampp, always manually install LAMP!

prokaryote Over a year ago

to be clear, I exported mydb.sql from phpMyAdmin, tried the import via mysql command line on the other computer.

e4c5 Over a year ago

Well ther you are! it confirms that it's the php.ini setting that limits execution time on your webserver that's to blame.

prokaryote Over a year ago

so the php.ini on the server from which I exported needs to be edited to up the execution time, and then re-export from phpMyAdmin? It seems odd that a php time constraint on one machine would cause a failure on another machine on which the server is not involved in importing the db.

Marc Delisle Over a year ago

It's not odd: the PHP time constraint on the exporting machine generated some malformed SQL inside your file.

|

Collectives™ on Stack Overflow

import mysql database exported with phpmyadmin into local lamp stack

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related