| Bug #61260 | MySQL slave thread is not applying changes to slave DB | ||
|---|---|---|---|
| Submitted: | 23 May 2011 8:48 | Modified: | 16 Jan 2013 16:49 |
| Reporter: | zohar aharoni | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: Replication | Severity: | S2 (Serious) |
| Version: | 5.5.8 | OS: | Linux (Centos5) |
| Assigned to: | Matthew Lord | CPU Architecture: | Any |
| Tags: | Exec_master_log_pos, replication, slave thread | ||
[23 May 2011 8:54]
Valeriy Kravchuk
Please, check if you can read that event from master binary log and relay log on slave (using mysqlbinlog). Maybe event is corrupted somehow...
[23 May 2011 9:06]
zohar aharoni
Yes, I can read the event on both files (master bin-log and relay-log)...
[27 Dec 2011 10:23]
Konstantin Vlasov
hi, i have some problem, any ideas?
[27 Dec 2011 10:28]
Konstantin Vlasov
I suggest that problem only appear in case of use Replicate_Ignore_DB option,
my 'show slave status':
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.10.31
Master_User: user1
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000605
Read_Master_Log_Pos: 189265512
Relay_Log_File: mysqld-relay.000019
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.000605
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: regru
Replicate_Ignore_DB: %
Replicate_Do_Table: xxx.table1
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 189265512
Relay_Log_Space: 106
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: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
[30 Dec 2011 18:50]
Valeriy Kravchuk
Are you sure that events coming from master are NOT related to that DB (or databases) that you asked to ignore? Please, check.
[1 Jan 2012 16:41]
Konstantin Vlasov
yea, i'm sure, because after reinitialize slave database replication work fine, and events going from master complitely apply to slave database, but later replication stops without any error.
[16 Feb 2012 23:11]
Trey 85Stang
I have experienced the same issue. I had a slave server that was 5 days behind due to this exact same issue. Unfortunately I was only monitoring seconds behind master which was happily sitting at 0 for 5 days. I have added binary log position monitoring to help. There was nothing in the logs on either the master or the slave. I did had a replicate ignore for information_schema and mysql though.
[16 Feb 2012 23:13]
Trey 85Stang
Forgot to mention this was with a 2 server(CentOS 5) master/master configuration each running mysql-5.1.36. If I can provide any further information let me know what it could be.
[16 Jan 2013 16:49]
Matthew Lord
We're sorry, but the bug system is not the appropriate forum for asking help on using MySQL products. Your problem is not the result of an obvious bug. Support on using our products is available both free in our forums at http://forums.mysql.com/ and for a reasonable fee direct from our skilled support engineers at http://www.mysql.com/support/ Thank you for your interest in MySQL.
Description: Hi, I have replication setup with 5 slaves. The master is 5.1.37 and all the slaves are 5.5.8. One of the slaves stopped working: Both IO thread and SQL thread are working as you can see below in "show slave status", but the SQL thread is not really applying the changes. There are no errors in the log file. On the slave: mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: master-db Master_User: replica Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.020833 Read_Master_Log_Pos: 358537711 Relay_Log_File: research-relay-bin.000002 Relay_Log_Pos: 252 Relay_Master_Log_File: mysql-bin.020828 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: db1,db2,db3,db4 Replicate_Ignore_DB: db5,db6 Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 975734937 Relay_Log_Space: 4573247637 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: 448291 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 300 1 row in set (0.00 sec) mysql> show processlist; +----+-----------------+-----------+------+---------+--------+----------------------------------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+-----------------+-----------+------+---------+--------+----------------------------------+------------------+ | 3 | event_scheduler | localhost | NULL | Daemon | 669 | Waiting for next activation | NULL | | 21 | system user | | NULL | Connect | 486 | Waiting for master to send event | NULL | | 22 | system user | | NULL | Connect | 426334 | Reading event from the relay log | NULL | | 33 | root | localhost | NULL | Query | 0 | NULL | show processlist | +----+-----------------+-----------+------+---------+--------+----------------------------------+------------------+ On the master: mysql> show processlist; 85218 replica research.com:54914 NULL Binlog Dump 713 Writing to net NULL How to repeat: Don't know