I've got a table in a MySQL database which currently stores stats from a website I developed....it has a primary key called id which is also set as unique and an index.
However, I noticed today that the stats stopped working...
The number of table rows was at 205,933....
When I tried to manually add more data to the table it stated that id 205,934 already existed and it couldn't add it because the column was a unique index. However id 205,934 doesn't exist.
After fiddling around removing a few rows and trying to add more, I now get the following error...
Can't open file: 'stats.MYD'. (errno: 145)
I assume that the table is corrupt....Does anyone know what may have caused the problem?
I'm running MySQL 3.23.32 running on Red Hat Linux
try using PHPMyAdmin to "copy" the table, give it another name, and give it a try....
just a shot in the dark...
a primary key called id which is also set as unique and an index.
He, he - I think you've overdone it with the definition of column "id". You don't need to index a primary key nor define it as unique. This is because a primary key *must*, by definition, be unique - so the RDBMS will enforce its uniqueness automatically. Similarly, the primary key is indexed automatically as well. So this may be causing your problems.
On the other hand, are you absolutely, positively sure that there is no record with id of 205,934 exists?
I did SELECT * FROM stats WHERE id="205934" and it returned nothing.....
The way I set it up, didn't seem to effect it for the first 205,933 rows :)
What is the column definition for your id field?