When setting up or maintaining a MariaDB replication environment, one of the common issues encountered is replication failure due to a fatal error,  specifically error code 1236. This issue often results in the slave not being able to catch up with the master due to an incorrect log position.

In this case, the error message is: Client Requested Master to Start Replication from Impossible Position

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.001477' at 169472755, the last event read from 'mysql-bin.001477' at 4, the last byte read from 'mysql-bin.001477' at 4.'

This error usually means the slave has requested to start replicating from a position in the binary log that is no longer available or incorrect.

Running the following command will give key details from the slave’s status:

show slave status \G;

The Slave_IO_Running is No, indicating that the IO thread, which reads from the master’s binary logs, is not running due to the error. To fix the error, we need to set the slave to the next available binary log file and position. 

This can be done by executing the following steps:

1. Stop the Slave: Before making any changes, stop the replication process:

stop slave;

2. Check the Master’s Binary Log Status: On the master server, use the following command to check the current log file and position:

show master status\G;

This will produce output similar to the following;

*************************** 1. row ***************************
            File: mysql-bin.001478
        Position: 490214447
    Binlog_Do_DB:
Binlog_Ignore_DB: “mysql”
1 row in set (0.000 sec)

ERROR: No query specified

Take note of the master log file value obtained from the master server, in this case, ‘mysql-bin.001478’.

3. Identify the Correct Log File and Position: From the error, it’s clear that the master log file mysql-bin.001477 and position 169472755 are no longer valid. The next log file mysql-bin.001478 is available, we can adjust the slave to start replication from this file and the start of the log (position 4).

The command to change the master log file and position:

CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.001478', MASTER_LOG_POS=4;

4. Restart the slave: Once the master log file and position have been adjusted, restart the replication process:

start slave;

5. Verify Replication Status: After starting the slave, verify that both Slave_IO_Running and Slave_SQL_Running are set to Yes. You can use:

show slave status \G;

Ensure there are no more errors in the Last_IO_Error field, and Seconds_Behind_Master should ideally start showing a value.

The 1236 error can occur due to incorrect or missing log files or positions in replication. By advancing the slave to the next available binary log file and position, as shown, you can often resolve the issue. Regularly monitoring replication status and ensuring proper binary log retention on the master will help avoid similar issues in the future.

Need help fixing MariaDB Replication Error 1236? Our expert team is here to assist you! Contact us for reliable support and troubleshooting solutions to resolve this error quickly and efficiently.