| Bug #31725 | Slave runs replication queries in wrong schema | ||
|---|---|---|---|
| Submitted: | 20 Oct 2007 12:16 | Modified: | 23 Oct 2007 15:18 |
| Reporter: | Patrick Galbraith | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | MySQL Server: Replication | Severity: | S2 (Serious) |
| Version: | 5.0.22 | OS: | Any (ubuntu 6.0.1 LTS Dapper) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | 5.0.22-Debian_0ubuntu6.06.3-log | ||
[20 Oct 2007 12:22]
Patrick Galbraith
master my.cnf: # replication server-id = 1 #slave-skip-errors = all log-bin = /var/lib/mysql/bin.log log-bin-index = /var/lib/mysql/log-bin.index expire-logs-days = 5 auto_increment_increment = 10 auto_increment_offset = 1 slave my.cnf (I tried to use "wildcard" but it doesn't replicate if I do): # The following can be used as easy to replay backup logs or for replication. server-id = 2 expire-logs-days = 4 #max_binlog_size = 104857600 # replication log-error = /var/lib/mysql/error.log log-warnings = 2 #slave-skip-errors = all relay-log = /var/lib/mysql/relay.log relay-log-info-file = /var/lib/mysql/relay-log.info relay-log-index = /var/lib/mysql/relay-log.index auto_increment_increment = 10 auto_increment_offset = 2 master-host = host master-user = user master-password = pass #replicate-wild-do-table = grazrserver.* #replicate-wild-do-table = grazr_accounts.* #replicate-wild-ignore-table = test.* #replicate-wild-ignore-table = regression.* #replicate-wild-ignore-table = mysql.* replicate-do-db = grazr_accounts replicate-do-db = grazrserver
[20 Oct 2007 12:37]
Sveta Smirnova
Thank you for the report. But version 5.0.22 is quite old and many replication bugs have been fixed since. Please try with current version 5.0.45 and if problem still persists provide binary log file.
[22 Oct 2007 14:15]
Patrick Galbraith
Sveta, Spasibo - thank you for you reply. I was hoping to keep within the confines of Ubuntu's package management for MySQL, but there doesn't seem to be a deb package for 5.0.45, only 5.0.22, so I will try source compile. I'll test with that version, and note my findings in this bug report about the results.
[23 Oct 2007 14:24]
Patrick Galbraith
This bug is not a problem with latest 5.0.45. We will upgrade to 5.0.45. Also, bug #20614 is fixed in 5.0.45.
[23 Oct 2007 15:18]
Sveta Smirnova
Thank you for the feedback. The report closed as "Can't repeat" because last comment.
Description: I have a simple slave - master setup, and have found certain queries run on the slave in the wrong schema, which breaks replication, or if have 'slave-skip-errors=all', the error is ignored, and data is not matching on the slave. How to repeat: On the master using schema grazr_accounts: mysql> select count(*) from readinglist_feeds; +----------+ | count(*) | +----------+ | 16 | +----------+ 1 row in set (0.00 sec) On the slave, same schema: mysql> select count(*) from readinglist_feeds; +----------+ | count(*) | +----------+ | 16 | +----------+ 1 row in set (0.00 sec) On the master: mysql> delete from readinglist_feeds where hosted_files_id_fk = 21; Query OK, 5 rows affected (0.00 sec) On the slave: mysql> select count(*) from readinglist_feeds; +----------+ | count(*) | +----------+ | 14 | +----------+ 1 row in set (0.00 sec) mysql> show slave status \G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: sundar Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: bin.000001 Read_Master_Log_Pos: 8179228 Relay_Log_File: relay.000002 Relay_Log_Pos: 8179222 Relay_Master_Log_File: bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: No Replicate_Do_DB: grazr_accounts,grazrserver,grazr_accounts,grazrserver Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 1049 Last_Error: Error 'Unknown database '''' on query. Default database: 'grazrserver'. Query: 'delete from readinglist_feeds where hosted_files_id_fk = 21' Skip_Counter: 0 Exec_Master_Log_Pos: 8179091 Relay_Log_Space: 8179359 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: NULL 1 row in set (0.00 sec) mysql> So, this query is trying to be run, by replication, on 'grazrserver' schema Mysqlbinlog, of the relay log on the slave: #071020 8:10:25 server id 1 end_log_pos 8179228 Query thread_id=13 exec_time=0 error_code=0 use grazr_accounts; SET TIMESTAMP=1192882225; delete from readinglist_feeds where hosted_files_id_fk = 21; # End of log file ROLLBACK /* added by mysqlbinlog */; /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; Why would this end up being applied to the wrong schema?