Hi Tobia,
> I remember only something like this:
>
InnoDB: The log sequence numbers xxxx and
> xxxx in ibdata files do not match the log sequence
> number xxxx in the ib_logfiles!
> But seems to be quite usual for after a bad
> shutdown.
Yes, this is the normal message when InnoDB has not been shut down properly.
In this case, InnoDB crash recovery will replay the InnoDB redo log entries
since the latest checkpoint, basically rolling forward all data files to
the latest LSN that is found in the redo logs.
It could be helpful to read my blog post on crash recovery:
http://mysqlserverteam.com/innodb-crash-recovery-improvements-in-mysql-5-7/
> The worrying thing for me is that there were
> nothing as "error" in the logs and the server
> booted up correctly, users could understand that
> something was lost only by they own because thay
> did not find the previus days data! As I said, I
> find normal to lose last transactions after a
> power fault, but not some days of records!
While I was developing and debugging the changes for the 5.7 feature that
my above blog post is describing at a high level, it sometimes occurred that
the modified crash recovery would skip the redo log replay. In the scope of my
code changes, because I was modifying the log checkpoint logic as well, the
only bad effect was that the system would lose some transactions.
Losing transactions is of course unacceptable; if commit returns and you are
running with innodb_flush_log_at_trx_commit=1, the commit must never be lost,
no matter if the system crashes after a commit. Eventually I fixed this,
and the crash tests, such as innodb.innodb_update_time no longer failed
on my development branch.
Generally, skipping the redo log replay (or deleting the redo logs
before startup) can cause much worse trouble. The contents of various data
files would no longer correspond to one given point of time
(or LSN aka log sequence number). One page could be at LSN 1000, the other
at LSN 1500, and so on. For example, if the B-tree root page was written out
at LSN 1000 while other pages in the B-tree were written out at LSN 1500, you
would have some unreachable nodes in the B-tree. If you had it the other way
around, you could have the root page pointing to uninitialized child pages.
This would usually lead to some assertion failure or crash, or corrupted data
being read.
It is only safe to discard the redo logs after completing a full checkpoint,
writing out all dirty pages from the buffer pool at the same LSN. This is what
the --innodb-log-file-size resizing feature in MySQL 5.6 does at server startup.
I did also a fair amount of crash testing on that; innodb.innodb-log-file-size
was written by me, and there was also some stress testing from our QA team.
> I really don't know exactly how innodb works. What
> happends if the engine could not flush data from
> ib_logfile0 to frm data files and the ib_logfile0
> file is filled?
The *.frm files are not managed by InnoDB, and they are not covered by any
redo log. As a result, DDL operations are not completely crash-safe, and
the *.frm files can get out of sync with the InnoDB-internal data dictionary.
That is being worked on.
With best regards,
Marko Mäkelä
D.Sc.(Tech)
Senior Principal Software Developer
InnoDB group at MySQL