MySQL Bugs: #12360: Using table named 'audit' for trigger causes server crash

Bug #12360	Using table named 'audit' for trigger causes server crash
Submitted:	3 Aug 2005 21:37	Modified:	4 Aug 2005 0:53
Reporter:	Robin Schumacher	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.10	OS:	Windows (Windows XP)
Assigned to:		CPU Architecture:	Any

Description:
I can crash MySQL by using a table named 'audit' and a trigger that references it.  

The nasty thing is (believe it or not), that once I restart MySQL, I can't run any procedure I wrote.  Server crashes again.  The issue seems to be the table named 'audit'.  When I drop that table and associated trigger and repeat the above scenario with a table called 'audittab', everything works like a champ.  Another nasty appears to be, after the corruption occurs, I have to drop ALL my previously created triggers to make things right again.  

How to repeat:
mysql> create table audit(userid varchar(30),time_of_action timestamp);
Query OK, 0 rows affected (0.58 sec)

mysql> create table trackme (custid int, orderamt decimal(7,2));
Query OK, 0 rows affected (0.53 sec)

mysql> delimiter //
mysql> create trigger t_trackme before insert on trackme
    -> for each row
    -> begin
    -> insert into audit (userid) values(current_user());
    -> end;
    -> //
Query OK, 0 rows affected (0.41 sec)

mysql> delimiter ;
mysql> insert into trackme values (1,123.70);
Query OK, 1 row affected (0.08 sec)

mysql> select * from audit;
+----------------+---------------------+
| userid         | time_of_action      |
+----------------+---------------------+
| root@localhost | 2005-08-03 13:36:02 |
+----------------+---------------------+
1 row in set (0.00 sec)

mysql> use information_schema;
Database changed
mysql> select trigger_name from triggers;
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql>

Server crashes.

Suggested fix:
Handle name 'audit' better for DDL operations.

I was unable to repeat. Could you please provide your
my.ini file ?

Thanks in advance.

c:\mysql\bin>mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.10-beta-nt

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

mysql> create database db;
Query OK, 1 row affected (0.00 sec)

mysql> use db;
Database changed
mysql> create table audit(userid varchar(30),time_of_action timestamp);
Query OK, 0 rows affected (0.06 sec)

mysql> create table trackme (custid int, orderamt decimal(7,2));
Query OK, 0 rows affected (0.06 sec)

mysql> delimiter //
mysql> create trigger t_trackme before insert on trackme
    -> for each row
    -> begin
    -> insert into audit (userid) values(current_user());
    -> end;
    -> //
Query OK, 0 rows affected (0.02 sec)

mysql> delimiter ;
mysql> insert into trackme values (1,123.70);
Query OK, 1 row affected (0.03 sec)

mysql> select * from audit;
+----------------+---------------------+
| userid         | time_of_action      |
+----------------+---------------------+
| root@localhost | 2005-08-03 18:57:12 |
+----------------+---------------------+
1 row in set (0.00 sec)

mysql> use information_schema;
Database changed
mysql> select trigger_name from triggers;
+--------------+
| trigger_name |
+--------------+
| t_trackme    |
+--------------+
1 row in set (0.03 sec)

mysql> select version();
+----------------+
| version()      |
+----------------+
| 5.0.10-beta-nt |
+----------------+
1 row in set (0.00 sec)

mysql>

my.cnf file

Attachment: my.ini (application/octet-stream, text), 8.92 KiB.

Note: I upgraded on XP to 5.10 from 5.09 and afterward had unprintable characters in my ROUTINES object in INFORMATION_SCHEMA.  Monty suggested I truncate the procs and proc_privs tables in the mysql database, which I did.  Before the truncates, I couldn't create a procedure - it crashed the server.  After the truncates, I was OK again.  Maybe I was the victim of a bad upgrade?

Hi Robin,

Yes I think your issue it is an upgrade case, you aren't the first case
with server crash upgrading from 5.0.9, unlucky until now we aren't able
for to reproduce the right steps for to get the crash.
Look the release note regarding triggers:

http://dev.mysql.com/doc/mysql/en/news-5-0-10.html

#

The namespace for triggers has changed. Previously, trigger names had to be unique per table. Now they must be unique within the schema (database). An implication of this change is that DROP TRIGGER syntax now uses a schema name instead of a table name (schema name is optional and, if omitted, the current schema will be used).

Note: When upgrading from a previous version of MySQL 5 to MySQL 5.0.10 or newer, you must drop all triggers before upgrading and re-create them after or DROP TRIGGER will not work after the upgrade. (Bug #5892)

If you are able the details how you did the upgrade I will try to follow
the same steps.

Thanks in advance.

First, thanks for the incredibly quick response!  I know I had some existing procedures in my 5.0.9 database, but am not sure about triggers.  Other than that, I just had one database with InnoDB tables.  

To upgrade, I just installed the 5.10 beta over the top of 5.0.9 and made sure the service was not started.

I installed 5.0.9 and created some procedures, then stopped the
server and installed 5.0.10 over the 5.0.9 installation. 
Repeated the same script reported here and the server not
crash. Something I missed here.

OK - thanks for giving it a try.  I'm willing to chalk it up to something quirkly on my machine alone then.  You can close this out.

Thanks again,

Robin