Description:
Hi,
since MySQL 8.0.17, gtid_executed and gtid_purged are consistent with the binary logs and InnoDB after an operating system crash with reduced durability (sync_binlog = 0 and innodb_flush_log_at_trx_commit = 2). See How to repeat for details.
In How to repeat, we see that gtid_purged has been updated after crash recovery. However, the documentation of gtid_purged ([1] and [2]) does not mention that gtid_purged is updated in crash recovery (below is what the documentation says). I would expect the documentation to be complete on this topic, and to mention that gtid_purged can be updated in crash recovery with all detail.
[1]: https://dev.mysql.com/doc/refman/8.4/en/replication-options-gtids.html#sysvar_gtid_purged
[2]: https://dev.mysql.com/doc/refman/8.4/en/replication-gtids-lifecycle.html#replication-gtids...
> The following categories of GTIDs are in gtid_purged:
>
> - GTIDs of replicated transactions that were committed with binary logging disabled on the replica.
>
> - GTIDs of transactions that were written to a binary log file that has now been purged.
>
> - GTIDs that were added explicitly to the set by the statement SET @@GLOBAL.gtid_purged.
More generally, it looks like the documentation has not been updated for the GTID Crash Safety Improvement of 8.0.17. In WL#9211 [3], I find below quotes, but I do not find matching information in the documentation of gtid_executed [4] and of mysql.gtid_executed [5]. In these documentation pages, I would expect to find information about "GTID is persisted in UNDO" and the impact on crash recovery (in the current doc, there are some mentions of the GTID persister thread / innodb/clone_gtid_thread, but they look outdated and incomplete without mentioning its role in detail).
[3]: https://dev.mysql.com/worklog/task/?id=9211
[4]: https://dev.mysql.com/doc/refman/8.4/en/replication-options-gtids.html#sysvar_gtid_execute...
[5]: https://dev.mysql.com/doc/refman/8.4/en/replication-gtids-concepts.html#replication-gtids-...
> (in the section about Requirements of WL9211)
>
> For all innodb transactions with GTID, GTID MUST be durable along with the transaction i.e. in case of a server crash, the GTID set in GTID_EXECUTED table must have all the committed and XA prepared transactions in Innodb after recovery.
>
> [...]
>
> NOTE: This is an independent requirement and can be tested without clone. It is implicit requirement of clone.
> (in the section about High Level Architecture of WL9211)
>
> GTIDs are now persistent within Innodb and guaranteed to be consistent with committed transaction.
>
> - GTID is persisted in UNDO log header when a transaction with GTID is committed in Innodb. Same way GTID is persisted for external XA prepare, commit and rollback.
>
> - GTIDs are persisted to mysql.gtid_executed table at intervals by a new innodb background thread "gtid_persistor".
>
> - Purge background is not allowed to purge undo logs till GTIDs are written to mysql.gtid_executed by "gtid persistor".
>
> - During recovery, the GTIDs are extracted from unpurged undo logs and send to gtid_persistor which persists them to gtid_executed table before replication GTID processing at startup.
While we are at this, I flagged other errors / improvements in the docs referenced in this report, see Suggested fix for details.
In above, I quoted and linked to the 8.4 docs, but it looks like 8.0, 9.7 and 26.7 docs (links below) are also affected (even 5.7 is affected in a lesser way, see Suggested fix).
https://dev.mysql.com/doc/refman/8.0/en/replication-options-gtids.html#sysvar_gtid_purged
https://dev.mysql.com/doc/refman/9.7/en/replication-options-gtids.html#sysvar_gtid_purged
https://dev.mysql.com/doc/refman/26.7/en/replication-options-gtids.html#sysvar_gtid_purged
I am setting this as S2 / Serious because incomplete / wrong documentation is not a minor thing.
Many thanks for looking into this,
Jean-François Gagné
How to repeat:
# Create and initialize a sandbox for our tests.
# (in a function to be used with different MySQL versions)
# (below is portable from MySQL 8.0 to 26.7, which is why we need sql_replica)
# (portability is also why we need cli_arg, more about this in Bug#118819)
# (dbda : [dbd]eployer [a]rguments)
# (we need replication crash safety as we will crash MySQL)
# (without the sleep 5 before calling s1, we might get errors because of a dbdeployer issue: https://github.com/ProxySQL/dbdeployer/issues/131)
# (WAIT_FOR_EXECUTED_GTID_SET is needed to set sync_binlog after CREATE TABLE on s1)
# (saving cli_arg to easily continue in the next steps)
function create_and_init_sandbox() {
sql_replica=replica; grep -q -e mysql_8.0.1 -e mysql_8.0.2[012] <<< $v && sql_replica=slave
cli_arg=""; grep -q -e mysql_9.[4567] -e mysql_2 <<< $v && cli_arg=--commands
local dbda="--gtid --repl-crash-safe -c skip-${sql_replica}-start"
dbdeployer deploy replication $v $dbda
cd ~/sandboxes/rsandbox_${v//./_}
./m <<< "CREATE DATABASE test_jfg"
./m <<< "CREATE TABLE test_jfg.t(id BIGINT NOT NULL PRIMARY KEY AUTO_INCREMENT)"
local gtid_executed="$(./m -N <<< "SELECT @@global.gtid_executed")"
sleep 5; ./s1 <<< "SELECT WAIT_FOR_EXECUTED_GTID_SET('$gtid_executed')" > /dev/null
for c in m s1; do ./$c <<< "SET GLOBAL sync_binlog = 0, innodb_flush_log_at_trx_commit = 2"; done
echo "cli_arg=$cli_arg" > jfg_env
}
# Create a sandbox for our tests with different versions.
# Then for each version, show gtid_executed and start a background job inserting in table t.
# Finally, after waiting for two seconds, crash Linux.
# (as each insert consumes a gtid, we can predict which id matches each gtid)
# (saving the vs variable to easily continue after reboot)
# (background create_and_init_sandbox in a sub-process to avoid job outputs)
# (the pv command is a trick to show execution time)
{
vs="mysql_8.0.16 mysql_8.0.17 mysql_8.0.46 mysql_8.4.11 mysql_9.7.2 mysql_26.7.0"
echo "vs='$vs'" > ~/sandboxes/jfg_env
( for v in $vs; do create_and_init_sandbox 2> /dev/null& done; wait; ) | pv -tN dbdepls. > /dev/null
echo
for v in $vs; do
gtid_executed=$(~/sandboxes/rsandbox_${v//./_}/m -N <<< "SELECT @@global.gtid_executed")
echo "gtid_executed=$gtid_executed" >> ~/sandboxes/rsandbox_${v//./_}/jfg_env
printf "%-13s %s\n" $v $gtid_executed
done
sudo sync # Without a sync, the crash might loose the sandboxes and the jfg_env files.
for v in $vs; do
( yes "INSERT INTO t(id) VALUE (NULL); DO SLEEP(0.02);" | ~/sandboxes/rsandbox_${v//./_}/m test_jfg & )
done
echo; sleep 2; echo c | sudo tee /proc/sysrq-trigger > /dev/null
}
dbdepls.: 0:00:42
mysql_8.0.16 00020617-1111-1111-1111-111111111111:1-35
mysql_8.0.17 00020718-1111-1111-1111-111111111111:1-35
mysql_8.0.46 00023647-1111-1111-1111-111111111111:1-35
mysql_8.4.11 00020512-1111-1111-1111-111111111111:1-35
mysql_9.7.2 00020903-1111-1111-1111-111111111111:1-35
mysql_26.7.0 00037701-1111-1111-1111-111111111111:1-35
Read from remote host 3.133.107.8: Connection reset by peer
Connection to 3.133.107.8 closed.
client_loop: send disconnect: Connection reset by peer
# Wait for reboot, reconnect, start sandboxes, and show gtid_executed and gtid_purged.
# From MySQL 8.0.17, gtid_executed is consistent with the content of InnoDB.
# Also from MySQL 8.0.17, gtid_purged is initialized after crash recovery.
{
. ~/sandboxes/jfg_env
( for v in $vs; do ~/sandboxes/rsandbox_${v//./_}/start_all > /dev/null& done; wait; ) | pv -tN start_alls > /dev/null
for v in $vs; do
echo; echo $v; cd ~/sandboxes/rsandbox_${v//./_}; . jfg_env
ids="$(cut -d : -f 2 <<< "$gtid_executed")"
last_id_init="$(cut -d - -f 2 <<< "$ids")"
sql="SELECT MAX(id) as m, @@global.gtid_executed as e, @@global.gtid_purged as p FROM t\G"
for c in m s1 s2; do
res="$(./$c $cli_arg test_jfg <<< "$sql")"
m=$(awk '$1 == "m:" {print $2}' <<< "$res")
e=$(awk '$1 == "e:" {print $2}' <<< "$res")
p=$(awk '$1 == "p:" {print $2}' <<< "$res")
ids="$(cut -d : -f 2 <<< "$e")"
last_id_now="$(cut -d - -f 2 <<< "$ids")"
test $m -eq $(($last_id_now - $last_id_init)) && val=OK || val=KO
printf "%-2s: %3s %s %42s %s\n" $c $m $val $e "$p"
done
done
}
start_alls: 0:00:15
mysql_8.0.16
m : 97 KO 00020617-1111-1111-1111-111111111111:1-35
s1: 97 KO 00020617-1111-1111-1111-111111111111:1-35
s2: 97 OK 00020617-1111-1111-1111-111111111111:1-132
mysql_8.0.17
m : 98 OK 00020718-1111-1111-1111-111111111111:1-133 00020718-1111-1111-1111-111111111111:36-133
s1: 103 OK 00020718-1111-1111-1111-111111111111:1-138 00020718-1111-1111-1111-111111111111:36-138
s2: 103 OK 00020718-1111-1111-1111-111111111111:1-138
mysql_8.0.46
m : 70 OK 00023647-1111-1111-1111-111111111111:1-105 00023647-1111-1111-1111-111111111111:36-105
s1: 54 OK 00023647-1111-1111-1111-111111111111:1-89 00023647-1111-1111-1111-111111111111:36-89
s2: 97 OK 00023647-1111-1111-1111-111111111111:1-132
mysql_8.4.11
m : 94 OK 00020512-1111-1111-1111-111111111111:1-129 00020512-1111-1111-1111-111111111111:36-129
s1: 102 OK 00020512-1111-1111-1111-111111111111:1-137 00020512-1111-1111-1111-111111111111:36-137
s2: 103 OK 00020512-1111-1111-1111-111111111111:1-138
mysql_9.7.2
m : 85 OK 00020903-1111-1111-1111-111111111111:1-120 00020903-1111-1111-1111-111111111111:36-120
s1: 90 OK 00020903-1111-1111-1111-111111111111:1-125 00020903-1111-1111-1111-111111111111:36-125
s2: 102 OK 00020903-1111-1111-1111-111111111111:1-137
mysql_26.7.0
m : 85 OK 00037701-1111-1111-1111-111111111111:1-120 00037701-1111-1111-1111-111111111111:36-120
s1: 98 OK 00037701-1111-1111-1111-111111111111:1-133 00037701-1111-1111-1111-111111111111:36-133
s2: 103 OK 00037701-1111-1111-1111-111111111111:1-138
Suggested fix:
Complete the documentation of gtid_purged, gtid_executed and mysql.gtid_executed. These should mention as minimum the update of gtid_purged on crash recovery, GTID in UNDO, the role of the gtid_persistor in updating mysql.gtid_executed and the consistency of the GTID State with the binlogs and InnoDB. It should also mentions that the InnoDB Purge can be blocked by the gtid_persistor lagging.
Below are other errors / improvements I flagged.
https://dev.mysql.com/doc/refman/8.4/en/replication-options-gtids.html#sysvar_gtid_execute...
> When the server starts, @@GLOBAL.gtid_executed is initialized. See binlog_gtid_simple_recovery for more information on how binary logs are iterated to populate gtid_executed.
"how binary logs are iterated to populate gtid_executed" in above is miss-leading, even for 5.7. The sysvar gtid_executed is not only updated from the binlogs, it is also updated from mysql.gtid_executed (including in 5.7, especially with binlogs disabled). Also, since 8.0.17, this sysvar initialization on crash recovery includes GTIDs in UNDO not processed by the gtid_persistor before the crash.
https://dev.mysql.com/doc/refman/8.4/en/replication-options-gtids.html#sysvar_gtid_execute...
> Scope: Global
> When used with global scope, this variable contains a representation [...]
"When used with global scope" implies that there is another way to use this variable, but the scope is only global. I suggest changing to "This variable, only available in global scope, contains a representation [...]", or more simply "This variable contains a representation [...]".
https://dev.mysql.com/doc/refman/8.4/en/replication-options-gtids.html#sysvar_gtid_purged
> Scope: Global
> The global value of the gtid_purged system variable (@@GLOBAL.gtid_purged) is a GTID set [...]
Like above (sysvar_gtid_executed), "The global value of the gtid_purged" implies that there is another value for this variable, but the scope is only global. Also, mentioning how to get the variable ("@@GLOBAL.gtid_purged") is irrelevant information not consistent with other variable descriptions (or at the least, not consistent with sysvar_gtid_executed), and I suggest removing it. I suggest changing to "The value of the gtid_purged system variable, only available in global scope, is a GTID set [...]" or more simply "The value of the gtid_purged system variable is a GTID set [...]", or even more simply and consistent with sysvar_gtid_executed "This variable contains a GTID set [...]".
https://dev.mysql.com/doc/refman/8.4/en/replication-options-gtids.html#sysvar_gtid_purged
https://dev.mysql.com/doc/refman/8.4/en/replication-gtids-lifecycle.html#replication-gtids...
Both sysvar_gtid_purged doc and the section "The gtid_purged System Variable" of the doc on "GTID Life Cycle" mentions "categories of GTIDs are in gtid_purged". I suggest having this only in "GTID Life Cycle" and linking to this in sysvar_gtid_purged.
More generally, I suggest refactoring these two to avoid duplicating information and linking to each others for detail. "GTID Life Cycle" already links to the sysvar_gtid_purged doc for "change the value of gtid_purged", we could use more of this for more compact docs.
Also consider refactoring gtid_executed and mysql.gtid_executed docs for consistency.
https://dev.mysql.com/doc/refman/8.4/en/replication-options-gtids.html#sysvar_binlog_gtid_...
> [...] the values of gtid_executed and gtid_purged are computed at startup based on the values of Previous_gtids_log_event in the most recent and oldest binary log files. [...]
The above is only partially right at best, and wrong at worse. These two variables are not "only" computed only from the binlogs, and this is also true in 5.7 (which allows running with GTIDs and without binlogs enabled). It might be interesting to review this whole section, with binlogs disabled in mind, and with also the 8.0.17 improvement in mind, including GTID in UNDO, and the gtid_persistor.
https://dev.mysql.com/doc/refman/8.4/en/replication-gtids-concepts.html#replication-gtids-...
> If binary logging is enabled (log_bin is ON), for the InnoDB storage engine only, the server updates the mysql.gtid_executed table in the same way as when binary logging or replica update logging is disabled, storing the GTID for each transaction at transaction commit time.
From what I see, the above is not true, or at least incomplete. It should at least mention the gtid_persistor because the gtid_persistor is what puts GTID in mysql.gtid_executed, not "transaction commit" which puts GTID in UNDO.
> If the server has a mix of InnoDB transactions and non-InnoDB transactions, which are written to the mysql.gtid_executed table individually, [...]
Above is not consistent with below from Bug#100118 (non-InnoDB transactions are not written to mysql.gtid_executed individually according to this bug). Also because of below, a way to monitor the GTID persistor thread (called GTID persister in the doc) should be mentioned in the doc, and a way to monitor the below-mentioned stall should also be mentioned.
> the GTID persister thread now writes GTIDs for both InnoDB and non-InnoDB transactions, and foreground commits are forced to wait if the GTID persister thread falls behind
Moreover...
> For this reason, it is recommended that you set gtid_executed_compression_period to 0, so that the compress_gtid_table thread is never activated.
...above from the doc is not consistent with below from Bug#100118.
> Also, the gtid_executed_compression_period default setting was changed from 1000 to 0 to disabled explicit compression of the mysql.gtid_executed table by default.
