Last_IO_Error: Got fatal error 1236

Hello Everyone,

My ‘master’ and the ‘replication’ server both restarted yesterday because of power outage.

Now after the things coming back to normal, My ‘replication’ server is not able to connect to the master and when i check the status on it, I see this error :-

I also tried to change the values to that of master in these 2 files ( relay-log.info & master.info ) on the replication server, as i could see some discrepancy in it…but still no luck :frowning:

I also copied the bin file (newcrmdb1-bin.000027) from the master server to the replication server , But still the same.

Can some one suggest a recourse ?

Thank you so much for your time.

After sudden reboot mysql rolled back last transactions in binary logs however slave already incremented its binary position so after master is up slave is not able to get correct binary position. To resolve this issue you need to point slave to new binary file created after the server reboot and mysql restart. The same issue happened to be just 2 days back. After setting new binary position I skipped few entries on slave which were updated on slave and rolled back in binary position.

add sync_binlog in your master my.cnf file to updated binary files during crashes.

mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 10.10.100.100
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000462
Read_Master_Log_Pos: 384546492
Relay_Log_File: mysql-relay-bin.001388
Relay_Log_Pos: 384546638
Relay_Master_Log_File: mysql-bin.000462
Slave_IO_Running: No
Slave_SQL_Running: Yes
Replicate_Do_DB: db_live
Replicate_Ignore_DB: mysql,information_schema,performance_schema
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: 384546492
Relay_Log_Space: 384546837
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
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: ‘Client requested master to start replication from impossible position; the first event ‘mysql-bin.000462’ at 384546492, the last event read from ‘./mysql-bin.000462’ at 4, the last byte read from ‘./mysql-bin.000462’ at 4.’
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
1 row in set (0.00 sec)

Change the binary file position on slave to new binary file mysql-bin.000463 in above case
change master to MASTER_LOG_FILE=‘mysql-bin.000463’, Master_Log_Pos=4;

Hope this will help you.

Hello Yogesh,

Appreciate your prompt reply.

I am a bit unclear as to what needs to be done on the SLAVE server , I am pasting the status below for your reference :-

=============
mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 10.222.1.218
Master_User: root
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: newcrmdb1-bin.000027
Read_Master_Log_Pos: 580324984
Relay_Log_File: DatabackupSRV-relay-bin.000043
Relay_Log_Pos: 4
Relay_Master_Log_File: newcrmdb1-bin.000027
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: 580324984
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: NULL
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: 'Client requested master to start replication from impossible position; the first event ‘newcrmdb1-bin.000027’ at 580324984, the ’

===============

Please let me know what in the above do i need to change ?

appreciate your clarification !!

Thank you for your time !!

Please also let me know the position of the ‘binary log file’ !!

Thank you

The next binary file would be newcrmdb1-bin.000028 after mysql restart, so change master to master_log_file=‘newcrmdb1-bin.000028’, master_log_pos=4.
I am sure this will work if you havent done much R&D on slave.

Yogesh,

These changes need to be done on the master server ?

How do i verify what is the current values for both variable on slave and master server ?

Secondly, What is the location of these 2 variables that i need to change ?

Please guide me a little !!

Thank you

Hello Yogesh,

Thank you for all the assistance, Well that really did help me :slight_smile:

I was initially confused with the command that you had mentioned above :stuck_out_tongue:

[PHP]change master to MASTER_LOG_FILE=‘mysql-bin.000463’, Master_Log_Pos=4;[/PHP]

That is actually a command, I realized it later :smiley: and that is what lead to the confusion :stuck_out_tongue:

Thank you once again !!

According to your show slave status output your slave was on binary file newcrmdb1-bin.000027 before the reboot, after the reboot master database incremented binary file to newcrmdb1-bin.000028 and a new starting position 4, so you need to start replication from this new position, so execute change master on slave server so that slave start replicating from new position

On slave server

slave> change master to MASTER_LOG_FILE=‘newcrmdb1-bin.000028’, Master_Log_Pos=4;
slave> start slave;

Thank you so very much for the elaboration and clarification too :slight_smile:

Highly appreciated !!

One last query though,

Where did you get the “Master_Log_Pos=4” from ?

Thank you :slight_smile:

4 is first binary position in mysql binary log file, if you process log file using mysqlbinlog utility you can see the output.

$ mysqlbinlog mysql-bin.000004

/!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1/;
/!40019 SET @@session.max_insert_delayed_threads=0/;
/!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0/;
DELIMITER /!/;
[COLOR=#FF0000]# at 4
#130325 16:20:55 server id 1 end_log_pos 107 Start: binlog v 4, server v 5.5.30-log created 130325 16:20:55 at startup

Warning: this binlog is either in use or was not closed properly.

ROLLBACK/!/;
BINLOG ’
DyxQUQ8BAAAAZwAAAGsAAAABAAQANS41LjMwLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAPLFBREzgNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA==
'/!/;

at 107

see red color text.