MySQL Bugs: #69828: Missleading error message if trigger fails

Bug #69828	Missleading error message if trigger fails
Submitted:	23 Jul 2013 13:29	Modified:	4 Jul 2015 22:38
Reporter:	Normann Koldrack	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Errors	Severity:	S3 (Non-critical)
Version:	5.5.32, 5.6.24	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	Column count doesn't match, fail, trigger

Description:
If a trigger is failing, a missleading message is submitted to the user suggesting the query itself is failing.

How to repeat:
Create two tables:
CREATE TABLE `test1` ( `id` int(11) NOT NULL, `data` int(11) NOT NULL);
CREATE TABLE `test2` ( `id` int(11) NOT NULL, `data` int(11) NOT NULL);
Define erroneous trigger on table 1:

CREATE TRIGGER `test_trigger` BEFORE INSERT ON `test1`
FOR EACH
ROW INSERT INTO `test2` ( `id` , `data` , `more_data` )
VALUES (NEW.`id` , 1);

insert data into table 1:
INSERT INTO `test1`(`id`, `data`) VALUES (1,2);

error msg:
#1136 - Column count doesn't match value count at row 1

This is wrong for the query itself, but for the trigger.

Suggested fix:
Report a hint that the trigger itself is failing.

Thank you for the bug report.

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.5.34-debug Source distribution

Copyright (c) 2000, 2013, 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> CREATE TABLE `test1` ( `id` int(11) NOT NULL, `data` int(11) NOT NULL);
Query OK, 0 rows affected (0.05 sec)

mysql> CREATE TABLE `test2` ( `id` int(11) NOT NULL, `data` int(11) NOT NULL);
Query OK, 0 rows affected (0.03 sec)

mysql> CREATE TRIGGER `test_trigger` BEFORE INSERT ON `test1`
    -> FOR EACH
    -> ROW INSERT INTO `test2` ( `id` , `data` , `more_data` )
    -> VALUES (NEW.`id` , 1);
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO `test1`(`id`, `data`) VALUES (1,2);
ERROR 1136 (21S01): Column count doesn't match value count at row 1
mysql>

small update. Same error on 5.6.x too.
tested with 5.6.24