Bug #76037 Innodb: Incorrect duplicate key error for autoinc column for partitioned table
Submitted: 25 Feb 2015 4:52 Modified: 13 Apr 2015 18:41
Reporter: Vinay Fisrekar Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical)
Version:5.7 OS:Any
Assigned to:

[25 Feb 2015 4:52] Vinay Fisrekar
Description:
Insert fails with duplicate key error. Insert should succeed.  Test scenario is about export/import of tablespace with partitioned table.
This error is showing after wl#6035 (add native partition support of innodb)
Test is failing in JET regression. 

How to repeat:
# Create .test file for following command and run

let $MYSQLD_DATADIR=`SELECT @@datadir`;
CREATE TABLE t1 (col_1_int int AUTO_INCREMENT, col_2_varchar VARCHAR (20), primary key (col_1_int))ENGINE = Innodb PARTITION BY HASH (col_1_int) PARTITIONS 3;
INSERT INTO t1 VALUES (1,'a1'),(2,'a2'),(3,'a3'),(4,'a4'),(5,'a5'),(6,'a6'),(7,'a7'),(8,'a8'),(9,'a9'),(10,'a10'),(11,'a11'),(12,'a12'),(13,'a13'),(14,'a14'),(15,'a15'),(16,'a16'),(17,'a17'),(18,'a18'),(19,'a19'),(20,'a20'),(21,'a21'),(22,'a22'),(23,'a23'),(24,'a24'),(25,'a25'),(26,'a26'),(27,'a27'),(28,'a28'),(29,'a29'),(30,'a30'),(31,'a31'),(32,'a32'),(33,'a33'),(34,'a34'),(35,'a35'),(36,'a36'),(37,'a37'),(38,'a38'),(39,'a39'),(40,'a40'),(41,'a41'),(42,'a42'),(43,'a43'),(44,'a44'),(45,'a45'),(46,'a46'),(47,'a47'),(48,'a48'),(49,'a49'),(50,'a50'),(51,'a51'),(52,'a52'),(53,'a53'),(54,'a54'),(55,'a55'),(56,'a56'),(57,'a57'),(58,'a58'),(59,'a59'),(60,'a60'),(61,'a61'),(62,'a62'),(63,'a63'),(64,'a64'),(65,'a65'),(66,'a66'),(67,'a67'),(68,'a68'),(69,'a69'),(70,'a70'),(71,'a71'),(72,'a72'),(73,'a73'),(74,'a74'),(75,'a75'),(76,'a76'),(77,'a77'),(78,'a78'),(79,'a79'),(80,'a80'),(81,'a81'),(82,'a82'),(83,'a83'),(84,'a84'),(85,'a85'),(86,'a86'),(87,'a87'),(88,'a88'),(89,'a89'),(90,'a90'),(91,'a91'),(92,'a92'),(93,' a93'),(94,'a94'),(95,'a95'),(96,'a96'),(97,'a97'),(98,'a98'),(99,'a99'),(100,'a100');
select * from t1;
#SET GLOBAL innodb_file_per_table = 1;
SHOW CREATE TABLE t1;
FLUSH TABLES t1 FOR EXPORT;
--copy_file $MYSQLD_DATADIR/test/t1#P#p0.ibd $MYSQLD_DATADIR/test/t1#P#p0_backup.ibd
--copy_file $MYSQLD_DATADIR/test/t1#P#p1.ibd $MYSQLD_DATADIR/test/t1#P#p1_backup.ibd
--copy_file $MYSQLD_DATADIR/test/t1#P#p2.ibd $MYSQLD_DATADIR/test/t1#P#p2_backup.ibd
--copy_file $MYSQLD_DATADIR/test/t1#P#p0.cfg $MYSQLD_DATADIR/test/t1#P#p0_backup.cfg
--copy_file $MYSQLD_DATADIR/test/t1#P#p1.cfg $MYSQLD_DATADIR/test/t1#P#p1_backup.cfg
--copy_file $MYSQLD_DATADIR/test/t1#P#p2.cfg $MYSQLD_DATADIR/test/t1#P#p2_backup.cfg
UNLOCK TABLES;
DROP TABLE t1;
#SET GLOBAL innodb_file_per_table = 1;
CREATE TABLE t1 (col_1_int int AUTO_INCREMENT, col_2_varchar VARCHAR (20), primary key (col_1_int))ENGINE = Innodb PARTITION BY HASH (col_1_int) PARTITIONS 3;
ALTER TABLE t1 DISCARD TABLESPACE;
--move_file $MYSQLD_DATADIR/test/t1#P#p0_backup.ibd $MYSQLD_DATADIR/test/t1#P#p0.ibd
--move_file $MYSQLD_DATADIR/test/t1#P#p1_backup.ibd $MYSQLD_DATADIR/test/t1#P#p1.ibd
--move_file $MYSQLD_DATADIR/test/t1#P#p2_backup.ibd $MYSQLD_DATADIR/test/t1#P#p2.ibd
--move_file $MYSQLD_DATADIR/test/t1#P#p0_backup.cfg $MYSQLD_DATADIR/test/t1#P#p0.cfg
--move_file $MYSQLD_DATADIR/test/t1#P#p1_backup.cfg $MYSQLD_DATADIR/test/t1#P#p1.cfg
--move_file $MYSQLD_DATADIR/test/t1#P#p2_backup.cfg $MYSQLD_DATADIR/test/t1#P#p2.cfg
ALTER TABLE t1 IMPORT TABLESPACE;
SHOW CREATE TABLE t1;
select * from t1;
--error 1062
INSERT INTO t1 VALUES (50,'a50');
INSERT INTO t1(col_2_varchar) VALUES('a101'),('a102'),('a103');
[13 Apr 2015 18:41] Daniel Price
Posted by developer:
 
Fixed as of the upcoming 5.7.8, 5.8.0 release, and here's the changelog entry:

During a table import operation, an "INSERT" failed with a duplicate key
error on an "AUTO_INCREMENT" column due to an incorrectly initialized
"AUTO_INCREMENT" value. 

Thank you for the bug report.