Search code examples
mysqllinuxphpmyadminmamp

SQL syntax errors when importing database into phpMyAdmin (MAMP)

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.

Solution

  • 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