Description:
The presence of the NO_AUTO_CREATE_USER value for the sql_mode option in a .ini file prevents an 8.0 server from starting, at least on Windows. This may be as expected for a removed option value, but I did not encounter any warnings about this while following the Windows upgrade process as documented.
The value is set explicitly in a my.ini file I had from 5.7 which I believe was generated with this value, and is not set in the default my.ini file in 8.0. I found the issue by reusing the modified 5.7 my.ini file (with the default sql_mode setting) with the 8.0 binaries, but was able to reproduce it by adding the value to an 8.0 .ini file.
No error is returned to the console or written in the MySQL server's error log if an attempt is made to start the service:
PS C:\Program Files\MySQL\MySQL Server 8.0\bin> net start mysql80slave
The mysql80slave service is starting..
The mysql80slave service could not be started.
The service did not report an error.
An error is returned to the console if an attempt is made to start the server using mysqld:
PS C:\Program Files\MySQL\MySQL Server 8.0\bin> .\mysqld --defaults-file="C:\ProgramData\MySQL\MySQL Server 8.0\my.ini"
2018-05-22T10:25:59.481548Z 0 [ERROR] [MY-011071] [Server] C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqld.exe: Error while setting value 'STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' to 'sql_mode'
2018-05-22T10:25:59.490780Z 0 [ERROR] [MY-010119] [Server] Aborting
2018-05-22T10:25:59.499787Z 0 [Note] [MY-010120] [Server] Binlog end
Again nothing is written to the MySQL server's error log.
The server had been checked using the Shell 8.0.11 util.checkForServerUpgrade utility and this was not reported there as a blocking issue. In fact the utility reported specifically there was no issue -
9) Usage of obsolete sql_mode flags
No issues found
for a 5.7 server with
# Set the SQL mode to strict
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
In https://dev.mysql.com/doc/refman/8.0/en/upgrading-from-previous-series.html, the value is noted as removed and stated as a potential issue for replication, but not as a blocking issue. It is not listed at https://dev.mysql.com/doc/refman/8.0/en/added-deprecated-removed.html which is the page of options requiring configuration changes. It is mentioned in https://dev.mysql.com/doc/refman/8.0/en/mysql-nutshell.html#mysql-nutshell-removals but only as a side note to another item.
The same issue was reported at https://github.com/laradock/laradock/issues/1492 and http://www.dreamwu.com/post-28.html .
How to repeat:
Add NO_AUTO_CREATE_USER to sql_mode option in .ini file and attempt to start the 8.0 server.
Remove NO_AUTO_CREATE_USER or comment out sql_mode line and attempt again.
Suggested fix:
Either handle the presence of the option value, or do any of the following that are possible:
- Report a relevant error on Windows service start
- Identify NO_AUTO_CREATE_USER as the problem on mysqld start
- Write to the server's error log
- Report the presence of the option value in the .ini file as an issue in Shell util.checkForServerUpgrade
- Note as an incompatible change in documentation pages listed above
