Why is MySQL `gtid_owned` session variable always empty after committing a transaction?

Question

I'm testing some work with MySQL Global Transaction IDs (GTIDs) and I'm having a hard time getting the most-recent session GTID. I've enabled GTIDs (global gtid_mode is set to ON_PERMISSIVE). 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 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.

Here's a session that demonstrates this issue:

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

mysql> SELECT @@session.gtid_next;
| AUTOMATIC           |

mysql> SELECT @@session.gtid_owned;
|                      |

mysql> START TRANSACTION;

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

mysql> SELECT @@session.gtid_next;
| AUTOMATIC           |

mysql> SELECT @@session.gtid_owned;
|                      |

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

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

mysql> SELECT @@session.gtid_next;
| AUTOMATIC           |

mysql> SELECT @@session.gtid_owned;
|                      |

mysql> COMMIT;

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

mysql> SELECT @@session.gtid_next;
| AUTOMATIC           |

mysql> SELECT @@session.gtid_owned;
|                      |

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

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

mysql> SELECT @@session.gtid_next;
| AUTOMATIC           |

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.

What am I doing wrong/missing? Or have I found a bug?

Generalist

Have you checked if GTID is enabled on configuration file (my.cnf): gtid-mode=ON ? – Spectra

SELECT @@global.gtid_mode; -> ON_PERMISSIVE – Generalist

Answer 1

A client only owns a transaction that is still open, so gtid_owned is cleared once the transaction commits. It also only shows a value if you have explicitly set one for gtid_next; using gtid_next=automatic will not populate gtid_owned. The use of gtid_owned is therefore limited to some internal operations and to cases like replication where the GTID is explicitly set in the binary log.

To get the list of gtids set by the current session, you could enable session_track_gtids (see https://dev.mysql.com/doc/en/server-system-variables.html#sysvar_session_track_gtids).

Note however that the 'mysql' command line client does not report the values returned by the session tracker; you'd need a client which lets you call the C API functions mysql_session_track_get_first() and mysql_session_track_get_next() (see https://dev.mysql.com/doc/en/mysql-session-track-get-first.html).

Misbegotten

I should have responded to this a long time ago, but I got sidetracked (I had given up on GTIDs). I've come back to it. Part of this response doesn't sound correct to me. The documentation specifically says of gtid_owned: "When used with session scope, the list holds all GTIDs that are owned by this client." It doesn't say that gtid_next must be set. If it were cleared once a transaction commits, it wouldn't be a LIST of "all GTIDs that are owned by this client." Even if it WERE cleared after every commit, my demonstration above shows that it's empty even during an uncommitted transaction. – Generalist

According to dev.mysql.com/doc/en/replication-gtids-lifecycle.html the GTID is not assigned until commit time. That's why it seems empty during the uncommited transaction - if gtid_owned is populated at all on the master, it's only after you've sent the COMMIT; and for the brief instant between when the server picks the GTID and when it writes to the binlog - and of course you'll have no opportunity to view the variable during that time unless you're using GDB or something. The list of owned GTIDs therefore really only has relevance to a replication slave. – Misbegotten

Try setting up STATEMENT-format logging on a server with gtid_mode on. INSERT INTO someTable VALUES (@@gtid_owned); - you'll see that value is empty on the master, but populated with a GTID on the slave. – Misbegotten

Then what even is the point of this session variable? It seems completely useless to me. – Generalist

Correct, it's of very little use to the end user. It exists to help the developers with testing and debugging - if you're writing the code dealing with a multi-threaded slave and some bug is causing transactions to be skipped or duplicated, then it's mighty helpful to know who owns which GTID. – Misbegotten

Answer 2

I test it in MySQL 8.0.12, and <= 8.0.12 should have the same results.

Personal opinions, may have some mistakes!

Gtid_owned and Gtid_executed are used when generate a new GTID to avoid a duplicate one. And Gtid_next affects the timing to use Gtid_owned.

If Gtid_next is AUTOMATIC, GTID is generated when the transaction is in flush stage in commit phase and saved in Gtid_owned; source code path in binlog.cc is MYSQL_BIN_LOG :: ordered_commit() --> MYSQL_BIN_LOG::process_flush_stage_queue() --> assign_automatic_gtids_to_flush_group;

And Gtid_owned will be removed in commit stage, source code is in binlog.cc too, the path is MYSQL_BIN_LOG::process_commit_stage_queue -> Gtid_state::update_commit_group -> update_gtids_impl_own_gtid_set;

So you can't get the Gtid_owned in this situation because mysql generate it and clear it at the final phase of transaction;

To get the value of Gtid_owned, just set Gtid_next to a precise gtid, so it will be saved in Gtid_owned immediately(without source code checks), so you can check this variable normally as the pic shows;

Another pic, the global gtid_owned save the used gtid and the client's thread_id after #, the previous pic shows the thread_id too.

Bardo

This doesn't seem like reasonable behavior, and also doesn't mesh with the documentation. First, in all normal cases, you should never need to set gtid_next. Ever. It's more for testing/troubleshooting. Many SQL commands are unavailable when gtid_next is not set to AUTOMATIC. Only SUPER can change gtid_next. As for the gtid_owned documentation, it says "the list holds all GTIDs that are owned by this client." ALL GTIDs. That wouldn't make sense if this only held a value between the flush stage and the commit stage. It couldn't hold ALL GTIDs owned by the client were that the case. – Generalist

GTIDs is generated between the flush stage and the commit stage, then saved in gtid_owned, so next transaction in same client or other clients can get the "the list of all GTIDs that are owned by this client and other client". Therefore MySQL will not generate a duplicate one. This is how I understanding the gtid_owned documentation. So gtid_owned actully saves the GTIDs used by the client but only in a short time at the end of transaction, and the global values is saved with the client's thread_id so other client can know the all used GTIDs. Maybe it's used by MySQL not users. @NickWill – Forbid

For more, set gtid_next to precise one may used by slave, because its GTIDs is not generated by itself, but the GTIDs recorded in relaylog(same contents with Master's binlog), so slave have to set a precise gtid. @NickWilliams – Forbid