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: | |
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
[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.