Problem with auth_log

[supertux]

I had a problem with the indexes of the auth_log table index files. The index file reported errors when doing a mysqldump to backup the db.

mysqldump: Can't get CREATE TABLE for table 'auth_log' (Can't open file: 'auth_log.MYD'. (errno: 145))

To fix the problem I had to replace the db files auth_log.MYD, auth_log.MYI and auth_log.frm with files from another cacti db (I have several instances running). Of course the table then lost all the log data.

It seems that the ID (smallint(5)) field was too small as the system is regularly accessed by my customers (needed 18 months to reach the limit).

[bulek]

Depending if you consider working hours and holidays or not it gives 10 - 40 logins per minute during whole 18 months period (I mean logins... not web hits or visits). This means your company has a lot of customers interested in cacti stats.

- bulek

[raX]

That is quite a lot of logins... even so I've upped all ID fields in 0.8 to mediumint(8). Also, the user login table in 0.8 does not rely on an auto-incriment ID column. The primary key is based on the username and timestamp (I believe).

-Ian

[supertux]

Thanks for your replies. As I said, I have absolutely no idea what went wrong with the table..

[raX]

The error you pasted here looked to be MySQL-specifc. Did you try running myisamchk on it or anything?

-Ian

[supertux]

Yes.. But with no success. Here is some output.

>:/var/lib/mysql/cacti/backup # myisamchk auth_log.MYI
Checking MyISAM file: auth_log.MYI
Data records: 756 Deleted blocks: 0
myisamchk: warning: Table is marked as crashed
- check file-size
myisamchk: warning: Size of datafile is: 274432 Should be: 274428
- check key delete-chain
- check record delete-chain
- check index reference
- check data record references index: 1
myisamchk: error: Found 757 keys of 756
MyISAM-table 'auth_log.MYI' is corrupted
Fix it using switch "-r" or "-o"

>:/var/lib/mysql/cacti/backup # myisamchk -o auth_log.MYI
- recovering (with keycache) MyISAM-table 'auth_log.MYI'
Data records: 756
>:/var/lib/mysql/cacti/backup # myisamchk auth_log.MYI
Checking MyISAM file: auth_log.MYI
Data records: 756 Deleted blocks: 0
- check file-size
- check key delete-chain
- check record delete-chain
- check index reference
- check data record references index: 1
- check data record references index: 2
>:/var/lib/mysql/cacti/backup # myisamchk auth_log.MYD
myisamchk: error: 'auth_log.MYD' doesn't have a correct index definition. You need to recreate it before you can do a repair

I also tried all other index recovery descriptions I could find in the mysql manual, but no success.

PS. Looking at the number of records I suppose my first guess at the problem was not a bright idea