Many developers face this error when trying to configure SQL mode in MySQL 8. The key issue stems from deprecated modes in newer MySQL versions. Let me share what I've learned from my production environment:

[mysqld]
# This will FAIL in MySQL 8:
sql_mode = STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

MySQL 8.0 removed several SQL modes that were available in earlier versions. The problematic one here is NO_AUTO_CREATE_USER which was deprecated in MySQL 5.7 and completely removed in 8.0.

Here's the corrected configuration that works in MySQL 8.0+:

[mysqld]
# Correct SQL mode for MySQL 8:
sql_mode = STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

After making changes:

sudo systemctl restart mysql
mysql -u root -p -e "SHOW VARIABLES LIKE 'sql_mode';"

Expected output should show your new SQL mode without errors.

If you need temporary changes:

SET GLOBAL sql_mode = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

• Using deprecated modes like NO_AUTO_CREATE_USER
• Incorrect file permissions on my.cnf
• Not restarting MySQL service after changes

For production servers, I recommend:

sql_mode = ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,
NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

When working with MySQL 8 (specifically version 8.0.20+), many developers encounter issues when trying to set persistent SQL modes. The error typically appears as:

2020-06-03T09:49:24.567568Z 0 [ERROR] [MY-000077] [Server]
/usr/sbin/mysqld: Error while setting value
'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'
to 'sql_mode'

The root cause stems from two primary factors:

• MySQL 8 has deprecated the NO_AUTO_CREATE_USER mode
• The configuration syntax has subtle but critical requirements

Here's the proper way to configure persistent SQL modes in MySQL 8:

[mysqld]
sql_mode=STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

Key differences from the problematic configuration:

• Use sql_mode (underscore) not sql-mode (hyphen)
• Remove deprecated NO_AUTO_CREATE_USER flag
• No quotes around the value
• No spaces after commas

After making changes, restart MySQL and verify:

sudo systemctl restart mysql
mysql -u root -p -e "SELECT @@GLOBAL.sql_mode;"

Expected output for the above configuration:

@@GLOBAL.sql_mode
STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

If you need temporary changes (without config file edits):

SET GLOBAL sql_mode = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

For session-specific changes:

SET SESSION sql_mode = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

• Don't mix underscore and hyphen notation (sql_mode vs sql-mode)
• Avoid including deprecated modes like NO_AUTO_CREATE_USER
• Never use single quotes around values in my.cnf
• Ensure no trailing whitespace in the configuration line

These SQL modes were removed in MySQL 8:

• NO_AUTO_CREATE_USER (completely removed)
• NO_KEY_OPTIONS (deprecated in 5.7, removed in 8.0)
• NO_TABLE_OPTIONS (deprecated in 5.7, removed in 8.0)