MDEV-25444: mysql --binary-mode is not able to replay some mysqlbinlog outputsbb-10.3-25444

Changes on top of Sachin’s patch. Specifically:
 1) Refined the parsing break condition to only change the parser’s
behavior for parsing strings in binary mode (behavior of \0 outside
of strings is unchanged).
 2) Prefixed binary_zero_insert.test with ‘mysql_’ to more clearly
associate the  purpose of the test.
 3) As the input of the test contains binary zeros (0x5c00),
different text editors can visualize this sequence differently, and
Github would not display it at all. Therefore, the input itself was
consolidated into the test and created out of hex sequences to make
it easier to understand what is happening.
 4) Extended test to validate that the rows which correspond to the
INSERTS with 0x5c00 have the correct binary zero data.

Reviewed By:
===========
Andrei Elkin <andrei.elkin@mariadb.com>

4 files changed, 224 insertions, 21 deletions

diff --git a/mysql-test/main/binary_zero_insert.result b/mysql-test/main/binary_zero_insert.result
deleted file mode 100644
index c46dc74f019..00000000000
--- a/mysql-test/main/binary_zero_insert.result
+++ /dev/null
@@ -1,6 +0,0 @@
-CREATE TABLE `tb` (`id` int(11) NOT NULL AUTO_INCREMENT,`cb` longblob DEFAULT NULL,
-PRIMARY KEY (`id`)) ENGINE=myisam AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;
-select count(*)=2 from tb;
-count(*)=2
-1
-drop table tb;
diff --git a/mysql-test/main/binary_zero_insert.test b/mysql-test/main/binary_zero_insert.test
deleted file mode 100644
index a8769199859..00000000000
--- a/mysql-test/main/binary_zero_insert.test
+++ /dev/null
@@ -1,15 +0,0 @@
-#
-# MDEV-25444 mysql --binary-mode is not able to replay some mysqlbinlog outputs
-#
-# After investigating it turns out to be a issue of mysql client not able to properly
-#  handle  \\\0 <0 in binary>.
-# In this test case we will be pipelining binary_zero_insert.bin into  mysql client.
-# binary_zero_insert.bin contains insert stmt with \\\0
-
-CREATE TABLE `tb` (`id` int(11) NOT NULL AUTO_INCREMENT,`cb` longblob DEFAULT NULL,
-PRIMARY KEY (`id`)) ENGINE=myisam AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;
-
---exec $MYSQL --binary-mode  test < $MYSQL_TEST_DIR/std_data/binary_zero_insert.bin
-select count(*)=2 from tb;
-
-drop table tb;
diff --git a/mysql-test/main/mysql_binary_zero_insert.result b/mysql-test/main/mysql_binary_zero_insert.result
new file mode 100644
index 00000000000..0bed7487b3e
--- /dev/null
+++ b/mysql-test/main/mysql_binary_zero_insert.result
@@ -0,0 +1,54 @@
+# Note: This test assumes NO_BACKSLASH_ESCAPES is not set in SQL_MODE.
+##############################
+#      Setup
+##############################
+#
+# Saving old state
+#
+set @old_sql_mode= @@global.SQL_MODE;
+set @@global.SQL_MODE= "";
+#
+# Create table for data entry
+#
+CREATE TABLE tb (`id` int(11) NOT NULL AUTO_INCREMENT,`cb` longblob DEFAULT NULL, PRIMARY KEY (`id`)) AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;
+RESET MASTER;
+##############################
+#      Test Case
+##############################
+#
+# \0 (0x5c00 in binary) should be allowed in data strings if
+# --binary-mode is enabled.
+#
+FOUND 10 /\x5c\x00/ in binary_zero_inserts.sql
+# MYSQL --binary-mode test < MYSQL_TMP_DIR/binary_zero_inserts.sql
+#
+# Ensure a row exists from each insert statement with a \0
+#
+SELECT COUNT(*)=8 from tb;
+COUNT(*)=8
+1
+#
+# Ensure that the binary zero was parsed and exists in the row data
+# Note: We only look for 00 because the 5c only served as an escape
+#       in parsing.
+#
+# MYSQL_DUMP test tb --hex-blob | grep INSERT > MYSQL_TMP_DIR/dump.sql
+FOUND 10 /00/ in dump.sql
+#
+# Ensure data consistency on mysqlbinlog replay
+#
+FLUSH LOGS;
+# MYSQL_BINLOG MYSQLD_DATADIR/binlog_file > MYSQL_TMP_DIR/binlog_zeros.sql
+FOUND 10 /\x5c\x00/ in binlog_zeros.sql
+# MYSQL --binary-mode test < MYSQL_TMP_DIR/binlog_zeros.sql
+# Table checksum is equivalent before and after binlog replay
+#
+# A \0 should still be treated as end-of-query in binary mode.
+#
+# MYSQL --binary-mode -B test < MYSQL_TMP_DIR/binary_zero_eoq.sql
+##############################
+#      Cleanup
+##############################
+SET @@global.sql_mode= @old_sql_mode;
+drop table tb;
+RESET MASTER;
diff --git a/mysql-test/main/mysql_binary_zero_insert.test b/mysql-test/main/mysql_binary_zero_insert.test
new file mode 100644
index 00000000000..b327c8a4d1e
--- /dev/null
+++ b/mysql-test/main/mysql_binary_zero_insert.test
@@ -0,0 +1,170 @@
+#
+# Purpose:
+#   This test ensures that the mysql client is able to properly handle the
+# binary data sequence 0x5c00, i.e. the null-terminating character \0, in a
+# string when --binary-mode is enabled. Specifically, this sequence is valid to
+# appear anywhere within a binary data string, and it should not end the string
+# or SQL command. Additionally, \0 outside of a string should still end the
+# query.
+#
+# Methodology:
+#   This test initially inserts data with binary strings containing \0. To
+# ensure the mysql client is able to process this data correctly, perl is used
+# to create a SQL file that contains \0 in strings, and this file is used as
+# input into the client. The row data is then validated by searching for binary
+# zeros in mysqldump output.
+#
+#
+# References:
+#   MDEV-25444: mysql --binary-mode is not able to replay some mysqlbinlog
+#               outputs
+
+--echo # Note: This test assumes NO_BACKSLASH_ESCAPES is not set in SQL_MODE.
+
+--source include/have_log_bin.inc
+
+--echo ##############################
+--echo #      Setup
+--echo ##############################
+
+--echo #
+--echo # Saving old state
+--echo #
+set @old_sql_mode= @@global.SQL_MODE;
+set @@global.SQL_MODE= "";
+
+--echo #
+--echo # Create table for data entry
+--echo #
+CREATE TABLE tb (`id` int(11) NOT NULL AUTO_INCREMENT,`cb` longblob DEFAULT NULL, PRIMARY KEY (`id`)) AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;
+
+# Will replay binlog later and we don't want to recreate the table
+RESET MASTER;
+
+
+--echo ##############################
+--echo #      Test Case
+--echo ##############################
+
+--echo #
+--echo # \0 (0x5c00 in binary) should be allowed in data strings if
+--echo # --binary-mode is enabled.
+--echo #
+--perl
+  my $dir= $ENV{'MYSQL_TMP_DIR'};
+  open (my $FILE, '>', "$dir/binary_zero_inserts.sql") or die "open(): $!";
+
+  print $FILE "TRUNCATE TABLE tb;\n";
+
+  # INSERT INTO tb(cb) VALUES(_binary '\0');
+  print $FILE "INSERT INTO tb(cb) VALUES (_binary '";
+  print $FILE pack "H*","5c00";
+  print $FILE "');\n";
+
+  # INSERT INTO tb(cb) VALUES(_binary '\0A');
+  print $FILE "INSERT INTO tb(cb) VALUES (_binary '";
+  print $FILE pack "H*","5c0041";
+  print $FILE "');\n";
+
+  # INSERT INTO tb(cb) VALUES(_binary 'A\0');
+  print $FILE "INSERT INTO tb(cb) VALUES (_binary '";
+  print $FILE pack "H*","415c00";
+  print $FILE "');\n";
+
+  # INSERT INTO tb(cb) VALUES(_binary 'A\0B');
+  print $FILE "INSERT INTO tb(cb) VALUES (_binary '";
+  print $FILE pack "H*","415c0042";
+  print $FILE "');\n";
+
+  # INSERT INTO tb(cb) VALUES(_binary '\0A\0');
+  print $FILE "INSERT INTO tb(cb) VALUES (_binary '";
+  print $FILE pack "H*","5c00415c00";
+  print $FILE "');\n";
+
+  # INSERT INTO tb(cb) VALUES(_binary '\\\0');
+  print $FILE "INSERT INTO tb(cb) VALUES (_binary '";
+  print $FILE pack "H*","5c5c5c00";
+  print $FILE "');\n";
+
+  # INSERT INTO tb(cb) VALUES(_binary '\0\0');
+  print $FILE "INSERT INTO tb(cb) VALUES (_binary '";
+  print $FILE pack "H*","5c005c00";
+  print $FILE "');\n";
+
+  # INSERT INTO tb(cb) VALUES(_binary '\\0');
+  print $FILE "INSERT INTO tb(cb) VALUES (_binary '";
+  print $FILE pack "H*","5c5c00";
+  print $FILE "');\n";
+
+  close ($FILE);
+EOF
+--let SEARCH_PATTERN= \x5c\x00
+--let SEARCH_FILE= $MYSQL_TMP_DIR/binary_zero_inserts.sql
+--source include/search_pattern_in_file.inc
+--echo # MYSQL --binary-mode test < MYSQL_TMP_DIR/binary_zero_inserts.sql
+--exec $MYSQL --binary-mode test < $MYSQL_TMP_DIR/binary_zero_inserts.sql
+
+--echo #
+--echo # Ensure a row exists from each insert statement with a \0
+--echo #
+SELECT COUNT(*)=8 from tb;
+
+--echo #
+--echo # Ensure that the binary zero was parsed and exists in the row data
+--echo # Note: We only look for 00 because the 5c only served as an escape
+--echo #       in parsing.
+--echo #
+--echo # MYSQL_DUMP test tb --hex-blob | grep INSERT > MYSQL_TMP_DIR/dump.sql
+--exec $MYSQL_DUMP test tb --hex-blob | grep INSERT > $MYSQL_TMP_DIR/dump.sql
+--let SEARCH_PATTERN= 00
+--let SEARCH_FILE= $MYSQL_TMP_DIR/dump.sql
+--source include/search_pattern_in_file.inc
+
+--echo #
+--echo # Ensure data consistency on mysqlbinlog replay
+--echo #
+--let $good_checksum= `CHECKSUM TABLE tb`
+let $MYSQLD_DATADIR= `SELECT @@datadir`;
+let $binlog_file= query_get_value(SHOW MASTER STATUS, File, 1);
+FLUSH LOGS;
+--echo # MYSQL_BINLOG MYSQLD_DATADIR/binlog_file > MYSQL_TMP_DIR/binlog_zeros.sql
+--exec $MYSQL_BINLOG $MYSQLD_DATADIR/$binlog_file > $MYSQL_TMP_DIR/binlog_zeros.sql
+--let SEARCH_PATTERN= \x5c\x00
+--let SEARCH_FILE= $MYSQL_TMP_DIR/binlog_zeros.sql
+--source include/search_pattern_in_file.inc
+--echo # MYSQL --binary-mode test < MYSQL_TMP_DIR/binlog_zeros.sql
+--exec $MYSQL --binary-mode test < $MYSQL_TMP_DIR/binlog_zeros.sql
+if ($good_checksum != `CHECKSUM TABLE tb`)
+{
+    die "Blob with binary zero data changed after binary log replay";
+}
+--echo # Table checksum is equivalent before and after binlog replay
+
+--echo #
+--echo # A \0 should still be treated as end-of-query in binary mode.
+--echo #
+--perl
+  my $dir= $ENV{'MYSQL_TMP_DIR'};
+  open (my $FILE, '>', "$dir/binary_zero_eoq.sql") or die "open(): $!";
+
+  # INSERT INTO tb(cb) VALUES(_binary 'text')\0
+  print $FILE "INSERT INTO tb(cb) VALUES (_binary 'text')";
+  print $FILE pack "H*","5c00";
+
+  close ($FILE);
+EOF
+--echo # MYSQL --binary-mode -B test < MYSQL_TMP_DIR/binary_zero_eoq.sql
+--exec $MYSQL --binary-mode -B test < $MYSQL_TMP_DIR/binary_zero_eoq.sql
+
+
+--echo ##############################
+--echo #      Cleanup
+--echo ##############################
+
+--remove_file $MYSQL_TMP_DIR/binary_zero_inserts.sql
+--remove_file $MYSQL_TMP_DIR/binary_zero_eoq.sql
+--remove_file $MYSQL_TMP_DIR/binlog_zeros.sql
+--remove_file $MYSQL_TMP_DIR/dump.sql
+SET @@global.sql_mode= @old_sql_mode;
+drop table tb;
+RESET MASTER;