MySQL Forums
Forum List  »  Newbie

AUTO_INCREMENT value increases by powers of two after INSERT ... SELECT
Posted by: Vahe Evoyan
Date: November 12, 2009 09:46AM

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)

Options: ReplyQuote




Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.