MDEV-15373 engine gtid_slave_pos table name disobeys lower-case-table-names

Replicated transaction extra gtid statement on slave failed to specify
an engine gtid_slave_pos name correctly. In case  lower-case-table-names > 0
the InnoDB table name was generated to reproduce the lower-case-table-names=0 version
which is of mixed cases.
In rpl.rpl_mdev12179 test run this triggered a failure to DROP table which
was due to the innodb table handle was not closed:
 InnoDB: Waited XYZ seconds for ref-count on table: `mysql`.`gtid_slave_pos_innodb`
on windows.
The closing issue was caused by having the table registered twice in the table cache,
for its lower- and mixed- case name versions. The DROP-table handler closed only
only one of the cache item to leave the 2nd one active.
(On Linux a failure occurs earlier at attempt to open an expected lower-cased table:

  Last_Error: Error during XID COMMIT: failed to update GTID state in mysql.gtid_slave_pos: 1146: Table 'mysql.gtid_slave_pos_InnoDB' doesn't exist

but the table's name as the message shows is not in the right case).

Fixed with consulting lower-case-table-names when the engine gtid-slave-pos table
is created.
Note the lower-case-table-names=a-value created table will not recognized when next
the lower case option changes to a different value.
In 10.4 a follow-up patch is going to lowercase gtid-slave-pos autocreated table
at once at their origination, and a warning is issued in the 10.3 current patch.

2 files changed, 54 insertions, 8 deletions

diff --git a/mysql-test/suite/rpl/r/rpl_mdev12179.result b/mysql-test/suite/rpl/r/rpl_mdev12179.result
index 40059375356..8373eb43774 100644
--- a/mysql-test/suite/rpl/r/rpl_mdev12179.result
+++ b/mysql-test/suite/rpl/r/rpl_mdev12179.result
@@ -1,5 +1,6 @@
 include/rpl_init.inc [topology=1->2]
 connection server_2;
+call mtr.add_suppression("The automatically created table.*name may not be entirely in lowercase");
 SET GLOBAL gtid_pos_auto_engines="innodb";
 ERROR HY000: This operation cannot be performed as you have a running slave ''; run STOP SLAVE '' first
 include/stop_slave.inc
@@ -152,12 +153,12 @@ a
 1
 2
 *** Verify that mysql.gtid_slave_pos_InnoDB is auto-created ***
-SELECT table_name, engine FROM information_schema.tables
+SELECT lower(table_name), engine FROM information_schema.tables
 WHERE table_schema='mysql' AND table_name LIKE 'gtid_slave_pos%'
  ORDER BY table_name;
-table_name	engine
+lower(table_name)	engine
 gtid_slave_pos	MyISAM
-gtid_slave_pos_InnoDB	InnoDB
+gtid_slave_pos_innodb	InnoDB
 include/stop_slave.inc
 SET sql_log_bin=0;
 INSERT INTO mysql.gtid_slave_pos SELECT * FROM mysql.gtid_slave_pos_InnoDB;
@@ -245,15 +246,24 @@ a
 3
 4
 *** Verify that mysql.gtid_slave_pos_InnoDB is auto-created ***
-SELECT table_name, engine FROM information_schema.tables
+SELECT lower(table_name), engine FROM information_schema.tables
 WHERE table_schema='mysql' AND table_name LIKE 'gtid_slave_pos%'
  ORDER BY table_name;
-table_name	engine
+lower(table_name)	engine
 gtid_slave_pos	MyISAM
-gtid_slave_pos_InnoDB	InnoDB
+gtid_slave_pos_innodb	InnoDB
 SELECT domain_id, max(seq_no) FROM mysql.gtid_slave_pos GROUP BY domain_id;
 domain_id	max(seq_no)
 0	13
+connection server_2;
+*** Restart the slave server to prove 'gtid_slave_pos_innodb' autodiscovery ***
+connection server_2;
+SELECT max(seq_no) FROM mysql.gtid_slave_pos_InnoDB into @seq_no;
+connection server_1;
+INSERT INTO t2(a) SELECT 1+MAX(a) FROM t2;
+include/save_master_gtid.inc
+connection server_2;
+include/sync_with_master_gtid.inc
 include/stop_slave.inc
 SET GLOBAL gtid_pos_auto_engines="";
 SET sql_log_bin=0;
diff --git a/mysql-test/suite/rpl/t/rpl_mdev12179.test b/mysql-test/suite/rpl/t/rpl_mdev12179.test
index a9113c91797..eb0f6c04b42 100644
--- a/mysql-test/suite/rpl/t/rpl_mdev12179.test
+++ b/mysql-test/suite/rpl/t/rpl_mdev12179.test
@@ -3,6 +3,8 @@
 --source include/rpl_init.inc
 
 --connection server_2
+call mtr.add_suppression("The automatically created table.*name may not be entirely in lowercase");
+
 --error ER_SLAVE_MUST_STOP
 SET GLOBAL gtid_pos_auto_engines="innodb";
 --source include/stop_slave.inc
@@ -161,7 +163,8 @@ let $wait_condition=
   SELECT EXISTS (SELECT * FROM information_schema.tables
                   WHERE table_schema='mysql' AND table_name='gtid_slave_pos_InnoDB');
 --source include/wait_condition.inc
-SELECT table_name, engine FROM information_schema.tables
+# MDEV-15373 lowercases 'table_name' to satisfy --lower-case-table-names options
+SELECT lower(table_name), engine FROM information_schema.tables
  WHERE table_schema='mysql' AND table_name LIKE 'gtid_slave_pos%'
  ORDER BY table_name;
 
@@ -225,7 +228,7 @@ let $wait_condition=
   SELECT EXISTS (SELECT * FROM information_schema.tables
                   WHERE table_schema='mysql' AND table_name='gtid_slave_pos_InnoDB');
 --source include/wait_condition.inc
-SELECT table_name, engine FROM information_schema.tables
+SELECT lower(table_name), engine FROM information_schema.tables
  WHERE table_schema='mysql' AND table_name LIKE 'gtid_slave_pos%'
  ORDER BY table_name;
 SELECT domain_id, max(seq_no) FROM mysql.gtid_slave_pos GROUP BY domain_id;
@@ -265,6 +268,39 @@ while (!$done)
 # Note that at this point, the contents of table t2, as well as the GTID
 # position, is non-deterministic.
 
+# MDEV-15373 engine gtid_slave_pos table name disobeys lower-case-table-names
+# This snippet verifies that engine gtid_slave_pos table is found,
+# its data are up-to-date.
+--write_file $MYSQLTEST_VARDIR/tmp/mysqld.2.expect
+wait
+EOF
+--connection server_2
+--shutdown_server 30
+--source include/wait_until_disconnected.inc
+
+--echo *** Restart the slave server to prove 'gtid_slave_pos_innodb' autodiscovery ***
+--append_file $MYSQLTEST_VARDIR/tmp/mysqld.2.expect
+restart: --skip-slave-start=0
+EOF
+
+--connection server_2
+--enable_reconnect
+--source include/wait_until_connected_again.inc
+SELECT max(seq_no) FROM mysql.gtid_slave_pos_InnoDB into @seq_no;
+
+--connection server_1
+INSERT INTO t2(a) SELECT 1+MAX(a) FROM t2;
+--source include/save_master_gtid.inc
+
+--connection server_2
+--source include/sync_with_master_gtid.inc
+if (`SELECT  max(seq_no) <> @seq_no + 1  FROM mysql.gtid_slave_pos_InnoDB`)
+{
+  SELECT * FROM mysql.gtid_slave_pos_InnoDB;
+  --die Inconsistent table
+}
+#
+# end of MDEV-15373
 
 #--connection server_2
 --source include/stop_slave.inc