| 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: | |
| Category: | MySQL Server: Replication | Severity: | S1 (Critical) |
| Version: | 5.0.37-community-log | OS: | Linux |
| Assigned to: | CPU Architecture: | Any | |
[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.
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.