Due to a database upgrade, importing a SQL file from a lower version into a higher version database throws an error:
The cause is the database SQL mode:
mysql> show variables like 'sql_mode'; # Check the current mode
| 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 |
Temporarily change the database mode:
mysql> set session
-> sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
mysql> source /home/s.sql # Re-import the data
Permanent Fix:
You can directly edit the my.cnf file and add the following line under [mysqld]:
sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
Reference: https://blog.csdn.net/qq_27229113/article/details/80176489