MDEV-21785: sequences used as default by other table not dumped in right order by mysqldumpbb-10.3-MDEV-21785

Dump sequences first.

This atch made to keep it small and
to keep number of queries to the server the same.

Order of tables in a dump can not be changed
(except sequences first) because mysql_list_tables
uses SHOW TABLES and I used SHOW FULL TABLES.

3 files changed, 115 insertions, 17 deletions

diff --git a/client/mysqldump.c b/client/mysqldump.c
index a964f96437d..e8601e5f7d5 100644
--- a/client/mysqldump.c
+++ b/client/mysqldump.c
@@ -42,6 +42,11 @@
 /* on merge conflict, bump to a higher version again */
 #define DUMP_VERSION "10.19"
 
+/**
+  First mysql version supporting sequences.
+*/
+#define FIRST_SEQUENCE_VERSION 100300
+
 #include <my_global.h>
 #include <my_sys.h>
 #include <my_user.h>
@@ -92,6 +97,11 @@
 /* Max length GTID position that we will output. */
 #define MAX_GTID_LENGTH 1024
 
+/* Dump sequence/tables control */
+#define DUMP_TABLE_ALL -1
+#define DUMP_TABLE_TABLE 0
+#define DUMP_TABLE_SEQUENCE 1
+
 static my_bool ignore_table_data(const uchar *hash_key, size_t len);
 static void add_load_option(DYNAMIC_STRING *str, const char *option,
                              const char *option_value);
@@ -3877,14 +3887,6 @@ static void dump_table(const char *table, const char *db, const uchar *hash_key,
   DBUG_ENTER("dump_table");
 
   /*
-    Check does table has a sequence structure and if has apply different sql queries
-  */
-  if (check_if_ignore_table(table, table_type) & IGNORE_SEQUENCE_TABLE)
-  {
-    get_sequence_structure(table, db);
-    DBUG_VOID_RETURN;
-  }
-  /*
     Make sure you get the create table info before the following check for
     --no-data flag below. Otherwise, the create table info won't be printed.
   */
@@ -4368,18 +4370,36 @@ err:
 } /* dump_table */
 
 
-static char *getTableName(int reset)
+static char *getTableName(int reset, int want_sequences)
 {
   MYSQL_ROW row;
 
   if (!get_table_name_result)
   {
-    if (!(get_table_name_result= mysql_list_tables(mysql,NullS)))
-      return(NULL);
+    if (mysql_get_server_version(mysql) >= FIRST_SEQUENCE_VERSION)
+    {
+      const char *query= "SHOW FULL TABLES";
+      if (mysql_query_with_error_report(mysql, 0, query))
+        return (NULL);
+
+      if (!(get_table_name_result= mysql_store_result(mysql)))
+        return (NULL);
+    }
+    else
+    {
+      if (!(get_table_name_result= mysql_list_tables(mysql,NullS)))
+        return(NULL);
+    }
   }
   if ((row= mysql_fetch_row(get_table_name_result)))
-    return((char*) row[0]);
+  {
+    if (want_sequences != DUMP_TABLE_ALL)
+      while (row && MY_TEST(strcmp(row[1], "SEQUENCE")) == want_sequences)
+        row= mysql_fetch_row(get_table_name_result);
 
+    if (row)
+      return((char*) row[0]);
+  }
   if (reset)
     mysql_data_seek(get_table_name_result,0);      /* We want to read again */
   else
@@ -5312,7 +5332,7 @@ static int dump_all_tables_in_db(char *database)
   {
     DYNAMIC_STRING query;
     init_dynamic_string_checked(&query, "LOCK TABLES ", 256, 1024);
-    for (numrows= 0 ; (table= getTableName(1)) ; )
+    for (numrows= 0 ; (table= getTableName(1, DUMP_TABLE_ALL)) ; )
     {
       char *end= strmov(afterdot, table);
       if (include_table((uchar*) hash_key,end - hash_key))
@@ -5346,7 +5366,19 @@ static int dump_all_tables_in_db(char *database)
       DBUG_RETURN(1);
     }
   }
-  while ((table= getTableName(0)))
+
+  if (mysql_get_server_version(mysql) >= FIRST_SEQUENCE_VERSION &&
+      !opt_no_create_info)
+  {
+    // First process sequences
+    while ((table= getTableName(1, DUMP_TABLE_SEQUENCE)))
+    {
+      char *end= strmov(afterdot, table);
+      if (include_table((uchar*) hash_key, end - hash_key))
+        get_sequence_structure(table, database);
+    }
+  }
+  while ((table= getTableName(0, DUMP_TABLE_TABLE)))
   {
     char *end= strmov(afterdot, table);
     if (include_table((uchar*) hash_key, end - hash_key))
@@ -5495,7 +5527,7 @@ static my_bool dump_all_views_in_db(char *database)
   {
     DYNAMIC_STRING query;
     init_dynamic_string_checked(&query, "LOCK TABLES ", 256, 1024);
-    for (numrows= 0 ; (table= getTableName(1)); )
+    for (numrows= 0 ; (table= getTableName(1, DUMP_TABLE_TABLE)); )
     {
       char *end= strmov(afterdot, table);
       if (include_table((uchar*) hash_key,end - hash_key))
@@ -5518,7 +5550,7 @@ static my_bool dump_all_views_in_db(char *database)
     else
       verbose_msg("-- dump_all_views_in_db : logs flushed successfully!\n");
   }
-  while ((table= getTableName(0)))
+  while ((table= getTableName(0, DUMP_TABLE_TABLE)))
   {
     char *end= strmov(afterdot, table);
     if (include_table((uchar*) hash_key, end - hash_key))
@@ -5648,7 +5680,7 @@ static int get_sys_var_lower_case_table_names()
 
 static int dump_selected_tables(char *db, char **table_names, int tables)
 {
-  char table_buff[NAME_LEN*2+3];
+  char table_buff[NAME_LEN*2+3], table_type[NAME_LEN];
   DYNAMIC_STRING lock_tables_query;
   char **dump_tables, **pos, **end;
   int lower_case_table_names;
@@ -5745,9 +5777,22 @@ static int dump_selected_tables(char *db, char **table_names, int tables)
       DBUG_RETURN(1);
     }
   }
+
+  if (mysql_get_server_version(mysql) >= FIRST_SEQUENCE_VERSION)
+  {
+    /* Dump Sequence first */
+    for (pos= dump_tables; pos < end; pos++)
+    {
+      DBUG_PRINT("info",("Dumping sequence(?) %s", *pos));
+      if (check_if_ignore_table(*pos, table_type) & IGNORE_SEQUENCE_TABLE)
+        get_sequence_structure(*pos, db);
+    }
+  }
   /* Dump each selected table */
   for (pos= dump_tables; pos < end; pos++)
   {
+    if (check_if_ignore_table(*pos, table_type) & IGNORE_SEQUENCE_TABLE)
+      continue;
     DBUG_PRINT("info",("Dumping table %s", *pos));
     dump_table(*pos, db, NULL, 0);
     if (opt_dump_triggers &&
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;
 
 #