[#MDEV-19223] AUTO_INCREMENT shows incorrectly in SHOW CREATE TABLE

I am using MariaDB in a Galera cluster.
I have a table set up with an AUTO_INCREMENT primary key and a unique key.

If I use ON DUPLICATE KEY UPDATE I can get the AUTO_INCREMENT to show incorrectly in the SHOW CREATE TABLE query.

In the example below the AUTO_INCREMENT of each node in our three node cluster increments by 3 offset from each other.

After the first set of inserts SHOW CREATE TABLE shows a reasonable AUTO_INCREMENT value, but after several INSERT ... ON DUPLICATE KEY UPDATE statements, it is wildly off. However the INFORMATION_SCHEMA shows the correct value.

The next insert has the value from INFORMATION_SCHEMA, so this is a bug only in SHOW CREATE TABLE.

mysql> select version();

+-----------------+

| version()       |

+-----------------+

| 10.1.38-MariaDB |

+-----------------+

1 row in set (0.00 sec)

mysql> create table `auto_inc_too_big` (

    ->   `id` int(11) NOT NULL AUTO_INCREMENT,

    ->   `thing_1` varchar(70) NOT NULL,

    ->   `thing_2` varchar(255) NOT NULL,

    ->   PRIMARY KEY (`id`),

    ->   UNIQUE KEY `thing_1_and_thing_2` (`thing_1`,`thing_2`)

    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Query OK, 0 rows affected (0.11 sec)

mysql> insert into `auto_inc_too_big` (`thing_1`, `thing_2`) values ('first', 'first'), ('first', 'second'), ('first', 'third'), ('second', 'first');

Query OK, 4 rows affected (0.02 sec)

Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from `auto_inc_too_big`;

+----+---------+---------+

| id | thing_1 | thing_2 |

+----+---------+---------+

|  5 | first   | first   |

|  8 | first   | second  |

| 11 | first   | third   |

| 14 | second  | first   |

+----+---------+---------+

4 rows in set (0.00 sec)

mysql> show create table `auto_inc_too_big`;

+------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| Table            | Create Table                                                                                                                                                                                                                                                                        |

+------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| auto_inc_too_big | CREATE TABLE `auto_inc_too_big` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `thing_1` varchar(70) NOT NULL,

  `thing_2` varchar(255) NOT NULL,

  PRIMARY KEY (`id`),

  UNIQUE KEY `thing_1_and_thing_2` (`thing_1`,`thing_2`)

) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8 |

+------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

1 row in set (0.01 sec)

mysql> insert into `auto_inc_too_big` (`thing_1`, `thing_2`) values ('first', 'first') on duplicate key update `thing_1` = 'first';

Query OK, 0 rows affected (0.00 sec)

mysql> insert into `auto_inc_too_big` (`thing_1`, `thing_2`) values ('first', 'second') on duplicate key update `thing_1` = 'first';

Query OK, 0 rows affected (0.01 sec)

mysql> insert into `auto_inc_too_big` (`thing_1`, `thing_2`) values ('first', 'third') on duplicate key update `thing_1` = 'first';

Query OK, 0 rows affected (0.00 sec)

mysql> insert into `auto_inc_too_big` (`thing_1`, `thing_2`) values ('second', 'first') on duplicate key update `thing_1` = 'second';

Query OK, 0 rows affected (0.00 sec)

mysql> select * from `auto_inc_too_big`;

+----+---------+---------+

| id | thing_1 | thing_2 |

+----+---------+---------+

|  5 | first   | first   |

|  8 | first   | second  |

| 11 | first   | third   |

| 14 | second  | first   |

+----+---------+---------+

4 rows in set (0.00 sec)

mysql> show create table `auto_inc_too_big`;

+------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| Table            | Create Table                                                                                                                                                                                                                                                                        |

+------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| auto_inc_too_big | CREATE TABLE `auto_inc_too_big` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `thing_1` varchar(70) NOT NULL,

  `thing_2` varchar(255) NOT NULL,

  PRIMARY KEY (`id`),

  UNIQUE KEY `thing_1_and_thing_2` (`thing_1`,`thing_2`)

) ENGINE=InnoDB AUTO_INCREMENT=29 DEFAULT CHARSET=utf8 |

+------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

mysql> select Auto_increment FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='auto_inc_too_big';

+----------------+

| Auto_increment |

+----------------+

|             15 |

+----------------+

1 row in set (1.38 sec)

mysql> insert into `auto_inc_too_big` (`thing_1`, `thing_2`) values ('second', 'second') on duplicate key update `thing_1` = `thing_1`;

Query OK, 1 row affected (0.01 sec)

mysql> select * from `auto_inc_too_big`;

+----+---------+---------+

| id | thing_1 | thing_2 |

+----+---------+---------+

|  5 | first   | first   |

|  8 | first   | second  |

| 11 | first   | third   |

| 14 | second  | first   |

| 18 | second  | second  |

+----+---------+---------+

5 rows in set (0.01 sec)

mysql> select Auto_increment FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='auto_inc_too_big';

+----------------+

| Auto_increment |

+----------------+

|             21 |

+----------------+

1 row in set (0.55 sec)

mysql> show create table `auto_inc_too_big`;

+------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| Table            | Create Table                                                                                                                                                                                                                                                                         |

+------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| auto_inc_too_big | CREATE TABLE `auto_inc_too_big` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `thing_1` varchar(70) NOT NULL,

  `thing_2` varchar(255) NOT NULL,

  PRIMARY KEY (`id`),

  UNIQUE KEY `thing_1_and_thing_2` (`thing_1`,`thing_2`)

) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8 |

+------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

mysql>

[MDEV-19223] AUTO_INCREMENT shows incorrectly in SHOW CREATE TABLE Created: 2019-04-09 Updated: 2019-11-27
Status:	Open
Project:	MariaDB Server
Component/s:	Server, Storage Engine - InnoDB
Affects Version/s:	10.1.38
Fix Version/s:	None

[MDEV-19223] AUTO_INCREMENT shows incorrectly in SHOW CREATE TABLE Created: 2019-04-09 Updated: 2019-11-27