Mysql – Slave replication stops with Last_SQL_Errno: 1032

amazon-linuxMySQLmysql-replicationmysql5.5

I have added an extra Slave server to an existing MySQL Replication. The Master server and the old Slave server are working fine without any issue, but the newly added server is stoping with the following error:

Last_SQL_Errno: 1032
Last_SQL_Error: Could not execute Update_rows event on table xxx.email_events; Can't find record in 'email_events', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysqld-bin.000410, end_log_pos 368808733

It will be fine for some hours after repairing.

Questions

Can we permanently skip Last_SQL_Errno: 1032?
Is there any issue with skipping this error?

Best Answer

You can locate the sql clause code like /usr/bin/mysqlbinlog -v --start-position=142743807 --stop-position=147399325 /data/mysql/data/master-bin.000010 > temp.log

Then compare slave and master database difference according to temp.log on specific pos. Then update slave database.

Then skip that line with mysql -e "stop slave; SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; start slave;";

Related Solutions

Mysql – Is replication from MySQL 5.5 master to a 5.1 slave possible

What you are trying to do is not generally supported. Quote from the Replication Compatibility Between MySQL Versions docs:

MySQL supports replication from one major version to the next higher major version. For example, you can replicate from a master running MySQL 4.1 to a slave running MySQL 5.0, from a master running MySQL 5.0 to a slave running MySQL 5.1, and so on.

and

Replication from newer masters to older slaves may be possible, but is generally not supported.

It might work for those versions, but it's far from a recommended setup. My opinion: don't do it.

Also read the Upgrading a Replication Setup

In other words, when upgrading to MySQL 5.5, the slaves must be MySQL 5.5 before you can upgrade the master to 5.5.

(and maybe this snippet although I don't think it's directly relevant if you re-import to 5.1)

Downgrading a replication setup to a previous version cannot be done once you have switched from statement-based to row-based replication, and after the first row-based statement has been written to the binlog.

MySQL binlog format dilemma

I assume you did this on one setup without cleaning up the slave and setting it up from scratch.

As the error message for the statement based replication tells you your application used some commands which can't be replicated using statement based replication. An example is a query like

INSERT INTO t (t) VALUES(NOW())

where NOW() will return different values when executed on the master and on the slave.

By doing this you have different data on the master and slave. Which is bad as depending on your slave your clients will read different data and subsequent writes will change other data, so you end up with data which is even more different.

Now you switch over to mixed replication, which might use row-based replication for some statements. With RBR you really need the exact same data as it has a hard time to identify the rows which were changed and update them.

So what should you do? - Configure your master to use mixed logging and then setup the slave using a consistent snapshot.

Best Answer

Related Solutions

Mysql – Is replication from MySQL 5.5 master to a 5.1 slave possible

MySQL binlog format dilemma

Related Topic