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 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] Miguel Solorzano
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.