MySQL Bugs: #12408: Regression of bug #9361: Replication breaks when using SUBSELECTS

Bug #12408	Regression of bug #9361: Replication breaks when using SUBSELECTS
Submitted:	6 Aug 2005 4:03	Modified:	16 Sep 2005 18:49
Reporter:	Jang Kim	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S1 (Critical)
Version:	4.1.13	OS:	Linux (Linux CentOS)
Assigned to:	MySQL Verification Team	CPU Architecture:	Any

Description:
There seems to be a regression resulting from the fix made in bug #9361.  I had a similar master -> slave setup that was failing in MySQL 4.1.11 in the exact same way.  So after seeing this bug, I upgraded to 4.1.13 to fix the issue.

The scenario described in bug #9361 no longer causes the replication slave to error out, but I ran into unusual DELETE errors downstream.

To test the bug fix, I used the exact same SQL verification script that Lars Thalmann posted to the bug.  Executing 'SHOW SLAVE STATUS' on the slave shows that there was no error, but executing the following SQL showed that the slave wasn't able to update the table:

mysql> select * from mailsettings;
+-------+-----------+----------------+------------+---------------+
| value | domain_id | mailaccount_id | program    | keey          |
+-------+-----------+----------------+------------+---------------+
| Yes   |         1 |           NULL | spamfilter | scan_incoming |
+-------+-----------+----------------+------------+---------------+
1 row in set (6.45 sec)

The mailsettings.value should be 'No'.  Executing the same UPDATE statement directly on the slave works properly as expected.

To test my scenario, I added the SQL statement 'DELETE FROM domains WHERE id = 1;' to the end of the original SQL verification script to see if it produced a similar error as the one I saw.

A 'SHOW SLAVE STATUS' on the slave reveals:

Slave_IO_Running: Yes
Slave_SQL_Running: No
Last_Errno: 1099
Last_Error: Error 'Table 'domains' was locked with a READ lock and can't be updated' on query. Default database: 'testrepl'. Query: 'DELETE FROM domains WHERE id = 1'

How to repeat:
Run the following SQL verification script:

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';

DELETE FROM domains WHERE id = 1;

Then check the slave db for errors.  Query both tables to see that the values were not modified.

Suggested fix:
The expected outcome is that the mailsettings.value is set to 'No' and the domains record is deleted.

The bug category should be "Replication".

I was unable to repeat with current 4.1.15 source servers:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.15-debug-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

master>grant all on *.* to 'replyguy'@'localhost' identified by 'thereplyguy';
Query OK, 0 rows affected (0.00 sec)

master>show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| hegel-bin.000003 |      180 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 4.1.15-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

slave>CHANGE MASTER TO
    -> MASTER_HOST='localhost',
    -> MASTER_USER='replyguy',
    -> MASTER_PASSWORD='thereplyguy',
    -> MASTER_LOG_FILE='hegel-bin.000003',
    -> MASTER_LOG_POS=180;
Query OK, 0 rows affected (0.16 sec)

slave>start slave;
Query OK, 0 rows affected (0.00 sec)

slave>show slave status\G
*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: localhost
                Master_User: replyguy
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: hegel-bin.000003
        Read_Master_Log_Pos: 180
             Relay_Log_File: hegel-relay-bin.000001
              Relay_Log_Pos: 47
      Relay_Master_Log_File: hegel-bin.000003
           Slave_IO_Running: Yes
          Slave_SQL_Running: Yes
            Replicate_Do_DB: 
        Replicate_Ignore_DB: 
         Replicate_Do_Table: 
     Replicate_Ignore_Table: 
    Replicate_Wild_Do_Table: 
Replicate_Wild_Ignore_Table: 
                 Last_Errno: 0
                 Last_Error: 
               Skip_Counter: 0
        Exec_Master_Log_Pos: 180
            Relay_Log_Space: 47
            Until_Condition: None
             Until_Log_File: 
              Until_Log_Pos: 0
         Master_SSL_Allowed: No
         Master_SSL_CA_File: 
         Master_SSL_CA_Path: 
            Master_SSL_Cert: 
          Master_SSL_Cipher: 
             Master_SSL_Key: 
      Seconds_Behind_Master: 0
1 row in set (0.00 sec)

master>use test
Database changed
master>drop table if exists mailsettings;
Query OK, 0 rows affected, 1 warning (0.00 sec)

master>drop table if exists domains;
Query OK, 0 rows affected, 1 warning (0.01 sec)

master>
master>create table mailsettings (
    ->   value           char(30),
    ->   domain_id       int,
    ->   mailaccount_id  int,
    ->   program         char(30),
    ->   keey            char(30)
    -> );
Query OK, 0 rows affected (0.07 sec)

master>
master>create table domains (
    ->   id              int,
    ->   domain          char(30)
    -> );
Query OK, 0 rows affected (0.07 sec)

master>
master>insert into mailsettings values ('Yes', 1, NULL, 'spamfilter',
    -> 'scan_incoming');
Query OK, 1 row affected (0.01 sec)

master>insert into domains values (1, 'example.com');
Query OK, 1 row affected (0.00 sec)

master>
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.keey='scan_incoming';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

master>
master>DELETE FROM domains WHERE id = 1;
Query OK, 1 row affected (0.01 sec)

slave>show slave status\G
*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: localhost
                Master_User: replyguy
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: hegel-bin.000003
        Read_Master_Log_Pos: 1193
             Relay_Log_File: hegel-relay-bin.000001
              Relay_Log_Pos: 1060
      Relay_Master_Log_File: hegel-bin.000003
           Slave_IO_Running: Yes
          Slave_SQL_Running: Yes
            Replicate_Do_DB: 
        Replicate_Ignore_DB: 
         Replicate_Do_Table: 
     Replicate_Ignore_Table: 
    Replicate_Wild_Do_Table: 
Replicate_Wild_Ignore_Table: 
                 Last_Errno: 0
                 Last_Error: 
               Skip_Counter: 0
        Exec_Master_Log_Pos: 1193
            Relay_Log_Space: 1060
            Until_Condition: None
             Until_Log_File: 
              Until_Log_Pos: 0
         Master_SSL_Allowed: No
         Master_SSL_CA_File: 
         Master_SSL_CA_Path: 
            Master_SSL_Cert: 
          Master_SSL_Cipher: 
             Master_SSL_Key: 
      Seconds_Behind_Master: 0
1 row in set (0.00 sec)

slave>use test;
Database changed
slave>show tables;
+----------------+
| Tables_in_test |
+----------------+
| domains        |
| mailsettings   |
+----------------+
2 rows in set (0.01 sec)