| Bug #87012 | Auto-Increment Starting Value = auto_increment_offset + auto_increment_increment | ||
|---|---|---|---|
| Submitted: | 11 Jul 2017 13:22 | Modified: | 11 Jul 2017 17:43 |
| Reporter: | Tom Stewart | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | 5.6.36 | OS: | CentOS (CentoOS 6.9 running on AWS t2.mirco. Kernal 2.6.32-696.1.1.el6.x86_64) |
| Assigned to: | MySQL Verification Team | CPU Architecture: | Any |
[11 Jul 2017 16:37]
MySQL Verification Team
Hi,
This is not a bug, just different behavior of MyISAM and InnoDB tables for auto_increment tables (as documented in mysql documentation). You are seeing different result as from 5.6 default storage engine is innodb and since you are not defining explicitly storage engine in 5.5 you get myisam and in 5.6+ you get innodb.
all best
Bogdan Kecman
mysql> select @@auto_increment_offset, @@auto_increment_increment, @@innodb_autoinc_lock_mode;
+-------------------------+----------------------------+----------------------------+
| @@auto_increment_offset | @@auto_increment_increment | @@innodb_autoinc_lock_mode |
+-------------------------+----------------------------+----------------------------+
| 28 | 64 | 1 |
+-------------------------+----------------------------+----------------------------+
1 row in set (0.00 sec)
mysql> CREATE TABLE test.auto_inc_test (column_1 varchar(1));
Query OK, 0 rows affected (0.09 sec)
mysql> INSERT INTO test.auto_inc_test (column_1) VALUES ('a');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO test.auto_inc_test (column_1) VALUES ('b');
Query OK, 1 row affected (0.06 sec)
mysql> INSERT INTO test.auto_inc_test (column_1) VALUES ('c');
Query OK, 1 row affected (0.02 sec)
mysql> ALTER TABLE test.auto_inc_test ADD COLUMN (column_2 INT KEY AUTO_INCREMENT);
Query OK, 0 rows affected (0.26 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM test.auto_inc_test;
+----------+----------+
| column_1 | column_2 |
+----------+----------+
| a | 92 |
| b | 156 |
| c | 220 |
+----------+----------+
3 rows in set (0.00 sec)
mysql> drop TABLE test.auto_inc_test;
Query OK, 0 rows affected (0.12 sec)
mysql> CREATE TABLE test.auto_inc_test (column_1 varchar(1)) engine=myisam;
Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO test.auto_inc_test (column_1) VALUES ('a');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO test.auto_inc_test (column_1) VALUES ('b');
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO test.auto_inc_test (column_1) VALUES ('c');
Query OK, 1 row affected (0.01 sec)
mysql> ALTER TABLE test.auto_inc_test ADD COLUMN (column_2 INT KEY AUTO_INCREMENT);
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM test.auto_inc_test;
+----------+----------+
| column_1 | column_2 |
+----------+----------+
| a | 28 |
| b | 92 |
| c | 156 |
+----------+----------+
3 rows in set (0.00 sec)
mysql>
[11 Jul 2017 17:43]
Tom Stewart
Hi, Thank you for your reply. I can confirm that running the statements in the description result in an InnoDB table in both my 5.5 and 5.6 servers. My default_storage_engine variable in 5.5 is set to InnoDB and if no engine is specified in the create statement on the 5.6 side the show create table statement on the 5.5 side shows as ENGINE=InnoDB: | auto_inc_test | CREATE TABLE `auto_inc_test` ( `column_1` varchar(1) DEFAULT NULL, `column_2` int(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`column_2`) ) ENGINE=InnoDB AUTO_INCREMENT=220 DEFAULT CHARSET=utf8 | Further if the engine is explicitly set to InnoDB in on the 5.6 side I still get the issue where in 5.6 the first auto-increment value is 92 rather than 28. I can confirm that specifying MyISAM as the storage engine in the create statement does not exhibit the issue I describe, only if InnoDB is the storage engine. Does this mean the the behavior for InnoDBB has changed from 5.5 to 5.6? Lastly you mention that this behavior is detailed in the documentation, can you supply a link for that as I was only able to fine the following page that does not detail the adding of the auto-increment column to an existing table and seems to indicate that the first auto-increment value assigned should be the auto_increment_offset; "auto_increment_offset determines the starting point for the AUTO_INCREMENT column value."
Description: Environment: CentoOS 6.9 running on AWS t2.mirco Kernal 2.6.32-696.1.1.el6.x86_64 MySQL 5.6.36 When adding an auto-increment column to an existing table that contains data, the starting value is equal to the auto_increment_offset + the auto_increment_increment rather than just the auto_increment_offset, for example if the auto_increment_offset = 28 and the auto_increment_increment = 64, I would expect the first row in the table to be assigned an auto-increment value of 28 but instead it is assigned a value of 92. This behavior is contrary to the behavior for the same series of statements in MySQL 5.5. Example: 5.6 variables: auto_increment_increment = 64 auto_increment_offset = 28 innodb_autoinc_lock_mode = 1 Statements: CREATE DATABASE test; CREATE TABLE test.auto_inc_test (column_1 varchar(1)); INSERT INTO test.auto_inc_test (column_1) VALUES ('a'); INSERT INTO test.auto_inc_test (column_1) VALUES ('b'); INSERT INTO test.auto_inc_test (column_1) VALUES ('c'); ALTER TABLE test.auto_inc_test ADD COLUMN (column_2 INT KEY AUTO_INCREMENT); Result of SELECT * FROM test.auto_inc_test: +----------+----------+ | column_1 | column_2 | +----------+----------+ | a | 92 | | b | 156 | | c | 220 | +----------+----------+ This does not happen if the table is created with the auto-increment column and data is subsequently inserted only when the table exists with data and an auto-increment column is added later. This also does not happen if the default values of auto_increment_offset and auto_increment_increment are used. This causes issues when replicating an alter table statement, adding auto-increment column to an existing table, between MySQL 5.6 and 5.5 (the replication in our environment is handled by Tungsten Replicator v4.0.7-696, and the issue is present when replicating in either direction). When replicating the alter table statement 5.5 exhibits the expected behavior of starting on auto_increment_offset of the server on which the statement was executed. This results in each rows auto-increment value being out but the value of the auto_increment_increment as shown in the following example: Example - MySQL 5.6 replicating to 5.5 via Tungsten Replicator v4.0.7-696: 5.6 variables: auto_increment_increment = 64 auto_increment_offset = 28 innodb_autoinc_lock_mode = 1 5.5 variables: auto_increment_increment = 64 auto_increment_offset = 38 innodb_autoinc_lock_mode = 1 Statements: CREATE DATABASE test; CREATE TABLE test.auto_inc_test (column_1 varchar(1)); INSERT INTO test.auto_inc_test (column_1) VALUES ('a'); INSERT INTO test.auto_inc_test (column_1) VALUES ('b'); INSERT INTO test.auto_inc_test (column_1) VALUES ('c'); ALTER TABLE test.auto_inc_test ADD COLUMN (column_2 INT KEY AUTO_INCREMENT); Result of SELECT * FROM test.auto_inc_test on MySQL 5.6: +----------+----------+ | column_1 | column_2 | +----------+----------+ | a | 92 | | b | 156 | | c | 220 | +----------+----------+ Result of SELECT * FROM test.auto_inc_test on MySQL 5.5: +----------+----------+ | column_1 | column_2 | +----------+----------+ | a | 28 | | b | 92 | | c | 156 | +----------+----------+ How to repeat: This can be consistently repeated using the variables and statements in the description.