Beware of your error logs. A story of indexes and alter table.
A few days ago I was contacted by a user that was getting table full errors. I explained that by default MySQL uses 4 byte pointers for row data and if the data file grows larger than 4 bytes MySQL will run out of space for pointers. I also explained about max_rows and avg_row_length and how they can be used with alter or create table to give himself larger row pointers. He took this information and happily altered his table expecting all of his data to be there and MySQL to begin adding new rows. What he got was a new table with only a few hundred rows of data. He pinged me in a panick asking what he did wrong. Having never seen such a thing myself I logged into the box. I checked mysqld.err and found that for the previous 10 days or so mysqld had been writing corrupt index warnings to the log. Fortunately he had a slave that we could recover the data from.
After some research the best explanation I can offer as to what went wrong is that when MySQL went to alter the table it did a read on the bad index and thought there was only 100 or so rows in the table. After copying those rows out it overwrote the good table with the temp table and return success thinking it had done a fine job of increasing the row pointer size. Which it had. The new table had 5 byte pointers.. just no data.
As a warning to everyone out there. Keep an eye (or script) on your err file because it can warn you about problems long before they bite you. It’s also a good practice to check your tables before altering them.
October 26th, 2005 at 9:39 am
And the backups. Don’t forget them. You wouldn’t believe how many times I’ve seen people begging and pleading for help, when they’re latest backup is six months old.
If your data is worth putting in a database, it’s worth backing up.