MySQL Bugs: #90554: Undesired change for Windows users in 8.0

Bug #90554	Undesired change for Windows users in 8.0
Submitted:	21 Apr 2018 12:49	Modified:	2 May 2018 9:02
Reporter:	Peter Laursen (Basic Quality Contributor)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Options	Severity:	S4 (Feature request)
Version:	8	OS:	Windows
Assigned to:		CPU Architecture:	Any

Description:
I compared 5.7 and 8.0 documentation as regards the server option --skip-grant-tables.

In 8.0 docs there is a passage (not found in 5.7 docs) reading "If the server is started with the --skip-grant-tables option to disable authentication checks, the server enables --skip-networking automatically to prevent remote connections." 

Now, in my experience most Windows users don't enable neither connectivity to the server through "named pipe" nor "shared memory". I have been working with hundreds of MySQL/Windows user over the past 15 years (as supporter for our applications) and I rarely saw any of them used. People use TCP even to localhost on Windows (unlike on Unix/Linux). Also the "Windows Installer" does not require at least one of "named pipe" nor "shared memory" enabled (and as far as I remember they are not selected as default). Further many popular clients can only connect with TCP.

This makes it impossible for such Windows users to reset a forgotten/lost password for root user.

And a "sahred pipe" may be shared over a network, so on Windows this really does not restrict connections to "localhost" as intended. 

How to repeat:
Read docs at https://dev.mysql.com/doc/refman/8.0/en/server-options.html#option_mysqld_skip-grant-table...

Suggested fix:
Revert this change on Windows. 

And if the quoted passage does not apply to Windows, then documentation should state that.

Fixed typo in synopsis.

I have 3 more considerations actually:

1) In Windows TCP to localhost is default - unlike Unix, where it is SOCKET. You will need to specify otherwise in the client (what is not always possible) if connection shall use pipe or memory. So disabling local TCP connections is a *much more* intrusive thing to do on Windows than on Unix/Linux.

2) Combining --no-defaults and --skip-grant-tables now *bite each others in the tail* (at least as far as I can understand, --no-defaults means that only TCP will be available as the options file is not being read. And -skip-grant-tables disallows TCP. This is "Catch 22", really!)

3) On Windows (at least) I think a much better option would be to use a --bind-address option (like "--bind-address=127.0.0.1") if the intention is to allow only connections from local host. Actually I remember this was the setting in the default MySQL configuration on Debian Linux many years ago (and maybe still is). It was said to "provide better compability" (ie. allow more clients to connect) than --skip-networking.

One more observation: when using --skip-grant-tables to start the server on Windows, the server won't even start unless explicitly specifying a pipe ("--socket .. ") or "--shared-memory" on the command line.

I did this:

Downloaded MySQL 8.0.11 .zip archive, unzipped, successfully executed "mysqld --initialize-insecure" and tried to start the server with

>mysqld --skip-grant-tables --port=3320
-- result: the server starts and quits again immediately.

Error log reads:
2018-04-21T17:58:02.389814Z 0 [System] [MY-010931] [Server] C:\Users\jazcy\Downloads\mysql-8.0.11-winx64\mysql-8.0.11-winx64\bin\mysqld.exe: ready for connections. Version: '8.0.11'  socket: ''  port: 0  MySQL Community Server - GPL.
2018-04-21T17:58:02.391174Z 0 [ERROR] [MY-010131] [Server] TCP/IP, --shared-memory, or --named-pipe should be configured on NT OS
2018-04-21T17:58:02.391871Z 0 [ERROR] [MY-010119] [Server] Aborting
2018-04-21T17:58:02.521775Z 0 [Warning] [MY-011311] [Server] Plugin mysqlx reported: 'All I/O interfaces are disabled, X Protocol won't be accessible'
2018-04-21T17:58:03.714223Z 0 [System] [MY-010910] [Server] C:\Users\jazcy\Downloads\mysql-8.0.11-winx64\mysql-8.0.11-winx64\bin\mysqld.exe: Shutdown complete (mysqld 8.0.11)  MySQL Community Server - GPL.

The culprit here obviously is that neither TCP, named pipe nor shared memory is available for communication with clients and this causes the server to shutdown itself (what makes some sense, as it is useless). 

If I specify a non-TCP connect option on the command line like 
mysqld --port=3306 --skip-grant-tables --shared-memory
.. the server starts and I can again connect with the CLI ("mysql -uroot -p --port=3320").

At least if --skip-networking should continue to be implicit with--skip-grant-tables, and if no pipe is specified, then it should be considered that --shared-memory also should be on Windows.  

I will not be surprised if MySQL developers haven't considered consequences for Windows users at all. Actually I will be surprised if they did at all. It will not be the first time.

Thank you for the bug report.

C:\tmp\mysql-8.0.11-winx64>bin\mysqld --initialize-insecure --basedir=c:\tmp\mysql-8.0.11-winx64 --datadir=c:\tmp\mysql-8.0.11-winx64\data

C:\tmp\mysql-8.0.11-winx64>bin\mysqld --skip-grant-tables --port=3320 --standalone --console
2018-04-21T19:33:38.030627Z 0 [System] [MY-010116] [Server] C:\tmp\mysql-8.0.11-winx64\bin\mysqld.exe (mysqld 8.0.11) starting as process 10200
2018-04-21T19:33:39.621692Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2018-04-21T19:33:39.647306Z 0 [System] [MY-010931] [Server] C:\tmp\mysql-8.0.11-winx64\bin\mysqld.exe: ready for connections. Version: '8.0.11'  socket: ''  port: 0  MySQL Community Server - GPL.
2018-04-21T19:33:39.655271Z 0 [ERROR] [MY-010131] [Server] TCP/IP, --shared-memory, or --named-pipe should be configured on NT OS
2018-04-21T19:33:39.656943Z 0 [ERROR] [MY-010119] [Server] Aborting
2018-04-21T19:33:39.794589Z 0 [Warning] [MY-011311] [Server] Plugin mysqlx reported: 'All I/O interfaces are disabled, X Protocol won't be accessible'
2018-04-21T19:33:41.390977Z 0 [System] [MY-010910] [Server] C:\tmp\mysql-8.0.11-winx64\bin\mysqld.exe: Shutdown complete (mysqld 8.0.11)  MySQL Community Server - GPL.

Let me summarize the solutions, I propose:

1) revert this change on Windows
2) make --bind-address to localhost rather than --skipnetworking implict (at least on Windows)
3) also make --shared-memory implicit with --skip-grant-tables on Windows

.. but I actually found another
4) don't make --skip-networking implicit with --skip-grant-tables if a "--port="/"-P" parameter is specified on the commandline.  This could/should apply to all OS'es. 

This morning I think I'll prefer solution 4) - because it is very logical IMO, that if the server was started with an explicit "--port="/"-P" then this port should be usable for connections.

And I'll also withdraw my statement that password cannot be reset if sesrver is started with --skip-grant-tables. Instructions on the updated docs page here https://dev.mysql.com/doc/refman/8.0/en/resetting-permissions.html will work.

If you don't wnat to change anything here docs should be updated:

"If the server is started with the --skip-grant-tables option to disable authentication checks, the server enables --skip-networking automatically to prevent remote connections." 

>>

"If the server is started with the --skip-grant-tables option to disable authentication checks, the server enables --skip-networking automatically to prevent remote connections. Note that on Windows this means, that if server is started with --skip-grants-table, then also --shared-memory and/or --enable-named-pipe must be specified. If not, the server has no connectivity options available and will close down by itself immediately after startup".

I wnat to change 3) also make --shared-memory implicit with --skip-grant-tables on Windows

It should probably be "named pipe" instead. Most Windows clients can use "named pipe", only few "shared memory".

Posted by developer:
 
With --skip-grant-tables:
2018-05-03T09:00:45.049091Z 0 [ERROR] [MY-010131] [Server] TCP/IP, --shared-memory, or --named-pipe should be configured on NT OS
2018-05-03T09:00:45.049583Z 0 [ERROR] [MY-010119] [Server] Aborting

With --shared-memory the server starts.

It would be nice if skip-grant-tables works the same on Linux and Windows, but the feature isn't broken.

This is technically a documented behavior so moving to a feature request.

Is there a way to get around this?
Can someone please make a simple step-by-step so that i can resett my mysql root passord?