MySQL Bugs: #17570: Using encrypted swap/disk on Mac OS X 10.4 may cause innodb table corruption

Bug #17570	Using encrypted swap/disk on Mac OS X 10.4 may cause innodb table corruption
Submitted:	20 Feb 2006 10:15	Modified:	28 Mar 2006 9:22
Reporter:	Not Supplied	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	Ver 14.7 Distrib 4.1.15, for apple-darwi	OS:	MacOS (OS X 10.4.4 and 10.4.5)
Assigned to:		CPU Architecture:	Any

Description:
We've been working to isolate a condition where a relatively low-volume DB has been getting its innodb tables corrupted on a regular basis. This began occurring on an otherwise stable setup. The same disks have been moved to a different system and the behavior continued (thus eliminating hardware issues, we believe). The "different system" was the system that previously hosted the DB without issues. We've also had the condition occur on one of the development systems.

We've had an absence of the occurrence, which was previously an every-other-day issue, for over a week, now, so we believe we know the problem. Unfortunately, we don't have the resources to setup a system and drive a load to test whether we can cause it to recur. I assume that y'all do have such resources.

There were two changes made that seem to have solved the problem:

- we turned off the System Preferences -> Security -> Use secure virtual memory option that's available in Mac OS X 10.4

- for "theft of system" security, we normally keep all sensitive information on "standard" OS X AES-encrypted disk images (NOT sparse images ... we use fixed-allocation images) ... when we turned off the secure VM option, we also moved the DB's data directory to a non-ecrypted OS X disk image

One or both of these seems to cause innodb table indexes to become corrupted. My bet would be that it's the encrypted VM, not the encrypted disk image.

We've seen the corruption on the following systems:

- A "loafing" dual-processor (1 GHz) G4 with 1 GB of memory running OS X 10.4.4 client

- An ol' G3 (500 MHz) with only 256 MB of memory running 10.4.4 client and now 10.4.5 (10.4.5 was just installed and it's been stable, but the system had been stabalized on 10.4.4).

- A development G4 laptop (1.67 GHz) with 1 GB of memory running OS X 10.4.4 client.

In fact, it's the ol' G3 that's rather stressed running the DB (not enough memory, swaps constantly) but that now seems to be stable. The config is a slight expansion (i.e., in resource allocation) of the small example my.cnf and the dual-processor's my.cnf was a slight expansion of the medium example my.cnf file.

The entire set of DB tables is < 100 MB and no single table has over 50K rows and the transaction rate is only thousands of queries per day, on a busy day.

How to repeat:
I suspect just setup a DB that uses innoDB tables and is running under the following conditions:

- running on Mac OS X 10.4.4 client

- has the System Preferences -> Security -> Use secure virtual memory option checked (i.e., ON)

- the MySQL installation (i.e., all of /usr/local), including the DB's data directory, is located on an encrypted disk image:

* created using Disk Utilities
* size: 6 GB
* encryption: AES-128
* format: read/write disk image
* disk-image file resides on root disk and is mounted "normally" on /Volumes
* /usr/local is a symbolic link to the directory named usr-local located on the disk image

Could not reproduce on:
Intel iMac, 10.4.5, with encrypted disk
PPC iBook, 10.4.5, with encrypted disk and swap

Ran sql-bench against both, for days.

Sorry to hear (well, in a way) that the problem could not be replicated.

Since changing our setup to move /usr/local back to the normal/unencrypted root disk volume and disabling encrypted swap we've not had any problems.

After running for a week or so on the iMac, I moved the DB back to the G4 MDD system (where the problems were originally experienced) but kept things on the normal/unencrypted root disk volume and kept encrypted swap disabled. It's been running there ever since, completely problem-free.

In addition, after another week or so, I setup the iMac as a slave/replication of the G4 MDD's DB and it's also been problem-free.

There are only 2 other factors I can think of:

- things were cured by a security update ... but that's not too likely as none coincided with the changes we made and the problems going away

- Retrospect backups being performed on the system (including the DB files while the DB was running [yes, of dubious value]), in addition to the encrypted swap and encrypted disks, is the combination that causes the problems ... however, we've always run Retrospect for backups in this way (i.e., even when we we only using an encrypted disk but no encrypted swap)

I'm still quite convinced there's a problem somewhere as the issue was portable across 2 very different systems with a complete rebuild of the environment surrounding the DB (i.e., only the data files were moved, the disk was not cloned).

The only other thing I could suggest is adding the Retrospect backup into the mix.