MySQL Group Replication Limitations: savepoints

on

Today in our series of articles related to MySQL Group Replication’s limitations, let’s have a quick look at Savepoints.

The manual is clear about this: Transaction savepoints are not supported.

The first thing to check then is if the application that will use our MySQL Group Replication Cluster is currently using savepoints.

We have two ways to find this, the first is using STATUS variables:

mysql> show global status like '%save%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Com_release_savepoint      | 2     |
| Com_rollback_to_savepoint  | 0     |
| Com_savepoint              | 4     |
| Handler_savepoint          | 0     |
| Handler_savepoint_rollback | 0     |
+----------------------------+-------+

So in our example above, it seems that the application might need some changes to remove those savepoints.

The second option is to use performance_schema:

mysql> SELECT event_name, count_star, sum_errors 
       FROM performance_schema.events_statements_summary_global_by_event_name
       WHERE event_name LIKE '%save%' AND count_star > 0 ;
+-------------------------------------+------------+------------+
| event_name                          | count_star | sum_errors |
+-------------------------------------+------------+------------+
| statement/sql/savepoint             |          4 |          2 |
| statement/sql/release_savepoint     |          2 |          2 |
+-------------------------------------+------------+------------+

So now that we know how to verify if the application was using savepoint, let’s verify what’s happening when savepoints are used in MySQL Group Replication:

mysql> start transaction;
mysql> select now();
...
mysql> savepoint fred;
ERROR 1290 (HY000): The MySQL server is running with the --transaction-write-set-extraction!=OFF 
                    option so it cannot execute this statement

transaction_write_set_extraction defines the algorithm used to hash the extracted writes that were made during a transaction. If you are using Group Replication, the process of extracting those writes from a transaction is crucial for conflict detection on all nodes part of the Group, but this also prevents us to use transaction savepoints as this statement is not compatible with write set extraction.

Update: this limitation has been removed on MySQL 5.7.19 and 8.0.1, see //lefred.be/content/mysql-group-replication-and-logical-backup/

4 thoughts on “MySQL Group Replication Limitations: savepoints

  1. Savepoints are also used when executing mysqldump with –single-transaction option.
    http://dev.mysql.com/doc/refman/5.7/en/mysqldump.html#option_mysqldump_single-transaction

    That means if you want to do a consistent non blocking mysqldump on a node of the group you need to put the node out of the cluster first.

    e.g.
    mysql> — Put the node out of the Group Replication cluster
    mysql> STOP group_replication;
    mysql> SET GLOBAL transaction_write_set_extraction=OFF;

    $ # Dump the entire node (instance)
    $ mysqldump –all-databases –triggers –routines –events –single-transaction > /mysqldump/dump_YYYYMMDD.sql

    mysql> — Bring back the node into the cluster
    mysql> SET GLOBAL transaction_write_set_extraction=XXHASH64;
    mysql> START group_replication;

    1. Hi Olivier,

      Thank you for this great addition in the explanation of the limitation’s consequence. I must admit I’m not a logical dump fan myself 😛

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

recent

Last Tweets

  • RT : 昨年開催されたOracle MySQL Innovation Day 2018 秋の模様がEnterpriseZineのDB Onlineに掲載されました / MySQL 8.0の新機能から寿司ビール問題まで――Oracle MySQL Innovation Day 2018 秋 via ,
  • If you have a ticket for the pre-FOSDEM Day and you are not able to join, please release your ticket, there are people on the waiting list ! Thank you in advance ! ,
  • When your laptop dies just before 2 weeks of conferences... ,
  • RT : EVENTS:  MySQL Day, Denver, CO Forget Skiing. On Tuesday, January 29, 2019 Denver is all about NoSQL + SQL = MySQL. Join us to learn about NoSQL+SQL, plus how to improve your database performance, secure your data and prevent downtime. Register today! ,
  • RT : MySQL is ubiquitious for cloud usage. In this series I will explore Oracle Cloud's IaaS offerings that support MySQL. In this particular post I will highlight IaaS Enterprise capabilities that help secure our cloud perimiter. ,
Locations of visitors to this page
categories