SQL syntax errors when importing database into phpMyAdmin (MAMP)

Question

In order to import a 120Mb database into phpMyAdmin with MAMP I split it up using the following:

split -l 100 /Applications/MAMP/htdocs/test/database_test_wordpress.sql /Applications/MAMP/htdocs/test/dbpart-

However when importing the second part I get the error:

Error
SQL query:

CREATE TABLE  `wp_comments` (

 `comment_ID` BIGINT( 20 ) UNSIGNED NOT NULL AUTO_INCREMENT ,
 `comment_post_ID` BIGINT( 20 ) UNSIGNED NOT NULL DEFAULT  '0',
 `comment_author` TINYTEXT NOT NULL ,
 `comment_author_email` VARCHAR( 100 ) NOT NULL DEFAULT  '',
 `comment_author_url` VARCHAR( 200 ) NOT NULL DEFAULT  '',
 `comment_author_IP` VARCHAR( 100 ) NOT NULL DEFAULT  '',
 `comment_date` DATETIME NOT NULL DEFAULT  '0000-00-00 00:00:00',
 `comment_date_gmt` DATETIME NOT NULL DEFAULT  '0000-00-00 00:00:00',
 `comment_content` TEXT NOT NULL ,
 `comment_karma` INT( 11 ) NOT NULL DEFAULT  '0',
 `comment_approved` VARCHAR( 20 ) NOT NULL DEFAULT  '1',
 `comment_agent` VARCHAR( 255 ) NOT NULL DEFAULT  '',
 `comment_type` VARCHAR( 20 ) NOT NULL DEFAULT  '',
 `comment_parent` BIGINT( 20 ) UNSIGNED NOT NULL DEFAULT  '0',
 `user_id` BIGINT( 20 ) UNSIGNED NOT NULL DEFAULT  '0',
PRIMARY KEY (  `comment_ID` ) ,
KEY  `comment_post_ID` (  `comment_post_ID` ) ,
MySQL said: Documentation

#1064 - 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 '' at line 18

Do I need to upgrade the database somehow? Or am I way off?! Sorry, new to this :-)

Note: I first tried increasing upload_max_filesize, memory_limit and post_max_size in MAMP's php.ini but on import I got a MySQL server has gone away error.

You can not split a file containing SQL commands spanning multiple lines just “randomly” by x lines and still expect it to work … — C3roe
– C3roe, Commented Dec 3, 2013 at 16:44
Try using php to import the sql file by reading the file file_get_contents, then mysql_query. This way you won't have to split up the sql file. — user1403947
– user1403947, Commented Dec 3, 2013 at 16:45

Mosty Mostacho · Accepted Answer · 2013-12-03 16:45:14Z

1

split is a linux command that will just slice text. You can't just do that and expect an import to work. In your particular example, the creation of a table is not being properly finished by a ;, which means you are most likely missing the creation of indexes or constraints below the cut part of the text.

I wouldn't slice the file at all and import the whole SQL file from the console instead of using php for that. This should do the trick:

mysql -u username -p database_name < file.sql

answered Dec 3, 2013 at 16:45

Mosty Mostacho

43.6k16 gold badges99 silver badges124 bronze badges

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

7 Comments

Caroline Elisa Over a year ago

Thanks Mosty, as I said I'm a newbie! - So I tried this mysql -u root -p druglessdoctor < /Applications/MAMP/htdocs/druglessdoctor/database_drugless_wordpress2.sql and got this

#1064 - 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 'mysql -u root -p druglessdoctor < /Applications/MAMP/htdocs/druglessdoctor/datab' at line 1

.

Mosty Mostacho Over a year ago

You seem to be running that in the mysql prompt. You should do that in your linux prompt (just quit mysql and you'll be there :)

Caroline Elisa Over a year ago

Yup I'm lost. On a Mac, I do this in Terminal?

Caroline Elisa Over a year ago

Thanks for bearing with me! But I just get -bash: mysql: command not found so I googled it and found I need to put something like this first /Applications/MAMP/Library/bin/mysql --host=localhost -uroot -proot which allows me to use say show databases; and see all of them - However not sure how to then use the line you suggested to target druglessdoctor?

Mosty Mostacho Over a year ago

You must not get into the mysql prompt. Do this from the terminal itself. Assuming druglessdoctor is your database, just write that /Applications/MAMP/Library/bin/mysql -u root -p druglessdoctor < /path/to/the/file.sql

|

Community · Accepted Answer · 2017-05-23 11:56:09Z

0

Thanks guys for pointing out that split was not the way to go!

Instead I managed to upload the original 120Mb file by editing the following limits before restarting MAMP:

In MAMP/bin/php/php5.4.10/conf/php.ini I changed the memory_limit to 200M, the post_max_size to 200M and the upload_max_filesize to 120M.

I then copied MAMP/Library/support-files/my-large.cnf to MAMP/Library and renamed it to my.cnf before setting max_allowed_packet to 100M

Was pointed in this direction MAMP FAQs and https://stackoverflow.com/a/13613140/767761

edited May 23, 2017 at 11:56

CommunityBot

11 silver badge

answered Dec 3, 2013 at 20:21

Caroline Elisa

1,2466 gold badges18 silver badges35 bronze badges

Collectives™ on Stack Overflow

SQL syntax errors when importing database into phpMyAdmin (MAMP)

2 Answers 2

7 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related