Re: multiple slave error: bogus data in log event

[Javier Cañete]

- servier_id:
master 5000
slave1 5005
slave2 5032

- GTID is enabled (this is something that we need too)

- mysql> show master status;
+------------------+-----------+--------------+------------------+---------------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+-----------+--------------+------------------+---------------------------------------------------+
| mysql-bin.000002 | 488400498 | | | ce433339-e9ef-11e5-82fd-fa163e711345:1-1275354193 |
+------------------+-----------+--------------+------------------+---------------------------------------------------+
1 row in set (0.00 sec)


SLAVE1
- mysql> show slave status \G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 172.16.125.226
Master_User: repl
Master_Port: 6612
Connect_Retry: 10
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 433842786
Relay_Log_File: relay-log-bin-channel1.000002
Relay_Log_Pos: 9774920
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: No
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
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: 366175098
Relay_Log_Space: 77442822
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: 37
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 1236
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'bogus data in log event; the first event 'mysql-bin.000002' at 356400498, the last event read from '/var/lib/mysql/binlog/mysql-bin.000002' at 433842786, the last byte read from '/var/lib/mysql/binlog/mysql-bin.000002' at 433842805.'
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 5000
Master_UUID: ce433339-e9ef-11e5-82fd-fa163e711345
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: System lock
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp: 160816 08:53:13
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: ce433339-e9ef-11e5-82fd-fa163e711345:1274854194-1275147535
Executed_Gtid_Set: ce433339-e9ef-11e5-82fd-fa163e711345:1-1255245135:1264404183-1266211883:1266404185-1267421698:1269904190-1270404189:1270404191-1270914748:1274804194-1274891218,
ef50a6c9-e9f2-11e5-8311-fa163e44d8b9:1-7
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name: channel1
Master_TLS_Version:
1 row in set (0.00 sec)


SLAVE2
mysql> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.125.226
Master_User: repl
Master_Port: 6612
Connect_Retry: 10
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 421988534
Relay_Log_File: relay-log-bin-channel1.000002
Relay_Log_Pos: 5116904
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
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: 361517082
Relay_Log_Space: 65588570
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: 33
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: 5000
Master_UUID: ce433339-e9ef-11e5-82fd-fa163e711345
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: System lock
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: ce433339-e9ef-11e5-82fd-fa163e711345:1274854194-1275102632
Executed_Gtid_Set: 7311b69a-e9f2-11e5-830e-fa163e32843c:1-9,
ce433339-e9ef-11e5-82fd-fa163e711345:1-1258230889:1264404183-1266032210:1266404185-1267355590:1267904187-1268904186:1268904188-1269032150:1269404189-1269450457:1270404191-1273504191:1273504193-1273804192:1273804194-1273867451:1274304194-1274873574
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name: channel1
Master_TLS_Version:
1 row in set (0.00 sec)



- Also if we start the slave with the error, it keeps working. Again, we think that since the slaves are not reading from the same position of the binlog (no concurrence readings), the error does not happen. When the slaves are up-to-date again and we run another test, we get the error again.