4


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;

as you see the serial is set to NOT NULL,
but when i run query like 

UPDATE DeviceList SET serial = NULL WHERE id = 1

It outputs 

1 row affected. ( Query took 0.0079 sec )

Why is this inserting null to not null field?
I want an error to occure: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 was empty by default
iw changed it to STRICT_ALL_TABLES via

SET GLOBAL sql_mode='STRICT_ALL_TABLES'
Noel Llevares
  • 15,018
  • 3
  • 57
  • 81
KoSMoS
  • 534
  • 2
  • 5
  • 19

2 Answers2

2

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

MySQL 5.6 defaults to NO_ENGINE_SUBSTITUTION.

MySQL 5.7 defaults to 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 stated in this page from the MySQL Reference Manual,

MySQL installation programs may configure the SQL mode during the installation process. For example, mysql_install_db creates a default option file named my.cnf in the base installation directory.

Noel Llevares
  • 15,018
  • 3
  • 57
  • 81
0

It should and does throw error. I'd guess the create table you posted in the question doesn't correspond actual table definition ( taking into account you have a syntax error - extra comma ).

a1ex07
  • 36,826
  • 12
  • 90
  • 103
  • well i updated to complete table definition hope this will help to find the problem – KoSMoS Mar 05 '13 at 20:48
  • 1
    @KoSMoS: double check if you don't have `before update` trigger which may change "serial" on the fly. – a1ex07 Mar 05 '13 at 20:51
  • @a1ex07 i did not create any triggers in mysql in my life in :D could there be any default triggers? in phpmyadmin it says "There are no triggers to display." – KoSMoS Mar 05 '13 at 21:07
  • @KoSMoS: One more thing to try : depends on server's OS, table names in mysql can be case-sensitive. So `DeviceList` and `devicelist` can be 2 different tables. Double check `SHOW CREATE TABLE DeviceList` and see if you have `serial NOT NULL` there... – a1ex07 Mar 05 '13 at 21:12