MySQL Bugs: #95077: gtid_owned session variable is always empty, in all circumstances

Bug #95077	gtid_owned session variable is always empty, in all circumstances
Submitted:	22 Apr 2019 19:03	Modified:	25 Jul 2019 23:31
Reporter:	Nicholas Williams	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S3 (Non-critical)
Version:	5.7.17-13	OS:	Ubuntu (Ubuntu 16.04)
Assigned to:		CPU Architecture:	Any
Tags:	GTID, transactions

Description:
I cannot get `gtid_owned` to be anything but blank. First, my GTID-related settings (the below-described bug also happens when `gtid_mode` is `ON_PERMISSIVE`):

mysql> show variables like '%gtid%';
+----------------------------------+-----------+
| Variable_name                    | Value     |
+----------------------------------+-----------+
| binlog_gtid_simple_recovery      | ON        |
| enforce_gtid_consistency         | ON        |
| gtid_executed_compression_period | 1000      |
| gtid_mode                        | ON        |
| gtid_next                        | AUTOMATIC |
| gtid_owned                       |           |
| gtid_purged                      |           |
| session_track_gtids              | OWN_GTID  |
+----------------------------------+-----------+

According to the documentation for `gtid_owned`:

>This read-only variable holds a list whose contents depend on its scope. When used with session scope, the list holds all GTIDs that are owned by this client; ...

So, I expect, after committing a transaction, that this session variable would contain one or more GTIDs; but it is always empty, no matter what I do. However, the global `gtid_executed` changes every time I commit a transaction, so I know that GTIDs are working.

How to repeat:
Here's a session that demonstrates this issue:

mysql> SELECT @@global.gtid_executed;
| a1c32161-89c4-11e8-856e-0242ac12001d:1-4 |

mysql> SELECT @@session.gtid_owned;
|                      |

-- explicit transaction
mysql> START TRANSACTION;

mysql> SELECT @@global.gtid_executed;
| a1c32161-89c4-11e8-856e-0242ac12001d:1-4 |

mysql> SELECT @@session.gtid_owned;
|                      |

mysql> INSERT INTO ........

mysql> SELECT @@global.gtid_executed;
| a1c32161-89c4-11e8-856e-0242ac12001d:1-4 |

mysql> SELECT @@session.gtid_owned;
|                      |

mysql> COMMIT;

mysql> SELECT @@global.gtid_executed;
| a1c32161-89c4-11e8-856e-0242ac12001d:1-5 |

mysql> SELECT @@session.gtid_owned;
|                      |

-- implicit transaction
mysql> INSERT INTO ........

mysql> SELECT @@global.gtid_executed;
| a1c32161-89c4-11e8-856e-0242ac12001d:1-6 |

mysql> SELECT @@session.gtid_owned;
|                      |

Notice that each time a transaction is committed (explicitly or implicitly/autocommit), the global list of executed GTIDs is incremented, but the session `gtid_owned` is always empty.

Hi,

Thanks for the report. The behavior is verified on 5.7.25. Now I do have to say that documentations states that this is "internal" value so you can't really depend on it, hence it can be classified as "not a bug". I'm setting it to "Verified" but let's see what the replication team will say about it.

Thanks
Bogdan

To be completely accurate, the documentation says, "This read-only variable is PRIMARILY for internal use." (Emphasis mine.) It does not say that it is solely for internal use. Additionally, the variable is very well-documented, which (IMO) means the public can rely on its behavior matching that documentation. Its behavior does not match the documentation. :-)

Hi,

yes, it does, exactly why I did verify the bug and not just thrown it under the bus :D

[25 July 2019 2:01] Sven Sandberg

Thanks for your replies. At the time the bug was reported, the manual was a
bit unclear, so I fully understand the reason for reporting the bug.

But (by coincidence) the manual has been updated between then and now. I
think it does describe the current behavior correctly now, and explains why
gtid_next is empty when using the steps in 'how to reproduce'. E.g., in the
second bullet at
https://dev.mysql.com/doc/refman/8.0/en/replication-options-gtids.html#sysvar_
gtid_owned it says "If gtid_next=AUTOMATIC is used for the session,
gtid_owned is only populated briefly during the execution of the commit
statement for the transaction, so it cannot be observed from the session
concerned, although it will be listed if @@global.gtid_owned is read at the
right point."

In particular, please note that the contents of gtid_owned depends on how you
use gtid_next. And note that a session stops owning a GTID at the time the
GTID is committed.

I guess I am not reading this with fresh eyes, and maybe I am missing
something though? If you still see any inconsistency between the manual and
the server behavior, can you please provide the steps to reproduce and the
quote from the 8.0 manual? Thank you.

You are indeed correct that, per the newly-updated documentation, this is working as expected. It was only a bug in respect to the previous version of the documentation for `gtid_owned`.

The bummer about this, though, is that it means it is essentially impossible to get "the GTID I just committed" in MySQL. There is, of course, `session_track_gtids`, which can be set to "OWN_GTID". However, AFAICT, the data made available by enabling this is only available to hyper-modern programmatic clients that use the `mysql_session_track_**` C API functions. There don't appear to currently be any clients for major platforms (Python, Java) that expose tracked session data (I grepped the source code of Connector/Python and Connector/J and there were no uses of `mysql_session_track`), and the CLI client doesn't expose this tracked data, either (unless I'm missing something, in which case, please tell me).

Could we change the purpose of this issue to be a feature request for the following?:

1) Adding a new session variable `gtid_tracked` that, when `session_track_gtids` is not "OFF", contains the GTID data that is tracked in the session.

2) Update Connector/Python and Connector/J to surface tracked session data (`mysql_session_track_**`) so that tracked GTIDs can be retrieved.