mysql shows warning instead of error when inserting and not giving a value to column that is set as not null - field does't have a default value

Question

6.24 running on windows 7 (from XAMPP v3.2.1). It seems strange to me that when I have a table with a column with Allow NULL set to NOT NULL and there is no Default value And I try to insert a row without specifying that column value I get a warning instead of an error.

I am pretty sure that in previous version MySQL did not allow that and produced and error and did not actually inserted the line. Is that a configuration issue? Is that how it should be?

Answer 1

I was able to reproduce your MySQL behaviour on my Linux Slackware MySQL 5.5.27:

mysql> CREATE TABLE `test` (
  `field1` int(10) unsigned NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Query OK, 0 rows affected (0.07 sec)

mysql> INSERT INTO test VALUES ();
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
  Level: Warning
   Code: 1364
Message: Field 'field1' doesn't have a default value
1 row in set (0.00 sec)

mysql> SELECT * FROM test;
+--------+
| field1 |
+--------+
|      0 |
+--------+
1 row in set (0.00 sec)

This is related to sql_mode variable:

1. @@GLOBAL.sql_mode
2. @@SESSION.sql_mode

When you set it to "TRADITIONAL", it starts to give an error:

mysql> SET @@SESSION.sql_mode='TRADITIONAL';
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO test VALUES ();
ERROR 1364 (HY000): Field 'field1' doesn't have a default value

Documentation about SQL modes