MySQL Forums :: Newbie :: AUTO_INCREMENT value increases by powers of two after INSERT ... SELECT


Advanced Search

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


Subject Written By Posted
AUTO_INCREMENT value increases by powers of two after INSERT ... SELECT Vahe Evoyan 11/12/2009 09:46AM
Re: AUTO_INCREMENT value increases by powers of two after INSERT ... SELECT Paul Svirin 11/12/2009 06:00PM
Re: AUTO_INCREMENT value increases by powers of two after INSERT ... SELECT Vahe Evoyan 11/13/2009 04:36AM
Re: AUTO_INCREMENT value increases by powers of two after INSERT ... SELECT Barry Galbraith 11/13/2009 05:35AM
Re: AUTO_INCREMENT value increases by powers of two after INSERT ... SELECT Vahe Evoyan 11/13/2009 05:55AM
Re: AUTO_INCREMENT value increases by powers of two after INSERT ... SELECT Barry Galbraith 11/13/2009 04:12PM
Re: AUTO_INCREMENT value increases by powers of two after INSERT ... SELECT Chad Bourque 11/13/2009 04:32PM
Re: AUTO_INCREMENT value increases by powers of two after INSERT ... SELECT Barry Galbraith 11/13/2009 05:03PM
Re: AUTO_INCREMENT value increases by powers of two after INSERT ... SELECT Guelphdad Lake 11/13/2009 05:47PM
Re: AUTO_INCREMENT value increases by powers of two after INSERT ... SELECT Chad Bourque 11/13/2009 06:11PM
Re: AUTO_INCREMENT value increases by powers of two after INSERT ... SELECT Rick James 11/20/2009 12:55AM
Re: AUTO_INCREMENT value increases by powers of two after INSERT ... SELECT Ben Hunter 11/20/2009 05:48AM


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.