phpBB2Refugees.com Logo
Not affiliated with or endorsed by the phpBB Group

Register •  Login 

Continue the legacy...

Welcome to all phpBB2 Refugees!Wave Smilie

This site is intended to continue support for the legacy 2.x line of the phpBB2 bulletin board package. If you are a fan of phpBB2, please, by all means register, post, and help us out by offering your suggestions. We are primarily a community and support network. Our secondary goal is to provide a phpBB2 MOD Author and Styles area.

Fixing MySQL Index Errors


 
Search this topic... | Search Knowledge Base (KB)... | Search Box
Register or Login to Post    Index » Knowledge Base (KB)  Previous TopicPrint TopicNext Topic
Author Message
espicom
Board Member



Joined: 24 Nov 2008

Posts: 55
Location: Woodstock, IL


flag
PostPosted: Sun Dec 28, 2008 8:19 pm 
Post subject: Fixing MySQL Index Errors

A guide to repairing MySQL tables

A 1016, 1030 or 1034 error from MySQL means that part of your data, the "index file", is screwed up, and needs to be fixed. Sometimes, these "main" error numbers are omitted from the error report, and you will just get a "code" that gives more detail on why the particular operation failed. The code is important to determining whether you can fix this yourself, or if you will have to get your host involved.

If you receive one of these errors with a code of "2", "9", "12", "13", "23", "24", "28", or "122", contact your host immediately - these errors cannot be dealt with by the following instructions, and are indicative of problems (possibly hardware) on the MySQL server.

If you have a tool provided by your host, such as phpmyadmin, to manipulate your database, you can use it to fix the table giving you the error by executing the following query:

Code:
REPAIR TABLE phpbb_users;


Of course, "phpbb_users" would need to be replaced with the name of the table reported in the error message that brought you to this page.

If you get a message about repair not being supported for the table type, you are not using the "standard" MyISAM type of table, and you will have to use a different command:

Code:
OPTIMIZE TABLE phpbb_users;


MySQL will turn this into a command that will do the repair on these other table types. Since it does not do a repair on MyISAM tables, though, you should try the REPAIR TABLE command first. Also, see "Note 2" below if the problem persists after the repair.

If you are still reading this, it is probably because your host has not provided a database tool. You've got two choices at this point: Ask the host to fix it, or upload a script to do the fixing for you. If you do not have FTP access to your site, though (such as on a "free forum host"), the second option is not available. You must ask your host to fix the problem.

Still here? OK, you've got FTP access, so you can upload programs to your website and run them.

The following program will allow you to repair all MySQL tables associated with your PHPBB system. It is probably not a good idea to leave this file on your board without putting it into a password-protected area. While it isn't "dangerous", it does lock tables during the repair operation, so someone could cause time-out errors by repeatedly calling it.

Code:
<?php
// Check and repair all tables in a PHPBB structure
// Author: espicom aka Jeff Brenton
// verify that we're supposed to run
// change 'PX42m3' to something unique for your site, so it will only
// execute if you call it as 'http://mysite.com/fixtables.php?PX42m3=1'
if (!isset($_GET['PX42m3']))
{
   header("Location: http://127.0.0.1/");
   die();
}

define('IN_PHPBB', true);
$phpbb_root_path = './';
include($phpbb_root_path . 'config.php');

// connect to the database server
$db = mysql_connect($dbhost,$dbuser,$dbpasswd);
if (!$db) die("Unable to connect to database!\n");

// select the PHPBB database
mysql_select_db($dbname,$db);

// get a list of tables for this PHPBB
$tablequery = "show tables like '".$table_prefix."%'";
$tablelist = mysql_query($tablequery,$db);


// cycle through them for repair
while ($tar = mysql_fetch_array($tablelist))
{
   $tablename = $tar[0];
   // output some verbosity for comfort
   echo "Now checking and repairing table $tablename ... ";
   $repres = mysql_query("REPAIR table $tablename");
   $result = mysql_fetch_array($repres);
   echo $result['Msg_text'] . "<br>\n";
}

?>

Just to note that this program is available here as a text file, which can be saved to disk by RIGHT-clicking on the link, then selecting "save as" from your browser's menu.

Save this file as "fixtables.php" in your phpbb root directory. This is the same directory where you will find "config.php". You can execute it by pointing your browser at your normal PHPBB URL and adding "/fixtables.php?" and your chosen "secret code" to the end of it. For example, if you normally visit your forum by typing "http://my.sitename.com/phpbb/", you start this process by going to the URL "http://my.sitename.com/phpbb/fixtables.php?PX42m3=1".

For a small board, the response will be pretty quick; it will take longer to run if errors are found and you have large tables.

Sometimes, an "Error 145" will require two (or more!) repair passes to resolve. Depending upon the size of your database, which tables need repair, and server timeouts, you can have a situation where not all tables get repaired on the first pass. In this case, you will need to run the script multiple times, but do not do so immediately. Once the script issues the command to repair a table, the MySQL server will continue to repair it, even if the web server "gives up" on this script. If you re-run the script too soon, a second request to repair the table will be queued up... and the script will probably time out again. Once a table has been repaired, later repair requests will return quickly with an "up to date" status, so the script will move on to the tables that weren't processed earlier.

If the "secret code" isn't on the URL line, this program will harmlessly redirect the user to a non-existent URL on their own computer. Well, it SHOULD be non-existent..

The "secret code" is not a mysterious thing. If you make no changes to the file, it is "PX42m3", as shown in the example URL above. It is there to protect you if you leave this file in place for future use, to prevent abuse by others. If you plan on removing the file when it is not needed, do not bother changing it, just use "?PX42m3=1" on the end of the URL. However, if you still can not figure out how to do it, and you understand that you should remove the program as soon as you are done with it, you can delete the following lines:

Code:
// verify that we're supposed to run
// change 'PX42m3' to something unique for your site, so it will only
// execute if you call it as 'http://mysite.com/fixtables.php?PX42m3=1'
if (!isset($_GET['PX42m3']))
{
   header("Location: http://127.0.0.1/");
   die();
}


Note 1: This script uses the REPAIR TABLE command, which is only supported for table type MyISAM. If you get a message about repair not being supported for the table type, edit the script to replace "REPAIR" with "OPTIMIZE", for compatibility with non-MyISAM table types, such as InnoDB. "OPTIMIZE" does not repair MyISAM tables, but it triggers the repair option for other MySQL table types.

Note 2: There can also be instances where a table says it is repaired, but still gives problems. This happens when an index file has some subtle forms of damage that the normal repair process does not handle. There is only one way to fix those errors, which involves telling MySQL to throw away the index completely, then rebuild it from scratch. This can be dangerous, which is why the script does NOT do this normally. It is highly suggested that you have your host make a backup of the table files first, just in case.

If you need to do this, edit the script to replace this line:

Code:
   $repres = mysql_query("REPAIR TABLE $tablename");


with this line:

Code:
   $repres = mysql_query("REPAIR TABLE $tablename USE_FRM");


If you are doing this through phpmyadmin or similar, the command becomes:

Code:
REPAIR TABLE phpbb_users USE_FRM;


Using this method is guaranteed to take longer than the standard method, because it will cause every table to be repaired, even if it has just been repaired. If possible, you should use some other tool to enter this repair query, so that you can apply it only to the tables that need it; this is especially important if your tables are large enough to require the script to be run multiple times (see above). So, you might want to try a much simpler script to do JUST a single table:

Code:
<?php
// Repair a table, named in the line below, by replacing the index file

$table_name = "search_words";

define('IN_PHPBB', true);
$phpbb_root_path = './';
include($phpbb_root_path . 'config.php');

// format table name, taking table prefix into account
if ((strpos($table_name,$table_prefix) === FALSE) or (strpos($table_name,$table_prefix) !== 0))
   $fix_table = $table_prefix . $table_name;
else
   $fix_table = $table_name;

// connect to the database server
$db = mysql_connect($dbhost,$dbuser,$dbpasswd);
if (!$db) die("Unable to connect to database!\n");

// select the PHPBB database
mysql_select_db($dbname,$db);

// Repair table, ignore errors
$tablequery = "repair table $fix_table USE_FRM";
mysql_query($tablequery,$db);

echo "PHPBB $table_name table repaired.";

?>


Edit the fourth line to have the name of the table from your error message, upload the script and run it. It will tell MySQL to repair just the one table. When it is done, delete the script.
Back to top
~Cowboy~
Board Member



Joined: 08 Dec 2008

Posts: 297
Location: Chicago


flag
PostPosted: Sun Dec 28, 2008 9:57 pm 
Post subject: Re: Fixing MySQL Index Errors

Nice tutorial espicom icon_biggrin.gif
Back to top
espicom
Board Member



Joined: 24 Nov 2008

Posts: 55
Location: Woodstock, IL


flag
PostPosted: Mon Dec 29, 2008 12:36 am 
Post subject: Re: Fixing MySQL Index Errors

Thanks - just updated the KB article I'd put up on phpbb.com.

And, in other news... This tutorial also applies to phpBB3, including the scripts to do the repairs.
Back to top
Display posts from previous:   
Register or Login to Post    Index » Knowledge Base (KB)  Previous TopicPrint TopicNext Topic
Page 1 of 1 All times are GMT
 
Jump to:  

Index • About • FAQ • Rules • Privacy • Search •  Register •  Login 
Not affiliated with or endorsed by the phpBB Group
Powered by phpBB2 © phpBB Group
Generated in 0.0275 seconds using 15 queries. (SQL 0.0014 Parse 0.0008 Other 0.0253)
phpBB Customizations by the phpBBDoctor.com
Template Design by DeLFlo and MomentsOfLight.com Moments of Light Logo