MySQL Bugs: #81852: Provide option to not allow client access during InnoDB recovery

Bug #81852	Provide option to not allow client access during InnoDB recovery
Submitted:	15 Jun 2016 5:56	Modified:	15 Jun 2016 8:25
Reporter:	Simon Mudd (OCA)	Email Updates:
Status:	Open	Impact on me:	None
Category:	MySQL Server	Severity:	S4 (Feature request)
Version:	5.6.27	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
During innodb recovery client connections are allowed.

In some cases such as when the clients are trying to insert data heavily, especially if the amount of InnoDB recovery is large this really does not help.

How to repeat:
Seen in 5.6.27. I believe 5.7 has changed a bit in its logging but I'm not sure if it's changed enough.

e.g. I saw:

InnoDB: 1655 transaction(s) which must be rolled back or cleaned up ...
and the load on the server ramped up to > 50 as the "big inserters" were desperately trying to insert new data. All that really did was to slow down the recovery process.

Eventually the server hit the max_connections so I couldn't even login to see what was going on.

e.g. See: http://merlin.wl0.org/201606/15/1438161379.png

After 45 minutes I killed the server (mysqld) and restarted it blocking access from the clients (tcp filter) after which reocovery took about 20 minutes, and I let them back in again.

Suggested fix:
I'd like to see 3 options:

(a) current behaviour - allow the client to connect and try to do inserts etc while recovery is happening (default)

(b) do not allow client connections (except for SUPER so we can monitor what is going on) to ensure that the recovery takes place on its own.
* Once recovery completes allow normal access.
* The advantage of this mode is that no changes will happen and probably the client will keep trying to connect. However, it won't be able to see what the problem is.

(c) allow client access but make DML changes fail with some error indicating the server is in recovery mode.
* The advantage of this mode is that the issue is visible to the client so it can take action if appropriate.

Note: I deliberately did not make this an InnoDB option as other storage engines would be affected too.

This is basically what offline_mode does.
https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_offline_mode

But offline_mode on a master would probably cause the I/O Threads on slaves to fail?

And for option c there is 'SET GLOBAL read_only=1'

Yes, the options are there, but they're not automatic during recovery.

So to rephrase:

While mysqld is recovering (on startup) optionally put the server into:

(i) offline mode, or
(ii) read_only mode

during this process.