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

[23 Jan 2007 0:57] Morgan Tocker
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)
[23 Jan 2007 6:53] MySQL Verification Team
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.
[23 Jan 2007 7:10] MySQL Verification Team
filemon-nt from sysinternals will show the "DELETE PEND" results for the accessing which are causing access denied errors.
[6 May 2007 11:34] Rene Vogt
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
[30 May 2007 20:55] MySQL Verification Team
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>
[1 Jun 2007 10:38] Georgi Kodinov
The effect described here is remedied by the fix for bug #25521. Since #25521 is closed, I'm marking this one as a duplicate.