Avoiding implicit default column values in MySQL

In a non-strict MySQL server mode, columns with NOT NULL and without any DEFAULT values are given implicit default values based on their data types. This can be useful when no values are explicitly set for them during a SQL INSERT. However this can be recipe for disaster if the MySQL server is later configured for strict mode. The following are the general rules governing implicit default column values:

Zero (0) for numeric data types.
Empty string for string data types other than ENUM.
Appropriate “Zero” values for temporal data types.

Take the following imaginary table for example, the columns of which are not given any default values.

CREATE TABLE `myusers` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `username` varchar(128) NOT NULL,
  `password` varchar(64) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

If you now run the following query, MySQL will insert a blank row – setting ‘username’, ‘password’ to an empty string – along with generating some warnings. (For a NULL column, NULL is inserted if the value is missing.)

mysql> INSERT INTO myusers VALUES();
Query OK, 1 row affected, 2 warnings (0.01 sec)
 
mysql> show warnings;
+---------+------+-----------------------------------------------+
| Level   | Code | Message                                       |
+---------+------+-----------------------------------------------+
| Warning | 1364 | Field 'username' doesn't have a default value |
| Warning | 1364 | Field 'password' doesn't have a default value |
+---------+------+-----------------------------------------------+
2 rows in set (0.00 sec)
 
mysql> SELECT * FROM myusers;
+----+----------+-------------------------------------------+
| id | username | password                                  |
+----+----------+-------------------------------------------+
|  1 | Bill     | DQTLFAbw62wcJToRqL                        |
|  2 |          |                                           |
+----+----------+-------------------------------------------+
 
2 rows in set (0.02 sec)

All is fine here. The problem occurs when your hosting provider changes the MySQL server settings to strict mode, usually due to MySQL upgrade or for some security reasons. This can cause your queries which do not explicitly define column values in an insert to fail (1364 error). A sample run with the above schema with strict mode enabled is shown below.

mysql> set sql_mode='STRICT_ALL_TABLES';
Query OK, 0 rows affected (0.00 sec)
 
mysql> INSERT INTO myusers VALUES();
ERROR 1364 (HY000): Field 'username' doesn't have a default value

The problem usually occurs when the programer does not explicitly define the columns and their values in a insert query and relies on MySQL to insert implicit default values for any columns not given. This can cause surprise, when a piece of code which worked flawlessly for months suddenly starts throwing errors. The obvious solution is to give default values for all NOT NULL columns or be disciplined enough to define all columns with their values in a insert query statement.

If you have any MySQL applications deployed, you can easily check which of your database tables has an empty DEFAULT value for NOT NULL columns with the following query.

USE information_schema;
 
SELECT TABLE_NAME, COLUMN_NAME
	FROM `columns`
	WHERE IS_NULLABLE = 'NO'
		AND COLUMN_DEFAULT IS NULL
		AND TABLE_SCHEMA= 'YOUR-DATABASE-NAME'

You can also check MySQL strict mode status with the following.

SELECT @@sql_mode