Not the answer you need?
Register and ask your own question!

a replication error in 5.5.25a-log percona version

ayalastrikeayalastrike EntrantCurrent User Role Participant
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 '
pVdzUBNaIXMhSgAAAIo8jCwAAP0AAAAAAAEAB2t4X2luZm8ADXNfdXNlcl9s b2dvXzcADgMDAwMD
AwQDDwMHBwMPBQQKACAAAAA=
pVdzUBhaIXMh0AAAAFo9jCwAAP0AAAAAAAEADv////8AwIeRFQNgUH01yQAA AC8AAAAAAAAAtAAA
AGZauT5RAQAABGpwZWdYAgAAHANzUBwDc1AAAAAAIDFjMWZhNTdiYWQ4ZjJm ZDJlYjUyOWZlNWU0
YWE5ZjM1AMCHkRUDYFB9NckAAAAvAAAAAAAAALQAAABmWrk+UQEAAARqcGVn 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

Comments

  • ayalastrikeayalastrike Entrant Current User Role Participant
    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)?
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.