Details
-
Bug
-
Status: Open (View Workflow)
-
Trivial
-
Resolution: Unresolved
-
10.1.38
-
None
-
None
Description
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.
Example:
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>
|