8 files changed, 210 insertions, 2 deletions
diff --git a/mysql-test/suite/sql_sequence/concurrent_create.result b/mysql-test/suite/sql_sequence/concurrent_create.result
index 7e68195f7e0..2473abef37d 100644
--- a/mysql-test/suite/sql_sequence/concurrent_create.result
+++ b/mysql-test/suite/sql_sequence/concurrent_create.result
@@ -31,3 +31,16 @@ connection con1;
 disconnect con1;
 connection default;
 DROP TABLE s1,s2;
+CREATE TABLE t1 (a INT) ENGINE=InnoDB;
+START TRANSACTION WITH CONSISTENT SNAPSHOT;
+connect  con1,localhost,root,,test;
+CREATE SEQUENCE s1 ENGINE=InnoDB;
+FLUSH TABLES;
+disconnect con1;
+connection default;
+SELECT NEXTVAL(s1);
+NEXTVAL(s1)
+1
+COMMIT;
+DROP TABLE t1;
+DROP SEQUENCE s1;
diff --git a/mysql-test/suite/sql_sequence/concurrent_create.test b/mysql-test/suite/sql_sequence/concurrent_create.test
index d6a57ff7d50..b27a6d3bdb9 100644
--- a/mysql-test/suite/sql_sequence/concurrent_create.test
+++ b/mysql-test/suite/sql_sequence/concurrent_create.test
@@ -56,3 +56,22 @@ FLUSH TABLES;
 --connection default
 
 DROP TABLE s1,s2;
+
+#
+# MDEV-24545 Sequence created by one connection remains invisible to another
+#
+CREATE TABLE t1 (a INT) ENGINE=InnoDB;
+START TRANSACTION WITH CONSISTENT SNAPSHOT;
+
+--connect (con1,localhost,root,,test)
+CREATE SEQUENCE s1 ENGINE=InnoDB;
+FLUSH TABLES;
+--disconnect con1
+
+--connection default
+SELECT NEXTVAL(s1);
+COMMIT;
+
+# Cleanup
+DROP TABLE t1;
+DROP SEQUENCE s1;
diff --git a/mysql-test/suite/sql_sequence/create.result b/mysql-test/suite/sql_sequence/create.result
index 14464c60e99..5a53a66c9a8 100644
--- a/mysql-test/suite/sql_sequence/create.result
+++ b/mysql-test/suite/sql_sequence/create.result
@@ -666,7 +666,7 @@ create temporary sequence s;
 drop temporary table s;
 create temporary table s (i int);
 drop temporary sequence s;
-ERROR 42S02: Unknown SEQUENCE: 'test.s'
+ERROR 42S02: 'test.s' is not a SEQUENCE
 drop table s;
 #
 # MDEV-15115 Assertion failure in CREATE SEQUENCE...ROW_FORMAT=REDUNDANT
diff --git a/mysql-test/suite/sql_sequence/create.test b/mysql-test/suite/sql_sequence/create.test
index 2c41fb3658b..ac3aae845cd 100644
--- a/mysql-test/suite/sql_sequence/create.test
+++ b/mysql-test/suite/sql_sequence/create.test
@@ -489,7 +489,7 @@ drop table s;
 create temporary sequence s;
 drop temporary table s;
 create temporary table s (i int);
---error ER_UNKNOWN_SEQUENCES
+--error ER_NOT_SEQUENCE2
 drop temporary sequence s;
 drop table s;
 
diff --git a/mysql-test/suite/sql_sequence/mysqldump.result b/mysql-test/suite/sql_sequence/mysqldump.result
index e6aedb57ea6..fb023cc5e36 100644
--- a/mysql-test/suite/sql_sequence/mysqldump.result
+++ b/mysql-test/suite/sql_sequence/mysqldump.result
@@ -2,8 +2,46 @@ CREATE SEQUENCE a1 engine=aria;
 CREATE TABLE t1(a INT, KEY (a)) KEY_BLOCK_SIZE=1024;
 insert into t1 values (1),(2);
 CREATE SEQUENCE x1 engine=innodb;
+# dump whole database
 CREATE SEQUENCE `a1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=Aria;
 SELECT SETVAL(`a1`, 1, 0);
+CREATE SEQUENCE `x1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=InnoDB;
+SELECT SETVAL(`x1`, 1, 0);
+/*!40101 SET @saved_cs_client     = @@character_set_client */;
+/*!40101 SET character_set_client = utf8 */;
+CREATE TABLE `t1` (
+  `a` int(11) DEFAULT NULL,
+  KEY `a` (`a`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1 KEY_BLOCK_SIZE=1024;
+/*!40101 SET character_set_client = @saved_cs_client */;
+INSERT INTO `t1` VALUES (1),(2);
+# dump by tables order 1
+CREATE SEQUENCE `a1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=Aria;
+SELECT SETVAL(`a1`, 1, 0);
+CREATE SEQUENCE `x1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=InnoDB;
+SELECT SETVAL(`x1`, 1, 0);
+/*!40101 SET @saved_cs_client     = @@character_set_client */;
+/*!40101 SET character_set_client = utf8 */;
+CREATE TABLE `t1` (
+  `a` int(11) DEFAULT NULL,
+  KEY `a` (`a`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1 KEY_BLOCK_SIZE=1024;
+/*!40101 SET character_set_client = @saved_cs_client */;
+INSERT INTO `t1` VALUES (1),(2);
+# dump by tables order 2
+CREATE SEQUENCE `a1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=Aria;
+SELECT SETVAL(`a1`, 1, 0);
+CREATE SEQUENCE `x1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=InnoDB;
+SELECT SETVAL(`x1`, 1, 0);
+/*!40101 SET @saved_cs_client     = @@character_set_client */;
+/*!40101 SET character_set_client = utf8 */;
+CREATE TABLE `t1` (
+  `a` int(11) DEFAULT NULL,
+  KEY `a` (`a`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1 KEY_BLOCK_SIZE=1024;
+/*!40101 SET character_set_client = @saved_cs_client */;
+INSERT INTO `t1` VALUES (1),(2);
+# dump by tables only tables
 /*!40101 SET @saved_cs_client     = @@character_set_client */;
 /*!40101 SET character_set_client = utf8 */;
 CREATE TABLE `t1` (
@@ -12,8 +50,12 @@ CREATE TABLE `t1` (
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1 KEY_BLOCK_SIZE=1024;
 /*!40101 SET character_set_client = @saved_cs_client */;
 INSERT INTO `t1` VALUES (1),(2);
+# dump by tables only sequences
+CREATE SEQUENCE `a1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=Aria;
+SELECT SETVAL(`a1`, 1, 0);
 CREATE SEQUENCE `x1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=InnoDB;
 SELECT SETVAL(`x1`, 1, 0);
+# end of dumps
 DROP TABLE a1,t1,x1;
 set default_storage_engine=InnoDB;
 create sequence t1;
diff --git a/mysql-test/suite/sql_sequence/mysqldump.test b/mysql-test/suite/sql_sequence/mysqldump.test
index 308f06d5e8d..d2afb2fd675 100644
--- a/mysql-test/suite/sql_sequence/mysqldump.test
+++ b/mysql-test/suite/sql_sequence/mysqldump.test
@@ -11,7 +11,18 @@ CREATE SEQUENCE a1 engine=aria;
 CREATE TABLE t1(a INT, KEY (a)) KEY_BLOCK_SIZE=1024;
 insert into t1 values (1),(2);
 CREATE SEQUENCE x1 engine=innodb;
+--echo # dump whole database
 --exec $MYSQL_DUMP --compact test
+--echo # dump by tables order 1
+--exec $MYSQL_DUMP --compact --tables test t1 a1 x1
+--echo # dump by tables order 2
+--exec $MYSQL_DUMP --compact --tables test a1 t1 x1
+--echo # dump by tables only tables
+--exec $MYSQL_DUMP --compact --tables test t1
+--echo # dump by tables only sequences
+--exec $MYSQL_DUMP --compact --tables test a1 x1
+--echo # end of dumps
+
 DROP TABLE a1,t1,x1;
 
 #
diff --git a/mysql-test/suite/sql_sequence/other.result b/mysql-test/suite/sql_sequence/other.result
index abc101b3c00..643233149d2 100644
--- a/mysql-test/suite/sql_sequence/other.result
+++ b/mysql-test/suite/sql_sequence/other.result
@@ -300,4 +300,63 @@ update t1 set p_first_name='Yunxi' where p_id=1;
 drop view v2;
 drop table t1,t2;
 drop sequence s1;
+#
+# MDEV-19273:Server crash in MDL_ticket::has_stronger_or_equal_type or
+# Assertion `thd->mdl_context.is_lock_owner(MDL_key::TABLE,
+# table->db.str, table->table_name.str, MDL_SHARED)' failed
+# in mysql_rm_table_no_locks
+#
+CREATE TABLE t1 (a INT);
+CREATE TEMPORARY TABLE tmp (b INT);
+LOCK TABLE t1 READ;
+DROP SEQUENCE tmp;
+ERROR 42S02: 'test.tmp' is not a SEQUENCE
+DROP TEMPORARY SEQUENCE tmp;
+ERROR 42S02: 'test.tmp' is not a SEQUENCE
+DROP SEQUENCE t1;
+ERROR HY000: Table 't1' was locked with a READ lock and can't be updated
+DROP TEMPORARY SEQUENCE t1;
+ERROR 42S02: Unknown SEQUENCE: 'test.t1'
+UNLOCK TABLES;
+DROP SEQUENCE t1;
+ERROR 42S02: 'test.t1' is not a SEQUENCE
+DROP TEMPORARY SEQUENCE t1;
+ERROR 42S02: Unknown SEQUENCE: 'test.t1'
+DROP TABLE t1;
+CREATE TABLE t (a INT);
+CREATE TEMPORARY TABLE s (f INT);
+CREATE SEQUENCE s;
+LOCK TABLE t WRITE;
+DROP SEQUENCE s;
+ERROR 42S02: 'test.s' is not a SEQUENCE
+DROP TEMPORARY SEQUENCE s;
+ERROR 42S02: 'test.s' is not a SEQUENCE
+UNLOCK TABLES;
+CREATE TEMPORARY SEQUENCE s;
+LOCK TABLE t WRITE;
+DROP TEMPORARY SEQUENCE s;
+UNLOCK TABLES;
+DROP TEMPORARY TABLE s;
+DROP SEQUENCE s;
+create table s(a INT);
+CREATE TEMPORARY TABLE s (f INT);
+LOCK TABLE t WRITE;
+DROP TEMPORARY TABLE s;
+CREATE TEMPORARY TABLE s (f INT);
+DROP TABLE s;
+DROP TABLE s;
+ERROR HY000: Table 's' was not locked with LOCK TABLES
+UNLOCK TABLES;
+DROP TABLE s;
+CREATE VIEW v1 as SELECT * FROM t;
+CREATE SEQUENCE s;
+DROP SEQUENCE IF EXISTS v1;
+Warnings:
+Note	4091	Unknown SEQUENCE: 'test.v1'
+DROP VIEW IF EXISTS s;
+Warnings:
+Note	4092	Unknown VIEW: 'test.s'
+DROP VIEW v1;
+DROP SEQUENCE s;
+DROP TABLE t;
 # End of 10.3 tests
diff --git a/mysql-test/suite/sql_sequence/other.test b/mysql-test/suite/sql_sequence/other.test
index 70c4efa40e5..0fbb2d0e2f9 100644
--- a/mysql-test/suite/sql_sequence/other.test
+++ b/mysql-test/suite/sql_sequence/other.test
@@ -315,4 +315,68 @@ drop view v2;
 drop table t1,t2;
 drop sequence s1;
 
+--echo #
+--echo # MDEV-19273:Server crash in MDL_ticket::has_stronger_or_equal_type or
+--echo # Assertion `thd->mdl_context.is_lock_owner(MDL_key::TABLE,
+--echo # table->db.str, table->table_name.str, MDL_SHARED)' failed
+--echo # in mysql_rm_table_no_locks
+--echo #
+
+CREATE TABLE t1 (a INT);
+CREATE TEMPORARY TABLE tmp (b INT);
+LOCK TABLE t1 READ;
+--error ER_NOT_SEQUENCE2
+DROP SEQUENCE tmp;
+--error ER_NOT_SEQUENCE2
+DROP TEMPORARY SEQUENCE tmp;
+--error ER_TABLE_NOT_LOCKED_FOR_WRITE
+DROP SEQUENCE t1;
+--error ER_UNKNOWN_SEQUENCES
+DROP TEMPORARY SEQUENCE t1;
+UNLOCK TABLES;
+--error ER_NOT_SEQUENCE2
+DROP SEQUENCE t1;
+--error ER_UNKNOWN_SEQUENCES
+DROP TEMPORARY SEQUENCE t1;
+
+# Cleanup
+DROP TABLE t1;
+
+
+CREATE TABLE t (a INT);
+CREATE TEMPORARY TABLE s (f INT);
+CREATE SEQUENCE s;
+LOCK TABLE t WRITE;
+--error ER_NOT_SEQUENCE2
+DROP SEQUENCE s;
+--error ER_NOT_SEQUENCE2
+DROP TEMPORARY SEQUENCE s;
+UNLOCK TABLES;
+CREATE TEMPORARY SEQUENCE s;
+LOCK TABLE t WRITE;
+DROP TEMPORARY SEQUENCE s;
+UNLOCK TABLES;
+DROP TEMPORARY TABLE s;
+DROP SEQUENCE s;
+
+create table s(a INT);
+CREATE TEMPORARY TABLE s (f INT);
+LOCK TABLE t WRITE;
+DROP TEMPORARY TABLE s;
+CREATE TEMPORARY TABLE s (f INT);
+DROP TABLE s;
+--error ER_TABLE_NOT_LOCKED
+DROP TABLE s;
+UNLOCK TABLES;
+DROP TABLE s;
+
+CREATE VIEW v1 as SELECT * FROM t;
+CREATE SEQUENCE s;
+
+DROP SEQUENCE IF EXISTS v1;
+DROP VIEW IF EXISTS s;
+
+DROP VIEW v1;
+DROP SEQUENCE s;
+DROP TABLE t;
 --echo # End of 10.3 tests