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

[23 Jul 2013 13:29] Normann Koldrack
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.
[23 Jul 2013 14:03] MySQL Verification Team
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>
[4 Jul 2015 22:38] Normann Koldrack
small update. Same error on 5.6.x too.
tested with 5.6.24