Need JDBC solution for MySQL Incorrect datetime value error

Question

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

Searching around I found the following questions to be quite insightful:

• MySQL Incorrect datetime value: '0000-00-00 00:00:00'
• Data truncation: Incorrect datetime value: ''
• MySQL: Data truncation: Incorrect datetime value: '2006-10-01 02:22:44'

But I can't take any of the solutions given in these answers because of the following reasons:

• Using any other JAR as mentioned here and here (although this isn't a maven project) isn't an option. I have to use the same jar.
• I can't use anything other than '0000-00-00 00:00:00' for the field deleted_at. Apparently, there is a buttload of checking against this. You see, initially, delete_at date can't be set (I would have used a flag like is_deleted. If there is a better way please tell me).
• And changing MySQL configuration isn't an option like mentioned here. He did a nice job of explaining it. I believe this is the main issue.

As to why you are encountering the issue, it's likely that the sql_mode setting for your session includes NO_ZERO_DATES.

I (sadly) don't have much control over these things as I'm not the only one working on this project. To give you an insight about the project, this is how I'm getting the error.

They don't use any logging. Hope you get the picture.

So, given the constraints I'm bound with, is there a way to insert '0000-00-00 00:00:00' other than the solutions given above (I'm hoping maybe using something in JDBC).

MySQL Server Version: 5.7.12-0ubuntu1

Answer 1

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.