Fixing SQL Error : 1016 Can't open file:

This is an archive of the phpBB 2.0.x support forum. Support for phpBB2 has now ended.
Forum rules
Following phpBB2's EoL, this forum is now archived for reference purposes only.
Please see the following announcement for more information: viewtopic.php?f=14&t=1385785
Locked
espicom
Registered User
Posts: 17905
Joined: Wed Dec 22, 2004 1:14 am
Location: Woodstock, IL

Fixing SQL Error : 1016 Can't open file:

Post by espicom »

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.

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 this script, and are indicative of problems (possibly hardware) on the MySQL server.

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, and 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.

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

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.

Sometimes, an "Error 145" will require two (or more!) repair passes to resolve. For months, I have not been able to figure out why... but I think I have, now.

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. Once a script like this tells MySQL to do the repair, the rapair on that table continues, wither or not the PHP script times out... but the PHP script may have terminated by the time MySQL is finished. Later runs skip over the early tables, because they're already repaired, meaning each pass gets futher through the tables.

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 table types.

Edit: Updated the list of error codes to call your host about, and attempt to short-circuit questions about "what is the secret code?"
Last edited by ric323 on Mon Dec 17, 2007 9:59 pm, edited 10 times in total.
Reason: Topic icon changed

espicom
Registered User
Posts: 17905
Joined: Wed Dec 22, 2004 1:14 am
Location: Woodstock, IL

Post by espicom »

Just to note that this program is available here as a text file, which can be saved to disk from your browser, modified to change the "secret word", and then uploaded to your forum directory.
Jeff
Fixing 1016/1030/1034 Errors | (obsolete link) | MySQL 4.1/5.x Client Error | phpBBv2 Logo in ACP
Support requests via PM are ignored!
"To be fully alive is to feel that everything is possible." - Eric Hoffer

vesselinpeev
Registered User
Posts: 52
Joined: Sat May 29, 2004 3:46 pm
Contact:

Post by vesselinpeev »

Thank you, espicom. I am now using your script. Didn't have a db problem as a reason to run it, but I tried it and all is ok. I noticed the database has become slightly smaller. Over on another topic I read your post that this not only repairs, but also optimizes the tables. Great! Thank you once again!

-Vesko

curt_grymala
Registered User
Posts: 2
Joined: Wed Jul 21, 2004 7:04 pm

Post by curt_grymala »

I just tried this fix, and I got the following output:
Now checking and repairing table phpbb_auth_access ... OK
Now checking and repairing table phpbb_banlist ... OK
Now checking and repairing table phpbb_categories ... OK
Now checking and repairing table phpbb_chatbox ... OK
Now checking and repairing table phpbb_chatbox_session ... OK
Now checking and repairing table phpbb_config ... Can't open file: 'phpbb_config.MYD'. (errno: 126)
Now checking and repairing table phpbb_confirm ... OK
Now checking and repairing table phpbb_disallow ... OK
Now checking and repairing table phpbb_forum_prune ... OK
Now checking and repairing table phpbb_forums ... Can't open file: 'phpbb_forums.MYD'. (errno: 126)
Now checking and repairing table phpbb_groups ... Can't open file: 'phpbb_groups.MYD'. (errno: 126)
Now checking and repairing table phpbb_ina_ban ... OK
Now checking and repairing table phpbb_ina_categories ... OK
Now checking and repairing table phpbb_ina_categories_data ... OK
Now checking and repairing table phpbb_ina_challenge_tracker ... OK
Now checking and repairing table phpbb_ina_challenge_users ... OK
Now checking and repairing table phpbb_ina_cheat_fix ... Can't open file: 'phpbb_ina_cheat_fix.MYD'. (errno: 126)
Now checking and repairing table phpbb_ina_data ... OK
Now checking and repairing table phpbb_ina_gamble ... OK
Now checking and repairing table phpbb_ina_gamble_in_progress ... OK
Now checking and repairing table phpbb_ina_games ... Can't open file: 'phpbb_ina_games.MYD'. (errno: 126)
Now checking and repairing table phpbb_ina_last_game_played ... Can't open file: 'phpbb_ina_last_game_played.MYD'. (errno: 126)
Now checking and repairing table phpbb_ina_rating_votes ... OK
Now checking and repairing table phpbb_ina_scores ... Can't open file: 'phpbb_ina_scores.MYD'. (errno: 126)
Now checking and repairing table phpbb_ina_sessions ... Can't open file: 'phpbb_ina_sessions.MYD'. (errno: 126)
Now checking and repairing table phpbb_ina_top_scores ... OK
Now checking and repairing table phpbb_ina_trophy_comments ... OK
Now checking and repairing table phpbb_posts ... Can't open file: 'phpbb_posts.MYD'. (errno: 126)
Now checking and repairing table phpbb_posts_text ... Can't open file: 'phpbb_posts_text.MYD'. (errno: 126)
Now checking and repairing table phpbb_privmsgs ... Can't open file: 'phpbb_privmsgs.MYD'. (errno: 126)
Now checking and repairing table phpbb_privmsgs_text ... Can't open file: 'phpbb_privmsgs_text.MYD'. (errno: 126)
Now checking and repairing table phpbb_ranks ... OK
Now checking and repairing table phpbb_search_results ... OK
Now checking and repairing table phpbb_search_wordlist ... Can't open file: 'phpbb_search_wordlist.MYD'. (errno: 126)
Now checking and repairing table phpbb_search_wordmatch ... Can't open file: 'phpbb_search_wordmatch.MYD'. (errno: 126)
Now checking and repairing table phpbb_sessions ... OK
Now checking and repairing table phpbb_smilies ... OK
Now checking and repairing table phpbb_themes ... OK
Now checking and repairing table phpbb_themes_name ... OK
Now checking and repairing table phpbb_topics ... OK
Now checking and repairing table phpbb_topics_watch ... Can't open file: 'phpbb_topics_watch.MYD'. (errno: 126)
Now checking and repairing table phpbb_user_group ... Can't open file: 'phpbb_user_group.MYD'. (errno: 126)
Now checking and repairing table phpbb_users ... Can't open file: 'phpbb_users.MYD'. (errno: 126)
Now checking and repairing table phpbb_vote_desc ... OK
Now checking and repairing table phpbb_vote_results ... OK
Now checking and repairing table phpbb_vote_voters ... OK
Now checking and repairing table phpbb_words ... OK


Any other suggestions?

espicom
Registered User
Posts: 17905
Joined: Wed Dec 22, 2004 1:14 am
Location: Woodstock, IL

Post by espicom »

Hmmm.... An errno 126 can be serious.

First suggestion - try running fixtables.php one more time. Sometimes, a second repair request fixes 126's. If it does not, you're going to need heavier-duty tools, and it may involve getting the MySQL server administrator involved, since some of the tools are command-line only.

Check How to Repair Tables on the MySQL Developers website. It covers everything. Some can be done through PHPMyAdmin, but running myisamchk means at least shell access to the server, and probably as root user... That means the server admin!

Good luck!
Jeff
Fixing 1016/1030/1034 Errors | (obsolete link) | MySQL 4.1/5.x Client Error | phpBBv2 Logo in ACP
Support requests via PM are ignored!
"To be fully alive is to feel that everything is possible." - Eric Hoffer

darksnape
Registered User
Posts: 55
Joined: Wed Feb 23, 2005 1:23 pm

Post by darksnape »

The script didn't repair my mysql error : Can you help ??

Common Error

Could not find oldest privmsgs

DEBUG MODE

SQL Error : 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AND privmsgs_from_userid = 2' at line 4

SELECT privmsgs_id FROM phpbb_privmsgs WHERE privmsgs_type = 2 AND privmsgs_date = AND privmsgs_from_userid = 2

Line : 286
File : /raid/www/halfbloodsworld.com/www/root/forum/privmsg.php

espicom
Registered User
Posts: 17905
Joined: Wed Dec 22, 2004 1:14 am
Location: Woodstock, IL

Post by espicom »

The script didn't repair my mysql error : Can you help ??


No, this script wouldn't help your error... it's not the database that is at fault here, its the code accessing it.

I addressed this question in another topic. There IS an error in your query:
SELECT privmsgs_id FROM phpbb_privmsgs WHERE privmsgs_type = 2 AND privmsgs_date = AND privmsgs_from_userid = 2


There has to be something after the equals sign and before the AND, and it is missing. The MySQL server is complaining about it. You're probably dealing with an incomplete MOD.

You still have not answered the question I posed in the other topic; are there any MODs that you installed that might have changed this query?
Jeff
Fixing 1016/1030/1034 Errors | (obsolete link) | MySQL 4.1/5.x Client Error | phpBBv2 Logo in ACP
Support requests via PM are ignored!
"To be fully alive is to feel that everything is possible." - Eric Hoffer

User avatar
hendrajana
Registered User
Posts: 2
Joined: Wed Mar 02, 2005 5:40 am

Post by hendrajana »

Code: Select all

Could not insert new word matches

DEBUG MODE

SQL Error : 1016 Can't open file: 'phpbb_search_wordmatch.MYD'. (errno: 144)

INSERT INTO phpbb_search_wordmatch (post_id, word_id, title_match) SELECT 6576, word_id, 0 FROM phpbb_search_wordlist WHERE word_text IN ('test', 'aja', 'nih', 'apa', 'masih', 'error')

Line : 251
File : functions_search.php
What can I do?

espicom
Registered User
Posts: 17905
Joined: Wed Dec 22, 2004 1:14 am
Location: Woodstock, IL

Post by espicom »

According to the perror program:
perror 144
Error code 144: Unknown error 144
144 = Table is crashed and last repair failed


This means that the repair built into MySQL isn't capable of fixing whatever the problem is on its own. Sometimes, a second repair operation will work, but usually this means you need to invoke the myisamchk program from the command line. It has a variety of "strength" levels that you work your way through until the repair is successful.

Unfortunately, if you are on a hosted service, this is something that can only be invoked by the people in charge of the MySQL server. Contact them and let them know.

If you host your own stuff, and have root or administrator access to the MySQL server, These links to the MySQL developer's site cover general repair operations within MySQL and using myisamchk to fix tables that REPAIR TABLE won't.
Jeff
Fixing 1016/1030/1034 Errors | (obsolete link) | MySQL 4.1/5.x Client Error | phpBBv2 Logo in ACP
Support requests via PM are ignored!
"To be fully alive is to feel that everything is possible." - Eric Hoffer

optimistmb
Registered User
Posts: 18
Joined: Fri Jul 23, 2004 1:14 pm

Post by optimistmb »

Thank you, this was a BIG help :)

dmtalon
Registered User
Posts: 37
Joined: Tue May 07, 2002 12:06 am
Location: Florence, KY
Contact:

Post by dmtalon »

Thanks a BUNCH for this!!! Saved my butt...

Dallace

Bill175
Registered User
Posts: 52
Joined: Fri Oct 10, 2003 12:40 am

Post by Bill175 »

I assume this works on "little" forums, that have no size to their database. I clearly have uploaded it as a php file and when trying to pull it up through the browser, it just gives "This page cannot be displayed".

Good try though.

espicom
Registered User
Posts: 17905
Joined: Wed Dec 22, 2004 1:14 am
Location: Woodstock, IL

Post by espicom »

Define "little" vs. "big". :wink:

Clearly, if your hosting provider puts a really short time-out on PHP scripts (I've seen as short as THREE seconds, 30 is normal) and your tables are of substantial size 40 or 50 MB or more), you can get time-outs.

These errors will tell you how long the time out value was, usually, and the tables that were completed will be listed. If your tables are big enough that this script can't get through even the first few before timing out, then you have to ask your hosting provider to manually invoke myisamchk for you. The "first few" should never be more than a few dozen K.

I just timed the repair operation on two tables, with 263,714 and 1,827,130 records each, at 2 min 51.23 sec total time. This will exceed most ISP's PHP limitations. Anyone dealing with PHPBB's with that sort of volume should not be on a free host, nor should they be on a host that doesn't provide shell access so that you can run the MySQL command line tools. That is the target audience of this script.

"This page cannot be displayed", however, is what you'll get if you put in the wrong "magic word", because it will redirect your browser to 127.0.0.1.
Jeff
Fixing 1016/1030/1034 Errors | (obsolete link) | MySQL 4.1/5.x Client Error | phpBBv2 Logo in ACP
Support requests via PM are ignored!
"To be fully alive is to feel that everything is possible." - Eric Hoffer

Bill175
Registered User
Posts: 52
Joined: Fri Oct 10, 2003 12:40 am

Post by Bill175 »

Your right, it was on my end :lol:

Ran your fix on a 130mg database, and there appears to be one problem it found, that it can't fix.

Now checking and repairing table phpbb_posts_text ... Found block that points outside data file at 56258444

What would that be?

Bill

espicom
Registered User
Posts: 17905
Joined: Wed Dec 22, 2004 1:14 am
Location: Woodstock, IL

Post by espicom »

Google is usually your friend in this situation, but there are lots of dead ends when you search on "Found block that points outside data file". The ones worth while all involve direct access to the MySQL database files, because many of the repair methods will truncate the file when they reach the bad pointer... This is a "make backup copies of the table files and experiment" sort of thing.

One suggestion made for version 4.0.2 of MySQL and later is to use:

Code: Select all

REPAIR TABLE phpbb_posts_text USE_FRM;
This tells MySQL to use the database layout file (.frm) and the data itself (.MYD), and ignore the existing index file (.MYI) in creating a new index. Like I said, this is something best done by the server administrator, who can make backups of the physical files before running this repair, because it CAN truncate the file when it hits the error.

If you have command line access and the correct privileges, you can use myisamchk to do this. It can fix things that the server can't do on its own. For more details, see the links in the message above that cover "error 144".
Jeff
Fixing 1016/1030/1034 Errors | (obsolete link) | MySQL 4.1/5.x Client Error | phpBBv2 Logo in ACP
Support requests via PM are ignored!
"To be fully alive is to feel that everything is possible." - Eric Hoffer

Locked

Return to “2.0.x Support Forum”