DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Refcards Trend Reports Events Over 2 million developers have joined DZone. Join Today! Thanks for visiting DZone today,
Edit Profile Manage Email Subscriptions Moderation Admin Console How to Post to DZone Article Submission Guidelines
View Profile
Sign Out
Refcards
Trend Reports
Events
Zones
Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Partner Zones AWS Cloud
by AWS Developer Relations
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Partner Zones
AWS Cloud
by AWS Developer Relations

Trending

  • Turbocharge Ab Initio ETL Pipelines: Simple Tweaks for Maximum Performance Boost
  • How Agile Works at Tesla [Video]
  • A React Frontend With Go/Gin/Gorm Backend in One Project
  • What Is Envoy Proxy?

Trending

  • Turbocharge Ab Initio ETL Pipelines: Simple Tweaks for Maximum Performance Boost
  • How Agile Works at Tesla [Video]
  • A React Frontend With Go/Gin/Gorm Backend in One Project
  • What Is Envoy Proxy?
  1. DZone
  2. Data Engineering
  3. Databases
  4. Another reason why SQL_SLAVE_SKIP_COUNTER is bad in MySQL

Another reason why SQL_SLAVE_SKIP_COUNTER is bad in MySQL

Peter Zaitsev user avatar by
Peter Zaitsev
·
Jul. 24, 13 · Interview
Like (0)
Save
Tweet
Share
5.57K Views

Join the DZone community and get the full member experience.

Join For Free

This post comes from Jervin Real at the MySQL Performance Blog.

It is everywhere in the world of MySQL that if your replication is broken because an event caused a duplicate key or a row was not found and it cannot be updated or deleted, then you can use ‘ STOP SLAVE;SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;START SLAVE; ’ and be done with it. In some cases this is fine and you can repair the offending row or statements later on. But what if the statement is part of a multi-statement transaction? Well, then it becomes more interesting, because skipping the offending statement will cause the whole transaction to be skipped. This is well documented in the manual by the way. So here’s a quick example.

3 rows on the master:

master> select * from t;
+----+-----+
| id | pid |
+----+-----+
|  1 |   1 |
|  2 |   2 |
|  3 |   3 |
+----+-----+
3 rows in set (0.00 sec)

2 on the slave:

slave> select * from t;
+----+-----+
| id | pid |
+----+-----+
|  1 |   1 |
|  3 |   3 |
+----+-----+
2 rows in set (0.00 sec)

Execute a transaction on the master to break replication:

master> BEGIN;
Query OK, 0 rows affected (0.00 sec)
master> DELETE FROM t WHERE id = 1;
Query OK, 1 row affected (0.00 sec)
master> DELETE FROM t WHERE id = 2;
Query OK, 1 row affected (0.00 sec)
master> DELETE FROM t WHERE id = 3;
Query OK, 1 row affected (0.00 sec)
master> COMMIT;
Query OK, 0 rows affected (0.01 sec)

Broken slave:

slave> show slave status \G
*************************** 1. row ***************************
...
Last_SQL_Errno: 1032
Last_SQL_Error: Could not execute Delete_rows event on table test.t; Can't find record in 't', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000002, end_log_pos 333
...
1 row in set (0.00 sec)

An attempt to fix replication only caused bigger inconsistencies on slave:

slave> STOP SLAVE; SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE;
Query OK, 0 rows affected (0.00 sec)
slave> select * from t;
+----+-----+
| id | pid |
+----+-----+
|  1 |   1 |
|  3 |   3 |
+----+-----+
2 rows in set (0.00 sec)

This happens because the replication honors transaction boundaries, and is definitely something you should consider the next time you try to use this workaround on a broken slave. Of course, there is pt-table-checksum and pt-table-sync to rescue you when inconsistencies occur, however, prevention is always better than cure. Make sure to put safeguards in place to prevent your slaves from drifting.

Lastly, the example above is for ROW-based replication as my colleague pointed out, but can similarly happen with STATEMENT for example with a duplicate key error.  You can optionally fix the error above by temporarily setting slave_exec_mode to IDEMPOTENT so errors because of missing rows are skipped, but then again, it does not apply in all cases like an UPDATE statement that cannot be applied because the row on the slave is missing.

Here is a demonstration of the problem with STATEMENT-based replication:

master> select * from t;
+----+-----+
| id | pid |
+----+-----+
|  4 |   1 |
|  6 |   3 |
+----+-----+
2 rows in set (0.00 sec)
slave> select * from t;
+----+-----+
| id | pid |
+----+-----+
|  4 |   1 |
|  5 |   2 |
|  6 |   3 |
+----+-----+
3 rows in set (0.00 sec)
master> BEGIN;
Query OK, 0 rows affected (0.00 sec)
master> delete from t where id = 4;
Query OK, 1 row affected (0.00 sec)
master> insert into t values (5,2);
Query OK, 1 row affected (0.00 sec)
master> delete from t where id = 6;
Query OK, 1 row affected (0.00 sec)
master> COMMIT;
Query OK, 0 rows affected (0.15 sec)
slave> show slave status \G
*************************** 1. row ***************************
...
               Last_SQL_Errno: 1062
               Last_SQL_Error: Error 'Duplicate entry '5' for key 'PRIMARY'' on query. Default database: 'test'. Query: 'insert into t values (5,2)'
...
1 row in set (0.00 sec)
slave> stop slave; set global sql_slave_skip_counter = 1; start slave;
Query OK, 0 rows affected (0.05 sec)
slave> select * from t;
+----+-----+
| id | pid |
+----+-----+
|  4 |   1 |
|  5 |   2 |
|  6 |   3 |
+----+-----+
3 rows in set (0.00 sec)




MySQL

Published at DZone with permission of Peter Zaitsev, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

Trending

  • Turbocharge Ab Initio ETL Pipelines: Simple Tweaks for Maximum Performance Boost
  • How Agile Works at Tesla [Video]
  • A React Frontend With Go/Gin/Gorm Backend in One Project
  • What Is Envoy Proxy?

Comments

Partner Resources

X

ABOUT US

  • About DZone
  • Send feedback
  • Careers
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 600 Park Offices Drive
  • Suite 300
  • Durham, NC 27709
  • support@dzone.com

Let's be friends: