MySQL Database Index Key Corruption

We recently experienced a rare occurrence of a MySQL database index key corruption on a UNIX based server. Although the cause was not identified, the symptoms and cure were obvious and eventually simple.

The symptom was the failure of new records to write to the MySQL database. All other interactions were operating normally, although the MySQL database had been responding with spuriously slow response times, perhaps due to traffic overloads. Requests to the database were simply rejected and not saved in the database.

No data was lost because we wrote comprehensive flat file back logging systems. All requests were saved as SQL dump strings, so al records were resurrected through a simple copy/paste of each SQL string to the database. We chose to insert each line manually to debug along the way, rather than import the entire SQL log.

During debugging, the code base proved to be fully intact and without bugs. Both the development and testing versions of the site operated perfectly. Only the production MySQL database server was having issues. When executing an insert request to the MySQL database manually, the database rejected the insert with an error of duplication on Key 1, which was simply the primary index.

The cure, oddly enough, was to edit the primary key, without making any changes, and saving the key. Simply saving the primary key again with the original settings put the MySQL database back into operation and the site resumed normal and expected operation.

The cause of the corruption of the primary index key is currently unknown, but appears to be related to problems on the server in general, possibly related to the overloading of traffic. It is a shared server, do the owner has most likely burdened the server in attempts to profit more money and avoid buying more servers to handle the overall volume.

If your server response time fluctuates from speedy to strangely slow, or if your connections appear to vaporize, take a look at the server load and get th server admin and perhaps MySQL database admin involved.