| Bug #46617 | Bogus "table is marked as crashed and should be repaired" | ||
|---|---|---|---|
| Submitted: | 8 Aug 2009 12:46 | Modified: | 11 May 2011 8:36 |
| Reporter: | Philip Stoev | Email Updates: | |
| Status: | Won't fix | Impact on me: | |
| Category: | MySQL Server: DDL | Severity: | S3 (Non-critical) |
| Version: | 5.1 | OS: | Any |
| Assigned to: | Jon Olav Hauglid | CPU Architecture: | Any |
[10 Aug 2009 20:24]
Philip Stoev
Engine is MyISAM, the default engine for mysql :-) . If some other engine was used, it would have been visible from the test case. Also, this is the only engine that exhibits this message. This test is non-concurrent and the commands are run sequentially. Due to the automatic nature of the test, after simplification some unnecessary queries may have remained.
[14 Aug 2009 16:31]
MySQL Verification Team
triage, kindly retriage this bug. Because original testcase was unreadable, here, for your ease of reading I made a better testcase that shows the problem. --------------- drop database if exists test; create database test; use test; create table t1 (`a` int) engine=myisam select 5; create table t2 (`a` int) engine=myisam select 1; create table t3 (`a` int) engine=myisam select 1; create or replace view v1 as select `a` from t1 union select `a` from `t3`; delimiter | create trigger `trg` after insert on `t3` for each row begin select * from `v1` limit 0 into @a ; end| delimiter ; create table if not exists `t3` as select `a` from t1; --------------- As you see, the trigger on t3 refers to a view which access t3. during the create table, i guess mysql gets confused as to the state of t3 :)
[17 Sep 2009 10:42]
Matthias Leich
Bug#47381 Wrong warning about crashed table, CREATE TABLE IF NOT EXISTS AS SELECT was marked as duplicate of this bug
[1 Nov 2010 15:35]
Jon Olav Hauglid
Not able to repeat this bug with current version of 5.5 (5.5-bugteam). Still able to repeat it with 5.1 (5.1-bugteam). Tested both with the test case posted here and the test case from Bug#47381.
[1 Nov 2010 16:15]
MySQL Verification Team
I concur. Just tested 5.1.53 and it gives table marked as crashed error. but it is not repeatable anymore on current mysql-5.5-security tree
[11 May 2011 8:36]
Jon Olav Hauglid
A 5.1 only bug that's triaged to be fixed only in trunk, means there's nothing to fix. Closing the bug as "Won't fix".
Description: The sequence of statements below causes the last CREATE ... SELECT to return ERROR HY000: Table 't1_base_0_A' is marked as crashed and should be repaired even though the table appears to be otherwise OK and can be SELECT-ed from. Therefore, the message appears bogus. Note that this test case was culled from a query log file with 20K statements, so some unnecessary queries or query fragments may have remained. How to repeat: --disable_abort_on_error CREATE TABLE `table0_int_autoinc` ( `int` int, pk integer auto_increment, `int_key` int, primary key (pk), key (`int_key` )); CREATE TABLE `table1_int_autoinc` ( `int` int, pk integer auto_increment, `int_key` int, primary key (pk), key (`int_key` )); INSERT IGNORE INTO table1_int_autoinc VALUES ('1', NULL, '1'); CREATE TABLE `table10_int_autoinc` ( `int` int, pk integer auto_increment, `int_key` int, primary key (pk), key (`int_key` )); CREATE SCHEMA IF NOT EXISTS testdb_A DEFAULT CHARACTER SET utf8; DELIMITER |; /* Sequence begin */ CREATE TABLE IF NOT EXISTS testdb_A . t1_base_0_A LIKE test . table10_int_autoinc ; ALTER TABLE testdb_A . t1_base_0_A ENGINE = InnoDB ; INSERT INTO testdb_A . t1_base_0_A ( `int_key` ) SELECT 1 FROM test . table10_int_autoinc ; COMMIT ; SELECT SLEEP(0 * 1 * 0 ) ; DROP TABLE testdb_A . t1_base_0_A /* Sequence end */ /* | /* /* Sequence begin */ CREATE TABLE IF NOT EXISTS testdb_A . t1_part_0_A (`int_key` INTEGER) SELECT 5 FROM test . table1_int_autoinc ; COMMIT ; SELECT SLEEP(0 * 0.91 * 0 ) ; DROP TABLE testdb_A . t1_part_0_A /* Sequence end */ /*| DELIMITER ;| DELIMITER |; /* /* Sequence begin */ CREATE TABLE IF NOT EXISTS testdb_A . t1_base_2_A LIKE test . table1_int_autoinc ; ALTER TABLE testdb_A . t1_base_2_A ENGINE = MyISAM ; INSERT INTO testdb_A . t1_base_2_A (`int_key`) SELECT 9 FROM test . table1_int_autoinc ; CREATE TABLE IF NOT EXISTS testdb_A . t1_base_2_A LIKE test . table1_int_autoinc ; ALTER TABLE testdb_A . t1_base_2_A ENGINE = MyISAM ; INSERT INTO testdb_A . t1_base_2_A (`int_key`) SELECT 6 FROM test . table1_int_autoinc ; CREATE TABLE IF NOT EXISTS testdb_A . t1_merge_2_A LIKE test . table1_int_autoinc ; ALTER TABLE testdb_A . t1_merge_2_A ENGINE = MERGE UNION ( testdb_A . t1_base_2_A , testdb_A . t1_base_2_A ); COMMIT ; SELECT SLEEP(0 * 0.56 * 0 ) ; DROP TABLE testdb_A . t1_merge_2_A /* Sequence end */ /*| CREATE OR REPLACE ALGORITHM = UNDEFINED VIEW testdb_A . t1_view_4_A AS SELECT SQL_NO_CACHE `int_key` FROM testdb_A . t1_part_0_A A UNION SELECT `int_key` FROM testdb_A . t1_base_0_A B /* */; DELIMITER |; /* /* Sequence begin */ CREATE PROCEDURE testdb_A . p1_4_A () BEGIN UPDATE testdb_A . t1_merge_3_A SET `int` = 2 WHERE `pk` > 0 ; UPDATE IGNORE testdb_A . t1_part_3_A SET `int` = 3 WHERE SLEEP( 0 * 0.81 * 0 ) = 0 ; END ; COMMIT ; SLEEP( 0 * 0.22 * 0 ) ; DROP PROCEDURE testdb_A . p1_4_A /* Sequence end */ /*| DELIMITER ;| DELIMITER |; /* Sequence begin */ CREATE TABLE IF NOT EXISTS testdb_A . t1_base_0_A LIKE test . table0_int_autoinc ; ALTER TABLE testdb_A . t1_base_0_A ENGINE = MyISAM ; INSERT INTO testdb_A . t1_base_0_A (`int_key`) SELECT 4 FROM test . table0_int_autoinc ; CREATE TABLE IF NOT EXISTS testdb_A . t1_base_3_A LIKE test . table0_int_autoinc ; ALTER TABLE testdb_A . t1_base_3_A ENGINE = MyISAM ; INSERT INTO testdb_A . t1_base_3_A (`int_key`) SELECT 9 FROM test . table0_int_autoinc ; CREATE TABLE IF NOT EXISTS testdb_A . t1_merge_3_A LIKE test . table0_int_autoinc ; ALTER TABLE testdb_A . t1_merge_3_A ENGINE = MERGE UNION ( testdb_A . t1_base_0_A , testdb_A . t1_base_3_A ); COMMIT| DELIMITER ;| DROP TABLE IF EXISTS testdb_A . t1_base_0_A; CREATE TEMPORARY TABLE IF NOT EXISTS testdb_A . t1_temp_1_A AS SELECT `int_key` FROM testdb_A . t1_base_2_A A /* */; CREATE TABLE IF NOT EXISTS testdb_A . t1_base_0_A AS SELECT `int_key` FROM testdb_A . t1_temp_1_A A UNION SELECT `int_key` FROM testdb_A . t1_base_3_A AS B WHERE `pk` BETWEEN 2 AND 0 /* */; DELIMITER |; CREATE TRIGGER testdb_A . tr1_2_A AFTER INSERT ON t1_base_0_A FOR EACH ROW BEGIN DELETE FROM testdb_A . t1_view_4_A WHERE `pk` > 3 LIMIT 0 ; END| DELIMITER ;| CREATE TABLE IF NOT EXISTS testdb_A . t1_base_0_A AS SELECT `int_key` FROM testdb_A . t1_part_0_A AS A /* FOR UPDATE */;