Using the employee example database as an example, the The test environment uses a single statement.

SELECT * FROM employees GROUP BY gender;

It works fine in the test environment, but it has problems online, reporting errors as follows.

1
2
3
4
5
6

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'mse.msc_k8s_cluster.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
        at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
        at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
        at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
        at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)

Reason

MySQL has the following constraints in ONLY_FULL_GROUP_BY mode.

When you execute a SELECT statement with GROUP BY and ORDER BY, you need to make sure that the columns you SELECT are in GROUP BY and ORDER BY.

As an analogy, if you have data as follows.

emp_no gender
1 M
2 M

Then SELECT emp_no,gender FROM employees GROUP BY gender; will only return one record, then the emp_no field can’t show both 1 and 2, in MySQL’s view, this is not the standard way to use, so there will be the beginning of the error report.

Solution

Add dependencies between columns

We can make emp_no correspond to gender one by one, for example, create a joint index between emp_no and gender. Then there will be no problem with the emp_no field.

But obviously there is still a problem here, because this has an impact on the data and the transformation cost is too high.

ANY_VALUE

We can use ANY_VALUE() to tell MySQL that the value of emp_no in the example, any value would be fine.

This solution is better than the first one, but there will still be a transformation cost to transform the query statement. For example, it would have to be changed to SELECT ANY_VALUE(emp_no),gender FROM employees GROUP BY gender;

Close ONLY_FULL_GROUP_BY

This restriction is brought by the ONLY_FULL_GROUP_BY schema and can be turned off by turning off the ONLY_ FULL_GROUP_BY mode.

Turn off ONLY_FULL_GROUP_BY globally

Find the MySQL configuration file (my.cnf) to modify and save. First find the configuration file path with the following command.

1
2
3

$ mysql --help | grep cnf
                      order of preference, my.cnf, $MYSQL_TCP_PORT,
/etc/my.cnf /etc/mysql/my.cnf /usr/local/etc/my.cnf ~/.my.cnf

Find it, edit and save it, restart MySQL and it will take effect.

1
2
3

[mysqld]
-sql_mode=ONLY_FULL_GROUP_BY,NO_ENGINE_SUBSTITUTION
+sql_mode=NO_ENGINE_SUBSTITUTION

Turn off ONLY_FULL_GROUP_BY by sql_mode variable

To see the current sql_mode.

1
2
3
4
5
6
7

mysql> SELECT @@sql_mode;
+-------------------------------------------+
| @@sql_mode                                |
+-------------------------------------------+
| ONLY_FULL_GROUP_BY,NO_ENGINE_SUBSTITUTION |
+-------------------------------------------+
1 row in set (0.02 sec)

Close it with the following command: SET SESSION sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY,',''));

Turn off ONLY_FULL_GROUP_BY in Spring Boot/HikariCP

Since connection-init-sql doesn’t support multiple MySQL statements, and the original SET SESSION time_zone='+08:00', it’s not easy to set this.

After looking up the MySQL documentation for setting variables, you can set multiple variables in one statement, for example, we can write it like this

1
2
3
4
5
6

spring:
datasource:
    hikari:
      connection-init-sql: >
        SET SESSION time_zone='+08:00',
        SESSION sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''))