MySQL Bugs: #94797: Auto_increment values may decrease when adding a generated column

Bug #94797	Auto_increment values may decrease when adding a generated column
Submitted:	27 Mar 2019 10:29	Modified:	27 Mar 2019 11:41
Reporter:	Fengchun Hua	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S2 (Serious)
Version:	5.7.23, 5.7.25	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Auto_increment will decrease after add generated column.

How to repeat:
try this with default config:

create table test(id int not null auto_increment primary key, c2 int);
insert into test (c2) values(1);
insert into test (c2) values(2);
insert into test (c2) select c2 from test;
show create table test;

Now auto_increment values = 6.

alter table test add column ge int as (c2 + 1);
show create table test;
Now auto_increment becomes 5.

Suggested fix:
I found that when table is altered, current auto_increment value will set to new table in handle0alter.cc:commit_inplace_alter_table. But when alter is add generated column, no values set to new table.
I think this auto_increment value should be set even alter is adding generated column.

Hello Fengchun Hua,

Thank you for the report and test case.
Verified as described with 5.7.25 build(8.0.15 not affected).

thanks,
Umesh

- 5.7.25

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

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

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 if not exists test;
use test;
Query OK, 1 row affected (0.00 sec)

mysql> use test;
create table test(id int not null auto_increment primary key, c2 int);
insert into test (c2) values(1);
insert into test (c2) values(2);
Database changed
mysql> create table test(id int not null auto_increment primary key, c2 int);
insert into test (c2) select c2 from test;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into test (c2) values(1);
show create table test\G
select * from Information_schema.tables where table_name = "test" \G
alter table test add column ge int as (c2 + 1);
show create table test\G
select * from Information_schema.tables where table_name = "test" \G
Query OK, 1 row affected (0.01 sec)

mysql> insert into test (c2) values(2);
Query OK, 1 row affected (0.00 sec)

mysql> insert into test (c2) select c2 from test;
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> show create table test\G
*************************** 1. row ***************************
       Table: test
Create Table: CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `c2` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> select * from Information_schema.tables where table_name = "test" \G
*************************** 1. row ***************************
  TABLE_CATALOG: def
   TABLE_SCHEMA: test
     TABLE_NAME: test
     TABLE_TYPE: BASE TABLE
         ENGINE: InnoDB
        VERSION: 10
     ROW_FORMAT: Dynamic
     TABLE_ROWS: 4
 AVG_ROW_LENGTH: 4096
    DATA_LENGTH: 16384
MAX_DATA_LENGTH: 0
   INDEX_LENGTH: 0
      DATA_FREE: 0
 AUTO_INCREMENT: 6
    CREATE_TIME: 2019-03-27 11:52:19
    UPDATE_TIME: 2019-03-27 11:52:19
     CHECK_TIME: NULL
TABLE_COLLATION: latin1_swedish_ci
       CHECKSUM: NULL
 CREATE_OPTIONS:
  TABLE_COMMENT:
1 row in set (0.02 sec)

mysql> alter table test add column ge int as (c2 + 1);
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table test\G
*************************** 1. row ***************************
       Table: test
Create Table: CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `c2` int(11) DEFAULT NULL,
  `ge` int(11) GENERATED ALWAYS AS ((`c2` + 1)) VIRTUAL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> select * from Information_schema.tables where table_name = "test" \G
*************************** 1. row ***************************
  TABLE_CATALOG: def
   TABLE_SCHEMA: test
     TABLE_NAME: test
     TABLE_TYPE: BASE TABLE
         ENGINE: InnoDB
        VERSION: 10
     ROW_FORMAT: Dynamic
     TABLE_ROWS: 4
 AVG_ROW_LENGTH: 4096
    DATA_LENGTH: 16384
MAX_DATA_LENGTH: 0
   INDEX_LENGTH: 0
      DATA_FREE: 0
 AUTO_INCREMENT: 5
    CREATE_TIME: 2019-03-27 11:52:19
    UPDATE_TIME: NULL
     CHECK_TIME: NULL
TABLE_COLLATION: latin1_swedish_ci
       CHECKSUM: NULL
 CREATE_OPTIONS:
  TABLE_COMMENT:
1 row in set (0.00 sec)

- 8.0.15

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

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

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> set session information_schema_stats_expiry=0;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'information_schema_stats_expiry';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| information_schema_stats_expiry | 0     |
+---------------------------------+-------+
1 row in set (0.01 sec)

mysql> create database if not exists test;
Query OK, 1 row affected (0.00 sec)

mysql> use test;
Database changed
mysql> create table test(id int not null auto_increment primary key, c2 int);
insert into test (c2) values(1);
insert into test (c2) values(2);
insert into test (c2) select c2 from test;
show create table test\G
select * from Information_schema.tables where table_name = "test" \G
alter table test add column ge int as (c2 + 1);
show create table test\G
select * from Information_schema.tables where table_name = "test" \GQuery OK, 0 rows affected (0.01 sec)

mysql> insert into test (c2) values(1);
Query OK, 1 row affected (0.01 sec)

mysql> insert into test (c2) values(2);
Query OK, 1 row affected (0.00 sec)

mysql> insert into test (c2) select c2 from test;
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> show create table test\G
*************************** 1. row ***************************
       Table: test
Create Table: CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `c2` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.01 sec)

mysql> select * from Information_schema.tables where table_name = "test" \G
*************************** 1. row ***************************
  TABLE_CATALOG: def
   TABLE_SCHEMA: test
     TABLE_NAME: test
     TABLE_TYPE: BASE TABLE
         ENGINE: InnoDB
        VERSION: 10
     ROW_FORMAT: Dynamic
     TABLE_ROWS: 4
 AVG_ROW_LENGTH: 4096
    DATA_LENGTH: 16384
MAX_DATA_LENGTH: 0
   INDEX_LENGTH: 0
      DATA_FREE: 0
 AUTO_INCREMENT: 6
    CREATE_TIME: 2019-03-27 11:58:44
    UPDATE_TIME: 2019-03-27 11:58:44
     CHECK_TIME: NULL
TABLE_COLLATION: utf8mb4_0900_ai_ci
       CHECKSUM: NULL
 CREATE_OPTIONS:
  TABLE_COMMENT:
1 row in set (0.02 sec)

mysql> alter table test add column ge int as (c2 + 1);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table test\G
*************************** 1. row ***************************
       Table: test
Create Table: CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `c2` int(11) DEFAULT NULL,
  `ge` int(11) GENERATED ALWAYS AS ((`c2` + 1)) VIRTUAL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

mysql> select * from Information_schema.tables where table_name = "test" \G
*************************** 1. row ***************************
  TABLE_CATALOG: def
   TABLE_SCHEMA: test
     TABLE_NAME: test
     TABLE_TYPE: BASE TABLE
         ENGINE: InnoDB
        VERSION: 10
     ROW_FORMAT: Dynamic
     TABLE_ROWS: 4
 AVG_ROW_LENGTH: 4096
    DATA_LENGTH: 16384
MAX_DATA_LENGTH: 0
   INDEX_LENGTH: 0
      DATA_FREE: 0
 AUTO_INCREMENT: 6
    CREATE_TIME: 2019-03-27 11:58:44
    UPDATE_TIME: NULL
     CHECK_TIME: NULL
TABLE_COLLATION: utf8mb4_0900_ai_ci
       CHECKSUM: NULL
 CREATE_OPTIONS:
  TABLE_COMMENT:
1 row in set (0.00 sec)

- Didn't check earlier versions as generated column support available since 5.7

In reply to the comment dated "[28 Mar 14:25] Omer Barnir" that I do not see in the bug report any more but got via email notification:

My original comment showing that test case is not repeatable in old MariaDB version was added 3 minutes BEFORE the bug got a verification comment (as you can clearly see from the history of comments). 

At that time it was important detail added, pointing out possible regression in MySQL 5.7 vs older code. It was anything but "an attempt to advertise MariaDB". I am not sure if it influenced anything, but I do not see a test on older MySQL 5.5.x or 5.6.x version and the only hint about possible regression (to be checked, by the way) was my comment, now hidden from anyone.

Valeriy,

Testing in old versions of MySQL (5.5, 5.6 etc.) in order to provide potential regression information before/after verification is valuable,  always welcome and appreciated. 

Reporting behavior in (an unrelated release of) MariaDB does not serve a similar purpose.