Bug #80790 mysqldump creates invalid dumpfile when generated columns exist
Submitted: 18 Mar 2016 13:16 Modified: 18 Mar 2016 21:28
Reporter: Shane Bester (Platinum Quality Contributor) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: mysqldump Command-line Client Severity:S2 (Serious)
Version:5.7.11, 5.7.29, 8.0.19 OS:Any
Assigned to: CPU Architecture:Any

[18 Mar 2016 13:16] Shane Bester
Description:
mysqldump creates an invalid insert statement when table contains generated column first:

INSERT INTO `t` (`b`) VALUES (,1);

How to repeat:
drop table if exists t;
create table t (a int generated always as (1) virtual,b int)engine=innodb;
insert into t(b) values(1);

Now check:
  mysqldump test t
[18 Mar 2016 21:28] MySQL Verification Team
Thank you for the bug report.

C:\dbs>5.7\bin\mysqldump -uroot -p --port=3570 test t
Enter password: ******
-- MySQL dump 10.13  Distrib 5.7.13, for Win64 (x86_64)
--
-- Host: localhost    Database: test
-- ------------------------------------------------------
-- Server version       5.7.13

/*!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 `t`
--

DROP TABLE IF EXISTS `t`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `t` (
  `a` int(11) GENERATED ALWAYS AS (1) VIRTUAL,
  `b` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `t`
--

LOCK TABLES `t` WRITE;
/*!40000 ALTER TABLE `t` DISABLE KEYS */;
INSERT INTO `t` (`b`) VALUES ,1);
/*!40000 ALTER TABLE `t` 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-03-18 18:26:56

C:\dbs>
[9 Jul 2016 10:45] MySQL Verification Team
also:
 http://bugs.mysql.com/bug.php?id=81926
[6 Aug 2016 13:08] Larysa Sherepa
I tried this patch and it looks working now:

LOCK TABLES `t` WRITE;
/*!40000 ALTER TABLE `t` DISABLE KEYS */;
INSERT INTO `t` (`b`) VALUES (1);
/*!40000 ALTER TABLE `t` ENABLE KEYS */;
UNLOCK TABLES;

--- mysql-5.7.14-old/client/mysqldump.c	2016-07-12 13:55:26.000000000 +0200
+++ mysql-5.7.14/client/mysqldump.c	2016-08-06 14:52:35.000000000 +0200
@@ -3817,8 +3817,12 @@
                       "Not enough fields from table %s! Aborting.\n",
                       result_table);
 
-        if (!real_columns[i])
-          continue;
+          if (!real_columns[i]) {
+              if (extended_insert && !opt_xml && i == 0)
+                      dynstr_set_checked(&extended_row,"(");
+              continue;
+          }
+          
         /*
            63 is my_charset_bin. If charsetnr is not 63,
            we have not a BLOB but a TEXT column.
@@ -3838,7 +3842,7 @@
         {
           if (i == 0)
             dynstr_set_checked(&extended_row,"(");
-          else
+          else if (!real_columns[i])
             dynstr_append_checked(&extended_row,",");
 
           if (row[i])
[6 Aug 2016 13:12] Larysa Sherepa
previous patch has a typo, this is fixed

                       "Not enough fields from table %s! Aborting.\n",
                       result_table);
 
-        if (!real_columns[i])
-          continue;
+          if (!real_columns[i]) {
+              if (extended_insert && !opt_xml && i == 0)
+                      dynstr_set_checked(&extended_row,"(");
+              continue;
+          }
+          
         /*
            63 is my_charset_bin. If charsetnr is not 63,
            we have not a BLOB but a TEXT column.
@@ -3838,7 +3842,7 @@
         {
           if (i == 0)
             dynstr_set_checked(&extended_row,"(");
-          else
+          else if (real_columns[i-1])
             dynstr_append_checked(&extended_row,",");
 
           if (row[i])
[23 Jul 2018 13:50] Ruud H.G. van Tol
The default should be that MySQL ignores (maybe with a warning) any value in the dump to be stored into a generated column.

So the dump can contain values for generated columns, but they get ignored at insert.
[23 Jul 2018 14:13] Peter Laursen
@Ruud

There was a (heated) discussion about this before:
http://blog.sqlyog.com/beware-virtual-columns-may-render-backups-unusable/

MariaDB does exactly as you suggest - ignores INSERTs to virtual columns. I dont remember though if it also does for UPDATES and if there are warnings or not.
[22 Feb 17:15] Christian Roser
This bug also appears in the most recent versions (verified on 5.7.28 / 5.7.29).

Additionally to creating invalid dump files which cannot be restored it seems to multiply the rows read by mysqldump.

I have a database with one table using generated stored columns. The table is only 82M but when I dump the table the mysqldump only stops when the target filesystem runs out of diskspace. 

I search for a specific expression in select on the table:
# mysql -BN -e 'select * from <able>' <database> | grep -o 'Ball Finial' | wc -l
1064

# date; mysqldump <database> <table> > dumptest;
Sat 22 Feb 2020 06:11:17 PM CET
^C
# date
Sat 22 Feb 2020 06:11:29 PM CET
# grep -o 'Ball Finial' dumptest  | wc -l
48888
# du -sh dumptest
2.0G	dumptest

The dump also shows the syntax errors:
VALUES ,'5000','ZF01','2019-01-03...
[24 Feb 5:39] MySQL Verification Team
Bug #98717 marked as duplicate of this one
[24 Feb 5:42] MySQL Verification Team
-- 8.0.19 - affected

DROP SCHEMA IF EXISTS test;
CREATE SCHEMA test;
use test

CREATE TABLE `t1` (
  `gen_id` varchar(16) GENERATED ALWAYS AS (concat(`col2`,`col3`)) STORED NOT NULL,
  `col2` varchar(10) NOT NULL,
  `col3` BIGINT DEFAULT NULL,
  PRIMARY KEY (`gen_id`)
); 

insert into t1 (col2, col3) values(LEFT(UUID(), 8), 08154711);
insert into t1 (col2, col3) values(LEFT(UUID(), 8), 08154711);
insert into t1 (col2, col3) values(LEFT(UUID(), 8), 08154711);
insert into t1 (col2, col3) values(LEFT(UUID(), 8), 08154711);
insert into t1 (col2, col3) values(LEFT(UUID(), 8), 08154711);

 bin/mysqldump -uroot -S /tmp/mysql_ushastry.sock test t1
-- MySQL dump 10.13  Distrib 8.0.19, for linux-glibc2.12 (x86_64)
--
-- Host: localhost    Database: test
-- ------------------------------------------------------
-- Server version       8.0.19

/*!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 */;
/*!50503 SET NAMES utf8mb4 */;
/*!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 `t1`
--

DROP TABLE IF EXISTS `t1`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `t1` (
  `gen_id` varchar(16) GENERATED ALWAYS AS (concat(`col2`,`col3`)) STORED NOT NULL,
  `col2` varchar(10) NOT NULL,
  `col3` bigint DEFAULT NULL,
  PRIMARY KEY (`gen_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `t1`
--

LOCK TABLES `t1` WRITE;
/*!40000 ALTER TABLE `t1` DISABLE KEYS */;
INSERT INTO `t1` (`col2`, `col3`) VALUES ,'38632dde',8154711),,'38632dde',8154711),'386373d1',8154711),,'38632dde',8154711),'386373d1',8154711),'3863b12d',8154711),,'38632dde',8154711),'386373d1',8154711),'3863b12d',8154711),'3863efb9',8154711),,'38632dde',8154711),'386373d1',8154711),'3863b12d',8154711),'3863efb9',8154711),'3898e495',8154711);
/*!40000 ALTER TABLE `t1` 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 2020-02-24  6:41:02
[24 Feb 6:34] Christian Roser
Growth of the dumpfile is exponentially with the amount of rows in the table:

10 rows in table:       55 'rows' in dump
100 rows in table:     5050 rows in dump
1000 rows in table:   500500 rows in dump
10000 rows in table: 50005000 rows in dump