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/
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;
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 😛
I think you can use the new mysqlpump tools with single-transaction option to do a non blocking backup on GR;
[…] and GR: here and […]