MySQL Bugs: #25767: trigger prevents table from being repaired

Bug #25767	trigger prevents table from being repaired
Submitted:	23 Jan 2007 0:57	Modified:	1 Jun 2007 10:38
Reporter:	Morgan Tocker	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S2 (Serious)
Version:	5.0,5.1.14	OS:	Windows (Windows)
Assigned to:	Georgi Kodinov	CPU Architecture:	Any
Tags:	bfsm_2007_02_01, bfsm_2007_05_31, triggers

Description:
Having a trigger on a table can prevent a table from being repaired.  This seems to be related to an error 13 (permission denied).

Verified in 5.0.22, 5.0.26, 5.0.30, 5.0.32

How to repeat:
DROP TABLE IF EXISTS `t1`;

CREATE TABLE `t1` (
`id` INT NOT NULL auto_increment PRIMARY KEY,
`lasttouched` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
`update_seq` INT NOT NULL default '0',
UNIQUE KEY `update_seq` (`update_seq`)
) ENGINE=MyISAM;

DELIMITER |
CREATE TRIGGER tr1 BEFORE UPDATE ON t1
FOR EACH ROW
BEGIN
IF OLD.lasttouched=NEW.lasttouched THEN
SET NEW.update_seq = (SELECT IFNULL(MAX(update_seq),0)+1 FROM t1);
END IF;
END|
DELIMITER ;

Then -

mysql> REPAIR TABLE t1;
+---------+--------+----------+----------------------+
| Table | Op | Msg_type | Msg_text |
+---------+--------+----------+----------------------+
| test.t1 | repair | error | 13 when fixing table |
| test.t1 | repair | status | Operation failed |
+---------+--------+----------+----------------------+
2 rows in set, 3 warnings (0.09 sec)

mysql> REPAIR TABLE t1;
+---------+--------+----------+---------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+---------+--------+----------+---------------------------------------------+
| test.t1 | repair | error | File '.\test\t1.MYD' not found (Errcode: 2) |
+---------+--------+----------+---------------------------------------------+
1 row in set, 2 warnings (0.00 sec)

5.1.14 also affected. mysqld-debug.exe even crashes. mysqld-nt.exe gave these results:

mysql> REPAIR TABLE t1;
+---------+--------+----------+----------------------+
| Table   | Op     | Msg_type | Msg_text             |
+---------+--------+----------+----------------------+
| test.t1 | repair | error    | 13 when fixing table |
| test.t1 | repair | status   | Operation failed     |
+---------+--------+----------+----------------------+
2 rows in set, 2 warnings (0.02 sec)

mysql> show warnings;
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                                                                                             |
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Error |    7 | Error on rename of 'F:\mysql-noinstall-5.1.14-beta-win32\mysql-5.1.14-beta-win32\data\test\t1.TMD' to 'F:\mysql-noinstall-5.1.14-beta-win32\mysql-5.1.14-beta-win32\data\test\t1.MYD' (Errcode: 13) |
| Error |    6 | Error on delete of 'F:\mysql-noinstall-5.1.14-beta-win32\mysql-5.1.14-beta-win32\data\test\t1.MYD' (Errcode: 13)                                                                                    |
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> REPAIR TABLE t1;
+---------+--------+----------+---------------------------------------------+
| Table   | Op     | Msg_type | Msg_text                                    |
+---------+--------+----------+---------------------------------------------+
| test.t1 | repair | error    | File '.\test\t1.MYD' not found (Errcode: 2) |
+---------+--------+----------+---------------------------------------------+
1 row in set, 2 warnings (0.00 sec)

mysql> show warnings;
+-------+------+---------------------------------------------+
| Level | Code | Message                                     |
+-------+------+---------------------------------------------+
| Error |   29 | File '.\test\t1.MYD' not found (Errcode: 2) |
| Error | 1017 | Can't find file: 't1' (errno: 2)            |
+-------+------+---------------------------------------------+
2 rows in set (0.00 sec)

mysql>

2007/01/23  08:51    <DIR>          .
2007/01/23  08:51    <DIR>          ..
2007/01/23  08:50             8,642 t1.frm
2007/01/23  08:51             1,024 t1.MYI
2007/01/23  08:51                 0 t1.TMD
2007/01/23  08:50               247 t1.TRG
2007/01/23  08:50                34 tr1.TRN
               5 File(s)          9,947 bytes
               2 Dir(s)  60,650,860,544 bytes free

So the .MYD is missing !! This requires manual intervention to correct.

filemon-nt from sysinternals will show the "DELETE PEND" results for the accessing which are causing access denied errors.

Same problem seems to occur on merge tables. If I do a repair on one of the merged tables while someone do a select on the merge table I get an error 13 during repair and all data in the merged table are lost!!!
Tested against mysql version 5.0.37

Wonder if this has been fixed already?  5.1.20BK on windows works:

mysql> CREATE TABLE `t1` (
    -> `id` INT NOT NULL auto_increment PRIMARY KEY,
    -> `lasttouched` timestamp NOT NULL default CURRENT_TIMESTAMP on update
    -> CURRENT_TIMESTAMP,
    -> `update_seq` INT NOT NULL default '0',
    -> UNIQUE KEY `update_seq` (`update_seq`)
    -> ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.03 sec)

mysql>
mysql> DELIMITER |
mysql> CREATE TRIGGER tr1 BEFORE UPDATE ON t1
    -> FOR EACH ROW
    -> BEGIN
    -> IF OLD.lasttouched=NEW.lasttouched THEN
    -> SET NEW.update_seq = (SELECT IFNULL(MAX(update_seq),0)+1 FROM t1);
    -> END IF;
    -> END|
Query OK, 0 rows affected (0.05 sec)

mysql> DELIMITER ;
mysql> REPAIR TABLE t1;
+--------+--------+----------+----------+
| Table  | Op     | Msg_type | Msg_text |
+--------+--------+----------+----------+
| db1.t1 | repair | status   | OK       |
+--------+--------+----------+----------+
1 row in set (0.13 sec)

mysql> select version();
+-------------------+
| version()         |
+-------------------+
| 5.1.20-beta-debug |
+-------------------+
1 row in set (0.00 sec)

mysql>

The effect described here is remedied by the fix for bug #25521. Since #25521 is closed, I'm marking this one as a duplicate.