Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I am having issues with the columns_priv table in the mysql system database. I cannot add new users currently.

I have tried repairing it using mysqlcheck --auto-repair --all-databases --password but I get the following output:

mysql.columns_priv
Error    : Incorrect file format 'columns_priv'
error    : Corrupt

Is there any other way to repair this table, or how do I go about replacing it with a blank table? What would I lose by doing that?

Thank you

Edit (Additional Info) mysqld is currently using 100% cpu constantly.

Looking at show processlist, I get:

mysql> show processlist;
+-----+------------------+-----------+-------+---------+------+-------------------+------------------------------------------------------------------------------------------------------+
| Id  | User             | Host      | db    | Command | Time | State             | Info                                                                                                 |
+-----+------------------+-----------+-------+---------+------+-------------------+------------------------------------------------------------------------------------------------------+
|   5 | debian-sys-maint | localhost | mysql | Query   | 1589 | Opening tables    | ALTER TABLE tables_priv
   MODIFY Column_priv set('Select','Insert','Update','References')
     COLL |                                                                        |
| 752 | root             | localhost | NULL  | Query   |    0 | NULL              | show processlist                                                                                     |
+-----+------------------+-----------+-------+---------+------+-------------------+------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
share|improve this question
    
Restore from backup? Restart from scratch with all system tables using mysql_install_db and re-add all users again. And make sure you find the cause for this corruption - this should not happen. –  gertvdijk Dec 12 '12 at 10:42
    
The cause was probably due to the SAN going offline / read only. The server became unresponsive and the datacentre did a reboot after the SAN recovered. I'll attempt to restore a backup and hope for the best. –  psynnott Dec 12 '12 at 11:01
    
Dropping the table and restoring it from a backup worked ok :) –  psynnott Dec 12 '12 at 11:22

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.