Bug 286711 - Better default config for avoiding and suggestion for better handling InnoDB corruptions
Summary: Better default config for avoiding and suggestion for better handling InnoDB ...
Status: RESOLVED UPSTREAM
Alias: None
Product: Akonadi
Classification: Frameworks and Libraries
Component: server (show other bugs)
Version: 4.7
Platform: Ubuntu Linux
: NOR wishlist
Target Milestone: ---
Assignee: kdepim bugs
URL:
Keywords:
: 302499 (view as bug list)
Depends on:
Blocks:
 
Reported: 2011-11-15 17:15 UTC by Stephan Diestelhorst
Modified: 2023-04-25 16:33 UTC (History)
6 users (show)

See Also:
Latest Commit:
Version Fixed In:


Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Stephan Diestelhorst 2011-11-15 17:15:22 UTC
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/)
Comment 1 Christophe Marin 2012-02-03 17:04:19 UTC
(In reply to comment #0)
> Version:           4.7 (using KDE 4.7.2) 
> OS:                Linux
> 

> 
> 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
> 

Did you try these settings with a laptop ? Does it affect the power consumption ?
Comment 2 Stephan Diestelhorst 2012-02-03 17:10:35 UTC
On 3 February 2012 18:04, Christophe Giboudeaux <cgiboudeaux@gmx.com> wrote:
>> 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
>>
>
> Did you try these settings with a laptop ? Does it affect the power consumption
> ?

Not really. With the current craze about virtuoso-t and everything, it
is hard to get reliable power measurements, anyway.

Stephan
Comment 3 Stephan Diestelhorst 2012-02-03 17:11:32 UTC
On 3 February 2012 18:10, Stephan Diestelhorst
<stephan.diestelhorst@gmail.com> wrote:
> On 3 February 2012 18:04, Christophe Giboudeaux <cgiboudeaux@gmx.com> wrote:
>>> 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
>>>
>>
>> Did you try these settings with a laptop ? Does it affect the power consumption
>> ?
>
> Not really. With the current craze about virtuoso-t and everything, it
> is hard to get reliable power measurements, anyway.

What I meant was: Yes, I currently run these on a laptop. But no, I
cannot comment on power consumption because the whole setup goes to
high CPU utilisations eratically.
Comment 4 Daniel Vrátil 2013-03-02 21:10:16 UTC
*** Bug 286567 has been marked as a duplicate of this bug. ***
Comment 5 Daniel Vrátil 2013-03-02 21:11:30 UTC
*** Bug 302499 has been marked as a duplicate of this bug. ***
Comment 6 Justin Zobel 2021-03-09 05:46:35 UTC
Thank you for the bug report.

As this report hasn't seen any changes in 5 years or more, we ask if you can please confirm that the issue still persists.

If this bug is no longer persisting or relevant please change the status to resolved.
Comment 7 Carl Schwan 2023-04-25 16:33:20 UTC
Both

sync_binlog = 1
innodb_flush_log_at_trx_commit = 1

are now set by default on mysql