MySQL Bugs: #62639: Slave server erroneously replicates DROP temporary table statement

Bug #62639	Slave server erroneously replicates DROP temporary table statement
Submitted:	6 Oct 2011 10:27	Modified:	27 Jan 2012 16:16
Reporter:	Cosmin Sanda	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S3 (Non-critical)
Version:	5.5.16 MySQL Community Server	OS:	Linux
Assigned to:		CPU Architecture:	Any
Tags:	configuration, flush, replication, temporary tables

Description:
Having configured a slave to replicate a single database from the master server, the binary logs generated by the slave contain DROP IF EXISTS statements pointing to a different database not included in the replication. This potentially can potentially break another slave for which the first slave acts as a master.
There are no traces of CREATE TEMPORARY TABLE statements in relationship with the DROP STATEMENTS in the binary logs.
It should be noted that the temporary tables are created on the master at the beginning of a lengthy process in which time the slave is flushing its logs with FLUSH BINARY LOGS. Note that even ignoring the temporary tables with replicate-ignore-table and making sure that the databases are not used interchangeably does not help in avoiding the DROP STATEMENTS to end up in the slave's binary logs.

How to repeat:
Having a replication set-up, create a lengthy process in which a temporary table is created at the beginning. The temporary table is to be created on a database that is not replicated.
During the process, FLUSH BINARY LOGS of the slave. At the end of the process, one of the binary logs will contain DROP STATEMENTS for the temporary table that should not have been replicated. No traces of CREATE TEMPORARY TABLE whatsoever.

Please, send my.cnf file content from master.

MASTER my.cnf

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
old_passwords=1
log-bin=mysql-bin
server-id=1
max_connections=2048
key_buffer_size=1024000000
myisam_sort_buffer_size=256000000
read_rnd_buffer_size = 2000000
join_buffer_size = 4000000
read_buffer_size = 2000000
sort_buffer_size = 16000000
table_cache = 6000
thread_cache_size = 200
max_allowed_packet = 32000000
query_cache_limit = 2000000
query_cache_size = 128000000
query_cache_type = 1
tmp_table_size = 220000000
tmpdir=/dev/shm

log-slow-queries=/var/lib/mysql/slowqueries.log
long_query_time=20

max_connect_errors=100000

[mysql.server]
user=mysql
basedir=/var/lib

[mysqld_safe]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
open-files-limit=8192

PRIMARY SLAVE my.cnf

[mysqld]
log-bin=mysql-bin
server-id=20001
max_allowed_packet=128M
max_connections=2048
table_cache = 6000
replicate-wild-do-table=my_db1.%
max_connect_errors=100000

open-files-limit=8192
thread_cache_size = 1800

Thank you for the feedback.

I can not repeat described behavior. Please try with current version 5.5.19 and if problem still exists provide us binary log with problem statement.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".