MySQL Bugs: #14540: REPAIR, OPTIMIZE or ANALYZE a VIEW crashes server when underlying table is gone

Bug #14540	REPAIR, OPTIMIZE or ANALYZE a VIEW crashes server when underlying table is gone
Submitted:	1 Nov 2005 11:29	Modified:	9 Nov 2005 3:31
Reporter:	Shane Bester (Platinum Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Views	Severity:	S2 (Serious)
Version:	5.0.15, 5.0.16bk	OS:	Linux (Linux, Windows)
Assigned to:	Igor Babaev	CPU Architecture:	Any

Description:
When running REPAIR, ANALYZE or OPTIMIZE TABLE on a view who's underlying table has been dropped, the server crashes.  CHECK seems to work fine.

mysql> DROP TABLE IF EXISTS t1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> DROP VIEW IF EXISTS v1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE t1(id INT) ENGINE=MyISAM;
Query OK, 0 rows affected (0.02 sec)

mysql> CREATE VIEW v1 AS SELECT id FROM t1;
Query OK, 0 rows affected (0.00 sec)

mysql> DROP TABLE t1;
Query OK, 0 rows affected (0.01 sec)

mysql> OPTIMIZE TABLE v1;
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql>

How to repeat:
DROP TABLE IF EXISTS t1;
DROP VIEW IF EXISTS v1;
CREATE TABLE t1(id INT) ENGINE=MyISAM;
CREATE VIEW v1 AS SELECT id FROM t1;
DROP TABLE t1;
OPTIMIZE TABLE v1;

Suggested fix:
Not sure...

Verified just as described with 5.0.16-BK (ChangeSet@1.1957, 2005-10-29 13:11:34+04:00, konstantin@mysql.com) on Linux:

[openxs@Fedora 5.0]$ bin/mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6 to server version: 5.0.16

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> create table tcr(id int) engine=MyISAM;
Query OK, 0 rows affected (0,08 sec)

mysql> create view vcr as select id from tcr;
Query OK, 0 rows affected (0,15 sec)

mysql> drop table tcr;
Query OK, 0 rows affected (0,00 sec)

mysql> optimize table vcr;
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql>
Number of processes running now: 0
051101 12:56:55  mysqld restarted

We also have misleading messages when table t1 is not dropped:

mysql> CREATE TABLE t1(id INT) ENGINE=MyISAM;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE VIEW v1 AS SELECT id FROM t1;
Query OK, 0 rows affected (0.01 sec)

mysql> OPTIMIZE TABLE v1;
+---------+----------+----------+-----------------------------------------------------------+
| Table   | Op       | Msg_type | Msg_text                                                  |
+---------+----------+----------+-----------------------------------------------------------+
| test.v1 | optimize | note     | The storage engine for the table doesn't support optimize |
+---------+----------+----------+-----------------------------------------------------------+
1 row in set (0.00 sec)

mysql> ANALYZE TABLE v1;
+---------+---------+----------+----------------------------------------------------------+
| Table   | Op      | Msg_type | Msg_text                                                 |
+---------+---------+----------+----------------------------------------------------------+
| test.v1 | analyze | note     | The storage engine for the table doesn't support analyze |
+---------+---------+----------+----------------------------------------------------------+
1 row in set (0.00 sec)

mysql> REPAIR TABLE v1;
+---------+--------+----------+---------------------------------------------------------+
| Table   | Op     | Msg_type | Msg_text                                                |
+---------+--------+----------+---------------------------------------------------------+
| test.v1 | repair | note     | The storage engine for the table doesn't support repair |
+---------+--------+----------+---------------------------------------------------------+
1 row in set (0.00 sec)

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/31840

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/31841

ChangeSet
  1.1963 05/11/02 13:44:58 igor@rurik.mysql.com +5 -0

  sql_table.cc, handler.h:
    Fixed bug #14540.
    Added error mnemonic code HA_ADMIN_NOT_BASE_TABLE
    to report that an operation cannot be applied for views.
  view.test, view.result:
    Added a test case for bug #14540.
  errmsg.txt:
    Fixed bug #14540.
    Added error ER_CHECK_NOT_BASE_TABLE.

Fix will appear in 5.0.17.

Noted in 5.0.16 changelog.