Bug #52282 Solution for the replication of CREATE IF NOT EXISTS and DROP IF EXISTS commands
Submitted: 22 Mar 2010 18:42
Reporter: SINISA MILIVOJEVIC Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Replication Severity:S4 (Feature request)
Version:5.1+ OS:Any
Assigned to: Assigned Account CPU Architecture:Any

[22 Mar 2010 18:42] SINISA MILIVOJEVIC
Description:
This feature request is created in order to make possible better solution then the one that will be implemented in order to fix a bug #47442.

Although this is about fixing a buggy behaviour, this is still a feature request, because this solution required the introduction of some new fields in the replication event's metadata.

The problem arises in the case of:

CREATE TABLE IF NOT EXISTS ...

and 

DROP TABLE IF EXISTS ....

and when the (non)existence of the entity differs between master and slave. Entity can exist on the master, but not on the slave, or the other way around.

This also covers the case for CREATE ... SELECT ... as DDL part has to be logged separately without SELECT part (see bug #47442).

How to repeat:
Run :

CREATE TABLE IF NOT EXISTS....

or

DROP TABLE IF EXISTS ...

in the case when entity exist on either master or the slave.

Suggested fix:
What we need is a new flag in replication event metadata, for all DDL's 
which contain IF EXISTS, IF NOT EXISTS clauses. Then, if the result of 
this clause mismatches with result(s) on any slave compared to the same 
one on the master, replication should be stopped. I don't think that our 
server should handle all out-of-sync situations by itself, id est automatically. 

In order for this to function, DDL or the DDL part of the statement has to be logged irrespective of whether the entity exists on master or not, which means irrespective if command has executed or not.

This also means that for CREATE ... SELECT... , SELECT part should be written in the error message (in slave status) and should be run manually by the DBA, if deemed necessary. That means that we would need a number of new error messages which would verbosely describe the situation and the fix, for any of the mismatches. That is one more reason why this is a feature request.

There are other variants of this solution possible. I would be more then glad to discuss them.