| 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: | |
| 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
[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 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);
