Bug #71235 MySQL allows incoming connections during InnoDB crash recovery
Submitted: 27 Dec 2013 10:12 Modified: 22 Oct 2014 15:12
Reporter: Simon Mudd (OCA) Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:5.6.14 OS:Any
Assigned to: Assigned Account CPU Architecture:Any
Tags: incoming connections, innodb, recovery

[27 Dec 2013 10:12] Simon Mudd
Description:
As per the Synopsis: MySQL allows incoming connection to the database server while the InnoDB engine is recovering from a crash.

I'm not 100% sure but it seems that it is possible to change data in innodb tables while this process is happening which seems to be rather strange as you may be "corrupting" the recovered data.

Also if you start to allow changes to InnoDB tables and recovery is ongoing then there'll be contention between the normal SQL threads and any recovery process and it is unclear to me in what state the database will end up in.

All of this leads me to think that access to InnoDB tables should NOT be allowed during the recovery process.

How to repeat:
see above.

Suggested fix:
1. If not documented already please document the expected current behaviour on recovery of the InnoDB database during startup and whether or not clients are allowed to connect to MySQL and whether or not they can access and modify InnoDB tables and how this interacts with the recovery process.

2. For GTID usage describe how the recovery process works with a server setup using GTIDs, as currently the GTID state is not stored in the database but in binlogs which are external to the database and therefore if you read the binlogs you may not actually recover the same state you recover to.

3. Given current behaviour I think it might be sensible to:
(a) prevent MySQL client access until all engines have "recovered". Given InnoDB is the default engine now this seems sane and should also ensure that the recovered state and changes made by clients do not cause incorrect data to be stored in the database.
(b) If you allow access to the MySQL server provide a InnoDB setting to prevent access to InnoDB tables: either block until the recovery has completed which may be undesirable, or given an error message saying that the table is not available as InnoDB is recovering.
(c) I used to be able to avoid this recovery (if the data was not important) by removing the ib_logfileX files. I tried this on a MEM db (5.6.14 enterprise) and the server crashed during recovery, so it seems this quick fix (of recovering without replaying iblogfile data no longer works [under some circumstances]).

So while these comments may be considered feature requests I consider (1) and (2) as bugs [insufficient documentation] and 3 an important improvement under circumstances where you really may be interested in recovering and not corrupting any data after some sort of failure.
[27 Dec 2013 17:46] Arnaud Adant
For the first point, you have this manual page :

http://dev.mysql.com/doc/refman/5.6/en/innodb-recovery.html

During the first part of the recovery involving the redo logs, it is not possible to write anything (no connection allowed). Then the server is up while the remaining recovery steps are done in the background.

About the crash without redo logs, it is expected that an unclean shutdown recovery requires the redo logs. This needs to be confirmed with the InnoDB team.
[27 Dec 2013 20:31] Sveta Smirnova
Thank you for the report.

Your first claim is wrong: no connection allowed until InnoDB finishes all startup actions, including crash recovery. I just checked and confirmed it.

Regarding to request #3 I think it is not necessary, because #1 is wrong and works as expected: no connection allowed until InnoDB finishes crash recovery.

Therefore I verify only request #2 as documentation request.
[29 Dec 2013 18:45] Simon Mudd
Arnaud and Sveta: Thanks for your response. I'll try and see if I can reproduce what I thought I saw happening and if I can I'll provide more information here.
[31 Dec 2013 12:07] Sveta Smirnova
Thank you for the feedback and log files.

I tested different recovery. In the initial description you wrote about crash recovery and I tested that recv_init_crash_recovery in storage/innobase/log/log0recv.cc does not allow parallel connections. Looks like I need to check behavior of recv_apply_hashed_log_recs as well.
[9 Jan 2014 19:10] Sveta Smirnova
I still cannot repeat described behavior.

Please send us full error log file.
[13 Jan 2014 8:12] Simon Mudd
Full log file attached as requested.
[14 Jan 2014 16:28] Arnaud Adant
Hi Simon,

Is not this bug a duplicate of this documentation bug ?

see also : Bug 18042054 - IMPROVE THE INNODB RECOVERY PROCESS DOCUMENTATION 

We could add more information on GTID there too.

MySQL allowing incoming connections during the second phase of the recovery is expected. 

Arnaud Adant
[14 Jan 2014 16:42] Sveta Smirnova
Simon,

please answer on Arnaud's question: I did not see anomaly which I expected to find in the error log file, therefore I tend to still think this is more documentation than server bug.
[16 Jan 2014 7:06] Simon Mudd
The "expected" is I think as per documentation at the moment. I'm not sure most people would really expect this behaviour. I see a case for doing things this way on a box that is not very busy or there's not much recovery to do.

However, on a _busy_ server which has to recover from a crash I wonder whether this behaviour is actually wise as the amount of locking you are likely to generate, and the extra load you cause by multiple access to the same tables/files, would I expect give the potential for problems.  Also clients that connect will be under the impression that things are working fine when that may not be the case.

Luckily MySQL crashes rarely for me, or undergoes crash recovery infrequently (a server may have crashed), so it's infrequent enough to not get noticed often.

I'm also unaware of how to visibly _see_ that this recovery process is actually ongoing as there are no status variables to indicate such a state, and for a server which may be in this state for a couple of hours or more which I've seen (on a recent MEM db server recovery) loading up mysqld when it's busy anyway seems unhelpful.

For those people who do not want to accept long amounts of downtime, and may prefer to switch over to a new master because of the server that's recovering, this lack of visibility means monitoring this situation is harder as to many intents and purposes the server "seems" up and is taking connections but its performance will be severely impacted. It would be much more visible if mysqld were simply not taking any connections.  Many people therefore might prefer to not allow connections during this period, which is what I'm arguing for (as a configurable option, even if default behaviour remains the same).

So I guess for now:
- improving documentation makes this whole process clearer.
- improving logging perhaps a little might make it clearer what's actually happening
- potentially adding some status variable to indicate this process would make the status more visible to monitoring processes

If any of that can be done I think it would be worthwhile.
[16 Jan 2014 16:46] Sveta Smirnova
Thank you for the feedback.

I will leave it as a docs bug for now. Documentation team will decide which one to mark as duplicate.
[21 Oct 2014 18:14] Daniel Price
Regarding the documentation requests for this bug:

The InnoDB recovery process documentation has been revised (5.0 to 5.7). The changes should appear within the next 24 hours. 

http://dev.mysql.com/doc/refman/5.6/en/innodb-recovery.html

Key points added:
- The time it takes to roll back an incomplete transaction can be three or four times the amount of time a transaction is active before it is interrupted, depending on server load.
- Removing redo logs to speed up the recovery process is not recommended, even if some data loss is acceptable. Removing
redo logs should only be considered an option after a clean shutdown is performed
- You cannot cancel transactions that are in the process of being rolled back.
- After redo log application, InnoDB attempts to accept connections as early as possible, to reduce downtime.

A note regarding one of the initial questions about and GTIDs and the recovery process (information provided by the Replication Development team):  

"The binary log is written transactionally together with the InnoDB transaction and it is used during InnoDB recovery to determine which prepared transactions should rollback and which should commit. So if a GTID transaction is complete in the binlog, then it will be committed in InnoDB. If a GTID transaction is partial or missing in the binlog, then it will be rolled back and the binary log will be truncated."
[22 Oct 2014 15:12] Daniel Price
As per Sveta's note above, marking this bug as a duplicate of internal #Bug 18042054.