Friday, August 15, 2008

MySQL server_errno=1236 problem and resolution


mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: test
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000045
Read_Master_Log_Pos: 78736775
Relay_Log_File: localhost-relay-bin.000032
Relay_Log_Pos: 78736912
Relay_Master_Log_File: mysql-bin.000045
Slave_IO_Running: No
Slave_SQL_Running: Yes
Replicate_Do_DB: search_production,dw_am
Replicate_Ignore_DB: mysql
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: 78736775
Relay_Log_Space: 78736912
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)

Check mysql error log file, get below error info:
080815 12:13:44 [ERROR] Error reading packet from server: Client requested master to start replication from impossible position ( server_errno=1236)
080815 12:13:44 [ERROR] Got fatal error 1236: 'Client requested master to start replication from impossible position' from master when reading data from binary log
080815 12:13:44 [Note] Slave I/O thread exiting, read up to log 'mysql-bin.000045', position 78736775

Because master bin log file size is 78736164, but slave want to read 78736775, cause mysql 1236 error number, This problem caused by log flush exception.

-rw-rw---- 1 mysql mysql 78736164 Aug 15 10:18 mysql-bin.000045

This problem can be resolved using below method:

mysql> STOP SLAVE;
mysql> CHANGE MASTER TO MASTER_LOG_FILE=[NEXT BIN LOG FILE], MASTER_LOG_POS=98;
mysql> START SLAVE;

[NEXT BIN LOG FILE] is 'mysql-bin.000046' in this problem, MASTER_LOG_POS value is always 98 or 4.

No comments :