Description:
Here is sample table:
CREATE TABLE `sbtest1` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`k` int(10) unsigned NOT NULL DEFAULT '0',
`c` char(120) NOT NULL DEFAULT '',
`pad` char(60) NOT NULL DEFAULT '',
`pad_c` int(11) GENERATED ALWAYS AS ((char_length(`pad`) + char_length(`c`))) STORED,
PRIMARY KEY (`id`),
KEY `k_1` (`k`)
) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB AUTO_INCREMENT=1731923 DEFAULT CHARSET=latin1 COMPRESSION='none' ENCRYPTION='N'
mysql> create table sbtest2 like sbtest1;
Query OK, 0 rows affected (0,03 sec)
CREATE TABLE `sbtest2` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`k` int(10) unsigned NOT NULL DEFAULT '0',
`c` char(120) NOT NULL DEFAULT '',
`pad` char(60) NOT NULL DEFAULT '',
`pad_c` int(11) GENERATED ALWAYS AS ((char_length(`pad`) + char_length(`c`))) STORED,
PRIMARY KEY (`id`),
KEY `k_1` (`k`)
) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=latin1 COMPRESSION='none' ENCRYPTION='N'
mysql> insert into sbtest2 select * from sbtest1;
ERROR 3105 (HY000): The value specified for generated column 'pad_c' in table 'sbtest2' is not allowed.
I saw same error in -> https://bugs.mysql.com/bug.php?id=78082
How to repeat:
See description
Suggested fix:
The one option is to list all columns and exclude generated:
mysql> insert into sbtest2(`id`, `k`, `c`, `pad`) select `id`, `k`, `c`, `pad` from sbtest1;
Query OK, 1731922 rows affected (22,18 sec)
Records: 1731922 Duplicates: 0 Warnings: 0
But it is seems to be troublesome.
