Details
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
Gliffy Diagrams
Attachments
Issue Links
- relates to
-
MDEV-8109 unexpected CAST result
-
- In Review
-
- links to
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
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
Hi. Now that it is "upstream-fixed", are there more chances to see this fixed in MariaDB?
Fix proposal: http://myoffice.izhnet.ru/~af/mdev4829-patch
Hi Federico,
Yes, it seems to be another known legacy bug: http://bugs.mysql.com/bug.php?id=42910