Bug #69324 mysql issues delete statement in log for memory table when executing query
Submitted: 27 May 2013 8:46 Modified: 21 Jul 2014 19:43
Reporter: Gary Liu Email Updates:
Status: Not a Bug Impact on me:
Category:MySQL Server: Replication Severity:S3 (Non-critical)
Version:5.5.22 OS:Linux
Assigned to: CPU Architecture:Any

[27 May 2013 8:46] Gary Liu
I want to ignore memory tables replication with step 2 setting in my.cnf, but if i execute the sql statement in step 5, the delete statement for memory tables(step 6) will appear in binary log.
How do i solve this problem? Can i add "replicate-ignore-db=db2" to ignore the replication in slave. Is there any other way to fix it?

Third paragraph of functionality added or changed
Replication: For replication of MEMORY (HEAP) tables: Made the master automatically write a DELETE FROM statement to its binary log when a MEMORY table is opened for the first time since the master's startup. This is for the case where the slave has replicated a nonempty MEMORY table, and then the master is shut down and restarted: the table is now empty on the master; the DELETE FROM empties it on the slave as well.

How to repeat:
Steps to reproduce:
create database db1;
use db1;
create table tb1(col1 int primary key, col2 int);

create database db2;
use db2;
create table tb1(col1 int primary key, col2 int) ENGINE=MEMORY;

binlog-ignore-db = db2

/etc/init.d/mysql restart

mysql -u root -p[PASSWORD] db1; 

SELECT * FROM information_schema.key_column_usage;

check if there is below sql statement in binary log:
DELETE FROM `db2`.`tb1`
[21 Jul 2014 19:43] Sveta Smirnova
Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Please read at http://dev.mysql.com/doc/refman/5.5/en/replication-rules-db-options.html how MySQL evaluates replication filtering rules.