Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
5.5.32, 5.5(EOL), 10.0(EOL), 10.1(EOL)
-
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
Attachments
Issue Links
- relates to
-
-
- Closed
-
- links to
Activity
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 |
|
+------------+---------------------+
|
Sorry (the FixVersion was incorrect). It is fixed in 10.1.9, but not in 10.0
So there's two different patches. Second one is quite massive, but i think it makes code look more understandable.