| Bug #90848 | tables with generated columns cannot be reliably exported/imported | ||
|---|---|---|---|
| Submitted: | 12 May 2018 17:26 | ||
| Reporter: | Shane Bester (Platinum Quality Contributor) | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: DML | Severity: | S3 (Non-critical) |
| Version: | 5.7, 8.0, 5.7.32, 8.0.22 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[12 May 2018 17:26]
MySQL Verification Team
This interferes plenty with my ability to create dumps of database and make testcases...
[12 May 2018 17:31]
MySQL Verification Team
How this affects me: i create database dumps in order to make testcases for bugs. For the purpose of reducing a testcase, i like to use mysqldump --extended-insert=0 but then the rows cannot be inserted due to this bug. mysqldump --extended-insert=1 creates an importable file but it cannot be reduced by removing lines/rows.
[24 Nov 2020 6:41]
MySQL Verification Team
see https://bugs.mysql.com/bug.php?id=101727
[24 Nov 2020 6:44]
MySQL Verification Team
Bug #101727 marked as duplicate of this one
[24 Nov 2020 7:03]
wj huang
Hello, I want to know what's the expected behavior. It's expected to return a warning instead of an error?
Description: The bug is that single-row inserts fail but multi-row inserts succeed, with warning. See testcase output: mysql> create table t ( -> a int, -> b int generated always as (null) virtual not null -> ) engine=innodb; Query OK, 0 rows affected (0.06 sec) mysql> insert into t(a) values (0); ERROR 1048 (23000): Column 'b' cannot be null mysql> show warnings; +-------+------+---------------------------+ | Level | Code | Message | +-------+------+---------------------------+ | Error | 1048 | Column 'b' cannot be null | +-------+------+---------------------------+ 1 row in set (0.00 sec) mysql> insert into t(a) values (0),(0),(0); Query OK, 3 rows affected, 1 warning (0.00 sec) Records: 3 Duplicates: 0 Warnings: 1 mysql> show warnings; +---------+------+---------------------------+ | Level | Code | Message | +---------+------+---------------------------+ | Warning | 1048 | Column 'b' cannot be null | +---------+------+---------------------------+ 1 row in set (0.00 sec) How to repeat: #Execute this in mysql client : set sql_mode=''; drop table if exists t; create table t ( a int, b int generated always as (null) virtual not null ) engine=innodb; insert into t(a) values (0); -- fails insert into t(a) values (0),(0),(0); -- works Suggested fix: 1. Should this table DDL be allowed in the first place? Many more examples... 2. Why multi-row inserts the data but single-row doesn't??