[MDEV-32362] Backup of VIRTUALS colums is represented in restore Created: 2023-10-06  Updated: 2024-01-02

Status: Open
Project: MariaDB Server
Component/s: Backup
Affects Version/s: 10.6.14
Fix Version/s: None

Type: Bug Priority: Minor
Reporter: Kim Gert Nielsen Assignee: Unassigned
Resolution: Unresolved Votes: 0
Labels: None


 Description   

When doing a backup of a table with a virtual colum (GENERATED ALWAYS) the mysqldump tools fails to see its a generated column and adds it to the restore

example

CREATE TABLE example(
 
        firstname VARCHAR(255),
 
        lastname VARCHAR(255),
 
        fullname VARCHAR(512) AS (CONCAT(firstname, ' ', lastname)) VIRTUAL
 
);
 
 
 
insert into example(firstname, lastname) values('Donald', 'Duck');

then a mysqldump --opt generates

LOCK TABLES `example` WRITE;
 
/*!40000 ALTER TABLE `example` DISABLE KEYS */;
 
INSERT INTO `example` VALUES ('Donald','Duck','Donald Duck');
 
/*!40000 ALTER TABLE `example` ENABLE KEYS */;
 
UNLOCK TABLES;
 
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

It should actually ignore it since its generated.

The example above is very simple but we have seen issues with restoring due to this.

 Comments   
Comment by Roman [ 2023-12-18 ]

Been trying to track this down for days, as I am not too closely familiar with mariadb/mysql landscape.

I am migrating from on-prem MariaDB to RDS Aurora, which is MySQL-based, and ran into this exact issue. The dump contains these generated values. And then the loading of the dump fails with:

> ERROR 3105 (HY000) at line 2217: The value specified for generated column 'x' in table 'y' is not allowed.

It appears that MySQL had this exact bug fixed years ago. Here it is for reference:

https://github.com/mysql/mysql-server/commit/513552e30ba4cabe638cabb5a69cb1a82a96c7c5

Was released in 5.7.9 https://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-9.html

Comment by Kim Gert Nielsen [ 2024-01-02 ]

Yeah it seems the dump tool has gotten very little attention for many years.

Generated at Thu Feb 08 10:30:46 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.