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

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.039 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.