Bug #65906 Need indication of whether the server has had to perform recovery on startup

• Submitted: 16 Jul 2012 6:01
• Modified: 16 Jul 2012 11:19
• Reporter: Simon Mudd (OCA)
• Status: Verified
• Category: MySQL Server: Options
• Severity: S4 (Feature request)
• OS: Any
• CPU Architecture: Any
• Tags: windmill

[16 Jul 2012 6:01] Simon Mudd

Description:
Currently I'm unaware of any way to query mysqld to ask it if it has had to perform "recovery" on startup. This probably refers mainly to versions of mysqld using InnoDB and other "transactional" type engines but the issue is the same.  We can detect if mysqld has been restarted by it having a low 'uptime' but it's not possible to detect if the startup has involved recovery.

For people monitoring database systems it's very important to be able to know if the server has been restarted and has had to perform recovery as this indicates an "error condition" rather than a clean startup which is normal which [probably] requires no attention.

The information is available by manually scanning the log_error file, but having to do this manually, and externally, from the mysqld process is annoying. it should be possible to query this information directly from the mysqld server itself.

How to repeat:
Run kill -9 on a mysqld process. Watch it recover (mysqld_safe restarts mysqld again). You'll see entries if using the InnoDB engine indicating the recovery process in the log_error file.

Suggested fix:
Provide a global variable (counter which is by default 0?) to indicate that recovery took place. Possibly (given this may refer to different engines) provide a per engine specific variable to indicate the same issue. Note: for MyISAM recovery of "corrupt tables" you could probably do the same thing and increment this counter for the number of corrupt tables that have been auto-repaired (if using myisam_recover_options).

e.g.

mysqld_performed_recovery = 1  (only 0 or 1, 1 if any of the engine specific settings is > 0)
innodb_performed_recovery = 1 (likely to be 0 or 1?)
myisam_performed_recovery = 10 # as this would be on a per file basis ...

[16 Jul 2012 6:22] MySQL Verification Team

Just an idea, perhaps implementing an option called --shutdown-file (the opposite of --init-file) could be workable solution.  On normal shutdown server would execute some SQL to record normal shutdown.  If the shutdown SQL was not executed it means server crashed.

[16 Jul 2012 7:50] Simon Mudd

Basically use the way MyISAM detects "not-cleanly shutdown" tables?
Yes, I see that as being a valid thing to do. Mark the file with an "open" state, and at the end of the shutdown sequence mark it as "shutdown cleanly".  Then check on startup if the state was shutdown cleanly and indicate this.  That would certainly help catch an "unexpected crash" of mysqld, but nevertheless having engine specific information would also be useful.

So generally I'm not too worried about the exact mechanism but just a means of detecting this situation,
and avoid horrible parsing of the log-error file for stuff that looks like a possible crash.