Description:
mysqldump produce wrong insert sql when dumping table with virtual json column as the primary key, leading '(' after 'VALUES' is missing and replaced by ',':
INSERT INTO `oss_split_request` (`order_json`) VALUES ,'{\"id\": 320760412, \"wares\": [{\"id\": 832}, {\"id\": 833}, {\"id\": 834}], \"shopId\": 1344, \"created\": 1464345359000, \"orderStatus\": 4}');
when I replace the primary key with AUTO_INCREMENT column, everything is OK.
How to repeat:
1. table structure :
mysql> show create table oss_split_request\G
*************************** 1. row ***************************
Table: oss_split_request
Create Table: CREATE TABLE `oss_split_request` (
`order_id` bigint(20) GENERATED ALWAYS AS (json_extract(`order_json`,'$.id')) STORED NOT NULL,
`order_json` json NOT NULL,
PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
2. there is only one row in the table:
mysql> select * from oss_split_request;
+-----------+---------------------------------------------------------------------------------------------------------------------------------+
| order_id | order_json |
+-----------+---------------------------------------------------------------------------------------------------------------------------------+
| 320760412 | {"id": 320760412, "wares": [{"id": 832}, {"id": 833}, {"id": 834}], "shopId": 1344, "created": 1464345359000, "orderStatus": 4} |
+-----------+---------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
3. dump table
/usr/local/mysql-5.7.11/bin/mysqldump -uroot -p -S /tmp/mysql.sock test oss_split_request
-- MySQL dump 10.13 Distrib 5.7.11, for Linux (x86_64)
--
-- Host: localhost Database: test
-- ------------------------------------------------------
-- Server version 5.7.11-log
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Table structure for table `oss_split_request`
--
DROP TABLE IF EXISTS `oss_split_request`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `oss_split_request` (
`order_id` bigint(20) GENERATED ALWAYS AS (json_extract(`order_json`,'$.id')) STORED NOT NULL,
`order_json` json NOT NULL,
PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `oss_split_request`
--
LOCK TABLES `oss_split_request` WRITE;
/*!40000 ALTER TABLE `oss_split_request` DISABLE KEYS */;
INSERT INTO `oss_split_request` (`order_json`) VALUES ,'{\"id\": 320760412, \"wares\": [{\"id\": 832}, {\"id\": 833}, {\"id\": 834}], \"shopId\": 1344, \"created\": 1464345359000, \"orderStatus\": 4}');
/*!40000 ALTER TABLE `oss_split_request` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
-- Dump completed on 2016-06-20 16:23:12
Suggested fix:
avoid to use the virtual json column as primary key before the bug fixed.
Description: mysqldump produce wrong insert sql when dumping table with virtual json column as the primary key, leading '(' after 'VALUES' is missing and replaced by ',': INSERT INTO `oss_split_request` (`order_json`) VALUES ,'{\"id\": 320760412, \"wares\": [{\"id\": 832}, {\"id\": 833}, {\"id\": 834}], \"shopId\": 1344, \"created\": 1464345359000, \"orderStatus\": 4}'); when I replace the primary key with AUTO_INCREMENT column, everything is OK. How to repeat: 1. table structure : mysql> show create table oss_split_request\G *************************** 1. row *************************** Table: oss_split_request Create Table: CREATE TABLE `oss_split_request` ( `order_id` bigint(20) GENERATED ALWAYS AS (json_extract(`order_json`,'$.id')) STORED NOT NULL, `order_json` json NOT NULL, PRIMARY KEY (`order_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec) 2. there is only one row in the table: mysql> select * from oss_split_request; +-----------+---------------------------------------------------------------------------------------------------------------------------------+ | order_id | order_json | +-----------+---------------------------------------------------------------------------------------------------------------------------------+ | 320760412 | {"id": 320760412, "wares": [{"id": 832}, {"id": 833}, {"id": 834}], "shopId": 1344, "created": 1464345359000, "orderStatus": 4} | +-----------+---------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) 3. dump table /usr/local/mysql-5.7.11/bin/mysqldump -uroot -p -S /tmp/mysql.sock test oss_split_request -- MySQL dump 10.13 Distrib 5.7.11, for Linux (x86_64) -- -- Host: localhost Database: test -- ------------------------------------------------------ -- Server version 5.7.11-log /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; -- -- Table structure for table `oss_split_request` -- DROP TABLE IF EXISTS `oss_split_request`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `oss_split_request` ( `order_id` bigint(20) GENERATED ALWAYS AS (json_extract(`order_json`,'$.id')) STORED NOT NULL, `order_json` json NOT NULL, PRIMARY KEY (`order_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `oss_split_request` -- LOCK TABLES `oss_split_request` WRITE; /*!40000 ALTER TABLE `oss_split_request` DISABLE KEYS */; INSERT INTO `oss_split_request` (`order_json`) VALUES ,'{\"id\": 320760412, \"wares\": [{\"id\": 832}, {\"id\": 833}, {\"id\": 834}], \"shopId\": 1344, \"created\": 1464345359000, \"orderStatus\": 4}'); /*!40000 ALTER TABLE `oss_split_request` ENABLE KEYS */; UNLOCK TABLES; /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; -- Dump completed on 2016-06-20 16:23:12 Suggested fix: avoid to use the virtual json column as primary key before the bug fixed.