Bug #47381 Wrong warning about crashed table, CREATE TABLE IF NOT EXISTS AS SELECT

• Submitted: 16 Sep 2009 16:00
• Modified: 17 Sep 2009 10:41
• Reporter: Matthias Leich
• Status: Duplicate
• Category: MySQL Server: DDL
• Severity: S3 (Non-critical)
• Version: 5.1,5.4
• OS: Any
• CPU Architecture: Any

[16 Sep 2009 16:00] Matthias Leich

Description:
My script (problem found in RQG test):
--------------------------------------
--disable_abort_on_error
CREATE TABLE t1 (f3 INT, f1 INTEGER, f2 INT, PRIMARY KEY (f1)) ENGINE = MyISAM;
INSERT INTO t1 VALUES  (NULL, 1, 2),(2, 2, NULL);
CREATE TABLE t2 AS SELECT f2,f3,f1 FROM t1 WHERE f1 = 1;
delimiter |;
CREATE TRIGGER tr1 AFTER INSERT ON t2 FOR EACH ROW
BEGIN
   DELETE FROM t1 WHERE f1 IN (SELECT f1 FROM t2 WHERE f1 = 1);
END|
delimiter ;|

# If I run the following INSERT instead of the CREATE TABLE IF NOT EXISTS t2 ...
#              INSERT INTO t2    SELECT f1,f2,f3 FROM (SELECT * FROM t2) AS A;
# I get
# Warnings:
# Warning   1048  Column 'f1' cannot be null

# Here I get ERROR HY000: Table 't2' is marked as crashed and should be repaired
# !!!!!      Why this?    !!!!!
CREATE TABLE IF NOT EXISTS t2 AS SELECT f1,f2,f3 FROM (SELECT * FROM t2) AS A;

SELECT * FROM t2;

Result on mysql-next-bugfixing 2009-09-16 09:01:28
--------------------------------------------------
CREATE TABLE t1 (f3 INT, f1 INTEGER, f2 INT, PRIMARY KEY (f1)) ENGINE = MyISAM;
INSERT INTO t1 VALUES  (NULL, 1, 2),(2, 2, NULL);
CREATE TABLE t2 AS SELECT f2,f3,f1 FROM t1 WHERE f1 = 1;
CREATE TRIGGER tr1 AFTER INSERT ON t2 FOR EACH ROW
BEGIN
DELETE FROM t1 WHERE f1 IN (SELECT f1 FROM t2 WHERE f1 = 1);
END|
CREATE TABLE IF NOT EXISTS t2 AS SELECT f1,f2,f3 FROM (SELECT * FROM t2) AS A;
ERROR HY000: Table 't2' is marked as crashed and should be repaired
SELECT * FROM t2;
f2	f3	f1
2	NULL	1
1	2	0

My environment:
---------------
- mysql-next-bugfixing 2009-09-16 12:23:16
- mysql-5.1-bugteam 2009-09-16 09:01:28
- ./BUILD/compile-pentium64-debug-max
- Linux OpenSuSE 11.0 (64 Bit)
- Intel Core2Duo

How to repeat:
Please see above

[16 Sep 2009 16:12] Valeriy Kravchuk

Thank you for the bug report. Verified just as described with recent 5.1.40 from bzr.

Table is NOT corrupted really:

mysql> CREATE TABLE IF NOT EXISTS t2 AS SELECT f1,f2,f3 FROM (SELECT * FROM t2) AS A;
ERROR 1194 (HY000): Table 't2' is marked as crashed and should be repaired
mysql> select * from t2;
+------+------+----+
| f2   | f3   | f1 |
+------+------+----+
|    2 | NULL |  1 |
|    1 |    2 |  0 |
+------+------+----+
2 rows in set (0.00 sec)

mysql> check table t2;
+---------+-------+----------+----------+
| Table   | Op    | Msg_type | Msg_text |
+---------+-------+----------+----------+
| test.t2 | check | status   | OK       |
+---------+-------+----------+----------+
1 row in set (0.01 sec)

[17 Sep 2009 10:41] Matthias Leich

Duplicate of
  Bug#46617 Bogus "table is marked as crashed and should be repaired"