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] Shane Bester
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 2020 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 2020 5:39] MySQL Verification Team
Bug #98717 marked as duplicate of this one
[24 Feb 2020 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 2020 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
[7 Oct 2021 20:59] Patrick Dunham
8.0.23 is still affected by this. For anyone else who runs into this, a simple workaround is to ensure that the first column is not generated.

For example, on 8.0.23 given the following snippet, `test_genfirst` creates an unusable insert statement when being backed up - while `test_genlast` works perfectly fine.

CREATE DATABASE test;
USE test;
CREATE TABLE `test_genfirst` (
    `id` VARCHAR(20) GENERATED ALWAYS AS (
        CONCAT_WS(
            '-',
            DATE_FORMAT(`date_packaged`, '%Y%m%d'),
            LPAD(`num`, 2, '0')
        )
    ) STORED,
    `date_packaged` DATE,
    `num` INT,
    PRIMARY KEY (`id`)
);

CREATE TABLE `test_genlast` (
    `date_packaged` DATE,
    `num` INT,
    `id` VARCHAR(20) GENERATED ALWAYS AS (
        CONCAT_WS(
            '-',
            DATE_FORMAT(`date_packaged`, '%Y%m%d'),
            LPAD(`num`, 2, '0')
        )
    ) STORED,
    PRIMARY KEY (`id`)
);

INSERT INTO `test_genfirst` (`date_packaged`, `num`) VALUES
    ('2021-10-01', 1),
    ('2021-10-01', 2),
    ('2021-10-01', 3),
    ('2021-10-01', 4),
    ('2021-10-01', 5),
    ('2021-10-02', 1),
    ('2021-10-02', 2),
    ('2021-10-03', 1),
    ('2021-10-04', 1),
    ('2021-10-05', 1);

INSERT INTO `test_genlast` (`date_packaged`, `num`) VALUES
    ('2021-10-01', 1),
    ('2021-10-01', 2),
    ('2021-10-01', 3),
    ('2021-10-01', 4),
    ('2021-10-01', 5),
    ('2021-10-02', 1),
    ('2021-10-02', 2),
    ('2021-10-03', 1),
    ('2021-10-04', 1),
    ('2021-10-05', 1);

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.064 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.