MDEV-11227 - mysqlimport -l doesn't issue UNLOCK TABLES

Implementation of MDEV-7660 introduced unwanted incompatible change:
modifications under LOCK TABLES with autocommit enabled are rolled back on
disconnect. Previously everything was committed, because LOCK TABLES didn't
adjust autocommit setting.

This patch restores original behavior by reverting some changes done in
MDEV-7660:
- sql/sql_parse.cc: do not reset autocommit on LOCK TABLES
- sql/sql_base.cc: do not set autocommit on UNLOCK TABLES
- test cases: main.lock_tables_lost_commit, main.partition_explicit_prune,
  rpl.rpl_switch_stm_row_mixed, tokudb.nested_txn_implicit_commit,
  tokudb_bugs.db806

But it makes InnoDB tables under LOCK TABLES ... READ [LOCAL] not protected
against DML. To restore protection some changes from WL#6671 were merged,
specifically MDL_SHARED_READ_ONLY and test cases.

WL#6671 merge highlights:
- Not all tests merged.
- In MySQL LOCK TABLES ... READ acquires MDL_SHARED_READ_ONLY for all engines,
  in MariaDB MDL_SHARED_READ is always acquired first and then upgraded to
  MDL_SHARED_READ_ONLY for InnoDB only.
- The above allows us to omit MDL_SHARED_WRITE_LOW_PRIO implementation in
  MariaDB, which is rather useless with InnoDB. In MySQL it is needed to
  preserve locking behavior between low priority writes and LOCK TABLES ... READ
  for non-InnoDB engines (covered by sys_vars.sql_low_priority_updates_func).
- Omitted HA_NO_READ_LOCAL_LOCK, we rely on lock_count() instead.
- Omitted "piglets": in MariaDB stream of DML against InnoDB table may lead to
  concurrent LOCK TABLES ... READ starvation.
- HANDLER ... OPEN acquires MDL_SHARED_READ instead of MDL_SHARED in MariaDB.
- Omitted SNRW->X MDL lock upgrade for IMPORT/DISCARD TABLESPAECE under LOCK
  TABLES.
- Omitted strong locks for views, triggers and SP under LOCK TABLES.
- Omitted IX schema lock for LOCK TABLES READ.
- Omitted deadlock weight juggling for LOCK TABLES.

Full WL#6671 merge status:
- innodb.innodb-lock: fully merged
- main.alter_table: not merged due to different HANDLER solution
- main.debug_sync: fully merged
- main.handler_innodb: not merged due to different HANDLER solution
- main.handler_myisam: not merged due to different HANDLER solution
- main.innodb_mysql_lock: fully merged
- main.insert_notembedded: fully merged
- main.lock: not merged (due to no strong locks for views)
- main.lock_multi: not merged
- main.lock_sync: fully merged (partially in MDEV-7660)
- main.mdl_sync: not merged
- main.partition_debug_sync: not merged due to different HANDLER solution
- main.status: fully merged
- main.view: fully merged
- perfschema.mdl_func: not merged (no such test in MariaDB)
- perfschema.table_aggregate_global_2u_2t: not merged (didn't fail in MariaDB)
- perfschema.table_aggregate_global_2u_3t: not merged (didn't fail in MariaDB)
- perfschema.table_aggregate_global_4u_2t: not merged (didn't fail in MariaDB)
- perfschema.table_aggregate_global_4u_3t: not merged (didn't fail in MariaDB)
- perfschema.table_aggregate_hist_2u_2t: not merged (didn't fail in MariaDB)
- perfschema.table_aggregate_hist_2u_3t: not merged (didn't fail in MariaDB)
- perfschema.table_aggregate_hist_4u_2t: not merged (didn't fail in MariaDB)
- perfschema.table_aggregate_hist_4u_3t: not merged (didn't fail in MariaDB)
- perfschema.table_aggregate_thread_2u_2t: not merged (didn't fail in MariaDB)
- perfschema.table_aggregate_thread_2u_3t: not merged (didn't fail in MariaDB)
- perfschema.table_aggregate_thread_4u_2t: not merged (didn't fail in MariaDB)
- perfschema.table_aggregate_thread_4u_3t: not merged (didn't fail in MariaDB)
- perfschema.table_lock_aggregate_global_2u_2t: not merged (didn't fail in MariaDB)
- perfschema.table_lock_aggregate_global_2u_3t: not merged (didn't fail in MariaDB)
- perfschema.table_lock_aggregate_global_4u_2t: not merged (didn't fail in MariaDB)
- perfschema.table_lock_aggregate_global_4u_3t: not merged (didn't fail in MariaDB)
- perfschema.table_lock_aggregate_hist_2u_2t: not merged (didn't fail in MariaDB)
- perfschema.table_lock_aggregate_hist_2u_3t: not merged (didn't fail in MariaDB)
- perfschema.table_lock_aggregate_hist_4u_2t: not merged (didn't fail in MariaDB)
- perfschema.table_lock_aggregate_hist_4u_3t: not merged (didn't fail in MariaDB)
- perfschema.table_lock_aggregate_thread_2u_2t: not merged (didn't fail in MariaDB)
- perfschema.table_lock_aggregate_thread_2u_3t: not merged (didn't fail in MariaDB)
- perfschema.table_lock_aggregate_thread_4u_2t: not merged (didn't fail in MariaDB)
- perfschema.table_lock_aggregate_thread_4u_3t: not merged (didn't fail in MariaDB)
- sys_vars.sql_low_priority_updates_func: not merged
- include/thr_rwlock.h: not merged, rw_pr_lock_assert_write_owner and
  rw_pr_lock_assert_not_write_owner are macros in MariaDB
- sql/handler.h: not merged (HA_NO_READ_LOCAL_LOCK)
- sql/mdl.cc: partially merged (MDL_SHARED_READ_ONLY only)
- sql/mdl.h: partially merged (MDL_SHARED_READ_ONLY only)
- sql/lock.cc: fully merged
- sql/sp_head.cc: not merged
- sql/sp_head.h: not merged
- sql/sql_base.cc: partially merged (MDL_SHARED_READ_ONLY only)
- sql/sql_base.h: not merged
- sql/sql_class.cc: fully merged
- sql/sql_class.h: fully merged
- sql/sql_handler.cc: merged partially (different solution in MariaDB)
- sql/sql_parse.cc: partially merged, mostly omitted low priority write part
- sql/sql_reload.cc: not merged comment change
- sql/sql_table.cc: not merged SNRW->X upgrade for IMPORT/DISCARD TABLESPACE
- sql/sql_view.cc: not merged
- sql/sql_yacc.yy: not merged (MDL_SHARED_WRITE_LOW_PRIO, MDL_SHARED_READ_ONLY)
- sql/table.cc: not merged (MDL_SHARED_WRITE_LOW_PRIO)
- sql/table.h: not merged (MDL_SHARED_WRITE_LOW_PRIO)
- sql/trigger.cc: not merged
- storage/innobase/handler/ha_innodb.cc: merged store_lock()/lock_count()
  changes (in MDEV-7660), didn't merge HA_NO_READ_LOCAL_LOCK
- storage/innobase/handler/ha_innodb.h: fully merged in MDEV-7660
- storage/myisammrg/ha_myisammrg.cc: not merged comment change
- storage/perfschema/table_helper.cc: not merged (no MDL support in MariaDB PFS)
- unittest/gunit/mdl-t.cc: not merged
- unittest/gunit/mdl_sync-t.cc: not merged

MariaDB specific changes:
- handler.heap: different HANDLER solution, MDEV-7660
- handler.innodb: different HANDLER solution, MDEV-7660
- handler.interface: different HANDLER solution, MDEV-7660
- handler.myisam: different HANDLER solution, MDEV-7660
- main.mdl_sync: MDEV-7660 specific changes
- main.partition_debug_sync: removed test due to different HANDLER solution,
  MDEV-7660
- main.truncate_coverage: removed test due to different HANDLER solution,
  MDEV-7660
- mysql-test/include/mtr_warnings.sql: additional cleanup, MDEV-7660
- mysql-test/lib/v1/mtr_report.pl: additional cleanup, MDEV-7660
- plugin/metadata_lock_info/metadata_lock_info.cc: not in MySQL
- sql/sql_handler.cc: MariaDB specific fix for mysql_ha_read(), MDEV-7660

6 files changed, 162 insertions, 116 deletions

diff --git a/mysql-test/t/debug_sync.test b/mysql-test/t/debug_sync.test
index aead6f3ac2e..89414939f59 100644
--- a/mysql-test/t/debug_sync.test
+++ b/mysql-test/t/debug_sync.test
@@ -381,21 +381,40 @@ DROP TABLE IF EXISTS t1;
 --enable_warnings
 #
 # Test.
-CREATE TABLE t1 (c1 INT);
-LOCK TABLE t1 READ;
-    connect (con1,localhost,root,,);
-    # Retain action after use. First used by general_log.
-    SET DEBUG_SYNC= 'wait_for_lock SIGNAL locked EXECUTE 2';
-    send INSERT INTO t1 VALUES (1);
+CREATE TABLE t1 (c1 INT) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1);
+SELECT GET_LOCK('mysqltest_lock', 100);
+
+connect (con1,localhost,root,,);
+--echo # Sending:
+--send UPDATE t1 SET c1=GET_LOCK('mysqltest_lock', 100);
+
+connect (con2,localhost,root,,);
+let $wait_condition=
+  select count(*) = 1 from information_schema.processlist
+  where state = "User lock" and
+        info = "UPDATE t1 SET c1=GET_LOCK('mysqltest_lock', 100)";
+--source include/wait_condition.inc
+
+# Retain action after use. First used by general_log.
+SET DEBUG_SYNC= 'wait_for_lock SIGNAL locked EXECUTE 2';
+send INSERT INTO t1 VALUES (1);
+
 connection default;
 # Wait until INSERT waits for lock.
 SET DEBUG_SYNC= 'now WAIT_FOR locked';
-# let INSERT continue.
-UNLOCK TABLES;
-    connection con1;
-    --echo retrieve INSERT result.
-    reap;
-    disconnect con1;
+# let UPDATE continue.
+SELECT RELEASE_LOCK('mysqltest_lock');
+connection con1;
+--echo # Reaping UPDATE
+reap;
+SELECT RELEASE_LOCK('mysqltest_lock');
+
+connection con2;
+--echo retrieve INSERT result.
+reap;
+disconnect con1;
+disconnect con2;
 connection default;
 DROP TABLE t1;
 
diff --git a/mysql-test/t/innodb_mysql_lock.test b/mysql-test/t/innodb_mysql_lock.test
index 85ba41860df..39ea7e5df88 100644
--- a/mysql-test/t/innodb_mysql_lock.test
+++ b/mysql-test/t/innodb_mysql_lock.test
@@ -64,6 +64,7 @@ set @@autocommit=1;
 connection default;
 
 disconnect con1;
+disconnect con2;
 disconnect con3;
 
 
@@ -116,54 +117,6 @@ connection default;
 disconnect con37346;
 drop table t1;
 
-
---echo #
---echo # Bug #42147 Concurrent DML and LOCK TABLE ... READ for InnoDB 
---echo #            table cause warnings in errlog
---echo #
-
---echo #
---echo # Note that this test for now relies on a global suppression of 
---echo # the warning "Found lock of type 6 that is write and read locked"
---echo # This suppression rule can be removed once Bug#42147 is properly
---echo # fixed. See bug page for more info.
---echo #
-
---disable_warnings
-DROP TABLE IF EXISTS t1;
---enable_warnings
-
-CREATE TABLE t1 (i INT) engine= innodb;
-
---echo # Get user-level lock
-connection con2;
-SELECT get_lock('bug42147_lock', 60);
-
-connection default;
---send INSERT INTO t1 SELECT get_lock('bug42147_lock', 60)
-
-connection con2;
-let $wait_condition=
-  SELECT COUNT(*) > 0 FROM information_schema.processlist
-  WHERE state = 'User lock' 
-  AND info = 'INSERT INTO t1 SELECT get_lock(\'bug42147_lock\', 60)';
---source include/wait_condition.inc
-LOCK TABLES t1 READ;
-SELECT release_lock('bug42147_lock');
-let $wait_condition=
-  SELECT COUNT(*) > 0 FROM information_schema.processlist
-  WHERE state = 'executing'
-  AND info = 'INSERT INTO t1 SELECT get_lock(\'bug42147_lock\', 60)';
---source include/wait_condition.inc
-UNLOCK TABLES;
-
-connection default;
---reap
-
-disconnect con2;
-DROP TABLE t1;
-
-
 --echo #
 --echo # Bug#53798 OPTIMIZE TABLE breaks repeatable read
 --echo #
diff --git a/mysql-test/t/insert_notembedded.test b/mysql-test/t/insert_notembedded.test
index 713eaf5db40..2769aee8d8a 100644
--- a/mysql-test/t/insert_notembedded.test
+++ b/mysql-test/t/insert_notembedded.test
@@ -156,41 +156,5 @@ connection default;
 
 DROP DATABASE meow;
 
-#
-# Bug#28587 SELECT is blocked by INSERT waiting on read lock, even with low_priority_updates
-#
---echo connection: default
-set low_priority_updates=1;
---disable_warnings
-drop table if exists t1;
---enable_warnings
-create table t1 (a int, b int, unique key t1$a (a));
-lock table t1 read;
-connect (update,localhost,root,,);
-connection update;
---echo connection: update
-set low_priority_updates=1;
-show variables like 'low_priority_updates';
-let $ID= `select connection_id()`;
---send insert into t1 values (1, 2) ON DUPLICATE KEY UPDATE b = 2;
-connection default;
-# we must wait till the insert opens and locks the table
-let $wait_condition=
-  select count(*) = 1 from information_schema.processlist
-  where state = "Waiting for table level lock" and id = $ID;
---source include/wait_condition.inc
-connect (select,localhost,root,,);
---echo connection: select
-select * from t1;
-connection default;
---echo connection: default
-select * from t1;
-connection default;
-disconnect update;
-disconnect select;
-unlock tables;
-drop table t1;
-set low_priority_updates=default;
-
 set local sql_mode=default;
 set global sql_mode=default;
diff --git a/mysql-test/t/lock_sync.test b/mysql-test/t/lock_sync.test
index 07c16acc72a..af8435f7fbb 100644
--- a/mysql-test/t/lock_sync.test
+++ b/mysql-test/t/lock_sync.test
@@ -998,6 +998,102 @@ DROP TABLE t1;
 disconnect con1;
 disconnect con2;
 
+
+--echo #
+--echo # Bug#28587 SELECT is blocked by INSERT waiting on read lock, even with low_priority_updates
+--echo #
+set low_priority_updates=1;
+--disable_warnings
+drop table if exists t1;
+drop table if exists t2;
+--enable_warnings
+set debug_sync='RESET';
+create table t1 (a int, b int, unique key t1$a (a));
+create table t2 (j int, k int);
+set debug_sync='after_lock_tables_takes_lock SIGNAL parked WAIT_FOR go';
+--echo # Sending:
+--send insert into t2 select * from t1;
+connect (update,localhost,root,,);
+connection update;
+set debug_sync='now WAIT_FOR parked';
+set low_priority_updates=1;
+show variables like 'low_priority_updates';
+let $ID= `select connection_id()`;
+--send insert into t1 values (1, 2) ON DUPLICATE KEY UPDATE b = 2;
+connect (select,localhost,root,,);
+# we must wait till the insert opens and locks the table
+let $wait_condition=
+  select count(*) = 1 from information_schema.processlist
+  where state = "Waiting for table level lock" and id = $ID;
+--source include/wait_condition.inc
+select * from t1;
+set debug_sync='now SIGNAL go';
+connection default;
+disconnect update;
+disconnect select;
+--echo # Reaping INSERT SELECT
+--reap
+drop tables t1, t2;
+set low_priority_updates=default;
+set debug_sync='RESET';
+
+
+--echo #
+--echo # Additional test coverage for LOCK TABLES ... READ LOCAL
+--echo # for InnoDB tables.
+--echo #
+--echo # Check that we correctly handle deadlocks which can occur
+--echo # during metadata lock upgrade which happens when one tries
+--echo # to use LOCK TABLES ... READ LOCAL for InnoDB tables.
+
+--enable_connect_log
+CREATE TABLE t1 (i INT) ENGINE=InnoDB;
+CREATE TABLE t2 (j INT) ENGINE=InnoDB;
+
+--echo # Execute LOCK TABLE READ LOCK which will pause after acquiring
+--echo # SR metadata lock and before upgrading it to SRO lock.
+SET DEBUG_SYNC="after_open_table_mdl_shared SIGNAL locked WAIT_FOR go";
+--echo # Sending:
+--send LOCK TABLE t1 READ LOCAL
+
+connect (con1, localhost, root);
+SET DEBUG_SYNC="now WAIT_FOR locked";
+--echo # Execute RENAME TABLE which will try to acquire X lock.
+--echo # Sending:
+--send RENAME TABLE t1 TO t3, t2 TO t1, t3 TO t2
+
+connect (con2, localhost, root);
+--echo # Wait until RENAME TABLE is blocked.
+let $wait_condition=
+  select count(*) = 1 from information_schema.processlist
+  where state = "Waiting for table metadata lock" and
+        info = "RENAME TABLE t1 TO t3, t2 TO t1, t3 TO t2";
+--source include/wait_condition.inc
+--echo # Resume LOCK TABLE statement. It should try to
+--echo # upgrade SR lock to SRO lock which will create
+--echo # deadlock due to presence of pending X lock.
+--echo # Deadlock should be detected and LOCK TABLES should
+--echo # release its MDL and retry opening of tables.
+SET DEBUG_SYNC="now SIGNAL go";
+
+connection con1;
+--echo # RENAME TABLE should be able to complete. Reap it.
+--reap
+
+connection default;
+--echo # Reap LOCK TABLES.
+--reap
+--echo # Check that we see new version of table.
+SELECT * FROM t1;
+UNLOCK TABLES;
+
+--echo # Clean-up.
+SET DEBUG_SYNC="RESET";
+disconnect con1;
+disconnect con2;
+DROP TABLES t1, t2;
+--disable_connect_log
+
 # Check that all connections opened by test cases in this file are really
 # gone so execution of other tests won't be affected by their presence.
 --source include/wait_until_count_sessions.inc
diff --git a/mysql-test/t/status.test b/mysql-test/t/status.test
index e0b0d9c8838..7ab32241bcb 100644
--- a/mysql-test/t/status.test
+++ b/mysql-test/t/status.test
@@ -23,13 +23,13 @@ SET GLOBAL LOG_OUTPUT = 'FILE';
 
 connect (con1,localhost,root,,);
 connect (con2,localhost,root,,);
+connection default;
 
 flush status;
 
 show status like 'Table_lock%';
 select * from information_schema.session_status where variable_name like 'Table_lock%';
 
-connection con1;
 set sql_log_bin=0;
 set @old_general_log = @@global.general_log;
 set global general_log = 'OFF';
@@ -39,35 +39,46 @@ drop table if exists t1;
 
 create table t1(n int) engine=myisam;
 insert into t1 values(1);
-# Execute dummy select in order to ensure that tables used in the
-# previous statement are unlocked and closed.
-select 1;
+select get_lock('mysqltest_lock', 100);
 
 connection con2;
-lock tables t1 read;
-unlock tables;
-lock tables t1 read;
+--echo # Sending:
+--send update t1 set n = get_lock('mysqltest_lock', 100)
 
 connection con1;
+--echo # Wait for the first UPDATE to get blocked.
+let $wait_condition= select count(*) from INFORMATION_SCHEMA.PROCESSLIST
+                     where STATE = "User lock" and
+                           INFO = "update t1 set n = get_lock('mysqltest_lock', 100)";
+--source include/wait_condition.inc
+
 let $ID= `select connection_id()`;
+--echo # Sending:
 --send update t1 set n = 3
 
-connection con2;
-# wait for the other query to start executing
+connection default;
+--echo # wait for the second UPDATE to get blocked
 let $wait_condition= select 1 from INFORMATION_SCHEMA.PROCESSLIST
                      where ID = $ID and STATE = "Waiting for table level lock";
 --source include/wait_condition.inc
-unlock tables;
+select release_lock('mysqltest_lock');
+
+connection con2;
+--echo # Reaping first UPDATE
+--reap
+select release_lock('mysqltest_lock');
 
 connection con1;
+--echo # Reaping second UPDATE
 reap;
 show status like 'Table_locks_waited';
+
+connection default;
 drop table t1;
 set global general_log = @old_general_log;
 
 disconnect con2;
 disconnect con1;
-connection default;
 
 # End of 4.1 tests
 
diff --git a/mysql-test/t/view.test b/mysql-test/t/view.test
index 59ff87f0bad..c3f462eab18 100644
--- a/mysql-test/t/view.test
+++ b/mysql-test/t/view.test
@@ -4636,7 +4636,6 @@ DROP TABLE t1, t2;
 --echo #
 
 --disable_warnings
-DROP TABLE IF EXISTS t1;
 DROP VIEW IF EXISTS v1;
 DROP PROCEDURE IF EXISTS p1;
 --enable_warnings
@@ -4647,25 +4646,27 @@ connect (con3, localhost, root);
 connection default;
 
 CREATE VIEW v1 AS SELECT schema_name FROM information_schema.schemata;
-CREATE TABLE t1 (str VARCHAR(50));
-CREATE PROCEDURE p1() INSERT INTO t1 SELECT * FROM v1;
+CREATE PROCEDURE p1() SELECT COUNT(*), GET_LOCK('blocker', 100) FROM v1;
 
 --echo # CALL p1() so the view is merged.
+--disable_result_log
 CALL p1();
+--enable_result_log
+SELECT RELEASE_LOCK('blocker');
 
 connection con3;
-LOCK TABLE t1 READ;
+SELECT GET_LOCK('blocker', 100);
 
 connection default;
---echo # Try to CALL p1() again, this time it should block for t1.
+--echo # Try to CALL p1() again, this time it should block on "blocker".
 --echo # Sending:
 --send CALL p1()
 
 connection con2;
 let $wait_condition=
   SELECT COUNT(*) = 1 from information_schema.processlist
-  WHERE state = "Waiting for table level lock" AND
-        info = "INSERT INTO t1 SELECT * FROM v1";
+  WHERE state = "User lock" AND
+        info = "SELECT COUNT(*), GET_LOCK('blocker', 100) FROM v1";
 --source include/wait_condition.inc
 --echo # ... then try to drop the view. This should block.
 --echo # Sending:
@@ -4677,11 +4678,14 @@ let $wait_condition=
   WHERE state = "Waiting for table metadata lock" AND info = "DROP VIEW v1";
 --source include/wait_condition.inc
 --echo # Now allow CALL p1() to complete
-UNLOCK TABLES;
+SELECT RELEASE_LOCK('blocker');
 
 connection default;
 --echo # Reaping: CALL p1()
+--disable_result_log
 --reap
+--enable_result_log
+SELECT RELEASE_LOCK('blocker');
 
 connection con2;
 --echo # Reaping: DROP VIEW v1
@@ -4689,7 +4693,6 @@ connection con2;
 
 connection default;
 DROP PROCEDURE p1;
-DROP TABLE t1;
 disconnect con2;
 disconnect con3;