I have some code that collects top 10 scores in 3 difficulty categories, for a game, from a table, and stores them in all-times highscores table. It worked normally until today, when apparently it broke the .MYI file of the highscores table. Could my PHP code have broken it somehow? Here's the code:
(table and field names changed for better readability)
// For each difficulty
for ($d=1; $d<4; $d++) {
// Copy top 10 to all-times table
$res= db_query("INSERT INTO all_times_table (name, difficulty, level, score, tstamp, ip)
SELECT name, difficulty, level, score, tstamp, ip
FROM highscores_table
WHERE difficulty={$d}
ORDER BY score DESC LIMIT 10");
// Fetch 10th score in the updated all-times table
$res= db_query("SELECT score FROM all_times_table WHERE difficulty= {$d} ORDER BY score DESC LIMIT 9, 1");
$score= mysql_result($res, 0);
// Remove all rows with score lesser than 10th
if ($score)
db_query("DELETE FROM all_times_table WHERE difficulty={$d} AND score < {$score}");
}
// Purge original high scores table
db_query("TRUNCATE TABLE highscores_table");
The all times table has no indices as it stores only 10 scores in 3 categories, 30 rows in total, I find sequential read faster than indexed. The highscores table has a primary ID key which is not copied here, and has normal index on 'score' field.
I realize I didn't lock the tables during the procedure, but I seriously doubt there has been a collision, since we have up to 300 score submissions per week, so that's the number of rows this code has to pass through in the highscores table.
This code worked fine for several weeks, having it run once a week. Today it broke the table. I cannot access the table, not even from phpMyAdmin. It reports it "in use", if I try to view its structure, the error is:
#1016 - Can't open file: 'highscores_table.MYI'. (errno: 13)
I don't have access to the perror utility so I can't have more info, I'll have the server admins look into it, but I was wondering is there a possibility that my code broke the table, being poorly written or something?
Thanks!