MySQL Bugs: #72446: Unexpected trigger behavior in 5.6 : 1054 Unknown column '...' in 'NEW'

Bug #72446	Unexpected trigger behavior in 5.6 : 1054 Unknown column '...' in 'NEW'
Submitted:	24 Apr 2014 23:09	Modified:	20 May 2014 11:48
Reporter:	Andrew Garner	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S2 (Serious)
Version:	5.6.17	OS:	Linux (RHEL 6.5)
Assigned to:		CPU Architecture:	Any

Description:
Given two tables, "A" and "B", creating table A with a before insert trigger that set's a column's value based on data from table B can result in a 1054 error "Unknown column in NEW".  This is not reproducible on MySQL 5.5, but is reproducable on MySQL 5.6.15, 5.6.16 and 5.6.17 (earlier releases not tested).

For this to manifest, an attempt to insert into table A before creating table B (resulting in an expected error) - or alternatively by dropping / recreating table B must be occur.

How to repeat:
Test case against MySQL 5.6.17 on RHEL 6.5 using the packages from the mysql-community repo:

$ rpm -qa mysql-community\*
mysql-community-libs-5.6.17-4.el6.x86_64
mysql-community-server-5.6.17-4.el6.x86_64
mysql-community-common-5.6.17-4.el6.x86_64
mysql-community-release-el6-5.noarch
mysql-community-client-5.6.17-4.el6.x86_64

$ mysql -sse 'select @@version, @@version_comment'
5.6.17	MySQL Community Server (GPL)

$ cat <<EOF > mysql_trigger_bug.sql
DROP TABLE IF EXISTS table_A;
DROP TABLE IF EXISTS table_B;

CREATE TABLE table_A (
  id int(9) unsigned NOT NULL AUTO_INCREMENT,
  data varchar(255),
  PRIMARY KEY (id)
);

DELIMITER ;;
CREATE TRIGGER trigger_on_A
BEFORE INSERT ON table_A
FOR EACH ROW
SET NEW.data = (SELECT '42' FROM table_B)
;;
DELIMITER ;

INSERT INTO table_A (data) VALUES ('1');

CREATE TABLE table_B (
  id varchar(14) NOT NULL DEFAULT '',
  PRIMARY KEY (id)
);

INSERT INTO table_A (data) VALUES ('1');
EOF

This is vastly simplified from a problematic workload encountered in a production environment.

Running this results in this behavior:

$ mysql --force test < mysql_trigger_bug.sql
ERROR 1146 (42S02) at line 18: Table 'test.table_B' doesn't exist
ERROR 1054 (42S22) at line 26: Unknown column 'data' in 'NEW'

Obviously the first error is expected if table_B does not exist.  The second error is surprising and exists regardless of the content of table_B.

Suggested fix:
The expected results are:

$ mysql test -e 'select * from table_A';
+----+------+
| id | data |
+----+------+
|  1 | NULL |
+----+------+

This is the result on MySQL 5.5.37.   However on MySQL 5.6 a workaround was discovered by running:

FLUSH LOCAL TABLES;
INSERT INTO table_A (data) VALUES ('1');

Which allows the second insert to succeed and gives the same results as observed under MySQL 5.5.37.  Maybe there's some sort of caching going on here.

Thank you for the bug report.

C:\dbs>net start mysqld55
The MySQLD55 service is starting..
The MySQLD55 service was started successfully.

C:\dbs>55

C:\dbs>c:\dbs\5.5\bin\mysql -uroot --port=3550 --prompt="mysql 5.5 > "
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.38 Source distribution

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

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 5.5 > CREATE DATABASE XD;
Query OK, 1 row affected (0.01 sec)

mysql 5.5 > USE XD
Database changed
mysql 5.5 > CREATE TABLE table_A (
    ->   id int(9) unsigned NOT NULL AUTO_INCREMENT,
    ->   data varchar(255),
    ->   PRIMARY KEY (id)
    -> );
Query OK, 0 rows affected (0.11 sec)

mysql 5.5 > DELIMITER ;;
mysql 5.5 > CREATE TRIGGER trigger_on_A
    -> BEFORE INSERT ON table_A
    -> FOR EACH ROW
    -> SET NEW.data = (SELECT '42' FROM table_B)
    -> ;;
Query OK, 0 rows affected (0.09 sec)

mysql 5.5 > DELIMITER ;
mysql 5.5 > INSERT INTO table_A (data) VALUES ('1');
ERROR 1146 (42S02): Table 'xd.table_B' doesn't exist
mysql 5.5 > CREATE TABLE table_B (
    ->   id varchar(14) NOT NULL DEFAULT '',
    ->   PRIMARY KEY (id)
    -> );
Query OK, 0 rows affected (0.17 sec)

mysql 5.5 > INSERT INTO table_A (data) VALUES ('1');
Query OK, 1 row affected (0.01 sec)

mysql 5.5 > select * from table_A;
+----+------+
| id | data |
+----+------+
|  1 | NULL |
+----+------+
1 row in set (0.00 sec)

mysql 5.5 > exit
Bye

C:\dbs>net start mysqld56
The MySQLD56 service is starting.
The MySQLD56 service was started successfully.

C:\dbs>56

C:\dbs>c:\dbs\5.6\bin\mysql -uroot --port=3560 --debug-info --prompt="mysql 5.6 > "
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.19 Source distribution

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

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 5.6 > CREATE DATABASE XD;
Query OK, 1 row affected (0.01 sec)

mysql 5.6 > USE XD
Database changed
mysql 5.6 > CREATE TABLE table_A (
    ->   id int(9) unsigned NOT NULL AUTO_INCREMENT,
    ->   data varchar(255),
    ->   PRIMARY KEY (id)
    -> );
Query OK, 0 rows affected (0.33 sec)

mysql 5.6 > DELIMITER ;;
mysql 5.6 > CREATE TRIGGER trigger_on_A
    -> BEFORE INSERT ON table_A
    -> FOR EACH ROW
    -> SET NEW.data = (SELECT '42' FROM table_B)
    -> ;;
Query OK, 0 rows affected (0.14 sec)

mysql 5.6 > DELIMITER ;
mysql 5.6 > INSERT INTO table_A (data) VALUES ('1');
ERROR 1146 (42S02): Table 'xd.table_B' doesn't exist
mysql 5.6 > CREATE TABLE table_B (
    ->   id varchar(14) NOT NULL DEFAULT '',
    ->   PRIMARY KEY (id)
    -> );
Query OK, 0 rows affected (0.23 sec)

mysql 5.6 > INSERT INTO table_A (data) VALUES ('1');
ERROR 1054 (42S22): Unknown column 'data' in 'NEW'
mysql 5.6 > select * from table_A;
Empty set (0.00 sec)

mysql 5.6 >

[19 May 2014 17:15] Paul Dubois

Noted in 5.6.19, 5.7.5 changelogs.

The server could fail to properly reprepare triggers that referred to
another table after that table was truncated.

Fixed under the heading of Bug#18596756 FAILED PREPARING OF TRIGGER ON TRUNCATED TABLES CAUSE ERROR 1054