MDEV-23485: Change table to merge engine may cause table data lost.bb-10.6-MDEV-23485-pr1651

Problem:
If we want to change a table to merge engine, the original table
is not merge engine table and have rows may cause table data lost.

Solution:
Add check in the mysql_alter_table method, check if the original
table want to change to merge engine, it should't be merge engine
and have no rows.

closes #1651

4 files changed, 59 insertions, 0 deletions

diff --git a/mysql-test/main/merge.result b/mysql-test/main/merge.result
index 0d6f387a7bc..9550e470935 100644
--- a/mysql-test/main/merge.result
+++ b/mysql-test/main/merge.result
@@ -3038,6 +3038,7 @@ c1	c2
 411	421
 511	521
 #
+truncate table m1;
 ALTER TABLE m1 ENGINE=MRG_MyISAM UNION=(t1,t2)
 INSERT_METHOD=LAST;
 SELECT * FROM m1;
@@ -3445,6 +3446,7 @@ c1	c2
 411	421
 511	521
 #
+truncate table m1;
 ALTER TABLE m1 ENGINE=MRG_MyISAM UNION=(t1,t2)
 INSERT_METHOD=LAST;
 SELECT * FROM m1;
@@ -3919,3 +3921,14 @@ ERROR HY000: Unable to open underlying table which is differently defined or of
 DROP TRIGGER trg1;
 DROP TABLE t1;
 DROP TABLE m1;
+#
+# MDEV-23485 - Change table to merge engine may cause table data lost.
+#
+CREATE TABLE t1(a INT);
+ALTER TABLE t1 engine=MRG_MYISAM;
+drop table t1;
+CREATE TABLE t1(a INT);
+insert into t1() values(1);
+ALTER TABLE t1 engine=MRG_MYISAM;
+ERROR HY000: ALTER TABLE TO MERGE ENGINE is not supported for this operation, The original table is not merge engine and have rows may cause table data lost.
+drop table t1;
diff --git a/mysql-test/main/merge.test b/mysql-test/main/merge.test
index 888b41b24bd..f4a90fdce10 100644
--- a/mysql-test/main/merge.test
+++ b/mysql-test/main/merge.test
@@ -2346,6 +2346,7 @@ INSERT INTO m1 VALUES (511, 521);
 SELECT * FROM m1;
 #
 --echo #
+truncate table m1;
 ALTER TABLE m1 ENGINE=MRG_MyISAM UNION=(t1,t2)
   INSERT_METHOD=LAST;
 SELECT * FROM m1;
@@ -2512,6 +2513,7 @@ INSERT INTO m1 VALUES (511, 521);
 SELECT * FROM m1;
 #
 --echo #
+truncate table m1;
 ALTER TABLE m1 ENGINE=MRG_MyISAM UNION=(t1,t2)
   INSERT_METHOD=LAST;
 SELECT * FROM m1;
@@ -2919,3 +2921,15 @@ set global default_storage_engine=@save_default_storage_engine;
 # 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
+
+--echo #
+--echo # MDEV-23485 - Change table to merge engine may cause table data lost.
+--echo #
+CREATE TABLE t1(a INT);
+ALTER TABLE t1 engine=MRG_MYISAM;
+drop table t1;
+CREATE TABLE t1(a INT);
+insert into t1() values(1);
+--error ER_ALTER_TO_MERGE_ENGINE_NOT_SUPPORTED
+ALTER TABLE t1 engine=MRG_MYISAM;
+drop table t1;
diff --git a/sql/share/errmsg-utf8.txt b/sql/share/errmsg-utf8.txt
index 63c3ee30444..b1dc415acae 100644
--- a/sql/share/errmsg-utf8.txt
+++ b/sql/share/errmsg-utf8.txt
@@ -7962,3 +7962,5 @@ ER_NOT_ALLOWED_IN_THIS_CONTEXT
         eng "'%-.128s' is not allowed in this context"
 ER_DATA_WAS_COMMITED_UNDER_ROLLBACK
         eng "Engine %s does not support rollback. Changes were committed during rollback call"
+ER_ALTER_TO_MERGE_ENGINE_NOT_SUPPORTED
+        eng "%s is not supported for this operation, The original table is not merge engine and have rows may cause table data lost."
diff --git a/sql/sql_table.cc b/sql/sql_table.cc
index 15d190c3139..fe268d2c129 100644
--- a/sql/sql_table.cc
+++ b/sql/sql_table.cc
@@ -10131,6 +10131,36 @@ bool mysql_alter_table(THD *thd, const LEX_CSTRING *new_db,
     DBUG_RETURN(true);
   }
 
+  /*
+    MDEV-23485: Change table to merge engine may cause table data lost.
+
+    If we want to change a table to merge engine, the original table
+    should't be merge engine table and the table have no rows, otherwise
+    may cause table data lost.
+  */
+  if (table->file->ht->db_type != DB_TYPE_MRG_MYISAM &&
+      create_info->db_type->db_type == DB_TYPE_MRG_MYISAM)
+  {
+    READ_RECORD info;
+
+    if (init_read_record(&info, thd, table, NULL, NULL, 1, 1, FALSE))
+    {
+      DBUG_RETURN(true);
+    }
+
+    if (!info.read_record())
+    {
+      DBUG_PRINT("info", ("The original table is not merge table "
+                          "and have rows doesn't support alter"));
+      my_error(ER_ALTER_TO_MERGE_ENGINE_NOT_SUPPORTED, MYF(0),
+              "ALTER TABLE TO MERGE ENGINE");
+      end_read_record(&info);
+      DBUG_RETURN(true);
+    }
+
+    end_read_record(&info);
+  }
+
   if (table->s->tmp_table == NO_TMP_TABLE)
     mysql_audit_alter_table(thd, table_list);