MySQL Group Replication Limitations: savepoints

on
Share on FacebookShare on Google+Tweet about this on TwitterShare on LinkedIn

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 http://lefred.be/content/mysql-group-replication-and-logical-backup/

Share on FacebookShare on Google+Tweet about this on TwitterShare on LinkedIn

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 *

recent

Last Tweets Last Tweets

Locations of visitors to this page
categories