Bug #84748 Request transaction GTID in OK packet on COMMIT (without needing a round-trip).
Submitted: 31 Jan 2017 16:57 Modified: 6 Feb 2017 7:28
Reporter: Jean-François Gagné Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Replication Severity:S4 (Feature request)
Version:5.7 OS:Any
Assigned to: CPU Architecture:Any
Triage: Needs Triage: D5 (Feature request)

[31 Jan 2017 16:57] Jean-François Gagné
Description:
Hi,

currently, the only way to get the GTID of the last transaction is by enabling session_track_gtids ([1]) and using the 2 following connector functions:

- mysql_session_track_get_first ([2])
- and mysql_session_track_get_next ([3]).

[1]: https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_session_track_...

[2]: https://dev.mysql.com/doc/refman/5.7/en/mysql-session-track-get-first.html

[3]: https://dev.mysql.com/doc/refman/5.7/en/mysql-session-track-get-next.html

Having the GTID in the OK packet is better than getting it from a session variable (Bug#84747) as it saves a round-trip to the server.  However, to enable this performance gain, session_track_gtids needs to be enabled at the global level, which wastes resources when this information is not needed.  Setting session_track_gtids at the session level is not very useful, as it needs a round-trip to the server, which would be the same as reading a session variable.

To remove this problem, would you please consider implementing a way to tell MySQL at the statement level that the GTID of the last transaction should be put in the OK packet ?  Something like an optimizer hint would do.  I could suggest the following:

- COMMIT /* session_track_gtids = OWN_GTID */;
- INSERT /* session_track_gtids = OWN_GTID */ ...;
- UPDATE ... DELETE ...
- ALTER TABLE /* session_track_gtids = OWN_GTID */ ...;
- ...

Note that a hint on COMMIT is not enough, one should be able to get the last GTID in auto-commit mode and for DDL.

Many thanks,

JFG

How to repeat:
This is a feature request.

Suggested fix:
Implement a way to tell MySQL, in a statement, that the GTID of the last transaction should be put in the OK packet.
[6 Feb 2017 7:28] Umesh Shastry
Hello  Jean-François,

Thank you for the report and feature request!

Thanks,
Umesh