| Bug #79148 | Virtual columns makes mysqldump unusable. | ||
|---|---|---|---|
| Submitted: | 6 Nov 2015 5:46 | Modified: | 9 Nov 2015 6:59 |
| Reporter: | Peter Laursen (Basic Quality Contributor) | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | MySQL Server: mysqldump Command-line Client | Severity: | S1 (Critical) |
| Version: | 5.7.8 | OS: | Windows (probably any) |
| Assigned to: | CPU Architecture: | Any | |
[6 Nov 2015 5:46]
Peter Laursen
[6 Nov 2015 5:52]
Peter Laursen
correction: The INSERT statement about is what MySQL generates >> The INSERT statement about is what mysqldump generates
[6 Nov 2015 5:54]
Peter Laursen
One more correction: I also find any option >> I also do not find any option
[6 Nov 2015 6:41]
Peter Laursen
MariaDB got it right:
SELECT VERSION(); -- 10.1.8-MariaDB
CREATE DATABASE vctest;
USE vctest;
CREATE TABLE `t1`(
`id` INT NOT NULL,
`id3` INT AS ( id*3 ) VIRTUAL
);
INSERT INTO `t1` VALUES (1,3);
SHOW WARNINGS;
-- 1906 The value specified for computed column 'id3' in table 't1' ignored
SELECT * FROM t1;
/*
id id3
------ --------
1 3
*/
[6 Nov 2015 8:43]
Peter Laursen
Changing OS from "any" to "Windows - probably any"). I only tried with the 64 bit .msi installer on Windows, actually.
[6 Nov 2015 8:52]
MySQL Verification Team
Hello Peter, Thank you for the report. This is duplicate of internally logged Bug #20769542, which is fixed in 5.7.9 and change log details are: Noted in 5.7.9, 5.8.0 changelogs. mysqldump used incorrect syntax for generated column definitions. See, http://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-9.html Thanks, Umesh
[6 Nov 2015 8:53]
MySQL Verification Team
// 5.7.9 GA
[umshastr@hod03]/export/umesh/server/binaries/mysql-5.7.9: bin/mysql -uroot -S /tmp/mysql_ushastry.sock
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.9 MySQL Community Server (GPL)
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> create database test;
Query OK, 1 row affected (0.00 sec)
mysql> use test;
Database changed
mysql> CREATE TABLE `t1`(
-> `id` INT NOT NULL,
-> `id3` INT AS ( id*3 ) VIRTUAL
-> );
Query OK, 0 rows affected (0.01 sec)
mysql> insert into t1(id) values(1);
Query OK, 1 row affected (0.02 sec)
mysql> \q
Bye
[umshastr@hod03]/export/umesh/server/binaries/mysql-5.7.9: bin/mysqldump -uroot -S /tmp/mysql_ushastry.sock --databases test
-- MySQL dump 10.13 Distrib 5.7.9, for linux-glibc2.5 (x86_64)
--
-- Host: localhost Database: test
-- ------------------------------------------------------
-- Server version 5.7.9
/*!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 */;
--
-- Current Database: `test`
--
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `test` /*!40100 DEFAULT CHARACTER SET latin1 */;
USE `test`;
--
-- Table structure for table `t1`
--
DROP TABLE IF EXISTS `t1`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `t1` (
`id` int(11) NOT NULL,
`id3` int(11) GENERATED ALWAYS AS (id*3) VIRTUAL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!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` (`id`) VALUES (1);
/*!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 2015-11-06 9:43:55
[umshastr@hod03]/export/umesh/server/binaries/mysql-5.7.9: bin/mysqldump -uroot -S /tmp/mysql_ushastry.sock --databases test > t.sql
[umshastr@hod03]/export/umesh/server/binaries/mysql-5.7.9: bin/mysql -uroot -S /tmp/mysql_ushastry.sock < t.sql
[umshastr@hod03]/export/umesh/server/binaries/mysql-5.7.9: bin/mysql -uroot -S /tmp/mysql_ushastry.sock test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.7.9 MySQL Community Server (GPL)
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> select * from t1;
+----+------+
| id | id3 |
+----+------+
| 1 | 3 |
+----+------+
1 row in set (0.00 sec)
[6 Nov 2015 8:58]
Peter Laursen
OK .. I tried with 5.7.8 and the 'mysqldump' distributed with it. Here the INSERT listed both columns. We should try again with 5.7.9 then.
[6 Nov 2015 9:43]
Peter Laursen
OK .. in MySQL 5.7.9 INSERTs are like INSERT INTO table (columnslist_omitting_virtual_columns) VALUES (valueslist_omitting_computed values) in 5.7.8 it was just like in all previous versions INSERT INTO table VALUES (valueslist_for_all_columns_including_virtual) -- no columnslist That is of course a solution for 'mysqldump' itself, but there are a myriad of tools (phpMyAdmin, GUI tools, specialized applications etc.) generating similar SQL-dumps and they will now all not work if tables have virtual columns. Did you consider this? Maybe you deliberately wanted to break all other backup tools than your own? I would really have preferred the same solution as in MariaDB - ie. ignoring INSERTs to virtual columns server-side rather than a client-side solution. And the one solution does not exclude the other, BTW. So this is still a valid feature request, at least. Please reopen.
[6 Nov 2015 19:29]
Roy Lyseng
We have implemented the SQL standard in this area: the only value allowed for a generated column is DEFAULT, like: CREATE TABLE t1(a INTEGER, b INTEGER AS (a*3)); INSERT INTO t1 VALUES(5, DEFAULT); or INSERT INTO t1(a) VALUES (5); or INSERT INTO t1(a, b) VALUES(5, DEFAULT);
[9 Nov 2015 6:08]
Peter Laursen
http://blog.webyog.com/2015/11/09/beware-virtual-columns-may-render-backups-unusable
[9 Nov 2015 6:59]
Peter Laursen
For clarity: Even MariaDB does not insert anything at all to the virtual column(s). It ignores/strrips such values from an INSERT statement. So isn't also MariaDB's implementation in accordance with SQL standards? Please reopen this report as a feature request!
[12 Nov 2015 7:26]
Simon Mudd
The mysqldump page https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html is far from clear about behaviour here and how it has changed with virtual/generated columns. There's a reference to using 5.7.9 but no explanation of what's dumped (no examples) and how the behaviour changes. If you look up the text 'virtual' there's no reference to it either. So please make the page more complete, provide some examples of virtual column usage. Also provide an option to generate the content of a virtual column for systems that may not support that syntax/behaviour. This would not be used for dumping to 5.7+ but may be useful for doing an initial import into an older version of MySQL should that be necessary. For me mysqldump on 5.7.7 shows: ... DROP TABLE IF EXISTS `t1`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `t1` ( `id` int(11) NOT NULL, `id3` int(11) GENERATED ALWAYS AS (id*3) VIRTUAL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; /*!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` VALUES (1,3),(2,6),(3,9); <<========== /*!40000 ALTER TABLE `t1` ENABLE KEYS */; UNLOCK TABLES; /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; If 5.7.9 is fixed that's good. I'll check and see shortly. Also the text "If you have tables that contain generated columns, use the mysqldump utility provided with MySQL 5.7.9 or higher to create your dump files. The mysqldump utility provided in earlier releases uses incorrect syntax for generated column definitions (Bug #20769542). You can use the INFORMATION_SCHEMA.COLUMNS table to identify tables with generated columns." references bugs which are not publicly visible so the reference is impossible to follow up if you don't have access to the internal Oracle systems. It does not mention that the dump will not dump virtual columns so it's really not clear what behaviour to expect. For the inserts above I'd expect the insert line to be something like: INSERT INTO `t1` (`id`) VALUES (1),(2),(3); That is only mention the non-virtual columns and only provide the non-virtual column values. Doing anything else by default means you duplicate data unnecessarily and that is far from efficient.
[26 Apr 2019 14:42]
Paul Cre
If this is the case then there should be a warning on the relevant MySQL documentation website because this is pretty crucial informtation.
[26 Apr 2019 15:53]
MySQL Verification Team
Paul, no warnings are needed in the docs. This bug was fixed years ago. If 5.7.26 or 8.0.16 mysqldump still generates an unloadable dumpfile, kindly open a new bug report.
