Rollback causing MySQL table corruption

[Jan Ploski]

Hi,

I'm trying to employ zfs rollback for restoring a MySQL database to a
previous state. Unfortunately, my approach quite reliably produces
corrupted tables, while everything is fine if I simply use cp rather
than zfs rollback.

Let's say green/mysql/dbname contains the MySQL database I want to
roll back.

Here is what I do to create a snapshot using ZFS:

/etc/init.d/mysql stop
# destroy previous snapshot, if any
zfs destroy green/mysql/dbname@snap
zfs snapshot green/mysql/dbname@snap
/etc/init.d/mysql start

The ZFS-less alternative is:

/etc/init.d/mysql stop
# destroy previous snapshot, if any
rm -rf /tmp/snap/dbname
cp -a /green/mysql/dbname/* /tmp/snap/dbname
/etc/init.d/mysql start

Here is what I do to restore a snapshot using ZFS:

/etc/init.d/mysql stop
zfs rollback green/mysql/dbname@snap
/etc/init.d/mysql start

The ZFS-less alternative is:

/etc/init.d/mysql stop
cp -a /tmp/snap/dbname/* /green/mysql/dbname
/etc/init.d/mysql start

(Obviously, the advantage of snapshots over cp is savings in execution
time and disk space.)

The scenario that I am using to test this is pretty simple:
1. Run mysqlcheck on the database to check data integrity (no errors
reported).
2. Create a snapshot of the good database state (only once).
3. Make some (legal) modifications to the database via SQL.
4. Roll back to the snapshot.
5. Run mysqlcheck on the database to check data integrity.

When I use the ZFS-based solution, I can reliably see errors for the
individual changed tables after performing steps 3-4-5:

error : Size of datafile is: 19840 Should be: 19968
error : Corrupt

These errors are NOT present in step 1 before or after step 2
immediately after taking the snapshot. I was speculating that maybe
the snapshot is taken too early while MySQL has not completely shut
down, but no, even a delay of 30 seconds after /etc/init.d/mysql stop
and checking that no mysql process is running doesn't help.

So I guess it boils down to the question: am I doing something stupid
= does zfs snapshot/rollback have different semantics than cp in the
above scenario, or could there be a zfs(-fuse?) bug lurking in here
somewhere?

Best regards,
Jan Ploski

[sgheeren]

Both

cp -a will do different things when viewed from the linux POSIX file
buffering side. In a way, it will probably tell the linux kernel: forget
the previous copy, the files now constitue _this_ new data.

You might see if it helps to

su -
echo 3 > /proc/sys/vm/drop_caches

after the rollback.

This is my single shot a.t.m. Of course ZFS's own caches may be
involved, but that is much less likely (as everything should be 1:1 with
upstream). But if the above yields the same result, you can try if
restarting zfs-fuse makes any difference (be sure to combine both
approaches then so you know your not looking at cached data in whatever
sublayer).

I'd have to think about (a) whether all of this is a bug or simply a
fuse artefact (b) whether it can be easily be refuted if this apporach
helped.

I propose you create another bug report if you feel like it :)
> Best regards,
> Jan Ploski
>
>

[Jan Ploski]

Your cache flush workaround works wonderfully, but it leaves me
scared ;-). Bug report here: http://zfs-fuse.net/issues/65

[sgheeren]

On 06/20/2010 06:39 PM, Jan Ploski wrote:
> Your cache flush workaround works wonderfully, but it leaves me
> scared ;-). Bug report here: http://zfs-fuse.net/issues/65
>

Yeah it is the case of the issue where the intuitive reason is spot-on,
which makes it all the more serious. Might I say, your testing is
proving extremely valuable for zfs-fuse, as you are evidently working
different patterns.

Cheers,
Seth

[Rodrigo E. De León Plicet]

On Sun, Jun 20, 2010 at 4:09 PM, sgheeren <sghe...@hotmail.com> wrote:
> On 06/20/2010 06:39 PM, Jan Ploski wrote:
>> Your cache flush workaround works wonderfully, but it leaves me
>> scared ;-). Bug report here: http://zfs-fuse.net/issues/65
>>
> Yeah it is the case of the issue where the intuitive reason is spot-on,
> which makes it all the more serious. Might I say, your testing is
> proving extremely valuable for zfs-fuse, as you are evidently working
> different patterns.
>
> Cheers,
> Seth

Yeah, I was bitten by that during Oracle testing, too:

http://article.gmane.org/gmane.os.solaris.opensolaris.zfs/26157

Same workaround:

http://article.gmane.org/gmane.os.solaris.opensolaris.zfs/26162