/SQL/Error while setting value 'ONLY_FULL_GROUP_BY,ALLOW_INVALID_DATES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' to 'sql_mode'

Error while setting value 'ONLY_FULL_GROUP_BY,ALLOW_INVALID_DATES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' to 'sql_mode'

The SQL mode NO_AUTO_CREATE_USER was removed in MySQL 8.0, and it's no longer recognized.

https://dev.mysql.com/doc/refman/8.0/en/mysql-nutshell.html#mysql-nutshell-deprecations says:

The following features related to account management are removed:

Using GRANT to create users. Instead, use CREATE USER. Following this practice makes the NO_AUTO_CREATE_USER SQL mode immaterial for GRANT statements, so it too is removed.
Change your sql_mode to "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION". I tested this on my sandbox instance of 8.0.11 and it worked.

Either spelling of sql-mode or sql_mode are both fine.

Using quotes or omitting quotes are both fine.

Error while setting value 'ONLY_FULL_GROUP_BY,ALLOW_INVALID_DATES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' to 'sql_mode'

The SQL mode NO_AUTO_CREATE_USER was removed in MySQL 8.0, and it's no longer recognized.

https://dev.mysql.com/doc/refman/8.0/en/mysql-nutshell.html#mysql-nutshell-deprecations says:

The following features related to account management are removed:

Using GRANT to create users. Instead, use CREATE USER. Following this practice makes the NO_AUTO_CREATE_USER SQL mode immaterial for GRANT statements, so it too is removed.
Change your sql_mode to "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION". I tested this on my sandbox instance of 8.0.11 and it worked.

Either spelling of sql-mode or sql_mode are both fine.

Using quotes or omitting quotes are both fine.

Similar codes

3 votes236 views1 answer
RENAME table pl sql
SQL
3 votes288 views1 answer
Postgresql today - 1 year
SQL
1 vote181 views1 answer
Sql insert for each distinct value
SQL
0 vote193 views1 answer
Search mysql database for column
SQL
33 votes193 views6 answers
Sql delete
SQL
0 vote216 views1 answer
SQL Server Splitting a string column into multiple rows, while repeating ID column
SQL
0 vote272 views1 answer
Mengatasi error mysqldump: [ERROR] unknown option '--no-beep'
SQL
16 votes198 views3 answers
Jwt secret key generator laravel
SQL
0 vote197 views1 answer
Oracle search in date columns
SQL
1 vote270 views2 answers
Job for mysql.service failed because the control process exited with error code. see "systemctl status mysql.service" and "journalctl -xe" for details.
SQL