MySQL Bugs: #13305: multi-table update crashes slave if involves function

Bug #13305	multi-table update crashes slave if involves function
Submitted:	18 Sep 2005 10:54	Modified:	6 Oct 2005 20:50
Reporter:	Guilhem Bichot	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S3 (Non-critical)
Version:	5.0	OS:	Linux (linux)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
Run "how-to-repeat". Slave will crash on the first assertion in lock_tables() (debug build).

How to repeat:
Run this test (inspired from BUG#12844):
source include/master-slave.inc;

create table t1(a int);
insert into t1 values (1),(2);
create table t2(a int);
insert into t2 values (1),(2);

create table t3 (a int);
delimiter //;

create function f1(a int) returns int deterministic modifies sql data
begin
  insert into t3 values(a);
  return 0;
end//

delimiter ;//

# test for crash

update t1,t2 set t1.a=3, t2.a=3 where f1(t1.a) = 0;

Suggested fix:
commenting out this code of sql_parse.cc (starting from "if (lex->sql_command == SQLCOM_UPDATE_MULTI)") avoids the crash:

  if (thd->slave_thread)
  {
    if (lex->sql_command == SQLCOM_UPDATE_MULTI)
    {
      DBUG_PRINT("info",("need faked locked tables"));
      
      if (check_multi_update_lock(thd))
        goto error;

      /* Fix for replication, the tables are opened and locked,
         now we pretend that we have performed a LOCK TABLES action */
	 
      fake_prev_lock= thd->locked_tables;
      if (thd->lock)
        thd->locked_tables= thd->lock;
      thd->lock= 0;
      slave_fake_lock= 1;
    }

I would suggest this bug goes to Antony, as the code is his:
acurtis 1.271.1.149         if (lex->sql_command == SQLCOM_UPDATE_MULTI)
acurtis 1.218.43.1          {
acurtis 1.218.43.1            DBUG_PRINT("info",("need faked locked tables"));
acurtis 1.218.43.1
acurtis 1.393.2.4             if (check_multi_update_lock(thd))
acurtis 1.218.43.1              goto error;
acurtis 1.218.43.1
acurtis 1.218.43.1            /* Fix for replication, the tables are opened and locked,
acurtis 1.218.43.1               now we pretend that we have performed a LOCK TABLES action */
acurtis 1.218.43.1
acurtis 1.218.43.1            fake_prev_lock= thd->locked_tables;
acurtis 1.218.43.1            if (thd->lock)
acurtis 1.218.43.1              thd->locked_tables= thd->lock;
acurtis 1.218.43.1            thd->lock= 0;
acurtis 1.218.43.1            slave_fake_lock= 1;
acurtis 1.218.43.1          }

The fix for the BUG#7011 is no longer correct for 5.0. It tries to do prelocking which worked in 4.0 and 4.1 but in 5.0 SP and triggers do the same trick.

Thus BUG#7011 should be re-fixed in 5.0, one possible solution - for multi-updates perform all_tables_not_ok() test later when all locks are known.

does not happen with multi-DELETE (could not repeat using a similar testcase).

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/internals/30738

a duplicate of bug#12618