Bug #119086 Server crashes when executing a trigger whose dependent VIEW was dropped and recreated as a TABLE

• Submitted: 27 Sep 15:55
• Reporter: QiFan Liu
• Status: Open
• Category: MySQL Server: Stored Routines
• Severity: S2 (Serious)
• Version: 9.4.0
• OS: Any
• CPU Architecture: Any

[27 Sep 15:55] QiFan Liu

Description:
Hey! I found a crash in 9.4.0. Server crashes when executing a trigger whose dependent VIEW was dropped and recreated as a TABLE with the same name.

Server version: 9.4.0 MySQL Community Server - GPL

Copyright (c) 2000, 2025, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use test;
Database changed
mysql> CREATE  TABLE t1 (a INT );
Query OK, 0 rows affected (0.025 sec)

mysql> CREATE  TABLE t2 (b INT );
Query OK, 0 rows affected (0.032 sec)

mysql> CREATE  TRIGGER tr BEFORE INSERT  ON t1 FOR EACH ROW INSERT  INTO t2 SELECT * FROM vt;
Query OK, 0 rows affected (0.014 sec)

mysql> CREATE  VIEW vt AS  SELECT 1 AS x;
Query OK, 0 rows affected (0.008 sec)

mysql> INSERT  INTO t1 VALUES (1);
Query OK, 1 row affected (0.005 sec)

mysql> DROP  VIEW  IF EXISTS vt;
Query OK, 0 rows affected (0.007 sec)

mysql> CREATE  TABLE vt (f INT );
Query OK, 0 rows affected (0.032 sec)

mysql> INSERT  INTO t1 VALUES (2);
imchifan@imchifan-vm:~$ 

How to repeat:
CREATE  TABLE t1 (a INT );
CREATE  TABLE t2 (b INT );
CREATE  TRIGGER tr BEFORE INSERT  ON t1 FOR EACH ROW INSERT  INTO t2 SELECT * FROM vt;
CREATE  VIEW vt AS  SELECT 1 AS x;
INSERT  INTO t1 VALUES (1);
DROP  VIEW  IF EXISTS vt;
CREATE  TABLE vt (f INT );
INSERT  INTO t1 VALUES (2);
# Cleanup
DROP  TABLE vt, t1, t2;