MySQL Group Replication Limitations: savepoints


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 //

4 thoughts on “MySQL Group Replication Limitations: savepoints

  1. Savepoints are also used when executing mysqldump with –single-transaction option.

    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.

    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.


Last Tweets

  • Next week at ,
  • No Ping Will Tear Us Apart – Enabling member auto-rejoin in Group Replication !,
  • RT : 「三谷 さんのセッション、すべて良かったんですが特に良かったのが、 MySQL InnoDB ClusterのConsistency Levelのまとめ 」 「この辺は地雷キュアことyoku0825さんか運用キュアの三谷さんにおススメパターンを導いてほしい!(お前がやれ)」w  ,
  • RT : 【フォトスポット📸まとめ】 会場 フォトスポットにて撮影、投稿いただいた皆さんありがとうございました🙌 (投稿いただいた画像を使用させていただきました🙇‍♀️) 次回は今回よりも! 皆さんに楽しんでもらえる何かをご用意できればと思っております😃 ,
  • RT : Going to Live Austin but need a guide on what to do and see while there? Here is your guide on bats, breakfast, and BBQ! and be sure to drop by the kiosk @mysql_community,
Locations of visitors to this page