On a table with a trigger the same record is updated more than once in one statement
PBXT 1.0.09d, 1.0.10
MySQL 5.1.42, 6.0.9, 6.0-backup
The provided test case creates two tables with the default engine, populates the 1st table with several records, and sets up a trigger on the 1st table which inserts into another table.
Then, it performs simple updates on the 1st table.
The update should affect exactly one record (there is no randomness, values in WHERE are constants).
After a few iterations, the update hits the same record twice during one statement.
This can be seen from the records in the 2nd table (two records are inserted instead of one); but it is not an insert glitch -- if the test is run with binlogging, the binary log also contains two pairs of UPDATE/INSERT statements instead of one.
The binary log is what actually makes it a problem: after the first update is done, the server where the binary log is applied fails to find the row for the second update, and the process or replaying the log aborts with error 1032 (in replication environment, slave SQL thread aborts).
Could not reproduce with InnoDB or MyISAM.
# MTR test case:
# > cat t/pbxt_test.test
create table tb0_eng2 (
i1 int NOT NULL auto_increment, primary key (i1),
f2 char (32));
insert into tb0_eng2 (f1,f2) values (7, 'init_val'), (14, 'init_val'),
(10, 'init_val'), (14, 'init_val'), (4, 'init_val');
CREATE TABLE tb0_logs (
i1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (i1),
entry_dsc CHAR(100) );
Create trigger tb0_eng2_upd after update on tb0_eng2 for each row
insert into tb0_logs (entry_dsc)
let $prev_log = `SELECT COUNT(*) FROM tb0_logs`;
eval update tb0_eng2 set f2='Update #$counter' where f1=10;
let $new_log = `SELECT COUNT(*) FROM tb0_logs`;
let $run = `SELECT $new_log-
--echo # ROUND $counter finished, prev log count = $prev_log, new log count = $new_log
if (`SELECT $counter>=500`)
select * from tb0_logs order by i1 desc limit 5;
# Run as
# perl ./mysql-test-run.pl --mysqld=
If the test hits the problem, it says
+# ROUND ... finished, prev log count = ..., new log count = ...
+select * from tb0_logs order by i1 desc limit 5;
+10 Update row 3 Update #... -> Update #...
+9 Update row 3 Update #... -> Update #...
(note more than 1 row difference between old and new count in ROUND line, and a dummy update (same value before and after the update) in the topmost tb0_logs record.
If the test does not hit the problem, it stops after 500 iterations saying
+# Could not reproduce the problem in 500 iterations