Mysql Not Null Columns accepting null values

It actually doesn't accept NULL values it considers it as empty string. That's because you have your server in non-strict mode. That controls how MySQL handles invalid or missing values in inserts and updates. You can read more about modes here: http://dev.mysql.com/doc/refman/5.6/en/sql-mode.html#sql-mode-strict

mysql> insert into cities(state_id) values (20);
Query OK, 1 row affected, 1 warning (0.07 sec)

mysql> show warnings;
+---------+------+-------------------------------------------+
| Level   | Code | Message                                   |
+---------+------+-------------------------------------------+
| Warning | 1364 | Field 'name' doesn't have a default value |
+---------+------+-------------------------------------------+

mysql> select name is null from cities where id = LAST_INSERT_ID();
+--------------+
| name is null |
+--------------+
|            0 |
+--------------+
1 row in set (0.00 sec)

mysql> SET sql_mode = 'STRICT_ALL_TABLES';
Query OK, 0 rows affected (0.00 sec)

mysql> insert into cities(state_id) values (20);
ERROR 1364 (HY000): Field 'name' doesn't have a default value

To Károly Nagy's point,

You can use

SET SQL_MODE = 'STRICT_ALL_TABLES';

I'm sure you may already know but that is equivilent to:

SET @@SESSION.SQL_MODE = 'STRICT_ALL_TABLES';

Which, must be set for every session. And you can checky this by running this after the above statement.

SELECT @@SESSION.SQL_MODE; -- 'STRICT_ALL_TABLES'

SELECT @@GLOBAL.SQL_MODE; -- 'NO_ENGINE_SUBSTITUTION'

If you have access please set this at the global level:

SET @@GLOBAL.SQL_MODE = 'STRICT_ALL_TABLES';

Which becomes the default for every new session thereafter.

Cheers, Jay ;-]

NOTE: Tested On MySQL Version 5.6.23-log & MySQL Workbench 6.2.5