Bug #17744 Partitions: InnoDB, Trigger, rotten table leads to wrong result sets
Submitted: 27 Feb 2006 16:52 Modified: 24 Mar 2006 13:56
Reporter: Matthias Leich Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Partitions Severity:S1 (Critical)
Version:5.1 OS:
Assigned to: Mikael Ronström CPU Architecture:Any

[27 Feb 2006 16:52] Matthias Leich
Description:
SET SESSION storage_engine='InnoDB';
SET AUTOCOMMIT= 1;
set @@session.sql_mode= '';
CREATE TABLE t1 ( f_int1 INTEGER, f_int2 INTEGER,
f_char1 CHAR(10), f_charbig VARCHAR(1000)
) PARTITION BY RANGE(f_int1)
(PARTITION parta VALUES LESS THAN (0),
PARTITION partc VALUES LESS THAN (10),
PARTITION partf VALUES LESS THAN (2147483647));
INSERT INTO t1 SET f_int1 = 20,f_int2 = 20,f_char1 = '20',f_charbig = '===20===';
INSERT INTO t1 SET f_int1 = 16,f_int2 = 16,f_char1 = '16',f_charbig = '===16===';
INSERT INTO t1 SET f_int1 = 12,f_int2 = 12,f_char1 = '12',f_charbig = '===12===';
INSERT INTO t1 SET f_int1 = 8, f_int2 = 8, f_char1 = '8', f_charbig = '===8===';
INSERT INTO t1 SET f_int1 = 4, f_int2 = 4, f_char1 = '4', f_charbig = '===4===';

SELECT * FROM t1 ORDER BY f_int1;
f_int1  f_int2  f_char1 f_charbig
4       4       4       ===4===
8       8       8       ===8===
12      12      12      ===12===
16      16      16      ===16===
20      20      20      ===20===
# The BEFORE UPDATE trigger recalculates the column used in the
# partitioning function.
CREATE TRIGGER trg BEFORE UPDATE ON t1 FOR EACH ROW
BEGIN
SET new.f_int1 = old.f_int1 + 20,
new.f_int2 = old.f_int2 - 20,
new.f_charbig = '####updated per update trigger####';
END|
SELECT * FROM t1 ORDER BY f_int1;
f_int1  f_int2  f_char1 f_charbig
4       4       4       ===4===
8       8       8       ===8===
12      12      12      ===12===
16      16      16      ===16===
20      20      20      ===20===
# This UPDATE activates the trigger.
UPDATE t1
SET f_charbig = '####updated per update statement itself####';
SELECT * FROM t1 ORDER BY f_int1;
f_int1  f_int2  f_char1 f_charbig
24      -16     4       ####updated per update trigger####
28      -12     8       ####updated per update trigger####
32      -8      12      ####updated per update trigger####
36      -4      16      ####updated per update trigger####
40      0       20      ####updated per update trigger####
DROP TRIGGER trg;
UPDATE t1 SET f_int1 = CAST(f_char1 AS SIGNED INT),
f_int2 = CAST(f_char1 AS SIGNED INT),
f_charbig = CONCAT('===',f_char1,'===');
# Here are some records missing.
SELECT * FROM t1 ORDER BY f_int1;
f_int1  f_int2  f_char1 f_charbig
12      12      12      ===12===
16      16      16      ===16===
20      20      20      ===20===
    <--- Where are the records with f_int1 = 4 and 8   ??

# Assume the PRIMARY KEY (implicit created in case of InnoDB) is rotten.
# Just create a new one.
ALTER TABLE t1 ADD PRIMARY KEY (f_int1,f_int2);
# Now the result set is OK.
SELECT * FROM t1 ORDER BY f_int1;
f_int1  f_int2  f_char1 f_charbig
4       4       4       ===4===
8       8       8       ===8===
12      12      12      ===12===
16      16      16      ===16===
20      20      20      ===20===

Further experiments showed that the bug disappears if
- MyISAM is used instead of InnoDB      or
- Partitioning is removed                      or
- The action of UPDATE inclusive trigger is replaced by
  a corresponding UPDATE without trigger

I set the priority to P1, because so many SELECTs will
suffer from the wrong result sets.

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

How to repeat:
Please use my attached 
testscript ml1109.test
  copy it to mysql-test/t
  echo "Dummy" > r/ml1109.result   # Produce a dummy file with 
                                                   # expected results
  ./mysql-test-run ml1109
[27 Feb 2006 16:53] Matthias Leich
testscript

Attachment: ml1109.test (application/test, text), 2.26 KiB.

[20 Mar 2006 14:27] Mikael Ronström
Testing with latest 5.1.8 
Bug no longer reproducable so fixed by previous bug fix

Fixed in version 5.1.8 for sure
[24 Mar 2006 13:56] Jon Stephens
Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

Documented bugfix in 5.1.8 changelog. Closed.