1. ERROR 1067 (42000): Invalid default value for ‘date_time’.

This is caused by SQL Mode specifying NO_ZERO_DATE, which does not allow Null as the default date. To solve this problem, simply change the SQL Mode

SET @@session.sql_mode = REPLACE((SELECT @@session.sql_mode), ',NO_ZERO_DATE'.' ')
Copy the code

However, this will only change the SQL Mode of this connection. If you need to change the global SQL Mode, you only need to change @@session to @@global

SET @@global.sql_mode = REPLACE((SELECT @@global.sql_mode), ',NO_ZERO_DATE'.' ')
Copy the code

However, these are only temporary changes and will be restored after the database restarts. To make permanent changes, you need to modify the my.cnf configuration file. Each platform has a different profile address. Use the following command to find the default profile address:

mysqld --verbose --help | grep -A 1 "Default options"
Copy the code

Then modify the my.cnf file to add the following configuration, change it to the SQL Mode you want, and restart MySQL service.

[mysqld] sql_mode = "ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTIT UTION"Copy the code

If you do not know the default SQL Mode, you can query it and change it.

SELECT @@global.sql_mode
Copy the code

2. ERROR java.lang.IllegalStateException: please check your GROUP_CONCAT_MAX_LENGTH in mysql

Group_concat_max_len default length is 1024, easy to exceed, set as above. For a 32-bit system, the maximum value is 4294967295. For 64-bit systems, the maximum value is 18446744073709551615. Current Connection Settings

SET @@session.group_concat_max_len = 4294967295;
Copy the code

Global level Settings

SET @@global.group_concat_max_len = 4294967295;
Copy the code

Permanently modify, same as modify my.cnf file

[mysqld]
group_concat_max_len=4294967295
Copy the code

3. Summary

MySQL environment variables are classified into Session level, Global level, and configuration file level. You need to change the environment variables as required.