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

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.

Thank you for the bug report. Verified as described.

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.

Page generated in 0.052 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.