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

Post by espicom »

1064 != 1016

Your problem is you do not have group_id entries for all of your users, or you recently deleted a user who was a moderator of a group.

The first is the most probable, usually because you updated a pre-2.0.10 forum. Early versions of PHPBB2 handled user permissions differently than 2.0.10 and later, but somewhere along the line, the script needed to change the database to reflect these changes either disappeared, or was never distributed... However the topic Fix group_id being blank has a script that will take care of that.

If you deleted a moderator, though, you will have to add a moderator to the affected group(s) before you can do anything else with them.
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

Ricchione
Registered User
Posts: 2
Joined: Wed Oct 12, 2005 5:02 pm

Post by Ricchione »

Thank you very much, that did the trick! :D
Ricchione.

User avatar
tim_welch
Registered User
Posts: 163
Joined: Tue Aug 17, 2004 11:41 am
Location: London, UK
Contact:

Post by tim_welch »

Hi there. Every now and again I get messages similar to the one quoted below:
phpBB : Critical Error

Error creating new session

DEBUG MODE

SQL Error : 1016 Can't open file: 'phpbb_sessions.MYI'. (errno: 145)

INSERT INTO phpbb_sessions (session_id, session_user_id, session_start, session_time, session_ip, session_page, session_logged_in, session_admin) VALUES ('332bbb2d74d1548e29d8e688bb2b08c3', -1, 1129275713, 1129275713, '5156092c', 0, 0, 0)

Line : 173
File : sessions.php

...and I run the fixtables.php file from this post and everything sorts itself out, so that is good. But my question is why does it keep happening and what can one do to stop it? I guess, for me, this type of error appears once every month or so and whilst I often catch it quickly, sometimes my site can be 'unavailable' for a day. Could, for example, the fixtables.php script be automated to run every week?

My secondary question is whether anything can be done to customise this error so that visitors can see a different type of message. Something like:
There has been a database error on this forum. Please contact the administrators on admin@site.com and ask them to fix this. Alternatively, please visit our sister site http://www.sistersite.com. Thank you.

Error details...

phpBB : Critical Error

Error creating new session

DEBUG MODE

SQL Error : 1016 Can't open file: 'phpbb_sessions.MYI'. (errno: 145)

INSERT INTO phpbb_sessions (session_id, session_user_id, session_start, session_time, session_ip, session_page, session_logged_in, session_admin) VALUES ('332bbb2d74d1548e29d8e688bb2b08c3', -1, 1129275713, 1129275713, '5156092c', 0, 0, 0)

Line : 173
File : sessions.php

I realise this may be a mySQL or PHP thing rather than a phpBB-specific question, but I hope someone might have some suggestions.

elotroyo69
Registered User
Posts: 4
Joined: Tue Aug 16, 2005 2:17 pm

Post by elotroyo69 »

hi !
i run the forum in www.elotroyo.com/Foro
i have already run de fixetables.php several times ...

and returns normal:

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_config ... OK
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 ... OK
Now checking and repairing table phpbb_groups ... OK
Now checking and repairing table phpbb_posts ... OK
Now checking and repairing table phpbb_posts_text ... 122 when writing to datafile
Now checking and repairing table phpbb_privmsgs ... OK
Now checking and repairing table phpbb_privmsgs_text ... OK
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 ... OK
Now checking and repairing table phpbb_search_wordmatch ... OK
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 ... OK
Now checking and repairing table phpbb_user_group ... OK
Now checking and repairing table phpbb_users ... OK
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


but in any case i tried to log in or read posts ... keeps going this:

Could not obtain post/user information.

DEBUG MODE

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

SELECT u.username, u.user_id, u.user_posts, u.user_from, u.user_website, u.user_email, u.user_icq, u.user_aim, u.user_yim, u.user_regdate, u.user_msnm, u.user_viewemail, u.user_rank, u.user_sig, u.user_sig_bbcode_uid, u.user_avatar, u.user_avatar_type, u.user_allowavatar, u.user_allowsmile, p.*, pt.post_text, pt.post_subject, pt.bbcode_uid FROM phpbb_posts p, phpbb_users u, phpbb_posts_text pt WHERE p.topic_id = 3188 AND pt.post_id = p.post_id AND u.user_id = p.poster_id ORDER BY p.post_time ASC LIMIT 0, 20

Line : 398
File : viewtopic.php

please, anybody knows what could i do to fix it?

thanks for all,
fede

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

Post by espicom »

tim_welch wrote: Hi there. Every now and again I get messages similar to the one quoted below:

[stuff removed]

...and I run the fixtables.php file from this post and everything sorts itself out, so that is good. But my question is why does it keep happening and what can one do to stop it? I guess, for me, this type of error appears once every month or so and whilst I often catch it quickly, sometimes my site can be 'unavailable' for a day. Could, for example, the fixtables.php script be automated to run every week?


Automating the script can be done an number of ways. The simplest is to use the windows Task Manager, and add an entry to open the URL once a week at a slow time. Your browser will be open when you come back to the machine, with the results of the run.

If you have access to run cron jobs (Linux/Unix server) or the task manager (Windows server) on the server itself, it's usually easier to construct an SQL file to feed into the mysql command line program that does the same thing.
My secondary question is whether anything can be done to customise this error so that visitors can see a different type of message.


This error message is handled by a rather generic handler, and the text displayed is either hard-coded into sessions.php ("Error creating new session"), or in what is returned by the MySQL server, so customizations will be limited to just the first string.
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

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

Post by espicom »

elotroyo69 wrote: hi !
i run the forum in www.elotroyo.com/Foro
i have already run de fixetables.php several times ...

and returns normal:

Now checking and repairing table phpbb_posts_text ... 122 when writing to datafile


I didn't catch the part in RED when I answered your separate topic on this, but Error code 122: Disk quota exceeded. The MySQL server has exceeded its space allocation, so you need to contact the host to get this resolved.
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
tim_welch
Registered User
Posts: 163
Joined: Tue Aug 17, 2004 11:41 am
Location: London, UK
Contact:

Post by tim_welch »

espicom wrote: If you have access to run cron jobs (Linux/Unix server) or the task manager (Windows server) on the server itself, it's usually easier to construct an SQL file to feed into the mysql command line program that does the same thing.
I do have access to the server myself but I don't know how to do the mySQL script. Is there an example script that you could post here, assuming the URL of http://www.mysite.com/forum/fixtables.php and c:/directory/fixtables.php ?

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

Post by espicom »

Create a file that contains the SQL query to optimize/repair the tables you select. It will look like this:

Code: Select all

USE phpbbdatabase;
OPTIMIZE TABLE  `phpbb_auth_access` , `phpbb_banlist` , `phpbb_categories`, `phpbb_config` , `phpbb_confirm` , `phpbb_disallow` , `phpbb_extension_groups` , `phpbb_extensions` , `phpbb_forbidden_extensions` , `phpbb_forum_prune` , `phpbb_forums` , `phpbb_forums_watch` , `phpbb_groups` , `phpbb_posts` , `phpbb_posts_text` , `phpbb_privmsgs` , `phpbb_privmsgs_text` , `phpbb_quota_limits` , `phpbb_ranks` , `phpbb_search_results` , `phpbb_search_wordlist` , `phpbb_search_wordmatch` , `phpbb_sessions` , `phpbb_smilies` , `phpbb_themes` , `phpbb_themes_name` , `phpbb_topics` , `phpbb_topics_watch` , `phpbb_user_group` , `phpbb_users` , `phpbb_vote_desc` , `phpbb_vote_results` , `phpbb_vote_voters` , `phpbb_words` ;
Let's call it "optimize.sql". Next, you need a batch file (for Windows) or shell script (Linux/Unix) to execute it. Fortunately, they can be pretty much the same, except for the path to the command:

Code: Select all

c:\mysql\bin\mysql -uMYUSERNAME -pMYPASSWORD -hMYSQLADDRESS phpbbdatabase < optimize.sql
Save this as "phpbb_optimize.cmd" or "phpbb_optimize.sh", as appropriate. (Actually, the "use" in the sql portion is optional, if you supply the database name on the command line, but I like to make sure, when I can.)

Now, just add an entry into the scheduler or cron to call it when needed.
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
tim_welch
Registered User
Posts: 163
Joined: Tue Aug 17, 2004 11:41 am
Location: London, UK
Contact:

Post by tim_welch »

Ah, I see. So you don't need to use your actual .PHP file at all. I guess, because it's running locally direct to mySQL, the query is all you need. And I guess that if I wanted just to run this OPTIMIZE option on the sessions table only (as it seems to get all funny the most), then I can just use...

Code: Select all

USE phpbbdatabase; 
OPTIMIZE TABLE `phpbb_sessions`;
Thanks.

But why OPTIMIZE instead of REPAIR? I saw a posting of yours somewhere that mentioned using OPTIMIZE for certain table-types but I didn't really understand what you were saying... but is this why you are using OPTIMIZE?

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

Post by espicom »

But why OPTIMIZE instead of REPAIR? I saw a posting of yours somewhere that mentioned using OPTIMIZE for certain table-types but I didn't really understand what you were saying... but is this why you are using OPTIMIZE?


The portability between table types. REPAIR does a few things that OPTIMIZE doesn't do, according to the documentation, especially if you invoke the "USE_FRM" option... but most of the time, you are dealing with an index that just got out of sync with the data, and an optimize rebuilds the indexes, and does it for all table types that have separate indexes.

And, for periodic maintenance, it just seems better to use "optimize" instead of "repair", symantically.

The sessions table gets the most abuse. It is constantly having records added and deleted. The space of deleted records is eventually reused, but optimizing the table from time to time makes it smaller, and easier for MySQL to manage. Which seems like a silly thing to worry about, when you have 6GB data tables on the same server. :wink:
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
mad72
Registered User
Posts: 74
Joined: Thu Mar 13, 2003 4:20 pm
Location: Milan, Italy
Contact:

Post by mad72 »

I'm sorry I've opened another Topic but my problem persists even after running twice the script :(

http://www.phpbb.com/phpBB/viewtopic.ph ... 48#1818448

can you please help me?

bye, Marco

Timber_Ghost
Registered User
Posts: 59
Joined: Sat Jan 22, 2005 10:07 pm

Post by Timber_Ghost »

OK I'm about ready to pull my hair out...I have copied and pasted in notepad...save as a .txt file uploaded it to the correct area with config.php then changed it to a .php file and all I get is the same code displayed I just uploaded... :x

What the heck am I doing wrong...

Timber_Ghost
Registered User
Posts: 59
Joined: Sat Jan 22, 2005 10:07 pm

Post by Timber_Ghost »

Worked half the night on this...still cant get it to work

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

Post by espicom »

Timber_Ghost wrote: OK I'm about ready to pull my hair out...I have copied and pasted in notepad...save as a .txt file uploaded it to the correct area with config.php then changed it to a .php file and all I get is the same code displayed I just uploaded... :x

What the heck am I doing wrong...


You can download the file by right-clicking on the link in the second message on this topic; Select "save as", and then name it "fixtables.php". If you're using Internet Explorer, change the "file type" to "All files" before putting in the name, and it usually won't change the name to "fixtables.php.txt" on you. :roll:

You can then upload that to the same directory as config.php, as it will not have any extra stuff put in it by the various steps in the process of cutting/pasting. Getting back the same code usually means that the website did not think it was PHP code, so it did not interpret it.
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

JohnTheXtreme
Registered User
Posts: 10
Joined: Wed Oct 26, 2005 7:06 pm
Contact:

Post by JohnTheXtreme »

ome 1 please help meeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee

Locked

Return to “2.0.x Support Forum”