MySQL Bugs: #4468: DB appears to work after loading data fills the disk, but data isn't kept.

Bug #4468	DB appears to work after loading data fills the disk, but data isn't kept.
Submitted:	8 Jul 2004 17:23	Modified:	12 Jul 2004 8:16
Reporter:	Len Vishnevsky	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	4.0.18	OS:	Other (OpenBSD 3.1)
Assigned to:	Heikki Tuuri	CPU Architecture:	Any

Description:
I was loading data into an InnoDB table.  At some point the disk filled,
and the load process died, but a lot of the data was loaded.  I freed some
space on the disk.  For the next several days, I ran queries, updated
columns, and created new InnoDB tables and populated them without
any problems.

We installed a new, bigger disk and copied the mysql data files to it.
When we rebooted, the db had reverted to its state as of the time
the disk was full.  Maybe this is a feature, and the db started storing
the data on another partition and I needed to copy it over, but there
was no mention of this (as an error message or in the error log) so I
just lost several days worth of work.

How to repeat:
I don't have the resources to repeat the procedure.

Suggested fix:
Notify the user (on screen and in the error logs) that future transactions
will not be saved permanently unless action is taken.

Hi!

A very strange bug report. The reason has to be that you copied wrong data files to the new server, or that your file system uses some kind of a snapshot mechanism and somehow reverted the files back to an older state.

Also, are you sure that you committed the transactions that you ran during that period? If not, then they would be rolled back at the mysqld shutdown, and you would lose all the modifications.

Regards,

Heikki

You suggested three possibilities:

1) copied wrong data files
2) file system uses some kind of a snapshot mechanism and somehow
    reverted the files back to an older state.
3) uncommitted transactions

So:

1) The tables as of the disk full showed up after the new disk was installed,
    so that's not it.
2) Our SA said this about OpenBSD 3.1 "No snapshots. It's a normal FS."
3) The tables I created should have been copied over, even if the rows I
    inserted (and committed, I believe) hadn't been committed.

I understand why you might not be interested in trying to replicate the
error, but, out of curiousity, have you tried?

Thanks,

Len

Hi!

Did you remember to copy also the .frm files of the tables? If not, then MySQL thinks the tables do not exist.

I cannot imagine any InnoDB bug that would revert the state of the database several days back. This is almost certainly a user error.

Regards,

Heikki