Bug #18552 Partitions: SP with cursor, autoincrement PK, duplicate key on insert
Submitted: 27 Mar 2006 18:55 Modified: 10 Apr 2006 13:35
Reporter: Matthias Leich Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Partitions Severity:S2 (Serious)
Version:5.1 OS:
Assigned to: Assigned Account CPU Architecture:Any

[27 Mar 2006 18:55] Matthias Leich
Description:
CREATE TABLE t1_master ( f3 DECIMAL (5,3));

# The "problem" table.
# Attention: A table without partitioning is harmless.
#                 InnoDB shows the same bug.
#                 NDB does NOT show this bug.
CREATE TABLE t1 (
i1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (i1),
f1 INT,
f2 CHAR(15),
f3 DECIMAL (5,3),
f4 DATETIME
) ENGINE=MyISAM PARTITION BY HASH(i1) PARTITIONS 2;
INSERT INTO t1_master SET f3=50;
INSERT INTO t1_master SET f3=50;
create procedure ins_t1 ()
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE v3 DECIMAL(5,3);
DECLARE cur1 CURSOR FOR
SELECT f3 FROM t1_master;
DECLARE CONTINUE HANDLER FOR SQLSTATE '01000' SET done = 1;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
OPEN cur1;
FETCH cur1 INTO v3;
wl_loop: WHILE NOT done DO
INSERT INTO t1 (f3) VALUES (v3 );
FETCH cur1 INTO v3;
END WHILE wl_loop;
CLOSE cur1;
END//

# The not working stored procedure
CALL ins_t1();
ERROR 23000: Can't write; duplicate key in table 't1'

My environment:
   - Intel PC with Linux(SuSE 9.3)
   - MySQL compiled from source
         Version 5.1 last ChangeSet@1.2231.1.1, 2006-03-27

How to repeat:
Please use my attached testscript ml11120.test
  copy it to mysql-test/t
  echo "Dummy" > r/ml11120.result   # Produce a dummy file with 
                                                   # expected results
  ./mysql-test-run ml11120
[27 Mar 2006 18:56] Matthias Leich
script with testcase

Attachment: ml11120.test (application/test, text), 1.11 KiB.

[28 Mar 2006 9:33] Matthias Leich
Please see also Bug#18572 Partitions: crash on ALTER TABLE during stress testing.
Maybe both bugs have the same reason.
[10 Apr 2006 13:35] Mikael Ronström
I tried this bug with the patch for BUG #18753 and it fixes the issue
so I filed it as a duplicate