On a table with a trigger the same record is updated more than once in one statement

Reported by Elena Stepanova on 2010-01-26
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
PBXT
Undecided
Paul McCullagh

Bug Description

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

use test;

--disable_query_log
--disable_result_log
create table tb0_eng2 (
i1 int NOT NULL auto_increment, primary key (i1),
        f1 int,
        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)
                values (concat('Update row ', old.i1, ' ', old.f2, ' -> ', new.f2));

let $run=1;
let $counter=0;

while ($run)
{
        inc $counter;
        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-$prev_log=1`;

        --echo # ROUND $counter finished, prev log count = $prev_log, new log count = $new_log
        if (`SELECT $counter>=500`)
        {
                --echo # Could not reproduce the problem in $counter iterations
                exit;
        }
}
--enable_query_log
--enable_result_log
select * from tb0_logs order by i1 desc limit 5;

<EOF>

# Run as
# perl ./mysql-test-run.pl --mysqld=--default_storage_engine=pbxt pbxt_test

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;
+i1 entry_dsc
+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

Changed in pbxt:
assignee: nobody → Paul McCullagh (paul-mccullagh)
status: New → In Progress
Paul McCullagh (paul-mccullagh) wrote :

Hi Elena,

Thanks for the bug report. I can reproduce the error.

Paul McCullagh (paul-mccullagh) wrote :

Fix committed to 1.0.10g, release note: RN304

Changed in pbxt:
status: In Progress → Fix Committed
To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers