MDEV-8475 stale .TMM file causes MyiSAM and Aria engine to stop serving the table

Issue was two fold (both in MyISAM and Aria)
- optimize and repair failed if there was an old .TMM file around. As optimized and repair are protected against multiple execution, I decided to change so that we just truncate the file if it exists.
- I had missed to check for error condition if creation of the temporary index file failed. This caused the strange behaviour that it looked as if optimized would have worked once.

6 files changed, 135 insertions, 8 deletions

diff --git a/mysql-test/r/myisam-optimize.result b/mysql-test/r/myisam-optimize.result
new file mode 100644
index 00000000000..9451046fe08
--- /dev/null
+++ b/mysql-test/r/myisam-optimize.result
@@ -0,0 +1,69 @@
+#
+# MDEV-8475 stale .TMM file causes Aria engine to stop serving the table
+#
+create table t1 (pk int primary key, i int) engine=MyISAM;
+insert into t1 values (1,1),(2,2);
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `pk` int(11) NOT NULL,
+  `i` int(11) DEFAULT NULL,
+  PRIMARY KEY (`pk`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+OPTIMIZE TABLE t1;
+Table	Op	Msg_type	Msg_text
+test.t1	optimize	status	OK
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `pk` int(11) NOT NULL,
+  `i` int(11) DEFAULT NULL,
+  PRIMARY KEY (`pk`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+OPTIMIZE TABLE t1;
+Table	Op	Msg_type	Msg_text
+test.t1	optimize	status	Table is already up to date
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `pk` int(11) NOT NULL,
+  `i` int(11) DEFAULT NULL,
+  PRIMARY KEY (`pk`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+OPTIMIZE TABLE t1;
+Table	Op	Msg_type	Msg_text
+test.t1	optimize	status	Table is already up to date
+DROP TABLE t1;
+create table t1 (pk int primary key, i int) engine=aria;
+insert into t1 values (1,1),(2,2);
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `pk` int(11) NOT NULL,
+  `i` int(11) DEFAULT NULL,
+  PRIMARY KEY (`pk`)
+) ENGINE=Aria DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1
+OPTIMIZE TABLE t1;
+Table	Op	Msg_type	Msg_text
+test.t1	optimize	status	OK
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `pk` int(11) NOT NULL,
+  `i` int(11) DEFAULT NULL,
+  PRIMARY KEY (`pk`)
+) ENGINE=Aria DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1
+OPTIMIZE TABLE t1;
+Table	Op	Msg_type	Msg_text
+test.t1	optimize	status	Table is already up to date
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `pk` int(11) NOT NULL,
+  `i` int(11) DEFAULT NULL,
+  PRIMARY KEY (`pk`)
+) ENGINE=Aria DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1
+OPTIMIZE TABLE t1;
+Table	Op	Msg_type	Msg_text
+test.t1	optimize	status	Table is already up to date
+DROP TABLE t1;
diff --git a/mysql-test/r/repair_symlink-5543.result b/mysql-test/r/repair_symlink-5543.result
index 051c9ca3472..98ded32686e 100644
--- a/mysql-test/r/repair_symlink-5543.result
+++ b/mysql-test/r/repair_symlink-5543.result
@@ -2,13 +2,12 @@ create table t1 (a int) engine=myisam data directory='MYSQL_TMP_DIR';
 insert t1 values (1);
 repair table t1;
 Table	Op	Msg_type	Msg_text
-test.t1	repair	error	Can't create new tempfile: 'MYSQL_TMP_DIR/t1.TMD'
-test.t1	repair	status	Operation failed
+test.t1	repair	status	OK
 drop table t1;
 create table t2 (a int) engine=aria data directory='MYSQL_TMP_DIR';
 insert t2 values (1);
 repair table t2;
 Table	Op	Msg_type	Msg_text
-test.t2	repair	error	Can't create new tempfile: 'MYSQL_TMP_DIR/t2.TMD'
-test.t2	repair	status	Operation failed
+test.t2	repair	status	OK
 drop table t2;
+foobar5543
diff --git a/mysql-test/t/myisam-optimize.test b/mysql-test/t/myisam-optimize.test
new file mode 100644
index 00000000000..7c1fe4ec1c8
--- /dev/null
+++ b/mysql-test/t/myisam-optimize.test
@@ -0,0 +1,58 @@
+--echo #
+--echo # MDEV-8475 stale .TMM file causes Aria engine to stop serving the table
+--echo #
+create table t1 (pk int primary key, i int) engine=MyISAM;
+insert into t1 values (1,1),(2,2);
+--let $datadir=`SELECT @@datadir`
+
+--write_file $datadir/test/t1.TMM
+EOF
+
+--connect (con1,localhost,root,,)
+SHOW CREATE TABLE t1;
+OPTIMIZE TABLE t1;
+--disconnect con1
+
+--connect (con1,localhost,root,,)
+SHOW CREATE TABLE t1;
+OPTIMIZE TABLE t1;
+--disconnect con1
+
+--connect (con1,localhost,root,,)
+SHOW CREATE TABLE t1;
+OPTIMIZE TABLE t1;
+--disconnect con1
+
+# Cleanup
+--connection default
+DROP TABLE t1;
+
+#
+# Test also Aria
+#
+
+create table t1 (pk int primary key, i int) engine=aria;
+insert into t1 values (1,1),(2,2);
+--let $datadir=`SELECT @@datadir`
+
+--write_file $datadir/test/t1.TMM
+EOF
+
+--connect (con1,localhost,root,,)
+SHOW CREATE TABLE t1;
+OPTIMIZE TABLE t1;
+--disconnect con1
+
+--connect (con1,localhost,root,,)
+SHOW CREATE TABLE t1;
+OPTIMIZE TABLE t1;
+--disconnect con1
+
+--connect (con1,localhost,root,,)
+SHOW CREATE TABLE t1;
+OPTIMIZE TABLE t1;
+--disconnect con1
+
+# Cleanup
+--connection default
+DROP TABLE t1;
diff --git a/mysql-test/t/repair_symlink-5543.test b/mysql-test/t/repair_symlink-5543.test
index bad65a4175a..58cc810b1a9 100644
--- a/mysql-test/t/repair_symlink-5543.test
+++ b/mysql-test/t/repair_symlink-5543.test
@@ -22,5 +22,4 @@ repair table t2;
 drop table t2;
 
 --list_files $MYSQL_TMP_DIR foobar5543
---system rm $MYSQL_TMP_DIR/t1.TMD $MYSQL_TMP_DIR/t2.TMD
 
diff --git a/storage/maria/ha_maria.cc b/storage/maria/ha_maria.cc
index 48f2da42bfb..7043a2cb7ca 100644
--- a/storage/maria/ha_maria.cc
+++ b/storage/maria/ha_maria.cc
@@ -1597,7 +1597,7 @@ int ha_maria::repair(THD *thd, HA_CHECK *param, bool do_optimize)
 
   param->db_name= table->s->db.str;
   param->table_name= table->alias.c_ptr();
-  param->tmpfile_createflag= O_RDWR | O_TRUNC | O_EXCL;
+  param->tmpfile_createflag= O_RDWR | O_TRUNC;
   param->using_global_keycache= 1;
   param->thd= thd;
   param->tmpdir= &mysql_tmpdir_list;
@@ -1688,7 +1688,7 @@ int ha_maria::repair(THD *thd, HA_CHECK *param, bool do_optimize)
       thd_proc_info(thd, "Sorting index");
       error= maria_sort_index(param, file, fixed_name);
     }
-    if (!statistics_done && (local_testflag & T_STATISTICS))
+    if (!error && !statistics_done && (local_testflag & T_STATISTICS))
     {
       if (share->state.changed & STATE_NOT_ANALYZED)
       {
diff --git a/storage/myisam/ha_myisam.cc b/storage/myisam/ha_myisam.cc
index 6b02db1e0ba..3029909e79f 100644
--- a/storage/myisam/ha_myisam.cc
+++ b/storage/myisam/ha_myisam.cc
@@ -1012,6 +1012,7 @@ int ha_myisam::repair(THD* thd, HA_CHECK_OPT *check_opt)
   param.testflag= ((check_opt->flags & ~(T_EXTEND)) |
                    T_SILENT | T_FORCE_CREATE | T_CALC_CHECKSUM |
                    (check_opt->flags & T_EXTEND ? T_REP : T_REP_BY_SORT));
+  param.tmpfile_createflag= O_RDWR | O_TRUNC;
   param.sort_buffer_length=  THDVAR(thd, sort_buffer_size);
   param.backup_time= check_opt->start_time;
   start_records=file->state->records;
@@ -1062,6 +1063,7 @@ int ha_myisam::optimize(THD* thd, HA_CHECK_OPT *check_opt)
   param.op_name= "optimize";
   param.testflag= (check_opt->flags | T_SILENT | T_FORCE_CREATE |
                    T_REP_BY_SORT | T_STATISTICS | T_SORT_INDEX);
+  param.tmpfile_createflag= O_RDWR | O_TRUNC;
   param.sort_buffer_length=  THDVAR(thd, sort_buffer_size);
   if ((error= repair(thd,param,1)) && param.retry_repair)
   {
@@ -1181,7 +1183,7 @@ int ha_myisam::repair(THD *thd, HA_CHECK &param, bool do_optimize)
       thd_proc_info(thd, "Sorting index");
       error=mi_sort_index(&param,file,fixed_name);
     }
-    if (!statistics_done && (local_testflag & T_STATISTICS))
+    if (!error && !statistics_done && (local_testflag & T_STATISTICS))
     {
       if (share->state.changed & STATE_NOT_ANALYZED)
       {