When I create a database dump from Amazon RDS and then I try to import it locally, the result is ERROR 1064 (42000) at line 54.
At line 54 there is the following statement:
CREATE TABLE account_emailconfirmation (
The command used for dump is:
mysqldump -u user -h host.rds.amazonaws.com -p --default-character-set=utf8 --result-file=sync.sql database_name
The command used for import is:
mysql --user=root -p mpl -vv < sync.sql
And here is the output (verbosity increased).
--------------
CREATE TABLE `account_emailconfirmation` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`created` datetime(6) NOT NULL,
`sent` datetime(6) DEFAULT NULL,
`key` varchar(64) NOT NULL,
`email_address_id` int(11) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `key` (`key`),
KEY `acc_email_address_id_5bcf9f503c32d4d8_fk_account_emailaddress_id` (`email_address_id`),
CONSTRAINT `acc_email_address_id_5bcf9f503c32d4d8_fk_account_emailaddress_id` FOREIGN KEY (`email_address_id`) REFERENCES `account_emailaddress` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
--------------
ERROR 1064 (42000) at line 54: 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 '(6) NOT NULL,
`sent` datetime(6) DEFAULT NULL,
`key` varchar(64) NOT NULL,
' at line 3
Bye
The problem is with datetime(6). MySql introduced storing of fractional seconds in v5.6.4. The syntax for indicating fractional seconds in datetime - this is the (6) - is not recognised by previous mysql versions.
The data was exported from a mysql v5.6.4 or later, and was attempted to be imported into an earlier version. Since the error message starts with (6), I believe that this is the problem.