[MDEV-4829] BEFORE INSERT triggers dont issue 1406 error Created: 2013-07-31  Updated: 2015-11-18  Resolved: 2015-11-17

Status: Closed
Project: MariaDB Server
Component/s: Triggers
Affects Version/s: 5.5.32, 5.5, 10.0, 10.1
Fix Version/s: 10.1.9

Type: Bug Priority: Major
Reporter: Federico Razzoli Assignee: Alexey Botchkov
Resolution: Fixed Votes: 0
Labels: upstream-fixed, verified

Issue Links:
Relates
relates to MDEV-8109 unexpected CAST result Closed
Sprint: 10.1.7-1, 10.1.7-2, 10.1.8-1, 10.1.8-3, 10.1.8-4, 10.1.9-2, 10.1.9-3

 Description   

I created a BEFORE INSERT trigger which modifies a value making it too long. Despite the sql_mode, no error/warning appears.

MariaDB [test]> USE test;
 
Database changed
 
MariaDB [test]> SET @@session.sql_mode = 'STRICT_ALL_TABLES,STRICT_TRANS_TABLES'
 
;
 
Query OK, 0 rows affected (0.00 sec)
 
 
 
MariaDB [test]> DROP TABLE IF EXISTS t1;
 
Query OK, 0 rows affected (0.06 sec)
 
 
 
MariaDB [test]> CREATE TABLE t1 (
 
    ->  c CHAR(1) NOT NULL
 
    -> ) ENGINE = InnoDB;
 
Query OK, 0 rows affected (0.08 sec)
 
 
 
MariaDB [test]> DELIMITER ||
 
MariaDB [test]> CREATE TRIGGER t1_bi
 
    ->  BEFORE INSERT
 
    ->  ON t1
 
    ->  FOR EACH ROW
 
    -> BEGIN
 
    ->  SET NEW.c = 'www';
 
    -> END;
 
    -> ||
 
Query OK, 0 rows affected (0.04 sec)
 
 
 
MariaDB [test]> DELIMITER ;
 
MariaDB [test]> INSERT INTO t1 VALUES ('a');
 
Query OK, 1 row affected (0.00 sec)
 
Records: 1  Duplicates: 0  Warnings: 0
 
 
 
MariaDB [test]> SHOW WARNINGS;
 
Empty set (0.00 sec)
 
 
 
MariaDB [test]> SELECT * FROM t1;
 
+---+
 
| c |
 
+---+
 
| w |
 
+---+
 
1 row in set (0.00 sec)

Sorry, i dont have a MySQL installed locally anymore, so i dont know if this bug is mainstream.

Some notes...

  • only happens with before insert triggers, not with update triggers or procedures or functions
  • only happens when doing SET NEW.col, not when trying to UPDATE another table
  • only happens for long data, not if you try to set NULL a NOT NULL column

 Comments   
Comment by Elena Stepanova [ 2013-07-31 ]

Hi Federico,

Yes, it seems to be another known legacy bug: http://bugs.mysql.com/bug.php?id=42910

Comment by Elena Stepanova [ 2014-11-10 ]

Fixed in MySQL 5.7.5:

revno: 8074
 
revision-id: abhishek.ar.ranjan@oracle.com-20140522102654-e6kau2hswut4oio5
 
parent: mauritz.sundell@oracle.com-20140522095307-z4wse34z060ndn77
 
committer: Abhishek Ranjan <abhishek.ar.ranjan@oracle.com>
 
branch nick: mysql-trunk
 
timestamp: Thu 2014-05-22 15:56:54 +0530
 
message:
 
  WL#6614 : Define and reimplement IGNORE
 
  WL#6891 : Define and reimplement STRICT MODE
 
  
  IGNORE Reimplementation :
 
  This worklog reimplements IGNORE. In the previous implementation,
 
  errors were downgraded to warnings inside THD::raise_condition()
 
  based on SELECT_LEX::no_error. This patch changes it to use an
 
  Internal_error_handler activated just for statements which support
 
  IGNORE keyword to downgrade specific errors to warnings.
 
  
  Bugs Fixed by this worklog :
 
  
  - Bug#11744960 : INSERT IGNORE SHOULD RETURN WARNINGS
 
  - Bug#11752648 : MULTI-DELETE IGNORE DOES NOT REPORT WARNINGS
 
  - Bug#16522924 : UPDATE TRIGGER INVOKED WHEN UPDATE IGNORE
 
                   MEANS THAT NO UPDATE IS PERFORMED
 
  - Bug#16860715 : ASSERT IN PROTOCOL::END_STATEMENT DURING DELETE
 
                   IGNORE
 
  - Bug#16860829 : ASSERT IN DIAGNOSTICS_AREA::SET_ERROR_STATUS
 
  - Bug#17550423 : DELETE IGNORE GIVES INCORRECT RESULT WITH FOREIGN
 
                   KEY FOR PARENT TABLE
 
  
  
  STRICT Mode Reimplementation :
 
  This worklog reimplements STRICT mode. In the previous implementation
 
  When STRICT MODE was enabled, warning were upgraded to errors inside
 
  THD::raise_condition() based on THD::abort_on_warning flag.
 
  This patch uses an Internal_error_handler activated just for
 
  STRICT MODE to do the upgrade of specific warnings to errors.
 
  
  Bugs Fixed by this worklog :
 
  
  - BUG#11751889: TRIGGERS OVERRIDE STRICT SQL_MODE
 
  - Bug#16976939: FIX ERROR MESSAGE ON DUPLICATE INDEX CREATION AND
 
                  STRICT MODE
 
  - BUG#18526888: STRICT MODE DOES NOT APPLY TO MULTI DELETE STATEMENT

Comment by Federico Razzoli [ 2015-05-01 ]

Hi. Now that it is "upstream-fixed", are there more chances to see this fixed in MariaDB?

Comment by Alexey Botchkov [ 2015-09-24 ]

Fix proposal: http://myoffice.izhnet.ru/~af/mdev4829-patch

Comment by Aurélien LEQUOY [ 2015-10-14 ]

even if you replace :

SET NEW.c = 'www';

by

SET NEW.c = 'w';

it's will show 'w' instead of 'a', so it's don't work at all

Comment by Federico Razzoli [ 2015-10-14 ]

Aurélien, I don't know if I understand your comment. But if I set NEW.c = 'w', I expect the new row to contain 'w'... what's the problem?

Comment by Aurélien LEQUOY [ 2015-10-28 ]

BEFORE INSERT

INSERT INTO t1 VALUES ('a');

so for me I understand like that :

INSERT INTO t1 VALUES ('w');
then UPDATE t1 SET c='a';

if it was after insert this case is good, but in example it's before. it's was I found strange to see a 'w' there.

in this example I understood that like it was a default value, if this field is not set in insert

Comment by Alexey Botchkov [ 2015-11-16 ]

Proposed fix, sort of small one:
http://lists.askmonty.org/pipermail/commits/2015-November/008635.html

Comment by Alexey Botchkov [ 2015-11-16 ]

Here's the alternative fix where i'd modify the existing STRICT mode implementation using error handler.
http://lists.askmonty.org/pipermail/commits/2015-November/008636.html

Comment by Alexey Botchkov [ 2015-11-16 ]

So there's two different patches. Second one is quite massive, but i think it makes code look more understandable.

Comment by Daniel Black [ 2015-11-17 ]

holyfoot, would this fix the following transformation of a BEFORE insert correcting a column value too?

DELIMITER #
 
 
 
MariaDB [test]> create table x(LocID int not null);
 
    -> #
 
Query OK, 0 rows affected (0.00 sec)
 
 
 
MariaDB [test]> CREATE  TRIGGER xnotnull BEFORE INSERT ON x FOR EACH ROW BEGIN IF (NEW.LocID IS NULL) THEN     SET NEW.LocID=0;   END IF;  END#
 
Query OK, 0 rows affected (0.00 sec)
 
 
 
MariaDB [test]> insert into x values (NULL);
 
    -> #
 
ERROR 1048 (23000): Column 'LocID' cannot be null
 
MariaDB [test]> show warnings;
 
    -> #
 
+-------+------+-------------------------------+
 
| Level | Code | Message                       |
 
+-------+------+-------------------------------+
 
| Error | 1048 | Column 'LocID' cannot be null |
 
+-------+------+-------------------------------+
 
1 row in set (0.00 sec)
 
 
 
MariaDB [test]> select * from x;
 
    -> #
 
Empty set (0.00 sec)
 
 
 
 select @@sql_mode,@@version#
 
+------------+---------------------+
 
| @@sql_mode | @@version           |
 
+------------+---------------------+
 
|            | 10.0.22-MariaDB-log |
 
+------------+---------------------+

Comment by Sergei Golubchik [ 2015-11-17 ]

danblack, it's MDEV-8605.

Comment by Aurélien LEQUOY [ 2015-11-18 ]

will be fixed in 10.1.9 ?

Comment by Sergei Golubchik [ 2015-11-18 ]

Sorry (the FixVersion was incorrect). It is fixed in 10.1.9, but not in 10.0

Generated at Thu Feb 08 06:59:31 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.