When I upgraded my Java app that used MySQL 5.5 to MySQL 5.7, I encountered some problems, so I will leave the cause and remedy.
Datetime Field Overflow
When I INSERTed a record containing the maximum value 9999/12/31 23:59:59 into a table with a column of type datetime, Datetime Field Overflow occurred and failed.
In MySQL 5.6, the datetime type after the decimal point has been changed from floor to round.
→ Official documentation
Added the process to reset the milliseconds (.000) of the part where the milliseconds of the object instantiated by java.util.Calendar were acquired.
before
Calendar cal = Calendar.getInstance();
cal.set(9999, 11, 31, 23, 59, 59);
cal.getTime();
↓
after
Calendar cal = Calendar.getInstance();
cal.set(9999, 11, 31, 23, 59, 59);
cal.clear(Calendar.MILLISECOND);
cal.getTime();
As an object, since the millisecond at the timing of Calendar.getInstance () is acquired, it changes every execution timing. At this time, if the millisecond is less than .499, it is safe, but it is .500 or more. Then it will be out.
Previously it was truncated, so it was safe for milliseconds at any time.
Not limited to this case, it is important that the application does not depend on the DB specifications.
By the way, this change seems to follow the SQL standard. (From the official document)
No warning or error is given when such rounding occurs. This behavior follows the SQL standard, and is not affected by the server sql_mode setting.
A syntax error occurred when executing SQL with DISTINCT.
Starting with MySQL 5.7.5, ʻONLY_FULL_GROUP_BY` was specified as the default sql_mode. → Official documentation
sql_mode is a so-called validation-like function that checks the validity of syntax, and there are several types. Of these, ʻONLY_FULL_GROUP_BY` is "When aggregating by GROUP BY, is the column specified in the SELECT clause or ORDER BY clause included in the GROUP BY clause?" And "The column in the ORDER BY clause is included in the column list of DISTINCT." It checks "whether", and this time it violated the latter.
Fixed the SQL to be issued.
before
SELECT
DISTINCT HOGE,
FUGA
FROM
T_USER
ORDER BY
USER_ID DESC
↓
after
SELECT
DISTINCT HOGE,
FUGA,
USER_ID
FROM
T_USER
ORDER BY
USER_ID DESC
Make sure that the SQL you are issuing does not violate the current sql_mode setting, or if you have to, turn off that sql_mode.
that's all
Recommended Posts