Strict sql mode and errors

After MySQL version upgrade, there is a possibility that application will start getting error/exception for INSERT/UPDATE operation due missing default data/values as follows,

ERROR:  Field ‘column_name’ doesn’t have a default value

Also above error will trigger for the wrong data type or out of range value for the column with INSERT/UPDATE sql operation.

Reason for this error is new SQL_MODE default values (MySQL 5.7 ) that included STRICT_TRANS_TABLES value in it and sql_mode value in default my.cnf created after installation[ Under /etc/my.cnf or /usr/my.cnf ]

sql_mode MySQL version Default
<=5.6.5
>= 5.6.6 NO_ENGINE_SUBSTITUTION
5.7 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

Lets take scenario where application was running with MySQL 5.5 /5.6 and then upgraded to MySQL version to 5.7.
After upgrade your application will start getting error/exception for INSERT/UPDATE operation  missing default values as follows.

ERROR:  Field ‘column_name’ doesn’t have a default value

If you do further troubleshooting for table which you are having issue, you will see that in the table definition column using NOT NULL constraint but there is no default value for it.
So, in such case if your application sending INSERT with no value for column with not null constraint then mysql will throw an error for that INSERT operation, because of  STRICT_TRANS_TABLES in sql_mode.

Example:

mysql> show variables like 'sql_mode';
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value |
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
| sql_mode | 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 |
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> create table sqlmode_test (id int(11) primary key auto_increment, uname varchar(30) , language varchar(12) not null);
Query OK, 0 rows affected (0.04 sec)

mysql> set session sql_mode='STRICT_TRANS_TABLES';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show variables like 'sql_mode';
+---------------+---------------------+
| Variable_name | Value |
+---------------+---------------------+
| sql_mode | STRICT_TRANS_TABLES |
+---------------+---------------------+
1 row in set (0.00 sec)

mysql> insert into sqlmode_test(uname) values ('harsh');
ERROR 1364 (HY000): Field 'language' doesn't have a default value
mysql> set session sql_mode='';
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'sql_mode';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_mode | |
+---------------+-------+
1 row in set (0.00 sec)

mysql> insert into sqlmode_test(uname) values ('harsh');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+-----------------------------------------------+
| Level | Code | Message |
+---------+------+-----------------------------------------------+
| Warning | 1364 | Field 'language' doesn't have a default value |
+---------+------+-----------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from sqlmode_test;
+----+--------+----------+
| id | uname | language |
+----+--------+----------+
| 1 | harsh | |
+----+--------+----------+
1 rows in set (0.00 sec)

 

Solution:

Option 1:
Correct table column definition [ To avoid Error and warnings ]

Option 2:  [SQL with throw warning and not an error]
Remove strict sql mode
-Dynamically
SET GLOBAL sql_mode=”;
-Persist in my.cnf
sql_mode=
OR
sql_mode= ”

All set !!