Description:
Hi,
in [1], it is written that with "binlog_transaction_dependency_tracking = WRITESET_SETTION", two updates from the same session can be reordered.
[1]: https://dev.mysql.com/doc/refman/8.0/en/replication-options-binary-log.html#sysvar_binlog_...
MySQL 5.7.3 introduced mysql_reset_connection. According to [2] and [3], mysql_reset_connection() has effects similar to mysql_change_user() or an auto-reconnect.
[2]: https://dev.mysql.com/doc/refman/5.7/en/mysql-reset-connection.html
[3]: https://dev.mysql.com/doc/refman/8.0/en/mysql-reset-connection.html
However, an auto-reconnect would clear Write Set Session history, and it looks like mysql_reset_connection (cmd_reset_connection in Python) does not clear Write Set Session history (see How to repeat).
Thanks for looking into that,
JFG
How to repeat:
With the following Python script (I cannot test with the Unix client because I do not know how to call reset_session with the Unix client):
cnx = mysql.connector.connect(**config)
cnx.autocommit = True
cursor = cnx.cursor()
cursor.execute("set global binlog_transaction_dependency_tracking = COMMIT_ORDER")
cursor.execute("set global transaction_write_set_extraction = OFF")
cursor.execute("set global transaction_write_set_extraction = XXHASH64")
cursor.execute("set global binlog_transaction_dependency_tracking = WRITESET")
cursor.execute("drop table if exists test_jfg")
cursor.execute("create table test_jfg(id int primary key)")
cursor.execute("flush binary logs")
cursor.execute("insert into test_jfg(id) values(1)")
cursor.execute("insert into test_jfg(id) values(2)")
cursor.execute("set global binlog_transaction_dependency_tracking = WRITESET_SESSION")
cursor.execute("insert into test_jfg(id) values(3)")
cursor.execute("insert into test_jfg(id) values(4)")
cnx.close()
cnx = mysql.connector.connect(**config)
cnx.autocommit = True
cursor = cnx.cursor()
cursor.execute("insert into test_jfg(id) values(5)")
cursor.execute("insert into test_jfg(id) values(6)")
cnx.cmd_reset_connection()
cnx.autocommit = True
cursor = cnx.cursor()
cursor.execute("insert into test_jfg(id) values(7)")
cursor.execute("insert into test_jfg(id) values(8)")
cnx.close()
I get the following in the binlogs:
# mysqlbinlog | sed -n -e 's/orig.*//;s/.*_GTID.//p'
last_committed=0 sequence_number=1
last_committed=0 sequence_number=2
last_committed=2 sequence_number=3
last_committed=3 sequence_number=4
last_committed=2 sequence_number=5
last_committed=5 sequence_number=6
last_committed=6 sequence_number=7
last_committed=7 sequence_number=8
But I would expect transaction with sequence_number=7 to have last_committed=2 (like sequence_number=5) because I called cmd_reset_connection before inserting 7.
Suggested fix:
mysql_reset_connection should reset Write Set Session history.
