MySQL Bugs: #9361: Replication breaks when using SUBSELECTS.

Bug #9361	Replication breaks when using SUBSELECTS.
Submitted:	23 Mar 2005 15:05	Modified:	5 Jul 2005 7:33
Reporter:	Jeff Fisher	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S1 (Critical)
Version:	4.1.10	OS:	Whitebox Linux 3 (Linux 2.4.21)
Assigned to:	Lars Thalmann	CPU Architecture:	Any

Description:
We have a setup like this:

master -> replication master -> many slaves

Our slaves have been 4.1.10 for approximately 2 weeks and we decided to upgrade the master and the replication master (the MySQL instance where all the slaves connect). Upon doing so, everything seemed fine until we ran this query on the master:

UPDATE mailsettings ms, domains d SET ms.value='No' WHERE ms.domain_id = (SELECT id FROM domains WHERE domain='example.com') AND ms.mailaccount_id
IS NULL and ms.program='spamfilter' and ms.key='scan_incoming'

The error shown in "SHOW SLAVE STATUS" on the replication master was:

Last_Errno: 1100
Last_Error: Error 'Table 'domains' was not locked with LOCK TABLES' on query. Default database: 'lfcsystem'. Query: 'UPDATE mailsettings ms, domains d SET ms.value='No' WHERE ms.domain_id = (SELECT id FROM domains WHERE domain='iamnos3.ca') AND ms.mailaccount_id IS NULL and ms.program='spamfilter' and ms.key='scan_incoming''

We downgraded to 4.1.9 to test, and the problem went away. Also, if we run the query manually on a 4.1.10 test slave-- the query executes fine. 

Here are the settings from our master's my.cnf:

[mysqld]
log-bin
server-id=100
max-connections=600

And from our replication master's my.cnf:

[mysqld]
log-bin
log-slave-updates
server-id = 2
read-only
replicate-wild-do-table=lfcsystem.%
replicate-ignore-table=lfcsystem.disk_usage
replicate-ignore-table=lfcsystem.bandwidth_usage
replicate-ignore-table=lfcsystem.resource_usage
skip-innodb
max-connections=600

How to repeat:
If you fix replication and rerun that query (or any UPDATE query using a SUBSELECT), replication will break again.

Verified with this script (in 4.1, but works ok in 5.0):

drop table if exists mailsettings;
drop table if exists domains;

create table mailsettings (
  value           char(30),
  domain_id       int,
  mailaccount_id  int,
  program         char(30),
  keey            char(30)
);

create table domains (
  id              int,
  domain          char(30)
);

insert into mailsettings values ('Yes', 1, NULL, 'spamfilter', 'scan_incoming');
insert into domains values (1, 'example.com');

UPDATE mailsettings ms, domains d 
  SET ms.value='No' 
  WHERE ms.domain_id = 
    (SELECT id 
     FROM domains 
     WHERE domain='example.com') 
  AND ms.mailaccount_id IS NULL 
  AND ms.program='spamfilter' 
  AND ms.keey='scan_incoming';

I can confirm this: We're having a lot of troubles with this bug (still on 4.1.11), as well with a similar one (#10442) even crashing the slaves completely. It looks like replication is pretty much broken in (newer?) 4.1 releases when it comes to subqueries/unnamed views...

I run into the same problem as Jeff
previous config: Master 4.0.17-standard -> Slave 4.1.8-max
new config:       Master 4.0.17-standard -> Slave 4.1.11-standard
new config lead to replication error "... was not locked with 'LOCK TABLES'..." on insert-, update- and replace-Statements. It seems only to affect MYISAM-tables. InnoDB-tables are not affected.
switched back to 4.1.8

We have hit this bug as well on OS X version 4.1.12 (MySQL build).  All tables involved are InnoDB, so it isn't limited to MYISAM.  This query broke replication consistently, though would run manually.

UPDATE hsu.pst_breakdown_output_pruned as a 
INNER JOIN hsu.pst_forecasts as b 
ON b.group_num = a.group_num 
INNER JOIN hsu.opt_constraint_impacts as c 
ON c.group_num = a.group_num 
and c.period = b.period 
SET a.short_forecast_on = b.short_bid, 
a.forecast_on = b.long_bid, 
a.option_forecast_on = b.option_bid, 
a.impact_on = (select sum(dollars) from hsu.opt_constraint_impacts 
where period = 1 and group_num = b.group_num group by group_num 
and dt < 2004-05-01) 
WHERE b.period = 1

Just adding my vote, we hit this problem, too.

Same here.  I did not see this bug and put in my own: http://bugs.mysql.com/bug.php?id=11220

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

Have added two test cases for this bug in mysql-test/t/rpl_multi_update3.test.
With the below fix for BUG#10442, this BUG#9361 is also solved:

--- 1.443/sql/sql_parse.cc      2005-06-16 16:11:40 -04:00
+++ 1.444/sql/sql_parse.cc      2005-06-17 11:15:25 -04:00
@@ -1943,6 +1943,14 @@
   if (tables || &lex->select_lex != lex->all_selects_list)
     mysql_reset_errors(thd);

+  /* When subselects or time_zone info is used in a query
+   * we create a new TABLE_LIST containing all referenced tables
+   * and set local variable 'tables' to point to this list. */
+  if ((&lex->select_lex != lex->all_selects_list ||
+       lex->time_zone_tables_used) &&
+      lex->unit.create_total_list(thd, lex, &tables))
+    DBUG_VOID_RETURN;

The fix will be present in 4.1.13.

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

Documented in 4.1.13 changelog; closed.

The ChangeLog describes this bug as: "An UPDATE query containing a subselect caused replication to fail."

I am experiencing this bug (in 4.1.12) with "INSERT IGNORE" statements (with no subqueries) and have also seen it with "ALTER TABLE".

I hope that the fix that has been committed will solve the problem for all cases and not just UPDATE?

PS: any idea when 4.1.13 will be releasing? :)

Hi Simon,

I hope this will solve your failures too. :-)

If you have some test cases for your failures (small SQL scripts), 
I can try them on our source tree for 4.1.13 and add them to the 
mysql-test series of tests.

Best wishes,
Lars