Two methods to fix mysql tables

Mysql table repair is frequently used. We will teach you two ways to fix mysql tables. If you have encountered this problem before, take a look.

Solution 1:

 
 

  
  
shell> mysqlcheck -r -c -o -uuser -ppasswd db_name  
 
 

The second method to fix the mysql table is to use the following script, which is useful on the Internet. Put the script in your web root directory and then run the script from the web page.

 
 

  
  
Cat repair. php
  
  
Need to modify
  
  
$ Dbuser = "root"; ---> modify
  
  
$ Dbpw = ""; ---> modify
  
  
$ Dbname = "db_name"; --> modify
  
  
# $ Table = "search_profile ";
  
  
Mysql_connect ($ dbhost, $ dbuser, $ dbpw );
  
  
Mysql_select_db ($ dbname );
  
  
If (! Get_resource_var ("register_globals ")){
  
  
Foreach ($ HTTP_GET_VARS as $ key => $ val ){
  
  
$ Key = $ val;
  
  
}
  
  
}
  
  
Function checktable ($ table, $ loops = 0 ){
  
  
Global $ db, $ nohtml;
  
  
$ Result = mysql_query ("check table $ table ");
  
  
If (! $ Nohtml ){
  
  
Echo "Checking table $ table ";
  
  
Echo "TableOperationTypeText ";
  
  
} Else {
  
  
Echo "\ n >>>>>>>>>>>>>> Checking Table $ table \ n ";
  
  
Echo "---------------------------------
  
  
\ N ";
  
  
}
  
  
$ Error = 0;
  
  
While ($ r = mysql_fetch_row ($ result )){
  
  
If ($ r [2] = 'error '){
  
  
If ($ r [3] = "The handler for the table doesn't support check/repair "){
  
  
$ R [2] = 'status ';
  
  
$ R [3] = 'this table does not support check/repair/optimize ';
  
  
Unset ($ bgcolor );
  
  
$ Nooptimize = 1;
  
  
} Else {
  
  
$ Error = 1;
  
  
$ Bgcolor = 'red ';
  
  
Unset ($ nooptimize );
  
  
}
  
  
} Else {
  
  
Unset ($ bgcolor );
  
  
Unset ($ nooptimize );
  
  
}
  
  
If (! $ Nohtml ){
  
  
Echo "$ r [0] $ r [1] $ r [2] $ r [3]";
  
  
} Else {
  
  
Echo "$ r [0] | $ r [1] | $ r [2] | $ r [3]
  
  
\ N ";
  
  
}
  
  
}
  
  
If ($ error ){
  
  
If (! $ Nohtml ){
  
  
Echo "Repairing table $ table ";
  
  
} Else {
  
  
Echo ">>>>>>>>>>>>> Repairing Table $ table
  
  
\ N ";
  
  
}
  
  
$ Result2 = mysql_query ("repair table $ table ");
  
  
If ($ result2 [3]! = 'OK ')
  
  
$ Bgcolor = 'red ';
  
  
Else
  
  
Unset ($ bgcolor );
  
  
If (! $ Nohtml ){
  
  
Echo "$ result2 [0] $ result2 [1] $ result2 [2] $ result2 [3]";
  
  
} Else {
  
  
Echo "$ result2 [0] | $ result [1] | $ result2 [2] | $ result2 [3]
  
  
\ N ";
  
  
}
  
  
}
  
  
If ($ result2 [3] = 'OK' |! $ Error )&&! $ Nooptimize ){
  
  
If (! $ Nohtml ){
  
  
Echo "Optimizing table $ table ";
  
  
} Else {
  
  
Echo ">>>>>>>>>>>>> Optimizing Table $ table
  
  
\ N ";
  
  
}
  
  
$ Result3 = mysql_query ("optimize table $ table ");
  
  
$ Error = 0;
  
  
While ($ r3 = mysql_fetch_row ($ result3 )){
  
  
If ($ r3 [2] = 'error '){
  
  
$ Error = 1;
  
  
$ Bgcolor = 'red ';
  
  
} Else {
  
  
Unset ($ bgcolor );
  
  
}
  
  
If (! $ Nohtml ){
  
  
Echo "$ r3 [0] $ r3 [1] $ r3 [2] $ r3 [3]";
  
  
} Else {
  
  
Echo "$ r3 [0] | $ r3 [1] | $ r3 [2] | $ r3 [3]
  
  
\ N ";
  
  
}
  
  
}
  
  
}
  
  
If ($ error & $ loops ){
  
  
Checktable ($ table, ($ loops-1 ));
  
  
}
  
  
}
  
  
If ($ check ){
  
  
$ Tables = mysql_query ("show tables ");
  
  
If (! $ Nohtml ){
  
  
Echo "";
  
  
}
  
  
If ($ iterations ){
  
  
$ Iterations --;
  
  
}
  
  
While ($ table = mysql_fetch_row ($ tables )){
  
  
If (substr ($ table [0],-8 )! = 'Session '){
  
  
$ Answer = checktable ($ table [0], $ iterations );
  
  
If (! $ Nohtml ){
  
  
Echo "& nbsp ;";
  
  
}
  
  
Flush ();
  
  
}
  
  
}
  
  
If (! $ Nohtml ){
  
  
Echo "";
  
  
}
  
  
} Else {
  
  
Echo "Database Table Repair Utility for Discuz! (Require MySQL 3.23)
  
  
".
  
  
"This script will attempt to repair extends upted databases. While it can repair most common errors,
  
  
".
  
  
"It can not repair everything. As a side benefit it will also optimize your tables.
  
  
".
  
  
"This script was copyrighted by Jelsoft and modified by Crossday Studio to make it apply for Discuz !.
  
  
".
  
  
"Usage:
  
  
".
  
  
"Iterations = x (x is the number of times you wish for the script to attempt to repair a broken table)
  
  
".
  
  
"Nohtml = 1 (Output text only no html. Useful for cronjobs and you wish to receive text only)
  
  
".
  
  
"Check = 1 (without this you will get this screen and no checking)
  
  
".
  
  
"Example Usage:
  
  
".
  
  
"Repair. php? Html = 1 & check = 1 (most common usage, will check a forum and return html results)
  
  
"." Repair. php? Check = 1 & iterations = 5 (try to repair damaged tables 5 times )".
  
  
"";
  
  
}
  
  
?>
 
 

MySQL table management commands

Create a temporary table in php mysql

Quick table creation in mysql

Mysql table index information

In-depth study on MySQL's data deletion from multiple tables