Bug #82859 Generated columns should have version specific comments in dump
Submitted: 5 Sep 2016 8:25 Modified: 5 Sep 2016 16:16
Reporter: Daniël van Eeden (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: mysqldump Command-line Client Severity:S3 (Non-critical)
Version:5.7.14 OS:Any
Assigned to: CPU Architecture:Any
Tags: compatibility, generated columns

[5 Sep 2016 8:25] Daniël van Eeden
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 */;
[5 Sep 2016 16:16] Miguel Solorzano
Thank you for the bug report.