While trying to insert a record into a certain table, I'm getting an error as follows:
Data truncation: Incorrect datetime value: '0000-00-00 00:00:00' for column 'deleted_at' at row 1
deleted_at
is_deleted
As to why you are encountering the issue, it's likely that the sql_mode setting for your session includes.NO_ZERO_DATES
After three days of toil, I finally worked out a solution.
As mentioned in this answer, MySQL won't allow Zero Dates to be inserted if the variable sql_mode
is set with NO_ZERO_DATES
.
As to why you are encountering the issue, it's likely that the sql_mode setting for your session includes
NO_ZERO_DATES
.
Now, I could have just cleared the variable sql_mode
by doing the following in the command line:
SET GLOBAL sql_mode = '';
But as mentioned before, I'm not allowed to do this. So, I needed a session based solution. Which was given in this document itself.
SET SESSION sql_mode = '';
So, I added the following lines after opening the connection.
try (Statement statementSet = connection.createStatement()) {
statementSet.execute("SET SESSION sql_mode = ''");
}
And it worked. I was able to insert and update datetime fields with '0000-00-00 00:00:00'. This cleared the sql_mode
variable only for the current session. The global settings are as it is.
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)
I had to do this because I had no other option. This isn't recommended.