| Bug #60139 | Autoincrement is possible in multicolumn index for InnoDB Table | ||
|---|---|---|---|
| Submitted: | 16 Feb 2011 7:57 | Modified: | 3 Oct 2012 15:56 |
| Reporter: | Holger Thiel | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Documentation | Severity: | S3 (Non-critical) |
| Version: | 5.5.8, 5.5.9 | OS: | Any (Debian Etch, Windows XP) |
| Assigned to: | Paul DuBois | CPU Architecture: | Any |
| Tags: | autoincrement innodb multicolumn | ||
[16 Feb 2011 7:57]
Holger Thiel
[16 Feb 2011 11:07]
Valeriy Kravchuk
Let's say it is a documentation bug for now. http://dev.mysql.com/doc/refman/5.5/en/innodb-restrictions.html should be fixed, as this works: C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql -uroot -proot -P3312 test Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 21 Server version: 5.5.8 MySQL Community Server (GPL) Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved. This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL v2 license Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> CREATE TABLE `answerSet` ( -> `autoId` int(11) NOT NULL AUTO_INCREMENT, -> `part2Id` int(11) NOT NULL DEFAULT '0', -> `part3Id` int(11) NOT NULL DEFAULT '0', -> `comment` text, -> PRIMARY KEY (`autoId`,`part2Id`,`part3Id`), -> KEY `idx_part2Id` (`part2Id`) -> ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 -> ; Query OK, 0 rows affected (0.17 sec) mysql> INSERT INTO answerSet (comment) VALUES ('magdalena'); Query OK, 1 row affected (0.03 sec) mysql> INSERT INTO answerSet (comment) VALUES ('monty'); Query OK, 1 row affected (0.05 sec) mysql> select * from `answerSet`; +--------+---------+---------+-----------+ | autoId | part2Id | part3Id | comment | +--------+---------+---------+-----------+ | 3 | 0 | 0 | magdalena | | 4 | 0 | 0 | monty | +--------+---------+---------+-----------+ 2 rows in set (0.00 sec)
[12 Aug 2011 19:54]
Sheeri Cabral
Leaving it as a documentation issue violates the point of an auto-increment field, though. The point of an auto-increment field is to get DIFFERENT values. I have just run into a situation (on MySQL 5.1.45, which I am trying to replicate so I can make a good bug report) where an InnoDB table has an auto-increment value that is part of a composite primary key, and the server assigned the same auto-increment values to 2 different INSERTs. (according to the binlogs the inserts happened during the same second, with thread_id values that were 3 digits apart - aka thread_id 123 vs. thread_id 126). Here are some relevant variables: mysql> show variables like 'innodb_autoinc%'; +--------------------------+-------+ | Variable_name | Value | +--------------------------+-------+ | innodb_autoinc_lock_mode | 1 | +--------------------------+-------+ 1 row in set (0.00 sec) mysql> show variables like 'binlog_format'; +---------------+-----------+ | Variable_name | Value | +---------------+-----------+ | binlog_format | STATEMENT | +---------------+-----------+ 1 row in set (0.00 sec) mysql> show variables like 'slave_exec_mode'; +-----------------+--------+ | Variable_name | Value | +-----------------+--------+ | slave_exec_mode | STRICT | +-----------------+--------+ 1 row in set (0.00 sec)
[12 Aug 2011 21:38]
Sheeri Cabral
FWIW, the reason the autoincrement fields in my scenario are part of a composite index instead of a simple index with only one field is that the tables are partitioned, so the fact that the server actually chose the same autoincrement twice may be related to partitioning. I say this because we were thinking a workaround could be to add a second UNIQUE index only on the autoincrement field. But in theory that would not stop InnoDB from choosing the same autoincrement value; it would just cause bad data not to be put into the table....which means data would be lost.
[29 Aug 2011 18:27]
Sheeri Cabral
So we ran into a problem with MysQL 5.1.45 assigning a duplicate key for a table, and we are not sure why that duplicate key was generated. It was allowed to be inserted because the autoincrement was not the primary key, it was only part of the primary key, and in fact we cannot put a UNIQUE index on the autoincrement field because it is a partitioned table, and it's not partitioned on the autoincrement field.
I know i'm describing a different bug (MySQL/InnoDB picking the same autoincrement value) but I believe it's an issue because of the fact that the autoincrement is part of a multicolumn unique index, because if it wasn't I believe MySQL/InnoDB would not have been able to choose the same autoincrement value.
Here are the binlog entries:
# at 23790486
#110812 17:58:47 server id 91190 end_log_pos 23790514 Intvar
SET INSERT_ID=43881758/*!*/;
# at 23790514
#110812 17:58:47 server id 91190 end_log_pos 23790961 Query thread_id=701871 exec_time=0 error_code=0
SET TIMESTAMP=1313171927/*!*/;
INSERT INTO `my_table` (`created_at`, `ended_at`, `block_group_id`, `began_at`, `updated_at`, `block_id`, `mac`, `device_id`, `block_face`, `location_id`, `space_id`, `nic_id`, `state`) VALUES('2011-08-12 17:58:47', '9999-12-31 23:59:59', 8, '2011-08-12 17:53:17', '2011-08-12 17:58:47', 500040, 'B4-48', 3768, 'low', 224, 94, 4066, 'OCCUPIED')
/*!*/;
# at 23813055
#110812 17:58:47 server id 91190 end_log_pos 23813083 Intvar
SET INSERT_ID=43881758/*!*/;
# at 23813083
#110812 17:58:47 server id 91190 end_log_pos 23813528 Query thread_id=701874 exec_time=0 error_code=0
SET TIMESTAMP=1313171927/*!*/;
INSERT INTO `my_table` (`created_at`, `ended_at`, `block_group_id`, `began_at`, `updated_at`, `block_id`, `mac`, `device_id`, `block_face`, `location_id`, `space_id`, `nic_id`, `state`) VALUES('2011-08-12 17:58:47', '9999-12-31 23:59:59', 4, '2011-08-12 17:50:15', '2011-08-12 17:58:47', 86, 'EA-9C', 4408, 'high', 5161, 9611, 4741, 'VACANT')
/*!*/;
Here's the table creation:
mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 91190 |
+---------------+-------+
1 row in set (0.00 sec)
mysql> show create table my_table\G
*************************** 1. row ***************************
Table: my_table
Create Table: CREATE TABLE `my_table` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`space_id` int(11) NOT NULL,
`state` varchar(255) NOT NULL,
`began_at` datetime NOT NULL,
`ended_at` datetime NOT NULL DEFAULT '9999-12-31 23:59:59',
`created_at` datetime DEFAULT NULL,
`updated_at` datetime DEFAULT NULL,
`location_id` int(11) DEFAULT NULL,
`device_id` int(11) DEFAULT NULL,
`block_id` int(11) DEFAULT NULL,
`block_face` varchar(255) DEFAULT NULL,
`block_group_id` int(11) DEFAULT NULL,
`nic_id` int(11) DEFAULT NULL,
`mac` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`,`began_at`),
KEY `my_table_space_id_began_at_ended_at_idx` (`space_id`,`began_at`,`ended_at`),
KEY `my_table_began_at_ended_at_state_idx` (`began_at`,`ended_at`,`state`),
KEY `my_table_state_ended_at_began_at_idx` (`state`,`ended_at`,`began_at`),
KEY `my_table_space_id_ended_at_idx` (`space_id`,`ended_at`)
) ENGINE=InnoDB AUTO_INCREMENT=45482954 DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE (to_days(began_at))
(PARTITION p20100115 VALUES LESS THAN (734152) ENGINE = InnoDB,
PARTITION p20100122 VALUES LESS THAN (734159) ENGINE = InnoDB,
PARTITION p20100129 VALUES LESS THAN (734166) ENGINE = InnoDB,
PARTITION p20100205 VALUES LESS THAN (734173) ENGINE = InnoDB,
PARTITION p20100212 VALUES LESS THAN (734180) ENGINE = InnoDB,
PARTITION p20100219 VALUES LESS THAN (734187) ENGINE = InnoDB,
PARTITION p20100226 VALUES LESS THAN (734194) ENGINE = InnoDB,
PARTITION p20100305 VALUES LESS THAN (734201) ENGINE = InnoDB,
PARTITION p20100312 VALUES LESS THAN (734208) ENGINE = InnoDB,
PARTITION p20100319 VALUES LESS THAN (734215) ENGINE = InnoDB,
PARTITION p20100326 VALUES LESS THAN (734222) ENGINE = InnoDB,
PARTITION p20100402 VALUES LESS THAN (734229) ENGINE = InnoDB,
PARTITION p20100409 VALUES LESS THAN (734236) ENGINE = InnoDB,
PARTITION p20100416 VALUES LESS THAN (734243) ENGINE = InnoDB,
PARTITION p20100423 VALUES LESS THAN (734250) ENGINE = InnoDB,
PARTITION p20100430 VALUES LESS THAN (734257) ENGINE = InnoDB,
PARTITION p20100507 VALUES LESS THAN (734264) ENGINE = InnoDB,
PARTITION p20100514 VALUES LESS THAN (734271) ENGINE = InnoDB,
PARTITION p20100521 VALUES LESS THAN (734278) ENGINE = InnoDB,
PARTITION p20100528 VALUES LESS THAN (734285) ENGINE = InnoDB,
PARTITION p20100604 VALUES LESS THAN (734292) ENGINE = InnoDB,
PARTITION p20100611 VALUES LESS THAN (734299) ENGINE = InnoDB,
PARTITION p20100618 VALUES LESS THAN (734306) ENGINE = InnoDB,
PARTITION p20100625 VALUES LESS THAN (734313) ENGINE = InnoDB,
PARTITION p20100702 VALUES LESS THAN (734320) ENGINE = InnoDB,
PARTITION p20100709 VALUES LESS THAN (734327) ENGINE = InnoDB,
PARTITION p20100716 VALUES LESS THAN (734334) ENGINE = InnoDB,
PARTITION p20100721 VALUES LESS THAN (734339) ENGINE = InnoDB,
PARTITION p20100722 VALUES LESS THAN (734340) ENGINE = InnoDB,
PARTITION p20100723 VALUES LESS THAN (734341) ENGINE = InnoDB,
PARTITION p20100724 VALUES LESS THAN (734342) ENGINE = InnoDB,
PARTITION p20100725 VALUES LESS THAN (734343) ENGINE = InnoDB,
PARTITION p20100726 VALUES LESS THAN (734344) ENGINE = InnoDB,
PARTITION p20100727 VALUES LESS THAN (734345) ENGINE = InnoDB,
PARTITION p20100728 VALUES LESS THAN (734346) ENGINE = InnoDB,
PARTITION p20100729 VALUES LESS THAN (734347) ENGINE = InnoDB,
PARTITION p20100730 VALUES LESS THAN (734348) ENGINE = InnoDB,
PARTITION p20100731 VALUES LESS THAN (734349) ENGINE = InnoDB,
...
[daily partitions made the comment too large, so I have cut them from here]
....
PARTITION p20110826 VALUES LESS THAN (734740) ENGINE = InnoDB,
PARTITION p20110827 VALUES LESS THAN (734741) ENGINE = InnoDB,
PARTITION p20110828 VALUES LESS THAN (734742) ENGINE = InnoDB,
PARTITION p20110829 VALUES LESS THAN (734743) ENGINE = InnoDB,
PARTITION p20110830 VALUES LESS THAN (734744) ENGINE = InnoDB,
PARTITION p20110831 VALUES LESS THAN (734745) ENGINE = InnoDB,
PARTITION p20110901 VALUES LESS THAN (734746) ENGINE = InnoDB,
PARTITION p20110902 VALUES LESS THAN (734747) ENGINE = InnoDB) */
1 row in set (0.02 sec)
Here are related variables:
mysql> show variables like '%auto%inc%';
+-----------------------------+-------+
| Variable_name | Value |
+-----------------------------+-------+
| auto_increment_increment | 2 |
| auto_increment_offset | 2 |
| innodb_autoextend_increment | 8 |
| innodb_autoinc_lock_mode | 1 |
+-----------------------------+-------+
4 rows in set (0.00 sec)
mysql> show variables like 'binlog_format';
+---------------+-----------+
| Variable_name | Value |
+---------------+-----------+
| binlog_format | STATEMENT |
+---------------+-----------+
1 row in set (0.00 sec)
There are no batch inserts or INSERT...ON DUPLICATE KEY statements against that table.
[29 Aug 2011 18:29]
Sheeri Cabral
I have already checked the changelogs for MySQL 5.1.46 - 5.1.58, and none have a bugfix for this problem (http://dev.mysql.com/doc/refman/5.1/en/news-5-1-46.html notes bug 49032, but that's only relevant if the autoincrement value is a float or double, in this case it's an INT and thus not relevant). I did that because we cannot simply upgrade as a test, so please do not recommend an upgrade unless you can point out which version fixed this issue.
[3 Oct 2012 15:56]
Paul DuBois
Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products. The AUTO_INCREMENT column need not be the first column in an index, but it must be possible to perform an index lookup on it (which implies it should be the first column of *some* index). Here is the condition: An AUTO_INCREMENT column ai_col must be defined as part of an index such that it is possible to perform the equivalent of an indexed SELECT MAX(ai_col) lookup on the table to obtain the maximum column value. Typically, this is achieved by making the column the first column of some table index.
