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?

Answer 1

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.

Answer 2

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).