Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.5, 10.6, 10.7, 10.8, 10.9, 10.10
-
None
Description
I'm not quite sure if there is any practical importance of the accuracy of SHOW BINLOG EVENTS – that is, whether they are ever used for the purpose of replaying. But on some reason, there has been an effort to show some relevant information in its output, such as values of variables, and I don't think it works quite as intended.
As of 10.5-10.10, there are 5 variables which are conditionally appended to the binlog events. Out of those,
- foreign_key_checks and unique_checks are simply inverted, they are written to be set to 1 when the actual value is 0, and not written otherwise;
- sql_auto_is_null is syntactically incorrect, as it comes without a value;
- check_constraint_checks is written with a correct value 1 and not written when it's 0, which probably doesn't serve the purpose (to indicate that the check was relaxed on the original executor, which is why the statement went through);
- sql_if_exists seems okay comparing to the rest, it's written when it's set to 1, while default is 0. But even then, this logic of only writing the value when it's set assumes that the global value hasn't been changed on the replaying server which of course cannot be guaranteed.
The new variable system_versioning_insert_history which is being introduced in MDEV-16546 shares the logic with sql_if_exists in this regard.
--source include/have_log_bin.inc
|
--source include/have_innodb.inc
|
|
set foreign_key_checks= 0, sql_auto_is_null=1, check_constraint_checks=0, unique_checks=0; |
create table t (a int, check(a>1), foreign key(a) references x (x)) engine=InnoDB as select 1 as a; |
|
show binlog events;
|
|
# Cleanup
|
drop table t; |
10.5 6b8c43ba |
master-bin.000001 329 Gtid 1 371 GTID 0-1-1
|
master-bin.000001 371 Query 1 536 use `test`; set foreign_key_checks=1, sql_auto_is_null, unique_checks=1; create table t (a int, check(a>1), foreign key(a) references x (x)) engine=InnoDB as select 1 as a |