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:
None 
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:16] Patrick Galbraith
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?
[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.