Some MySQL slave errors?

• Master version: mysql-server-5.5.14-1.el5.remi
• Slave version: Percona-XtraDB-Cluster-server-5.5.24-23.6.340
• Binlog format: ROW based

I’m using PRM to setup HA for MySQL.

Sometime, MySQL slave stopped with some errors:

Error executing row event: 'Table 'reportingdb.tvc_ads_tag_date' doesn't exist'

[Warning] Slave SQL: Could not execute Update_rows event on table reportingdb.7k_banner_channel_tmp; Can't find record in '7k_banner_channel_tmp', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000854, end_log_pos 859094925, Error_code: 1032

120828 0:36:13 [Warning] Slave SQL: Could not execute Write_rows event on table reportingdb.7k_bookings_ver; Duplicate entry '1518' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.001022, end_log_pos 509479, Error_code: 1062

Error 'Error in list of partitions to DROP' on query. Default database: 'reportingdb'. Query: 'ALTER TABLE v3_ban_date_cpm7k DROP PARTITION pcurrent_201298'

and the mysql.sock is missing but the MySQL processes still exist. Pacemaker cannot restart due to the below errors:

InnoDB: Unable to lock ./ibdata1, error: 11
InnoDB: Check that you do not already have another mysqld process
InnoDB: using the same InnoDB data or log files.

I have to login via TCP (-h 127.0.0.1) and stop MySQL first, then let corosync start MySQL again.

In summary, the question is:

1. How do I solve the above slave errors and prevent them from happening in the future?
2. Why is the mysql.sock missing? How do I make the Pacemaker better handle this situation?

It’s better not to skip by hundreds. Just loop one-by-one:

until mysql -e "show slave status\G;" | grep -i "Slave_SQL_Running: Yes";do
  mysql -e "stop slave; SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; start slave;";
  sleep 1;
done

It now actually solves the reason, but it’s nice automatization for problem fixing. 

– 

4

just stop the slave using

mysql> stop slave;

Then set the global variable sql_slave_skip_counter to 1, for example:

mysql> set global sql_slave_skip_counter=1;

Then start the slave:

mysql> start slave;

Then check if the slave is working or not:

mysql> show slave status \G;

If the error is still there, set a bigger value in sql_slave_skip_counter like:

mysql> set global sql_slave_skip_counter=1000;

Again, check the status of the slave.

If you find the skip_sql value is non zero in the slave status then stop the slave again and do:

mysql> set global sql_slave_skip_counter=0;
mysql> start slave;

Share

Improve this answerFollow

edited Sep 19, 2012 at 12:33

Hannah Vernon – On Strike♦

69.2k2222 gold badges165165 silver badges308308 bronze badges

answered Sep 10, 2012 at 5:05

vijay

5111 bronze badge

• 6

skip slave counter is not something to take lightly. It is ONLY OK to use it when familiar with the context of the statement that failed replication and accepting that skipping it is an acceptable data drift on the slave. But as a general answer this is a way to break integrity of the slave as a true replica of the master 

– TechieGurlJan 18, 2013 at 3:07

• Dose set global sql_slave_skip_counter=1000; means ignore 1000 records or more? If i do so how much of my data is missed on my slave replication? – shgnInc Mar 1, 2015 at 20:03

source : https://dba.stackexchange.com/questions/23921/some-mysql-slave-errors