I was contacted this week by a previous client regarding a failure of processing data. This was a Contact, Financial and Media Management system I developed for a non-for-profit organization a LAMJ stack, and I’ve had to do nothing since deployment in the past 3 years, no bug fixes, no feature enhancements. The only thing lacking is additional management reporting, and data is extracted for this option now.
It runs under commodity Hardware, Linux and MySQL and it’s only unscheduled downtime was an power failure before UPS power was installed. However this all changed last week. Processing of regular scheduled encrypted data simply failed unexpectedly.
A summary of the environment.
- Data is inserted with AES_ENCRYPT(data,key);
- Data is retrieved with AES_DECRYPT(data,key);
- Data is never updated.
- New data is always added, and historical data always remains.
- The application has no end user access to modify data.
- The application has no function anywhere to modify the data, so no rouge happening could have occured.
- An AUTO_INCREMENT column and TIMESTAMP columns provide a level of auditing for data forensics.
- Backup copies of data exist up to 3 years for reference.
- The seed key has not changed.
The problem
Selecting the first 10 rows saved in the table (By AUTO_INCREMENT Key and confirmed by dates), 8 of 10 are now corrupt. Select the last 10 rows inserted, zero are corrupt. Across 20,000 records 75% are now corrupt.
A lot of analysis has been performed to identify and track the data that was recorded, a certain amount of data forensics, and it was confirmed information was successfully processed last month for example. As this performs financial transactions, there is a lot more auditing available and being reviewed however it is simply a mystery that I can’t solve.
- What options remain? is this a Hardware problem, Disk or even Memory.
- What other data maybe corrupt?
- How can more investigation occur to track the cause of the problem.
mysql> select version(); +------------------+ | version() | +------------------+ | 4.1.10a-standard | +------------------+ 1 row in set (0.00 sec)
