MySQL Bugs: #110462: Mysqldump table with virtual column wrong syntax

Bug #110462	Mysqldump table with virtual column wrong syntax
Submitted:	22 Mar 2023 10:40	Modified:	26 Apr 2023 17:30
Reporter:	Pedro Ferreira	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: mysqldump Command-line Client	Severity:	S3 (Non-critical)
Version:	8.0.32	OS:	Ubuntu (22.04)
Assigned to:		CPU Architecture:	x86 (x86_64)
Tags:	mysqldump

Description:
Run the following queries:

CREATE TABLE x (c0 BLOB AS ('a') VIRTUAL, c1 INT);
INSERT INTO x(c1) VALUES (1);

Then do mysqldump on the table:

mysqldump -u ... --skip-password sys x

The insert in the output will give:

INSERT INTO `x` (`c1`) VALUES ,1);

The query is not syntactically correct.

The compilation parameters are the same as issue 108148:

-DWITH_DEBUG=1 -DWITH_ASAN=ON -DWITH_UBSAN=ON and boost library version 1.77

How to repeat:
Run the statements above

Hello Pedro Ferreira,

Thank you for the report and feedback.

regards,
Umesh

- 8.0.32

 bin/mysql -uroot -S /tmp/mysql.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 8.0.32 MySQL Community Server - GPL

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

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.01 sec)

mysql> use test
Database changed
mysql> CREATE TABLE x (c0 BLOB AS ('a') VIRTUAL, c1 INT);
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO x(c1) VALUES (1);
Query OK, 1 row affected (0.01 sec)

mysql> \q
Bye
[umshastr@support-cluster03:/export/home/tmp/ushastry/mysql-8.0.32]$ bin/mysqldump -uroot -S /tmp/mysql.sock --skip-password --databases test
mysqldump: [Warning] Using a password on the command line interface can be insecure.
-- MySQL dump 10.13  Distrib 8.0.32, for Linux (x86_64)
--
-- Host: localhost    Database: test
-- ------------------------------------------------------
-- Server version       8.0.32

/*!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 */;

--
-- Current Database: `test`
--

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `test` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */;

USE `test`;

--
-- Table structure for table `x`
--

DROP TABLE IF EXISTS `x`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `x` (
  `c0` blob GENERATED ALWAYS AS (_utf8mb4'a') VIRTUAL,
  `c1` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `x`
--

LOCK TABLES `x` WRITE;
/*!40000 ALTER TABLE `x` DISABLE KEYS */;
INSERT INTO `x` (`c1`) VALUES ,1);          -- <-----------------
/*!40000 ALTER TABLE `x` 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 2023-03-22 12:24:18

Posted by developer:
 
Fixed as of the upcoming MySQL Server 8.0.34 / 8.1.0 releases, and here's the proposed changelog entry from the documentation team:

The mysqldump utility could generate invalid INSERT statements for
generated columns.

Thank you for the bug report.