| 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: | |
| 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: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.
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.