Geek Answers Handbook

JDBC solution for MySQL is required. Incorrect date and time value error.

When I try to insert a record into a specific table, I get an error message as follows:

Data truncation: invalid date and time: '0000-00-00 00:00:00' for column 'deleted_at' in row 1

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

But I cannot make any decision given in these answers due to the following reasons:

  • Using any other JAR as mentioned here and here (although this is not a maven project) is not an option. I have to use the same jar.
  • I can use nothing but 0000-00-00 00:00:00 'for the field deleted_at. There seems to be another attempt to verify this. You see, initially the date delete_at cannot be set (I would use a flag like is_deleted. If there is a better way, tell me).
  • And MySQL configuration change is not an option, as mentioned here . He explained it pretty well. I think this is the main problem.

Due to the fact that you are facing a problem, it is likely that the sql_mode parameter for your session includes NO_ZERO_DATES.

I ( ) , , . , .

enter image description here

. , .

, , , '0000-00-00 00:00:00', ( , , , - JDBC).

MySQL: 5.7.12-0ubuntu1

+4
1

.

, MySQL Zero Dates, sql_mode ​​ NO_ZERO_DATES.

, , , sql_mode NO_ZERO_DATES.

sql_mode, :

SET GLOBAL sql_mode = '';

, , . , . .

SET SESSION sql_mode = '';

, .

try (Statement statementSet = connection.createStatement()) {
    statementSet.execute("SET SESSION sql_mode = ''");
}

. datetime '0000-00-00 00:00:00'. sql_mode . , .

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)

, . .

+5

More articles:


All Articles