MySQL Bugs: #12616: CREATE TABLE fails to replicate unless there is a selected database.

Bug #12616	CREATE TABLE fails to replicate unless there is a selected database.
Submitted:	17 Aug 2005 6:54	Modified:	28 Sep 2005 11:14
Reporter:	David Nugent	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.9-beta-log	OS:	FreeBSD (FreeBSD 5.4)
Assigned to:		CPU Architecture:	Any

Description:
CREATE TABLE statements to not replicate when a table is created with a complete name including database, and the session has not yet selected a database.

How to repeat:
Two MySQL systems are configured to replicate to each other across all databases, excluding only 'mysql'; update/insert queries to each database will go to either one or the other but always the same host for each individual database.

On one of the systems (norminally the "master" since changes are being initiated there alone):

# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 58074 to server version: 5.0.9-beta-log

mysql> create database a;
Query OK, 1 row affected (0.00 sec)

mysql> create table a.a ( x int, y int );
Query OK, 0 rows affected (0.01 sec)

mysql> create database b;
Query OK, 1 row affected (0.00 sec)

mysql> create table b.a like a.a;
Query OK, 0 rows affected (0.00 sec)

Looking at the slave, after making sure that replication is up to date:

# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 76781 to server version: 5.0.9-beta-log

mysql> use a;
Database changed
mysql> show tables;
Empty set (0.00 sec)

mysql> use b;
Database changed
mysql> show tables;
Empty set (0.00 sec)

After using 'use a' on the master, creating tables in any database by absolute table name works as expected.

Suggested fix:
Always select a database before creating tables?

What replication options do you use on the slave?

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

(apologies, i must have missed an email somewhere)

Replication options being used are as I described which consist of only 2 statements:

replicate-ignore-db = mysql
replicate-ignore-db = test

I've just verified that this problem can be reproduced on 5.0.11-beta-log.

Not a bug, see http://dev.mysql.com/doc/mysql/en/replication-options.html

* --replicate-do-db=db_name

[...]
An example of what does not work as you might expect: If the slave is started with --replicate-do-db=sales and you issue the following statements on the master, the UPDATE statement is not replicated:

USE prices;
UPDATE sales.january SET amount=amount+1000;

If you need cross-database updates to work, use --replicate-wild-do-table=db_name.% instead.

The main reason for this “check-just-the-default-database” behavior is that it's difficult from the statement alone to know whether or not it should be replicated (for example, if you are using multiple-table DELETE or multiple-table UPDATE statements that go across multiple databases). It is also faster to check only the default database rather than all databases if there is no need.

Perhaps you did not read my post carefully enough, or I did not explain the situation well enough.

There are no --replicate-do-db options, only 2 --replicate-ignore-db.

Following the MySQL rules from the documented replication options, the queries should not have been discarded:

1. Are there any --replicate-do-db or --replicate-ignore-db rules?
-> No: Proceed to the next step.
2. Are we currently executing a stored procedure or function?
-> No: Proceed to the next step.
3. Are there any --replicate-*-table rules?
-> Yes: Proceed to the next step and begin evaluating the table rules
in the order shown (first the non-wild rules, and then the wild rules).
4. Are there any --replicate-do-table rules?
-> No: Proceed to the next step.
5. Are there any --replicate-ignore-table rules?
-> Yes: Does the table match any of them?
-> No: Proceed to the next step.
6. Are there any --replicate-wild-do-table rules?
-> No: Proceed to the next step.
7. Are there any --replicate-wild-ignore-table rules?
-> No: Proceed to the next step.
8. Is there another table to test against these rules?
-> Yes: Loop.
-> No: We have now tested all tables to be updated and could not match any rule.
-> Are there --replicate-do-table or --replicate-wild-do-table rules?
-> No: Execute the query and exit.

These queries are being dropped in error. At some point in this decision tree, MySQL is dropping the query, I suspect by erroneously testing something that depends on current database context rather than the context of the statement itself.

I find it very curious that the behaviour changes when a database is first selected which can be any database at all, even though it makes no difference at all to the context of the subsequent SQL statement since all tables are explicitly referenced via their database.

1. Are there any --replicate-do-db or --replicate-ignore-db rules?
->  No: Proceed to the next step.

Whoops, the correct decision here is Yes, but it makes no difference to the end result as it will go to the next step regardless.