Bug #25082 default database change on trigger execution breaks replication
Submitted: 14 Dec 2006 21:28 Modified: 11 Apr 2007 1:40
Reporter: Kolbe Kegel Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S2 (Serious)
Version:5.0.30 OS:Any
Assigned to: Alexander Nozdrin CPU Architecture:Any
Tags: replication, triggers

[14 Dec 2006 21:28] Kolbe Kegel
Description:
Upon trigger execution, the default database is apparently changed in such a way that replication can be broken if these conditions apply:

1. the default database selected on the master does not exist on the slave (db1)
2. the slave has a replicate-wild-do-table rule for the database explicitly referenced in the statement (db2)
3. the slave has a trigger on db2.t1
4. a statement is executed, with default database set to db1, that explicitly updates db2.t1

When the trigger finishes executing, it tries to reset the default database to the value before the trigger started (db1). That database does not exist on the slave, so an error is thrown, the transaction rolls back, and the slave SQL thread stops. 

Workarounds:

1. Avoid cross-database updates
2. Create an empty database (db1) on the slave

How to repeat:
Establish a replication setup such that the slave is running with --replicate-wild-do-table=db2.%

ON MASTER:

create database db1;
create database db2;

use db2;

create table db2.t1 (id int);
create table db2.t2 (id int unsigned not null auto_increment primary key, t timestamp);
create trigger t1_upd before update on db2.t1 for each row insert into db2.t2 () values ();

use db1;
insert into db2.t1 values (1),(2),(3);
update db2.t1 set id=5;

ON SLAVE:

SHOW SLAVE STATUS\G

Transcript:

mysql (m)> create database db1;
Query OK, 1 row affected (0.00 sec)

mysql (m)> create database db2;
Query OK, 1 row affected (0.01 sec)

mysql (m)> use db2;
Database changed

mysql (m)> create table db2.t1 (id int);
Query OK, 0 rows affected (0.01 sec)

mysql (m)> create table db2.t2 (id int unsigned not null auto_increment primary key, t timestamp);
Query OK, 0 rows affected (0.03 sec)

mysql (m)> create trigger t1_upd before update on db2.t1 for each row insert into db2.t2 () values ();
Query OK, 0 rows affected (0.00 sec)

mysql (m)> use db1;
Database changed
mysql (m)> insert into db2.t1 values (1),(2),(3);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql (m)> update db2.t1 set id=5;
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql (s)> show slave status\G
*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: 127.0.0.1
                Master_User: root
                Master_Port: 3332
              Connect_Retry: 60
            Master_Log_File: beluga-bin.000001
        Read_Master_Log_Pos: 860
             Relay_Log_File: beluga-relay-bin.000002
              Relay_Log_Pos: 914
      Relay_Master_Log_File: beluga-bin.000001
           Slave_IO_Running: Yes
          Slave_SQL_Running: No
            Replicate_Do_DB:
        Replicate_Ignore_DB:
         Replicate_Do_Table:
     Replicate_Ignore_Table:
    Replicate_Wild_Do_Table: db2.%
Replicate_Wild_Ignore_Table:
                 Last_Errno: 1049
                 Last_Error: Error 'Unknown database 'db1'' on query. Default database: 'db2'. Query: 'update db2.t1 set id=5'
               Skip_Counter: 0
        Exec_Master_Log_Pos: 776
            Relay_Log_Space: 998
            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: NULL
1 row in set (0.00 sec)

Suggested fix:
Server should either handle the case where the default database does not exist or take some other action to avoid changing the default database to a database that does not exist.
[14 Dec 2006 21:48] Kolbe Kegel
Andrei pointed out that it's not necessary to set up the replicate-wild-do-table rule to repeat this, since apparently the slave will allow the replication thread to set the default to a database that doesn't exist, as long as that database is not needed to satisfy any DML or DDL operations.

So, this problem can be repeated without any replicate-* rules, as long as the "db1" database does not exist on the slave.
[17 Dec 2006 15:31] Andrei Elkin
The reason of the failure is error procuded by
mysql_change_db called by epilog of trigger's execution to restore the default db.
On slave, currently it's acceptable not to have such db where there would be no
operations executed.
However trigger's handling is not aware of where execution has been passed to it from.
[23 Mar 2007 22:25] 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/22844

ChangeSet@1.2489, 2007-03-23 16:33:31+03:00, anozdrin@booka.opbmk +8 -0
  Fix BUG#25082: default database change on trigger execution
  breaks replication.
  
  Basically, the problem was that we could not switch back to
  the database that does not exist anymore.
  
  The fix is to always succeed changing database if we are called
  from stored-routine execution.
[27 Mar 2007 17:51] 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/23056

ChangeSet@1.2422, 2007-03-27 21:55:01+04:00, anozdrin@alik.opbmk +8 -0
  Fix for BUG#25082: default database change on trigger
  execution breaks replication.
  
  When a stored routine is executed, we switch current
  database to the database, in which the routine
  has been created. When the stored routine finishes,
  we switch back to the original database.
  
  The problem was that if the original database does not
  exist (anymore) after routine execution, we raised an error.
  
  The fix is to report a warning, and switch to the NULL database.
[3 Apr 2007 23:24] Konstantin Osipov
Pushed into 5.0.40 and 5.1.18
[11 Apr 2007 1:40] Paul Dubois
Noted in 5.0.40, 5.1.18 changelogs.

Restoration of the default database after stored routine or trigger
execution on a slave could cause replication to stop if the database
no longer existed.
[18 Apr 2007 15:46] Bugs System
Pushed into 5.1.18-beta
[18 Apr 2007 15:47] Bugs System
Pushed into 5.0.42
[26 Apr 2007 19:29] Paul Dubois
Moved the 5.0.40 changelog entry to 5.0.42.