Background

MySQL 5.7 provides a "Generated Column" feature, which can get a new column of data via function calculation. We can raise the query efficiency by adding an index to the virtual column or partition the table using the virtual column.

Failure Phenomenon

When importing or exporting the table with a virtual column using the "mysqldump" command of low version, there is an error report:

ERROR 3105 (HY000) at line 38: The value specified for the generated column 'col1' in table 'tb_01' is not allowed.

Analysis of cause

In the mysqldump version lower than 5.7.9, the system exports the data of the virtual column by mistake. However, it cannot insert the virtual column. Thus, the error above takes place.

Changes in MySQL 5.7.9：mysqldump used incorrect syntax for generated column definitions. (Bug #20769542)

Solution

Export the data from the table containing Generated Column by using "mysqldump" tool above Version 5.7.9.

Case review

1. Test table and data

 CREATE TABLE triangle (
  sidea double DEFAULT NULL,
  sideb double DEFAULT NULL,
  sidec double GENERATED ALWAYS AS (sqrt(((sidea * sidea) + (sideb * sideb)))) VIRTUAL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into triangle(sidea,sideb)values(1,1),(3,4),(6,8);

2. Export the data by using different versions of "mysqldump".

• Example: export data using "mysqldump" of low version.

LOCK TABLES `triangle` WRITE;
/*!40000 ALTER TABLE `triangle` DISABLE KEYS */;
INSERT INTO `triangle` VALUES (1,2,2.23606797749979),(3,4,5),(2,9,9.219544457292887);
/*!40000 ALTER TABLE `triangle` ENABLE KEYS */;
UNLOCK TABLES;

• Example: export data using "mysqldump" of low version.

LOCK TABLES `triangle` WRITE;
/*!40000 ALTER TABLE `triangle` DISABLE KEYS */;
INSERT INTO `triangle` (`sidea`, `sideb`) VALUES (1,2),(3,4),(2,9);
/*!40000 ALTER TABLE `triangle` ENABLE KEYS */;
UNLOCK TABLES;

Conclusions & Suggestions

Recommend to align the "mysqldump" to the "MySQL Server" version. Thus, it is possible to avoid compatibility problems resulting from new features like "Generated Column" in the use of lower versions.