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
– Sebas, Commented Apr 9, 2013 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
– user1032531, Commented Apr 9, 2013 at 15:06

DavidScherer · Accepted Answer · 2013-04-09 15:23:51Z

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 9, 2013 at 15:23

answered Apr 9, 2013 at 15:15

DavidScherer

9012 gold badges15 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user1032531 Over a year ago

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.

DavidScherer Over a year ago

@user1032531 Updated this answer to reflect my further comprehension.

user1032531 Over a year ago

I bet you are right! I never knew of MySQL STRICT mode, and will test it out. Thanks

Arun Killu · Accepted Answer · 2013-04-09 15:42:44Z

8

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

answered Apr 9, 2013 at 15:42

Arun Killu

14.3k5 gold badges38 silver badges66 bronze badges

Comments

Pontios · Accepted Answer · 2018-02-18 17:46:59Z

-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, 2018 at 17:46

Pontios

2,39429 silver badges36 bronze badges

2 Comments

Pontios Over a year ago

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.

jbdeguzman Over a year ago

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?

Collectives™ on Stack Overflow

MySQL Inserts record with NULL value in NOT NULL column

3 Answers 3

3 Comments

Comments

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

2 Comments

Linked

Related