Bug #48184 some options to CREATE TABLE should not be loggeed
Submitted: 20 Oct 2009 13:53
Reporter: Sven Sandberg Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Replication Severity:S2 (Serious)
Version:5.1+ OS:Any
Assigned to: Assigned Account CPU Architecture:Any
Tags: CREATE TABLE, replication
Triage: Triaged: D2 (Serious)

[20 Oct 2009 13:53] Sven Sandberg
Description:
CREATE TABLE has some options that are not good to replicate:

 - DATA DIRECTORY and INDEX DIRECTORY refer to directories that may only exist on master

 - WITH PARSER: if the parser is a plugin, it may only exist on the master (note that INSTALL PLUGIN is not replicated). This also applies to ALTER TABLE.

How to repeat:
==== rpl_create_table.test ====
--source include/have_simple_parser.inc
--source include/master-slave.inc

INSTALL PLUGIN simple_parser SONAME 'mypluglib.so';
CREATE TABLE t1 (a TEXT, FULLTEXT(a) WITH PARSER simple_parser);
--eval CREATE TABLE t2 (a INT) DATA DIRECTORY = '$MYSQLTEST_VARDIR'
--eval CREATE TABLE t3 (a INT) INDEX DIRECTORY = '$MYSQLTEST_VARDIR'

# Contains references to plugins and paths that may only exist on
# master.
SHOW BINLOG EVENTS;

DROP TABLE t1, t2, t3;
UNINSTALL PLUGIN simple_parser;

==== rpl_create_table-master.opt ====
$SIMPLE_PARSER_OPT

Suggested fix:
For DATA DIRECTORY and INDEX DIRECTORY, I think the master can just strip the directory from the query before logging it.

For WITH PARSER <plugin>, it is not clear what to do. Here are some options:

 (1) Log a warning, allow creating the table, and include the WITH PARSER option in the binlog.

 (2) Log a warning, allow creating the table, and strip WITH PARSER from the query before writing to the binlog.

 (3) Generate an error and don't execute the statement. Allow WITH PARSER only if SQL_LOG_BIN=0.
[19 May 2010 14:01] Rolf Martin-Hoster
I second the motion regarding data directory and index directory as it breaks replication.