Fixing 1016 and 1030 errors from MySQL
Description: A guide to repairing MySQL tables
In Categories: Administration
- Link to this article: Select All
[url=https://www.phpbb.com/support/docs/en/3.0/kb/article/fixing-1016-and-1030-errors-from-mysql/]Knowledge Base - Fixing 1016 and 1030 errors from MySQL[/url]
A 1016 or a 1030 error from MySQL means that part of your data, the "index file", is screwed up, and needs to be fixed.
If you receive a 1016 or 1030 error with a code of "2", "9", "12", "13", "23", "24", "28", or "122", contact your host immediately - these errors can not 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:
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:
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.
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:
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:
with this line:
If you are doing this through phpmyadmin or similar, the command becomes:
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:
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.
1198110805 - Approved - Marshalrusty
20081229 - Marked as also phpBB3 compatible - ric323
If you receive a 1016 or 1030 error with a code of "2", "9", "12", "13", "23", "24", "28", or "122", contact your host immediately - these errors can not 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: Select all
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: Select all
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: Select all
<?php
// Check and repair all tables in a PHPBB structure
// 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: Select all
// 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: Select all
$repres = mysql_query("REPAIR TABLE $tablename");with this line:
Code: Select all
$repres = mysql_query("REPAIR TABLE $tablename USE_FRM");If you are doing this through phpmyadmin or similar, the command becomes:
Code: Select all
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: Select all
<?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.
1198110805 - Approved - Marshalrusty
20081229 - Marked as also phpBB3 compatible - ric323