Bug #13227 Trigger fired by replication does not allow SELECTs

• Submitted: 15 Sep 2005 15:26
• Modified: 1 Feb 2006 12:52
• Reporter: Luis Gasca
• Status: Can't repeat
• Category: MySQL Server: Replication
• Severity: S2 (Serious)
• Version: 5.0.12
• OS: Windows (Windows 2003 Server)
• Assigned to: Andrei Elkin
• CPU Architecture: Any

[15 Sep 2005 15:26] Luis Gasca

Description:
Doing a select inside a trigger does not work *if and only if* the trigger is fired by a replication event in the slave. If you update the table locally, the same trigger works just as expected

How to repeat:
SETUP

[master]
CREATE TABLE tupdated (f1 int);
INSERT INTO tupdated values (-1);
INSERT INTO tupdated values (-2);

[slave]
CREATE TABLE tlookup (f1 int, f2 int);
insert into tlookup values (1,100),(2,200);
CREATE TABLE tresult (f2 int);

delimiter //

drop trigger tupdater//
create trigger tupdater before update on tupdated
for each row
begin
  DECLARE r integer;

  SELECT f2 INTO r FROM tlookup where f1=NEW.f1;
  INSERT INTO tresult values (r);
end //

[DIRECT UPDATE - WORKS]

[from mysql client connected to slave]

mysql> UPDATE tupdated SET f1=1 where f1=-1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * from tresult;
+------+
| f2   |
+------+
| 100  |
+------+
1 row in set (0.00 sec)

[UPDATE FROM REPLICATION - DOES NOT WORK]

[from mysql client connected to master]
mysql> UPDATE tupdated set f1=1 where f1=-2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

[from mysql client connected to slave]
mysql> SELECT * from tupdated;
+------+
| f1   |
+------+
|    1 |
|    1 | <-- update from master is ok
+------+
2 rows in set (0.00 sec)

mysql> SELECT * from tresult;
+------+
| f2   |
+------+
| 100  |
| NULL | <-- !!!
+------+
2 rows in set (0.00 sec)

Suggested fix:
[none]

[16 Sep 2005 0:55] John David Duncan

I verified this and I can add one more piece of information. 

It seems that after you attempt the update from the slave thread, subsequent direct updates on the slave will also fail.  

For example, after the test, do this directly on the slave:
insert into tlookup values (3,300) , (4,400) ;
insert into tupdated values (-3);
update tupdated set f1 = 3 where f1 = -3;
select * from tresult ;

This will have created another NULL row in tresult.

[16 Jan 2006 22:00] Andrei Elkin

The case is quite recurrent in my env with 5.0.19 though not exactly as it was reported.
To be continued.

[17 Jan 2006 9:20] Andrei Elkin

Test case was written. It helped to detect this failure occures randomly.
Trying to establish condtions for reliable recurrence.

[27 Jan 2006 10:20] Andrei Elkin

test to reproduce the problem

Attachment: rpl_trigger_select_looping.test (application/octet-stream, text), 1.87 KiB.

[27 Jan 2006 11:35] Bugs System

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/1736

[27 Jan 2006 11:36] Andrei Elkin

The bug seems to be fixed somewhere in between of 5.0.12, where it is reproducable,
and the current 5.0.19.
Test case is done and submitted as bk patch.

[30 Jan 2006 12:33] Bugs System

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/1857

[1 Feb 2006 12:52] Andrei Elkin

Seems to fixed in 5.0.19