| Bug #40013 | mixed replication: row based format could lead to stale tmp tables on the slave | ||
|---|---|---|---|
| Submitted: | 14 Oct 2008 10:18 | Modified: | 18 Mar 2009 15:21 |
| Reporter: | Alexander Y. Fomichev | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Replication | Severity: | S2 (Serious) |
| Version: | 5.1.26+, 5.1, 6.0 bzr | OS: | Any |
| Assigned to: | li zhou | CPU Architecture: | Any |
| Tags: | Contribution, temporary tables mixed row cleanup | ||
[14 Oct 2008 14:12]
Alexander Y. Fomichev
> as far as temporary tables just ignored in row based format, > temporary tables created in statement mode will not been droped > at cleanup time, beacaus close_temporary_tables checks only > current_stmt_binlog_row_based. Ugh, i was clean... for me only, i'm afraid, sorry :( temporary tables created in statement mode will not been droped *on the slave* because close_temporary_tables checks only current_stmt_binlog_row_based and so skip insertion of DROP TABLE instructions into binlog.
[14 Oct 2008 20:28]
Sveta Smirnova
Thank you for the report. Verified as described.
[12 Jan 2009 10:33]
Susanne Ebrecht
Bug #41969 was set as duplicate of this bug here.
[16 Feb 2009 9:49]
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/66444 2793 Leonard Zhou 2009-02-16 Bug#40013 Mixed replication can work for the operations of row based temp table. After we do 'insert into t1 select UUID()', the current format is changed to 'row' but the default format is still 'MIXED'. So we have problems when we do some operations after that clause. eg: can't create tmp table in slave... Our solution is to allow create/drop table if the default/current format is mixed/row and clear row based format flag.
[18 Feb 2009 8:55]
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/66732 2793 Leonard Zhou 2009-02-18 Bug#40013 mixed replication: row based format could lead to stale tmp tables on the slave In mixed mode, if we create a temporary table and do some update which switch to ROW format, the format will keep in ROW format. So when the session is end, temp table can't be dropped because at cleanup time we checks only current binlog format and so skip insertion of DROP TABLE instructions into binlog. Our solution is that when closing temp tables at cleanup time we write DROP TABLE into binlog if current binlog format is ROW but in MIX mode.
[23 Feb 2009 3:34]
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/67130 2793 Leonard Zhou 2009-02-23 Bug#40013 mixed replication: row based format could lead to stale tmp tables on the slave. In mixed mode, if we create a temporary table and do some update which switch to ROW format, the format will keep in ROW format until the session ends or the table is dropped explicitly. When the session ends, the temp table is dropped automaticly at cleanup time. but it checks only current binlog format and so skip insertion of DROP TABLE instructions into binlog. So the temp table can't be dropped correctly at slave. Our solution is that when closing temp tables at cleanup time we check both binlog format and binlog mode, and we could write DROP TABLE instructions into binlog if current binlog format is ROW but in MIX mode.
[13 Mar 2009 19:03]
Bugs System
Pushed into 5.1.33 (revid:joro@sun.com-20090313111355-7bsi1hgkvrg8pdds) (version source revid:azundris@mysql.com-20090224070618-mr7stu6rfcvoj18g) (merge vers: 5.1.33) (pib:6)
[15 Mar 2009 10:52]
Jon Stephens
Documented in the 5.1.33 changelog as follows:
When using MIXED replication format and temporary tables were
created in statement-based mode, but a later operation in the same
session caused a switch to row-based mode, the temporary tables were
not dropped on the slave at the end of the session.
This bug was introduced by the fix for Bug #20499.
See also Bug #43046.
Set bug status to NDI pending merge of fix to the 6.0 tree.
[18 Mar 2009 13:20]
Bugs System
Pushed into 6.0.11-alpha (revid:joro@sun.com-20090318122208-1b5kvg6zeb4hxwp9) (version source revid:azundris@mysql.com-20090224072212-51w0xg6doju2drup) (merge vers: 6.0.10-alpha) (pib:6)
[18 Mar 2009 15:21]
Jon Stephens
Fix also documented in the 6.0.11 changelog; closed.
[9 May 2009 16:40]
Bugs System
Pushed into 5.1.34-ndb-6.2.18 (revid:jonas@mysql.com-20090508185236-p9b3as7qyauybefl) (version source revid:jonas@mysql.com-20090508100057-30ote4xggi4nq14v) (merge vers: 5.1.33-ndb-6.2.18) (pib:6)
[9 May 2009 17:38]
Bugs System
Pushed into 5.1.34-ndb-6.3.25 (revid:jonas@mysql.com-20090509063138-1u3q3v09wnn2txyt) (version source revid:jonas@mysql.com-20090508175813-s6yele2z3oh6o99z) (merge vers: 5.1.33-ndb-6.3.25) (pib:6)
[9 May 2009 18:35]
Bugs System
Pushed into 5.1.34-ndb-7.0.6 (revid:jonas@mysql.com-20090509154927-im9a7g846c6u1hzc) (version source revid:jonas@mysql.com-20090509073226-09bljakh9eppogec) (merge vers: 5.1.33-ndb-7.0.6) (pib:6)
Description: IIUC: In mixed mode master could create temporary table being in STATEMENT binlog-format and switching to ROW just after. If so binlog will stay in row mod till the end of session (including TNX::cleanup at the and). as far as temporary tables just ignored in row based format, temporary tables created in statement mode will not been droped at cleanup time, beacaus close_temporary_tables checks only current_stmt_binlog_row_based. void close_temporary_tables(THD *thd) .... if (!mysql_bin_log.is_open() || thd->current_stmt_binlog_row_based) How to repeat: on the master: bowb ~ # mysql -e 'DROP TABLE IF EXISTS test; CREATE TABLE test (c CHAR(48))' test; bowb ~ # for i in `seq 5`;do mysql -e 'CREATE TEMPORARY TABLE test_tmp (c CHAR(48));INSERT INTO test SELECT UUID()' test;done bowb ~ # for i in `seq 5`;do mysql -e 'CREATE TEMPORARY TABLE test_tmp (c CHAR(48));INSERT INTO test SELECT UUID()' test;done on the slave: mysql> SHOW STATUS LIKE "Slave_open_temp_tables"\G *************************** 1. row *************************** Variable_name: Slave_open_temp_tables Value: 0 1 row in set (0.01 sec) mysql> SHOW STATUS LIKE "Slave_open_temp_tables"\G *************************** 1. row *************************** Variable_name: Slave_open_temp_tables Value: 5 1 row in set (0.00 sec) mysql> SHOW STATUS LIKE "Slave_open_temp_tables"\G *************************** 1. row *************************** Variable_name: Slave_open_temp_tables Value: 10 1 row in set (0.00 sec) Suggested fix: oops, i couldn't find something better then just clear_current_stmt_binlog_row_based() juts before close_temporary_tables (if initial log format was mixed) and hopes that *IF EXISTS* save me [as for temporary tables which created under 'row' binlog format] diff -urNp mysql-5.1.28-a/sql/sql_class.cc mysql-5.1.28-b/sql/sql_class.cc --- mysql-5.1.28-a/sql/sql_class.cc 2008-08-28 19:40:58.000000000 +0400 +++ mysql-5.1.28-b/sql/sql_class.cc 2008-10-14 11:18:48.256196550 +0400 @@ -844,6 +844,8 @@ void THD::cleanup(void) mysql_ha_cleanup(this); delete_dynamic(&user_var_events); hash_free(&user_vars); + if (variables.binlog_format == BINLOG_FORMAT_MIXED) + clear_current_stmt_binlog_row_based(); close_temporary_tables(this); my_free((char*) variables.time_format, MYF(MY_ALLOW_ZERO_PTR)); my_free((char*) variables.date_format, MYF(MY_ALLOW_ZERO_PTR));