Announcement

Announcement Module
Collapse
No announcement yet.

Last_IO_Error: Got fatal error 1236

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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 :-

    mysql> show slave status\G ;

    [.....]

    Slave_IO_Running: No
    Slave_SQL_Running: Yes


    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 '
    [.....]
    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

    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.

  • #2
    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.

    Comment


    • #3
      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 !!

      Comment


      • #4
        Please also let me know the position of the 'binary log file' !!

        Thank you

        Comment


        • #5
          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.

          Comment


          • #6
            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

            Comment


            • #7
              Hello Yogesh,

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

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

              PHP Code:
              change master to MASTER_LOG_FILE='mysql-bin.000463'Master_Log_Pos=4
              That is actually a command, I realized it later and that is what lead to the confusion :P

              Thank you once again !!

              Comment


              • #8
                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;

                Comment


                • #9
                  Thank you so very much for the elaboration and clarification too

                  Highly appreciated !!

                  One last query though,

                  Where did you get the "Master_Log_Pos=4" from ?

                  Thank you

                  Comment


                  • #10
                    Where did you get the "Master_Log_Pos=4" from ?
                    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 /*!*/;
                    # 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 '
                    DyxQUQ8BAAAAZwAAAGsAAAABAAQANS41LjMwLWxvZwAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAAA
                    AAAAAAAAAAAAAAAAAAAPLFBREzgNAAgAEgAEBAQEEgAAVAAEGg gAAAAICAgCAA==
                    '/*!*/;
                    # at 107


                    see red color text.

                    Comment

                    Working...
                    X