GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

a replication error in 5.5.25a-log percona version

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

  • a replication error in 5.5.25a-log percona version

    here i have 1 master 2 slave, they use the same mysql version(5.5.25a-log percona version).

    Then slave 2 break down, i used the bak files from slave 1 (using mysqldump), and then imported to slave 2
    when imported complete, then set slave 2 to catch up master, but replication error happened.

    mysql> show slave status\G
    *************************** 1. row ***************************
    Slave_IO_State: Waiting for master to send event
    Master_Host: 192.168.56.119
    Master_User: repl
    Master_Port: 3306
    Connect_Retry: 60
    Master_Log_File: mysql-bin.000090
    Read_Master_Log_Pos: 246003112
    Relay_Log_File: relay-bin.000002
    Relay_Log_Pos: 267860420
    Relay_Master_Log_File: mysql-bin.000087
    Slave_IO_Running: Yes
    Slave_SQL_Running: No
    Replicate_Do_DB:
    Replicate_Ignore_DB:
    Replicate_Do_Table:
    Replicate_Ignore_Table:
    Replicate_Wild_Do_Table:
    Replicate_Wild_Ignore_Table:
    Last_Errno: 1032
    Last_Error: Could not execute Update_rows event on table kx_info.s_user_logo_7; Can't find record in 's_user_logo_7', Error_code: 1032; handler error HA_ERR_END_OF_FILE; the event's master log mysql-bin.000087, end_log_pos 747388250
    Skip_Counter: 0
    Exec_Master_Log_Pos: 747387897
    Relay_Log_Space: 2987705016
    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: 0
    Last_IO_Error:
    Last_SQL_Errno: 1032
    Last_SQL_Error: Could not execute Update_rows event on table kx_info.s_user_logo_7; Can't find record in 's_user_logo_7', Error_code: 1032; handler error HA_ERR_END_OF_FILE; the event's master log mysql-bin.000087, end_log_pos 747388250
    Replicate_Ignore_Server_Ids:
    Master_Server_Id: 561193306

    Then i used mysqlbinlog to check in master machine
    /usr/local/mysql/bin/mysqlbinlog mysql-bin.000087 -vv --start-position=747387897 | less
    # at 747387968
    # at 747388042
    #121009 6:45:57 server id 561193306 end_log_pos 747388042 Table_map: `kx_info`.`s_user_logo_7` mapped to number 253
    #121009 6:45:57 server id 561193306 end_log_pos 747388250 Update_rows: table id 253 flags: STMT_END_F

    BINLOG '
    pVdzUBNaIXMhSgAAAIo8jCwAAP0AAAAAAAEAB2t4X2luZm8ADX NfdXNlcl9s b2dvXzcADgMDAwMD
    AwQDDwMHBwMPBQQKACAAAAA=
    pVdzUBhaIXMh0AAAAFo9jCwAAP0AAAAAAAEADv////8AwIeRFQNgUH01yQAA AC8AAAAAAAAAtAAA
    AGZauT5RAQAABGpwZWdYAgAAHANzUBwDc1AAAAAAIDFjMWZhNT diYWQ4ZjJm ZDJlYjUyOWZlNWU0
    YWE5ZjM1AMCHkRUDYFB9NckAAAAvAAAAAAAAALQAAABmWrk+UQ EAAARqcGVn WAIAABwDc1ClV3NQ
    AQAAACAxYzFmYTU3YmFkOGYyZmQyZWI1MjlmZTVlNGFhOWYzNQ ==
    '/*!*/;
    ### UPDATE kx_info.s_user_logo_7
    ### WHERE
    ### @1=51745159 /* INT meta=0 nullable=0 is_null=0 */
    ### @2=897405024 /* INT meta=0 nullable=0 is_null=0 */
    ### @3=201 /* INT meta=0 nullable=0 is_null=0 */
    ### @4=47 /* INT meta=0 nullable=0 is_null=0 */
    ### @5=0 /* INT meta=0 nullable=0 is_null=0 */
    ### @6=180 /* INT meta=0 nullable=0 is_null=0 */
    ### @7=0.362018 /* FLOAT meta=4 nullable=0 is_null=0 */
    ### @8=337 /* INT meta=0 nullable=0 is_null=0 */
    ### @9='jpeg' /* VARSTRING(10) meta=10 nullable=0 is_null=0 */
    ### @10=600 /* INT meta=0 nullable=0 is_null=0 */
    ### @11=1349714716 /* TIMESTAMP meta=0 nullable=0 is_null=0 */
    ### @12=1349714716 /* TIMESTAMP meta=0 nullable=0 is_null=0 */
    ### @13=0 /* INT meta=0 nullable=0 is_null=0 */
    ### @14='1c1fa57bad8f2fd2eb529fe5e4aa9f35' /* VARSTRING(32) meta=32 nullable=0 is_null=0 */
    ### SET
    ### @1=51745159 /* INT meta=0 nullable=0 is_null=0 */
    ### @2=897405024 /* INT meta=0 nullable=0 is_null=0 */
    ### @3=201 /* INT meta=0 nullable=0 is_null=0 */
    ### @4=47 /* INT meta=0 nullable=0 is_null=0 */
    ### @5=0 /* INT meta=0 nullable=0 is_null=0 */
    ### @6=180 /* INT meta=0 nullable=0 is_null=0 */
    ### @7=0.362018 /* FLOAT meta=4 nullable=0 is_null=0 */
    ### @8=337 /* INT meta=0 nullable=0 is_null=0 */
    ### @9='jpeg' /* VARSTRING(10) meta=10 nullable=0 is_null=0 */
    ### @10=600 /* INT meta=0 nullable=0 is_null=0 */
    ### @11=1349714716 /* TIMESTAMP meta=0 nullable=0 is_null=0 */
    ### @12=1349736357 /* TIMESTAMP meta=0 nullable=0 is_null=0 */
    ### @13=1 /* INT meta=0 nullable=0 is_null=0 */
    ### @14='1c1fa57bad8f2fd2eb529fe5e4aa9f35' /* VARSTRING(32) meta=32 nullable=0 is_null=0 */
    # at 747388250

    at the sametime, the data in master/slave1/slave2

    mysql> select * from s_user_logo_7 where uid=51745159 and pid=897405024;
    +------+----------+-----------+------+-----+-------+------+- ---------+-------+--------+--------+---------------------+-- -------------------+------------+--------------------------- -------+
    |HOST | uid | pid | logo | top | lleft | size | zoom | width | format | height | ctime | mtime | commentnum | md5 |
    +------+----------+-----------+------+-----+-------+------+- ---------+-------+--------+--------+---------------------+-- -------------------+------------+--------------------------- -------+
    |MASTER| 51745159 | 897405024 | 201 | 47 | 0 | 180 | 0.362018 | 337 | jpeg | 600 | 2012-10-09 00:45:16 | 2012-10-09 06:45:57 | 1 | 1c1fa57bad8f2fd2eb529fe5e4aa9f35 |
    +------+----------+-----------+------+-----+-------+------+- ---------+-------+--------+--------+---------------------+-- -------------------+------------+--------------------------- -------+
    |SLAVE1| 51745159 | 897405024 | 201 | 47 | 0 | 180 | 0.362018 | 337 | jpeg | 600 | 2012-10-09 00:45:16 | 2012-10-09 06:45:57 | 1 | 1c1fa57bad8f2fd2eb529fe5e4aa9f35 |
    +------+----------+-----------+------+-----+-------+------+- ---------+-------+--------+--------+---------------------+-- -------------------+------------+--------------------------- -------+
    |SLAVE2| 51745159 | 897405024 | 201 | 47 | 0 | 180 | 0.362018 | 337 | jpeg | 600 | 2012-10-09 00:45:16 | 2012-10-09 00:45:16 | 0 | 1c1fa57bad8f2fd2eb529fe5e4aa9f35 |
    +------+----------+-----------+------+-----+-------+------+- ---------+-------+--------+--------+---------------------+-- -------------------+------------+--------------------------- -------+

    i don't know why the error happened.
    When i skip this record, the same error will occur again, and only happened in s_user_logo table.

    Reference,

    binlog_format=mixed
    isolation_level=read_committed

    Table: s_user_logo_7
    Create Table: CREATE TABLE `s_user_logo_7` (
    `uid` int(11) NOT NULL DEFAULT '0' COMMENT 'SPLIT_KEY',
    `pid` int(11) NOT NULL DEFAULT '0',
    `logo` int(11) NOT NULL DEFAULT '0',
    `top` int(11) NOT NULL DEFAULT '0',
    `lleft` int(11) NOT NULL DEFAULT '0',
    `size` int(11) NOT NULL DEFAULT '0',
    `zoom` float NOT NULL DEFAULT '0',
    `width` int(11) NOT NULL DEFAULT '0',
    `format` varchar(10) NOT NULL DEFAULT '',
    `height` int(11) NOT NULL DEFAULT '0',
    `ctime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
    `mtime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `commentnum` int(11) NOT NULL DEFAULT '0',
    `md5` varchar(32) NOT NULL DEFAULT '',
    KEY `uid` (`uid`,`ctime`),
    KEY `pid` (`pid`),
    KEY `md5` (`md5`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1

  • #2
    i used the old mysqldump(5.0.56 v10.11) to generate bak files from slave 1.
    is it the root cause?
    should use the mysqldump (5.5.25a v10.13)?

    Comment

    Working...
    X