AUTO_INCREMENT value increases by powers of two after INSERT ... SELECT
After the INSERT..SELECT statement, the AUTO_INCREMENT of the table in which I'm inserting values from another table is equal to nearest (but bigger then inserted count) power of two. For example when I'm inserting 2 rows it equals 4, for 4 rows - 8, for 10 - 16 and so on.
Here is output of mysql:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 182
Server version: 5.1.37 Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> USE `bugtest`;
Database changed
#Creating two equivalent tables
mysql> CREATE TABLE tbl1 (
-> `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
-> `value` VARCHAR(32) NOT NULL,
-> PRIMARY KEY (`id`),
-> UNIQUE KEY `value` (`value`)
-> ) ENGINE=INNODB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.07 sec)
mysql> CREATE TABLE tbl2 (
-> `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
-> `value` VARCHAR(32) NOT NULL,
-> PRIMARY KEY (`id`),
-> UNIQUE KEY `value` (`value`)
-> ) ENGINE=INNODB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.04 sec)
# Inserting some rows into the first table
mysql> INSERT INTO `tbl1` (`id`, `value`) VALUES (NULL, 'val1'), (NULL, 'val2'), (NULL, 'val3'), (NULL, 'val4');
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> SHOW TABLE STATUS LIKE 'tbl1' \G
*************************** 1. row ***************************
Name: tbl1
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 4
Avg_row_length: 4096
Data_length: 16384
Max_data_length: 0
Index_length: 16384
Data_free: 0
Auto_increment: 5
Create_time: 2009-11-12 19:06:47
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
mysql> SHOW TABLE STATUS LIKE 'tbl2' \G
*************************** 1. row ***************************
Name: tbl2
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 0
Avg_row_length: 0
Data_length: 16384
Max_data_length: 0
Index_length: 16384
Data_free: 0
Auto_increment: 1
Create_time: 2009-11-12 19:07:01
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.01 sec)
mysql> INSERT INTO tbl2 (`id`, `value`) SELECT NULL, `value` FROM `tbl1` LIMIT 2;
Query OK, 2 rows affected (0.03 sec)
Records: 2 Duplicates: 0 Warnings: 0
# As you see Auto_increment is 4, but I've inserted only two rows
mysql> SHOW TABLE STATUS LIKE 'tbl2' \G
*************************** 1. row ***************************
Name: tbl2
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 2
Avg_row_length: 8192
Data_length: 16384
Max_data_length: 0
Index_length: 16384
Data_free: 0
Auto_increment: 4
Create_time: 2009-11-12 19:07:01
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.01 sec)
mysql> TRUNCATE tbl2;
Query OK, 0 rows affected (0.02 sec)
mysql> SHOW TABLE STATUS LIKE 'tbl2' \G
*************************** 1. row ***************************
Name: tbl2
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 0
Avg_row_length: 0
Data_length: 16384
Max_data_length: 0
Index_length: 16384
Data_free: 0
Auto_increment: 1
Create_time: 2009-11-12 19:07:01
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.01 sec)
mysql> INSERT INTO tbl2 (`id`, `value`) SELECT NULL, `value` FROM `tbl1` LIMIT 4;
Query OK, 4 rows affected (0.02 sec)
Records: 4 Duplicates: 0 Warnings: 0
# Only 4 rows, but Auto_increment is 8
mysql> SHOW TABLE STATUS LIKE 'tbl2' \G
*************************** 1. row ***************************
Name: tbl2
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 4
Avg_row_length: 4096
Data_length: 16384
Max_data_length: 0
Index_length: 16384
Data_free: 0
Auto_increment: 8
Create_time: 2009-11-12 19:07:01
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)