Bug #28877 Wrong database replication
Submitted: 4 Jun 2007 14:44 Modified: 7 Jun 2007 9:42
Reporter: Pascal Rolle Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Replication Severity:S1 (Critical)
Version:5.0.37-community-log OS:Linux
Assigned to: CPU Architecture:Any

[4 Jun 2007 14:44] Pascal Rolle
Description:
Hi,
I am getting a weird problem about replication.
Show slave status \G show these informations :

            Replicate_Do_DB: awm_commun,awm_commun
        Replicate_Ignore_DB: awm_admin,awm_appli,awm_inv,awm_itsv,awm_lic,awm_magic,awm_mon,mysql,tmp,awm_admin,awm_appli,awm_inv,awm_itsv,awm_lic,awm_magic,awm_mon,mysql,tmp
         Replicate_Do_Table:
     Replicate_Ignore_Table:
    Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
                 Last_Errno: 1146
                 Last_Error: Error 'Table 'awm_inv.inv_show_wks_live_4578_pcasares' doesn't exist' on query. Default database: 'awm_commun'. Query: 'insert into awm_inv.inv_show_wks_live_4578_pcasares values (...

So i think the master sent statements about awm_inv (which he shouldnt replicate) because he thinks the statement is for awm_commun ("Default database : 'awm_commun'").

Note :
 - the table is a temporary table.
 - I know i have all databases names in double but i dont know why and (after tests), it seems not to be bad for the replication (but if you know how to fix this, it would be cool)
 - here is (a part of) the conf for this server :
#----------------------------------------------------------
# configuration des db/tables repliquees en tant que maitre
#----------------------------------------------------------
binlog-do-db=awm_admin
binlog-do-db=awm_appli
binlog-do-db=awm_commun
binlog-do-db=awm_inv
binlog-do-db=awm_itsv
binlog-do-db=awm_lic
binlog-do-db=awm_magic
binlog-do-db=awm_mon

binlog-ignore-db=mysql
binlog-ignore-db=tmp

#----------------------------------------------------------
# configuration des db/tables repliquees en tant qu esclave
#----------------------------------------------------------
replicate-ignore-db=awm_admin
replicate-ignore-db=awm_appli
replicate-do-db=awm_commun
replicate-ignore-db=awm_inv
replicate-ignore-db=awm_itsv
replicate-ignore-db=awm_lic
replicate-ignore-db=awm_magic
replicate-ignore-db=awm_mon

replicate-ignore-db=mysql
replicate-ignore-db=tmp

How to repeat:
No clue.
[5 Jun 2007 7:12] Sveta Smirnova
Thank you for the report.

Please find master binary log which contains broken query, analyze it with help of mysqlbinlog program and check if there is USE awm_commun or USE awm_commun statement before wrong query. If you find it this is not MySQL bug. Otherwise upload binary log file to we can examine it. Also read carefully about how replication-do-* options work at http://dev.mysql.com/doc/refman/5.0/en/replication-options.html and http://dev.mysql.com/doc/refman/5.0/en/replication-rules.html
[5 Jun 2007 8:59] Pascal Rolle
Since the default database was awm_commun, i am sure, there was a use statement. Anyway, since my insert statement contains the database name, i expect MySQL replication process to analyze this sql statement (to find the embeded database's name in the sql statement).
I understand the importance of the "default database" but i think the user's sql statement is more important (should have a greater precendence) since it may contain the database name.
So, i might be wrong but, for me, it is a MySQL bug.
[5 Jun 2007 9:33] Sveta Smirnova
Thank you for the feedback.

But according to our documentation it is not a bug. See also "replicate-wild-do-table" hint at http://dev.mysql.com/doc/refman/5.0/en/replication-options.html
[7 Jun 2007 9:42] Pascal Rolle
My bad. I didnt see it. I also didnt find a workaround for this, is there one ?
I mean if i want to be able to insert into A.a when default database is B, how do i do ? Is there no way for this ?
[11 Oct 2007 2:38] James Day
Pascal, you should use the table rules if you want to do that. Those are more recently introduced and are generally closer to the behavior that people expect.