Description:
If SQL_MODE contains ANSI_QUOTES, output of mysqlpump contains quoted values that cause an error when importing the dump.
How to repeat:
mysql> SET GLOBAL SQL_MODE="ANSI_QUOTES";
mysql> CREATE DATABASE test;
mysql> USE test
mysql> CREATE TABLE t1 (id int primary key);
mysql> INSERT INTO t1 VALUES (1),(2),(3);
$ mysqlpump test > test.sql
Dump progress: 1/1 tables, 0/0 rows
mysqlpump: [WARNING] (3719) 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
Dump completed in 720
$ mysql < test.sql
ERROR 1064 (42000) at line 16: 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 '"test" /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!8' at line 1
Importing the dump fails because database and table names, as well as field names are enclosed in double quotes:
CREATE DATABASE /*!32312 IF NOT EXISTS*/ "test" /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */;
CREATE TABLE `test`."t1" (
"id" int NOT NULL,
PRIMARY KEY ("id")
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
Interestingly the INSERT INTO statement is correct:
INSERT INTO `test`.`t1` VALUES (1),(2),(3);
Suggested fix:
Check SQL_MODE and handle quotes correctly.
Description: If SQL_MODE contains ANSI_QUOTES, output of mysqlpump contains quoted values that cause an error when importing the dump. How to repeat: mysql> SET GLOBAL SQL_MODE="ANSI_QUOTES"; mysql> CREATE DATABASE test; mysql> USE test mysql> CREATE TABLE t1 (id int primary key); mysql> INSERT INTO t1 VALUES (1),(2),(3); $ mysqlpump test > test.sql Dump progress: 1/1 tables, 0/0 rows mysqlpump: [WARNING] (3719) 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. Dump completed in 720 $ mysql < test.sql ERROR 1064 (42000) at line 16: 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 '"test" /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!8' at line 1 Importing the dump fails because database and table names, as well as field names are enclosed in double quotes: CREATE DATABASE /*!32312 IF NOT EXISTS*/ "test" /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */; CREATE TABLE `test`."t1" ( "id" int NOT NULL, PRIMARY KEY ("id") ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci Interestingly the INSERT INTO statement is correct: INSERT INTO `test`.`t1` VALUES (1),(2),(3); Suggested fix: Check SQL_MODE and handle quotes correctly.