server crash imminent due to multiple registrations

Get help with installation and running phpBB 3.0.x here. Please do not post bug reports, feature requests, or MOD-related questions here.
Ideas Centre
Forum rules
END OF SUPPORT: 1 January 2017 (announcement)
mike=).
Registered User
Posts: 19
Joined: Wed Dec 24, 2008 4:21 pm

server crash imminent due to multiple registrations

Post by mike=). » Sat Nov 20, 2010 8:42 am

Support Request Template
What version of phpBB are you using? phpBB 3.0.6
What is your board's URL? http://www.oxanababy.com/forum
Who do you host your board with? dedicated server at WebXtra, Netherlands
How did you install your board? Someone else installed my board for me
Is your board a fresh install or a conversion? Conversion from phpBB2
Do you have any MODs installed? No, I don't think so
Is registration required to reproduce this issue? No
What styles do you currently have installed? prosilver (used by 0), Redsilver3* (used by 623) subsilver2 (unistalled)
What language(s) is your board currently using? english, russian
Which database type/version are you using? MySQL 5
What is your level of experience? New to PHP and phpBB
When did your problem begin? After the last upgrade from phpBB 2 to phpBB 3 approx summer 2010
Please describe your problem. Our forum is currently being flooded by dummy registrations 4-5 per minute, in spite of using a captcha (the members post nothing but always add one or more spam links to their profile). Currently our server is at the point of crashing (partition(s) are > 95% full)
The database size (32,34 MiB) does not match the size on disc.
Generated by SRT Generator ($Rev: 4502 $)

I would like to take emergency action first, to save the server, as support from the host is almost non-existant. (Temporarily disable registrations, ban bots...) After that purge, update and consolidate anything that needs to be attended to.

I'm reading up the "Preventing spam in phpBB 3.0.6 and above" thread. Noted that our captcha is useless.

Many thanks in advance, Mike

User avatar
noth
Registered User
Posts: 2472
Joined: Fri Jan 07, 2005 7:10 pm
Location: North Surrey
Contact:

Re: server crash imminent due to multiple registrations

Post by noth » Sat Nov 20, 2010 8:47 am

ACP allows Registration to be temporarily disabled, or dealt with on a manual enablement basis

also, let your hosts know about this and ask them for advice (always worth doing)

mike=).
Registered User
Posts: 19
Joined: Wed Dec 24, 2008 4:21 pm

Re: server crash imminent due to multiple registrations

Post by mike=). » Sat Nov 20, 2010 11:02 am

noth wrote:ACP allows Registration to be temporarily disabled, or dealt with on a manual enablement basis

also, let your hosts know about this and ask them for advice (always worth doing)
Thanks noth !!

Account activation is now disabled in User registration settings. Also I have deactivated the bots.
I've pruned (deleted) all users with less than 1 post since summer 2010. Now 111 users in total.

However, the database has now increased from 32,34 MiB to 32,35 MiB.
Is there any way to reduce the size of the database?

My host is well aware of the problem. Unfortunately they are not even replying to questions which have been awarded a support ticket. If you google references to the host you will see why they should not even be in business. Moving servers will be a next issue.

Thanks again noth, I hope that the immediate damage has been resolved...

I think the next issues are size of database, captcha update, maybe update to 3.0.7 then re-open the board.

Mike

User avatar
tbackoff
Former Team Member
Posts: 7022
Joined: Thu Jun 04, 2009 1:41 am
Location: cheerleading practice
Name: Tabitha Backoff

Re: server crash imminent due to multiple registrations

Post by tbackoff » Sat Nov 20, 2010 1:14 pm

mike=). wrote:captcha update, maybe update to 3.0.7
You should always stay up-to-date. ;)

What CAPTCHA were you using? The standard CAPTCHA has been broken by spambots and phpBB 3.0.6 includes the CAPTCHA plugin system, so you could easily go to a Q&A CAPTCHA, reCAPTCHA, or any of the plugins in the Customization Database.
Flying is the second best thrill to cheerleaders; being caught is the first.

mike=).
Registered User
Posts: 19
Joined: Wed Dec 24, 2008 4:21 pm

Re: server crash imminent due to multiple registrations

Post by mike=). » Sat Nov 20, 2010 6:29 pm

t_backoff wrote:
mike=). wrote:captcha update, maybe update to 3.0.7
You should always stay up-to-date. ;)

What CAPTCHA were you using? The standard CAPTCHA has been broken by spambots and phpBB 3.0.6 includes the CAPTCHA plugin system, so you could easily go to a Q&A CAPTCHA, reCAPTCHA, or any of the plugins in the Customization Database.
Thanks Tabitha!!

The captcha in use was the original phpBB2 CAPTCHA (without GD). I was going to update that next but now there appears to be a far more serious problem.. the board won't load at all.
After having taken all the actions in the above post, I left the board online. When checking back later I noticed there were 9-10 users online after having disabled registration and pruned all recent users. Then I found I was unable to enter the ACP, so I tried to open the board in a new window, and now it won't open at all.

The server itself seems OK.

Any help with this would be graetly appreciated !!

Mike

mike=).
Registered User
Posts: 19
Joined: Wed Dec 24, 2008 4:21 pm

Re: server crash imminent due to multiple registrations

Post by mike=). » Sat Nov 20, 2010 8:37 pm

Update... the server did indeed crash is back online now...

However.. although I login to the board, I can't login to the ACP.
(maybe this should be posted in a seperate thread, but it does seem related to the size of the database)

The error given is this:

General Error
SQL ERROR [ mysql4 ]

Incorrect key file for table './oxanabab_new/phpbb_log.MYI'; try to repair it [126]

SQL

INSERT INTO phpbb_log (user_id, log_ip, log_time, log_operation, log_data, log_type) VALUES (2, '82.173.144.133', 1290284771, 'LOG_ADMIN_AUTH_SUCCESS', '', 0)

BACKTRACE

FILE: includes/db/mysql.php
LINE: 174
CALL: dbal->sql_error()

FILE: includes/functions.php
LINE: 3224
CALL: dbal_mysql->sql_query()

FILE: includes/functions.php
LINE: 2856
CALL: add_log()

FILE: adm/index.php
LINE: 33
CALL: login_box()


I have no knowledge of mysql, and no idea how to resolve this. Any help would be awesome. Thanks.

Mike

User avatar
tbackoff
Former Team Member
Posts: 7022
Joined: Thu Jun 04, 2009 1:41 am
Location: cheerleading practice
Name: Tabitha Backoff

Re: server crash imminent due to multiple registrations

Post by tbackoff » Sat Nov 20, 2010 8:40 pm

You need to go into phpmyAdmin and repair your phpbb_log table. Make a database backup first, then run:

Code: Select all

REPAIR TABLE phpbb_log;
Flying is the second best thrill to cheerleaders; being caught is the first.

mike=).
Registered User
Posts: 19
Joined: Wed Dec 24, 2008 4:21 pm

Re: server crash imminent due to multiple registrations

Post by mike=). » Sat Nov 20, 2010 9:30 pm

t_backoff wrote:You need to go into phpmyAdmin and repair your phpbb_log table. Make a database backup first, then run:

Code: Select all

REPAIR TABLE phpbb_log;
Thanks again Tabitha !

I would do that (with some help maybe), but there is another even more serious problem to deal with first.

A few weeks ago I had an automated message from the server stating that a password reset had been requested. I immediately contacted the host telling them that I had not made that request. Instead of sorting things out, they answered "then someone else must have made the request using your control panel". I logged in and out and all was well. Today I logged in and the password no longer works.
I've sent another 'very polite' message to the host to reset the (Nodeworx) server control panel login. Until that has been done, there is no way I can do anything. They won't reply until Monday at the earliest.

I do notice many 'guests' online at the forum, but after the previous action, I don't think they will be able to cause much harm.

Thank you for your patience.

Mike

User avatar
tbackoff
Former Team Member
Posts: 7022
Joined: Thu Jun 04, 2009 1:41 am
Location: cheerleading practice
Name: Tabitha Backoff

Re: server crash imminent due to multiple registrations

Post by tbackoff » Sat Nov 20, 2010 9:32 pm

I'm sorry, you will have to wait for them to fix your password issue. Not much we can do on the phpBB side. ;)
Flying is the second best thrill to cheerleaders; being caught is the first.

mike=).
Registered User
Posts: 19
Joined: Wed Dec 24, 2008 4:21 pm

Re: server crash imminent due to multiple registrations

Post by mike=). » Sat Jan 01, 2011 12:46 pm

First and most importantly :) :

Very Best wishes, good health and prosperity to all at phpBB for 2011 !!

Tabitha, the server password issue is now fixed. It took some time and I've had a huge amount of work to do lately.

I have opened phpMyAdmin and this is what I found:

phpMyAdmin version: 2.11.9.4
MySQL client version: 4.0.21
used PHP extensions: mysql

Server 1 (my server)
server version: 5.0.77
Protocol version: 10
Server: Server 1 (Localhost via UNIX socket)
User: iworx@localhost
MySQL charset: UTF-8 Unicode
MySQL connection: utf8_unicode_ci

All that seems OK

However, my server is still at the point of crashing. Daily warnings: "the following partitions are nearly full: ./-95%"
I can not access the phpBB administration. This is the message:

Code: Select all

General Error
SQL ERROR [ mysql4 ]
Table './oxanabab_new/phpbb_log' is marked as crashed and should be repaired [145]
SQL
INSERT INTO phpbb_log (user_id, log_ip, log_time, log_operation, log_data, log_type) VALUES (2, '82.173.144.133', 1293884460, 'LOG_ADMIN_AUTH_SUCCESS', '', 0)
BACKTRACE

FILE: includes/db/mysql.php
LINE: 174
CALL: dbal->sql_error()

FILE: includes/functions.php
LINE: 3224
CALL: dbal_mysql->sql_query()

FILE: includes/functions.php
LINE: 2856
CALL: add_log()

FILE: adm/index.php
LINE: 33
CALL: login_box()
So I've checked the databases. There are 6:
- information schema (17)
- mysql (17)
- oxanabab
. _forum (57)
. _new (62)
. _phpbbthree (63)
- oxanababy_ru_-_phpbb (58)

Am I right in thinking there are at least 3 too many databases?

Next I checked the red tagged values, looking for anything that may be far too large. Here is some of what I found (I'll send a full report if needed):

Innodb_buffer_pool_reads 12 The number of logical reads that InnoDB could not satisfy from buffer pool and had to do a single-page read.

Handler_read_rnd 535 k The number of requests to read a row based on a fixed position. This is high if you are doing a lot of queries that require sorting of the result. You probably have a lot of queries that require MySQL to scan whole tables or you have joins that don't use keys properly.

Handler_read_rnd_next 45 M !!! The number of requests to read the next row in the data file. This is high if you are doing a lot of table scans. Generally this suggests that your tables are not properly indexed or that your queries are not written to take advantage of the indexes you have.

Created_tmp_disk_tables 149 The number of temporary tables on disk created automatically by the server while executing statements. If Created_tmp_disk_tables is big, you may want to increase the tmp_table_size value to cause temporary tables to be memory-based instead of disk-based.

Opened_tables 660 The number of tables that have been opened. If opened tables is big, your table cache value is probably too small.

Table_locks_waited 450 The number of times that a table lock could not be acquired immediately and a wait was needed. If this is high, and you have performance problems, you should first optimize your queries, and then either split your table or tables or use replication.

So there does indeed seem to be a problem with databases / tables.
Back to your previous advice.... to repair phpbb_log table.
How can I make a database backup with no access to the forum admin?
Where can I find the REPAIR TABLE php_log ?
Can I get rid of unused databases (maybe after saving in case ever needed again)?

Any help will be most appreciated. Please bear in mind that I do not know much about running servers, so if possible explain things as fully and clearly as possible. Many thanks in advance!!!

User avatar
soumik
Registered User
Posts: 486
Joined: Tue Nov 06, 2007 7:27 pm
Contact:

Re: server crash imminent due to multiple registrations

Post by soumik » Sat Jan 01, 2011 2:20 pm

Click on ". _new (62)". Click on "Check All" at the bottom of the table list, and on the "With Selected:" drop down menu, select "Repair table".
Powered by chocolate

mike=).
Registered User
Posts: 19
Joined: Wed Dec 24, 2008 4:21 pm

Re: server crash imminent due to multiple registrations

Post by mike=). » Sat Jan 01, 2011 4:07 pm

thanks soumik!!!

Done and I can now login to the forum admin... thanks ))).
To decrease the server load... can I remove any files, databases, logs etc.. and how?

User avatar
soumik
Registered User
Posts: 486
Joined: Tue Nov 06, 2007 7:27 pm
Contact:

Re: server crash imminent due to multiple registrations

Post by soumik » Sat Jan 01, 2011 6:59 pm

You need to upgrade to the latest version and use a CAPTCHA that is hard to break.
Since you say you do not have any mods installed, you can do the following :

*NOTE: Before proceeding backup all files and the database.*
  • Upload all new files from a download of the full version, EXCEPT for config.php, the /images and /files folders.
  • Go to yoursite.com/forumfolder/install/database_update.php
  • Let that run and then delete the install folder.
Now, let the 3.0.8 version of the style you were using and install an updated Russian language pack.

And change the CAPTCHA plugin to Q&A (or anything other than the default GD/non-GD CAPTCHA).
Powered by chocolate

mike=).
Registered User
Posts: 19
Joined: Wed Dec 24, 2008 4:21 pm

Re: server crash imminent due to multiple registrations

Post by mike=). » Sun Jan 02, 2011 12:07 pm

Hello soumik !! The following actions are now completed:

All files and the database are backed up, locally and on remote (file type gzip, all tables selected).

phpBB "Olympus" 3.0.8 downloaded.

Uploaded to the site by ftp, excepting the directories 'images', 'files' and the file config.php .
http://www.oxanababy.com/forum/install/ ... update.php - all seems to be ok.
(There is a mention about updating the boardfiles, whatever that may mean).

I now get the following error: "./cache/ is NOT writable."

Thanks again for your help...

Image

{I checked the old ./cache folder.. it is full of data... the new folder is of course not BUT some of the text appears to be illegible, i.e. ""}.

User avatar
soumik
Registered User
Posts: 486
Joined: Tue Nov 06, 2007 7:27 pm
Contact:

Re: server crash imminent due to multiple registrations

Post by soumik » Sun Jan 02, 2011 2:17 pm

CHMOD the cache directory to 777.
{I checked the old ./cache folder.. it is full of data... the new folder is of course not BUT some of the text appears to be illegible, i.e. ""}.
What about new and old cache directories? You should have only one cache directory.
Powered by chocolate

Locked

Return to “[3.0.x] Support Forum”