Stumble in MySQL 5.5-> 5.7

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

Event

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.

Cause

In MySQL 5.6, the datetime type after the decimal point has been changed from floor to round. → Official documentation

Coping

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.

Lesson

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.

DISTINCT Syntax error in SQL specified

Event

A syntax error occurred when executing SQL with DISTINCT.

Cause

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.

Coping

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

Lesson

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

Stumble in MySQL 5.5-> 5.7
Specify mysql socket in Hanami
Commands often used in MySQL operations
Store UUID data in MySQL in 16 bytes
Check MySQL logs in Docker environment
Implementation of DBlayer in Java (RDB, MySQL)
Edit Mysql with commands in Docker environment
How to use MySQL in Rails tutorial
Japanese setting of mysql in Docker container
Publish MySQL externally and log in on Ubuntu
Procedure to change lower_case_table_names = 1 in MySQL 8.0 of CentOS 8.3
Put CSV files containing "'" and "" "in MySQL in Ruby 2.3
Delete all records in a MySQL database table
MySQL error in Spring Unknown column'Value 1'in'field list'