MySQL Inserts record with NULL value in NOT NULL column

Question

Why does the first INSERT go through for table2. Note that table2.col_1 is NOT NULL. It doesn't insert NULL for col_1, but mysteriously converts the NULL value to an empty string. I am using MySQL Version 5.5.28. Thanks

mysql> DROP TABLE IF EXISTS table1, table2;

Query OK, 0 rows affected (0.01 sec)   

mysql> CREATE  TABLE IF NOT EXISTS table1 (
    -> id INT UNSIGNED NOT NULL AUTO_INCREMENT ,
    -> col_1 VARCHAR(45) NOT NULL ,
    -> col_2 VARCHAR(45) NOT NULL ,
    -> PRIMARY KEY (`id`))
    -> ENGINE = InnoDB;

Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE table2 LIKE table1;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO table1 (id, col_1, col_2) VALUES (NULL, "xxx","yyy");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO table2 (id, col_1, col_2) SELECT NULL, NULL, col_2 FROM table1 WHERE id=1;
Query OK, 1 row affected, 1 warning (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 1

mysql> SHOW WARNINGS;
+---------+------+-------------------------------+
| Level   | Code | Message                       |
+---------+------+-------------------------------+
| Warning | 1048 | Column 'col_1' cannot be null |
+---------+------+-------------------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM table2;
+----+-------+-------+
| id | col_1 | col_2 |
+----+-------+-------+
|  1 |       | yyy   |
+----+-------+-------+
1 row in set (0.00 sec)

mysql> INSERT INTO table2 (id, col_1, col_2) VALUES( NULL, NULL, "zzz");
ERROR 1048 (23000): Column 'col_1' cannot be null

mysql> SELECT * FROM table2;
+----+-------+-------+
| id | col_1 | col_2 |
+----+-------+-------+
|  1 |       | yyy   |
+----+-------+-------+
1 row in set (0.00 sec)

Strange, I'm doing the same and I do get the nulls inserted... Maybe you could specify PDO::PARAM_INT when you bind the value? — Sebas, Apr 09 '13 at 15:05
@JW Please explain. I just edited the original post to show how it only inserts a row if the INSERT is fed by a SELECT. — user1032531, Apr 09 '13 at 15:06
@Sebas. See the very bottom of my original post. Note that there was a warning, but I currently don't know how to see the warning. — user1032531, Apr 09 '13 at 15:11
possible duplicate of [MySQL ignores the NOT NULL constraint](http://stackoverflow.com/questions/2503712/mysql-ignores-the-not-null-constraint) — Sebas, Apr 09 '13 at 15:30

DavidScherer · Accepted Answer · 2013-04-09T15:23:51.087

12

You have MySQL's STRICT mode OFF. Turn it on and you'll get an error.

Otherwise you can test for those warnings with PDO via: http://php.net/manual/en/pdo.errorinfo.php

edited Apr 09 '13 at 15:23

answered Apr 09 '13 at 15:15

DavidScherer

863
1
14
26

Thanks David, My bad, turns out to have nothing to do with PDO or PHP. I edited the original post to show this. Sorry for the confusion. – user1032531 Apr 09 '13 at 15:23
@user1032531 Updated this answer to reflect my further comprehension. – DavidScherer Apr 09 '13 at 15:24
I bet you are right! I never knew of MySQL STRICT mode, and will test it out. Thanks – user1032531 Apr 09 '13 at 15:26

score 8 · Answer 2 · answered Apr 09 '13 at 15:42

This behavior is well documented in MySQL docs . MySQL doc

If you are not using strict mode, then whenever you insert an “incorrect” value into a column, such as a NULL into a NOT NULL column or a too-large numeric value into a numeric column, MySQL sets the column to the “best possible value” instead of producing an error:,but the warning count is incremented

score -1 · Answer 3 · answered Feb 18 '18 at 17:46

-1

I have tried setting MySQL's STRICT mode OFF and didn't work for me (I even changed it to "my.ini" ).

What worked for me was a BEFORE INSERT TRIGGER

Basically you do:

CREATE TRIGGER triggerName BEFORE INSERT ON customer
FOR EACH ROW
BEGIN
    if new.`customerName` = '' then
    signal sqlstate '45000'
    SET MESSAGE_TEXT = 'Customer Name Cannot be Empty!';
    end if;
END

In the MESSAGE_TEXT you can add whatever text you want the error to show.

Hope it helps!

Most of it found here

answered Feb 18 '18 at 17:46

Pontios

2,377
27
32

I am clearly saying in the start of my post "I have **tried** setting MySQL's STRICT mode OFF **and didn't work**". I am not saying this is the solution. – Pontios May 04 '18 at 21:30
2

What did you mean by "didn't work"? The actual problem of the asker is because his STRICT mode is OFF. So why would you say setting it OFF didn't work when what you need to do is to actually turn it ON? – jbdeguzman Sep 10 '18 at 02:16

MySQL Inserts record with NULL value in NOT NULL column

3 Answers3

Linked

Related