Description:
Generated columns in the generated SQL from mysqldump are not contained in version specific comments like most other version dependent features.
How to repeat:
== On MySQL 5.7.14
mysql> create table t1 (id serial, ip varbinary(16), ip_txt varchar(255) as (inet6_ntoa(ip)));
Query OK, 0 rows affected (0.05 sec)
mysql> insert into t1(ip) values(inet6_aton('::1'));
Query OK, 1 row affected (0.02 sec)
mysql> insert into t1(ip) values(inet6_aton('127.0.0.1'));
Query OK, 1 row affected (0.01 sec)
mysql [localhost] {msandbox} (test) > select id,ip_txt from t1;
+----+-----------+
| id | ip_txt |
+----+-----------+
| 1 | ::1 |
| 2 | 127.0.0.1 |
+----+-----------+
2 rows in set (0.00 sec)
$ mysqldump test t1 > /tmp/t1.sql
== On MySQL 5.6.25
$ mysql test < /tmp/t1.sql
ERROR 1064 (42000) at line 25: 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 'GENERATED ALWAYS AS (inet6_ntoa(`ip`)) VIRTUAL,
UNIQUE KEY `id` (`id`)
) ENGIN' at line 4
Suggested fix:
Change from:
22 DROP TABLE IF EXISTS `t1`;
23 /*!40101 SET @saved_cs_client = @@character_set_client */;
24 /*!40101 SET character_set_client = utf8 */;
25 CREATE TABLE `t1` (
26 `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
27 `ip` varbinary(16) DEFAULT NULL,
28 `ip_txt` varchar(255) GENERATED ALWAYS AS (inet6_ntoa(`ip`)) VIRTUAL,
29 UNIQUE KEY `id` (`id`)
30 ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;
31 /*!40101 SET character_set_client = @saved_cs_client */;
To:
22 DROP TABLE IF EXISTS `t1`;
23 /*!40101 SET @saved_cs_client = @@character_set_client */;
24 /*!40101 SET character_set_client = utf8 */;
25 CREATE TABLE `t1` (
26 `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
27 `ip` varbinary(16) DEFAULT NULL,
28 `ip_txt` varchar(255) /*!50706 GENERATED ALWAYS AS (inet6_ntoa(`ip`)) VIRTUAL */,
29 UNIQUE KEY `id` (`id`)
30 ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;
31 /*!40101 SET character_set_client = @saved_cs_client */;
Description: Generated columns in the generated SQL from mysqldump are not contained in version specific comments like most other version dependent features. How to repeat: == On MySQL 5.7.14 mysql> create table t1 (id serial, ip varbinary(16), ip_txt varchar(255) as (inet6_ntoa(ip))); Query OK, 0 rows affected (0.05 sec) mysql> insert into t1(ip) values(inet6_aton('::1')); Query OK, 1 row affected (0.02 sec) mysql> insert into t1(ip) values(inet6_aton('127.0.0.1')); Query OK, 1 row affected (0.01 sec) mysql [localhost] {msandbox} (test) > select id,ip_txt from t1; +----+-----------+ | id | ip_txt | +----+-----------+ | 1 | ::1 | | 2 | 127.0.0.1 | +----+-----------+ 2 rows in set (0.00 sec) $ mysqldump test t1 > /tmp/t1.sql == On MySQL 5.6.25 $ mysql test < /tmp/t1.sql ERROR 1064 (42000) at line 25: 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 'GENERATED ALWAYS AS (inet6_ntoa(`ip`)) VIRTUAL, UNIQUE KEY `id` (`id`) ) ENGIN' at line 4 Suggested fix: Change from: 22 DROP TABLE IF EXISTS `t1`; 23 /*!40101 SET @saved_cs_client = @@character_set_client */; 24 /*!40101 SET character_set_client = utf8 */; 25 CREATE TABLE `t1` ( 26 `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, 27 `ip` varbinary(16) DEFAULT NULL, 28 `ip_txt` varchar(255) GENERATED ALWAYS AS (inet6_ntoa(`ip`)) VIRTUAL, 29 UNIQUE KEY `id` (`id`) 30 ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1; 31 /*!40101 SET character_set_client = @saved_cs_client */; To: 22 DROP TABLE IF EXISTS `t1`; 23 /*!40101 SET @saved_cs_client = @@character_set_client */; 24 /*!40101 SET character_set_client = utf8 */; 25 CREATE TABLE `t1` ( 26 `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, 27 `ip` varbinary(16) DEFAULT NULL, 28 `ip_txt` varchar(255) /*!50706 GENERATED ALWAYS AS (inet6_ntoa(`ip`)) VIRTUAL */, 29 UNIQUE KEY `id` (`id`) 30 ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1; 31 /*!40101 SET character_set_client = @saved_cs_client */;