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
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