Bug #74253 gtid_next with empty transactions does not work inside stored procedures
Submitted: 7 Oct 2014 16:14 Modified: 26 Jun 2015 14:54
Reporter: Davi Arnaut (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Utilities: Binlog Events Severity:S3 (Non-critical)
Version:5.6 OS:Any
Assigned to: CPU Architecture:Any
Tags: empty transaction, gtid_next

[7 Oct 2014 16:14] Davi Arnaut
Description:
Setting gtid_next inside a stored procedure and committing an empty transaction does not work.

This makes it quite hard to automate functionality that depends on the use of gtid_next.

How to repeat:
mysql> delimiter |
mysql> CREATE PROCEDURE p()
    -> BEGIN
    ->   SET @@SESSION.GTID_NEXT='01010101-0101-0101-0101-010101010101:1';
    ->   START TRANSACTION;
    ->   COMMIT;
    ->   SET @@SESSION.GTID_NEXT='AUTOMATIC';
    -> END|
Query OK, 0 rows affected (0.00 sec)

mysql> CALL p()|
ERROR 1790 (HY000): @@SESSION.GTID_NEXT cannot be changed by a client that owns a GTID. The client owns 01010101-0101-0101-0101-010101010101:1. Ownership is released on COMMIT or ROLLBACK.

Suggested fix:
Move the check inside mysql_parse (see code around gtid_empty_group_log_and_cleanup) to somewhere else where it is not associated with parsing a query.
[7 Oct 2014 16:23] Miguel Solorzano
Thank you for the bug report. Verified as described.
[26 Jun 2015 14:54] David Moss
The following was noted in the 5.7.8 changelog:
Setting gtid_next inside a stored procedure and committing an empty transaction caused an error. This was due to the empty transaction not being correctly logged to consume the GTID specified by setting gtid_next.