MySQL Group Replication Limitations: savepoints
Mysql 09-Jan-2017

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.