Mysql does not execute error when inserting or updating to zero in non-empty field

Question

I have a table

CREATE TABLE `devicelist` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `ip` varchar(15) COLLATE utf8_unicode_ci DEFAULT NULL, `serial` varchar(100) COLLATE utf8_unicode_ci NOT NULL, `networkname` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL, `login` varchar(130) COLLATE utf8_unicode_ci DEFAULT NULL, `password` varchar(130) COLLATE utf8_unicode_ci DEFAULT NULL, `peopleid` int(10) unsigned NOT NULL, `deviceid` int(10) unsigned NOT NULL, `placeid` int(10) unsigned NOT NULL, `stationid` int(10) unsigned NOT NULL, `place` text COLLATE utf8_unicode_ci, `date` date NOT NULL, `active` tinyint(1) NOT NULL DEFAULT '0', PRIMARY KEY (`id`), KEY `placeid` (`placeid`), KEY `stationid` (`stationid`), KEY `peopleid` (`peopleid`), KEY `deviceid` (`deviceid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; 

since you see the serial is set to NOT NULL ,
but when I run the query like

 UPDATE DeviceList SET serial = NULL WHERE id = 1 

He outputs

 1 row affected. ( Query took 0.0079 sec ) 

Why is this null insertion into a non-empty field?
I want an error to occur: D

/// ******************************** EDIT

well i found the answer
The problem was in sql_mode
https://dev.mysql.com/doc/refman/5.6/en/sql-mode.html#sql-mode-strict
my sql_mode is empty by default.
iw changed it to STRICT_ALL_TABLES in

 SET GLOBAL sql_mode='STRICT_ALL_TABLES' 

Answer 1

He must and makes a mistake. I would suggest that the create table that you posted in the question does not match the actual definition of the table (given that you have a syntax error - an extra comma).

Answer 2

MySQL sucked the hell out of my day because of this, so I'm just sharing what I learned about it.

MySQL 5.6 by default is NO_ENGINE_SUBSTITUTION .

MySQL 5.7 by default: ONLY_FULL_GROUP_BY , STRICT_TRANS_TABLES , NO_ZERO_IN_DATE , NO_ZERO_DATE , ERROR_FOR_DIVISION_BY_ZERO , NO_AUTO_CREATE_USER , NO_ENGINE_SUBSTITUTION .

As indicated on this page in the MySQL Reference Guide,

MySQL installers can configure SQL mode during the installation process. For example, mysql_install_db creates a default settings file called my.cnf in the base installation directory.