Friday, July 21, 2006

MySQL Error Handling

Sometimes, if we work with MySQL database, when there are an error (like cannot dumping database or table corrupt), MySQL would echoing the error code, so the Database Administrator can recognize the error and recover the database.

But, what should we do? What kind of error is that?

Ok. Just go to the MySQL bin directory and then type perror ERROR_CODE. For example, if your MySQL echoing error 127 then you must type perror 127. MySQL would give you an explanation what kind of error of it.

If there are a table corrupt, you can use myisamchk or mysqlcheck syntax to analyze, check, repair or optimize the database.

What is the different between myisamchk and mysqlcheck ?

If you use myisamchk, your MySQL database must be shutting down.
If you use mysqlcheck, you can repair the database while your MySQL database is running up.

Example syntax :

This is to analyze the its database with root user :

[root@ppbj root]# mysqlcheck -a its -u root -p
Enter password:

And then the terminal would echoing the status of MySQL tables like below :

its.adminweb OK
its.t_activity OK
its.t_activity_image OK
its.t_branch OK
its.t_category OK
its.t_contact OK
its.t_division OK
its.t_download OK
its.t_event OK
its.t_group_access OK
its.t_menu OK
its.t_menu_level OK
its.t_news OK
its.t_news_review Table is already up to date
its.t_polling OK
its.t_polling_log OK
its.t_polling_option OK
its.t_procedure Table is already up to date
its.t_profile OK
its.t_profile_group OK
its.t_retailnews OK
its.t_retailnews_image Table is already up to date
its.trel_profile_group OK
its.tsys_lkp OK
its.tsys_sql Table is already up to date
its.tsys_string OK
its.tsys_user OK
its.tsys_var OK

Another mysqlcheck option is :
-a, to analyze the database
-c, to check the database
-r, to repair the database
-o, to optimize the database

No comments: