[Bug 286711] New: Better default config for avoiding and suggestion for better handling InnoDB corruptions

Tue Nov 15 17:15:22 GMT 2011

https://bugs.kde.org/show_bug.cgi?id=286711

           Summary: Better default config for avoiding and suggestion for
                    better handling InnoDB corruptions
           Product: Akonadi
           Version: 4.7
          Platform: Ubuntu Packages
        OS/Version: Linux
            Status: UNCONFIRMED
          Severity: wishlist
          Priority: NOR
         Component: IMAP resource
        AssignedTo: ervin at kde.org
        ReportedBy: stephan.diestelhorst at gmail.com
                CC: vkrause at kde.org, kdepim-bugs at kde.org

Version:           4.7 (using KDE 4.7.2) 
OS:                Linux

My laptop has somewhat unstable suspend / resume behaviour and also sometimes
just freezes, which requires me to do a hard power cycle of the system, ending
up with an unclean file system (LUKS -> LVM -> Reiser stack).

With Akonadi's MySQL settings, InnoDB then ends up with corrupted data and
refuses to start the SQL server.

111109 15:08:29 [Note] Plugin 'FEDERATED' is disabled.
111109 15:08:29  InnoDB: Initializing buffer pool, size = 80.0M
111109 15:08:29  InnoDB: Completed initialization of buffer pool
InnoDB: Log scan progressed past the checkpoint lsn 0 699961455
111109 15:08:29  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
InnoDB: Doing recovery: scanned up to log sequence number 0 699963569
111109 15:08:30  InnoDB: Starting an apply batch of log records to the
database...
InnoDB: Progress in percents: 26 27 28 29 30 31 32 33 34 35 36 37 38
39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61
62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84
85 86 87 88 89 90 91 92 93 94 95 96 97 98 99
InnoDB: Apply batch completed
111109 15:08:30  InnoDB: Started; log sequence number 0 699963569
111109 15:08:30 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.1.54-1ubuntu4'  socket:
'/home/syon/.local/share/akonadi/socket-d-allen/mysql.socket'  port: 0
 (Ubuntu)
InnoDB: Database page corruption on disk or a failed
InnoDB: file read of page 4479.
InnoDB: You may have to recover from a backup.
111109 15:09:33  InnoDB: Page dump in ascii and hex (16384 bytes):
...
111109 15:09:33  InnoDB: Page checksum 1923164640,
prior-to-4.0.14-form checksum 1959713716
InnoDB: stored checksum 2520600866, prior-to-4.0.14-form stored
checksum 1959713716
InnoDB: Page lsn 0 90554488, low 4 bytes of lsn at page end 90554488
InnoDB: Page number (if stored to page already) 4479,
InnoDB: space id (if created with >= MySQL-4.1.1 and stored already) 11
InnoDB: Page may be an index page where index id is 0 38
InnoDB: (index "PRIMARY" of table "akonadi"."parttable")
InnoDB: Database page corruption on disk or a failed
InnoDB: file read of page 4479.
InnoDB: You may have to recover from a backup.
InnoDB: It is also possible that your operating
InnoDB: system has corrupted its own file cache
InnoDB: and rebooting your computer removes the
InnoDB: error.
InnoDB: If the corrupt page is an index page
InnoDB: you can also try to fix the corruption
InnoDB: by dumping, dropping, and reimporting
InnoDB: the corrupt table. You can use CHECK
InnoDB: TABLE to scan your table for corruption.
InnoDB: See also http://dev.mysql.com/doc/refman/5.1/en/forcing-recovery.html
InnoDB: about forcing recovery.
InnoDB: Ending processing because of a corrupt database page.
The only fix then is to manually delete the ~/.local/share/akonadi/db_data/
folder and let AKonadi rebuild the database.

According to http://www.chriscalender.com/?tag=innodb-power-failure this can be
remedied by setting the following settings in mysql.conf:

sync_binlog = 1
innodb_flush_log_at_trx_commit = 1

This is different from the current default settings.

Circumstantial evidence suggests that this actually helps, since changing these
values, I have not yet had the same corruptions again, although I had to
shutdown the machine uncleanly several times and InnoDB had to fix up things.

My suggestion is thus to make these settings the default and in addition offer
the user some guidance, for example by suggesting how to remove the DB files
and telling him / her of the consequences (your email is NOT lost).

Reproducible: Sometimes

Steps to Reproduce:
Synchronise a large IMAP resource, such as a GMail account and while doing
this, power cycle the machine.

Actual Results:  
InnoDB data is corrupted on the next start of the system, MySQL does not start
and KMail exits / does nothing.

Expected Results:  
Various:
* InnoDB does not crash / can fixup the data base -> seems to work with the
setings above
* if InnoDB is finding a broken DB, someone (KMail? Akonadi? FAQ entry?) tells
me how to fix the mss with as little hassle as possible (AFAICS, this is rm
~/.local/share/akonadi/db_data/)

-- 
Configure bugmail: https://bugs.kde.org/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are on the CC list for the bug.