MDEV-19745 BACKUP STAGE BLOCK_DDL hangs on flush sequence table

Problem was that FLUSH TABLES where trying to read latest sequence state
which conflicted with a running ALTER SEQUENCE. Removed the reading
of the state, when opening a table for FLUSH, as it's not needed in this
case.

Other thing:
- Fixed a potential issue with concurrently running ALTER SEQUENCE where
  the later ALTER could potentially read old data

5 files changed, 26 insertions, 11 deletions

diff --git a/include/my_base.h b/include/my_base.h
index 50a9abe0bc6..dc5b135628f 100644
--- a/include/my_base.h
+++ b/include/my_base.h
@@ -55,6 +55,10 @@
 */
 #define HA_OPEN_FOR_ALTER		8192U
 
+/* Open table for FLUSH */
+#define HA_OPEN_FOR_FLUSH               8192U
+
+
 /* The following is parameter to ha_rkey() how to use key */
 
 /*
diff --git a/sql/ha_sequence.cc b/sql/ha_sequence.cc
index 4eabd820071..bf3b5ce2cd0 100644
--- a/sql/ha_sequence.cc
+++ b/sql/ha_sequence.cc
@@ -108,8 +108,12 @@ int ha_sequence::open(const char *name, int mode, uint flags)
       MY_TEST(flags & HA_OPEN_INTERNAL_TABLE);
     reset_statistics();
 
-    /* Don't try to read the initial row the call is part of create code */
-    if (!(flags & (HA_OPEN_FOR_CREATE | HA_OPEN_FOR_REPAIR)))
+    /*
+      Don't try to read the initial row if the call is part of CREATE, REPAIR
+      or FLUSH
+    */
+    if (!(flags & (HA_OPEN_FOR_CREATE | HA_OPEN_FOR_REPAIR |
+                   HA_OPEN_FOR_FLUSH)))
     {
       if (unlikely((error= table->s->sequence->read_initial_values(table))))
         file->ha_close();
@@ -121,7 +125,8 @@ int ha_sequence::open(const char *name, int mode, uint flags)
       The following is needed to fix comparison of rows in
       ha_update_first_row() for InnoDB
     */
-    memcpy(table->record[1], table->s->default_values, table->s->reclength);
+    if (!error)
+      memcpy(table->record[1], table->s->default_values, table->s->reclength);
   }
   DBUG_RETURN(error);
 }
diff --git a/sql/sql_base.cc b/sql/sql_base.cc
index 090ced59f60..d7be718b1ca 100644
--- a/sql/sql_base.cc
+++ b/sql/sql_base.cc
@@ -598,12 +598,15 @@ bool flush_tables(THD *thd, flush_tables_type flag)
     else
     {
       /*
-        HA_OPEN_FOR_ALTER is used to allow us to open the table even if
-        TABLE_SHARE::incompatible_version is set.
+        HA_OPEN_FOR_FLUSH is used to allow us to open the table even if
+        TABLE_SHARE::incompatible_version is set. It also will tell
+        SEQUENCE engine that we don't have to read the sequence information
+        (which may cause deadlocks with concurrently running ALTER TABLE or
+        ALTER SEQUENCE) as we will close the table at once.
       */
       if (!open_table_from_share(thd, share, &empty_clex_str,
                                  HA_OPEN_KEYFILE, 0,
-                                 HA_OPEN_FOR_ALTER,
+                                 HA_OPEN_FOR_ALTER | HA_OPEN_FOR_FLUSH,
                                  tmp_table, FALSE,
                                  NULL))
       {
diff --git a/sql/sql_sequence.cc b/sql/sql_sequence.cc
index c0cbd532331..c0925da59e1 100644
--- a/sql/sql_sequence.cc
+++ b/sql/sql_sequence.cc
@@ -453,7 +453,7 @@ int SEQUENCE::read_initial_values(TABLE *table)
     /*
       There is already a mdl_ticket for this table. However, for list_fields
       the MDL lock is of type MDL_SHARED_HIGH_PRIO which is not usable
-      for doing a able lock. Get a proper read lock to solve this.
+      for doing a table lock. Get a proper read lock to solve this.
     */
     if (table->mdl_ticket == 0)
     {
@@ -929,6 +929,8 @@ bool Sql_cmd_alter_sequence::execute(THD *thd)
 
   table= first_table->table;
   seq= table->s->sequence;
+
+  seq->write_lock(table);
   new_seq->reserved_until= seq->reserved_until;
 
   /* Copy from old sequence those fields that the user didn't specified */
@@ -961,18 +963,18 @@ bool Sql_cmd_alter_sequence::execute(THD *thd)
              first_table->db.str,
              first_table->table_name.str);
     error= 1;
+    seq->write_unlock(table);
     goto end;
   }
 
-  table->s->sequence->write_lock(table);
   if (likely(!(error= new_seq->write(table, 1))))
   {
     /* Store the sequence values in table share */
-    table->s->sequence->copy(new_seq);
+    seq->copy(new_seq);
   }
   else
     table->file->print_error(error, MYF(0));
-  table->s->sequence->write_unlock(table);
+  seq->write_unlock(table);
   if (trans_commit_stmt(thd))
     error= 1;
   if (trans_commit_implicit(thd))
diff --git a/sql/table.cc b/sql/table.cc
index bec89dbb440..73c3bd4b3ba 100644
--- a/sql/table.cc
+++ b/sql/table.cc
@@ -3891,7 +3891,8 @@ enum open_frm_error open_table_from_share(THD *thd, TABLE_SHARE *share,
   outparam->write_row_record= NULL;
 
   if (share->incompatible_version &&
-      !(ha_open_flags & (HA_OPEN_FOR_ALTER | HA_OPEN_FOR_REPAIR)))
+      !(ha_open_flags & (HA_OPEN_FOR_ALTER | HA_OPEN_FOR_REPAIR |
+                         HA_OPEN_FOR_FLUSH)))
   {
     /* one needs to run mysql_upgrade on the table */
     error= OPEN_FRM_NEEDS_REBUILD;