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:
Category:MySQL Server Severity:S4 (Feature request)
Version:5.6.27 OS:Any
Assigned to: CPU Architecture:Any

[15 Jun 2016 5:56] Simon Mudd
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.
[15 Jun 2016 6:09] Simon Mudd
Note: I deliberately did not make this an InnoDB option as other storage engines would be affected too.
[15 Jun 2016 7:37] Daniël van Eeden
This is basically what offline_mode does.

But offline_mode on a master would probably cause the I/O Threads on slaves to fail?
[15 Jun 2016 7:39] Daniël van Eeden
And for option c there is 'SET GLOBAL read_only=1'
[15 Jun 2016 8:25] Simon Mudd
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.