Troll invasion in my forum, now it doesn't work ok

[Puntocom]

Hello, I have hundred of trolls in my site since some time. I realized this too late! so yesterday I started using the Spam cleaner to delete some users and I also removed tons off-topic threads and posts. They are following each other so I think this may be causing problems.

Now I have problems even trying to delete a simple thread or message; when I try to use the spam cleaner I get this:

Zend_Db_Statement_Mysqli_Exception: Mysqli statement execute error : Query execution was interrupted - library/Zend/Db/Statement/Mysqli.php:214

I tried to modify the my.cnf file without success. I'm running XenForo 1.1.2 with no plugins activated, no outdated templates. It happens in several browsers (chrome, firefox) in GNU/Linux and Windows 7.

I can't even rebuild the user cache, it gets stuck at this: An error occurred or the request was stopped.

I tried to restart mysql and the server and it doesn't help.

I'm desesperated. Please, could you help me?

--- UPDATE

finally I managed to rebuild caches one by one.

 

[Jake Bunce]

That is due to a timeout in your MySQL connector. In short, contact your host or server person to increase the timeout, or try upgrading PHP and/or the MySQL client library in PHP.

You may also want to look at optimizing your server configuration. How long does the process run before that error comes up? What kind of server (shared, VPS, dedicated)?

 

[Puntocom]

Thank you. I'm running a VPS, I have just upgraded the php5-mysql and php5-mysqli but I have the same problem.

It takes 32 seconds, I have tried with an user that has 655 messages and another one with 1335.

I set up in my.cnf innodb_lock_wait_timeout = 1000 and innodb_lock_wait_timeout = 50000, the result is the same. I'll look about optimization, I'm trying with http://mysqltuner.pl/mysqltuner.pl , I'll post the results (and ideally the solution) later

 

[Puntocom]

I made a mysqlcheck -o on the database as mysqltune recommended and I have increased innodb_lock_wait_timeout to 1000 (I also tried more values), restarted mysql but I have the same problem.

Any idea?

 

[craigiri]

Jake would know better, but perhaps you can delete the messages from one user (the big users) direct from phpmyadmin using their user ID? And then after, delete the users from XF???

I have never, even on my old forum, had to delete a user with that many messages. The real answer here is to start using manual activation and QA captcha and make sure you don't let spammers ruin your board.

 

[Puntocom]

I think it would cause inconsistencies in the db. I can't also delete several messages at once sometimes :S. After mysql restart I'm able to delete them.

They are not spam bots, they are human trolls. I have closed the register and now I manually add new users.

 

[Jake Bunce]

I made a mysqlcheck -o on the database as mysqltune recommended and I have increased innodb_lock_wait_timeout to 1000 (I also tried more values), restarted mysql but I have the same problem.

Any idea?

Same error? Query execution was interrupted? That means the timeout is still interfering. innodb_lock_wait_timeout is not the correct setting for this error. Based on information I found on Google this error has to do with the MySQL client library in PHP, not the MySQL server itself. But I am not sure of exactly what to edit where in this case.

Jake would know better, but perhaps you can delete the messages from one user (the big users) direct from phpmyadmin using their user ID? And then after, delete the users from XF???

That's possible. A soft deletion is simply a flag for each thread / post, as well as a record in the deletion log, similar to this:

http://xenforo.com/community/resources/soft-delete-all-moderated-posts-queries.371/

I will look at this tomorrow.

 

[Puntocom]

Interesting. Thank you!

Could you please help me with the SQL query to soft-delete all threads & messages from an user?

 

[Jake Bunce]

Try these queries. The red is the user_id of the user whose posts and threads you want to soft delete. The blue is the deletion info (user_id and username of the user who is doing the deleting, as well as a stated reason for the log). Backup first:

UPDATE xf_post
SET message_state = 'deleted'
WHERE message_state = 'visible'
AND user_id = 2;

INSERT INTO xf_deletion_log (content_type, content_id, delete_date, delete_user_id, delete_username, delete_reason)
	SELECT 'post', post_id, UNIX_TIMESTAMP(), 1, 'admin', 'manually deleted via query'
	FROM xf_post
	WHERE message_state = 'deleted'
	AND user_id = 2
ON DUPLICATE KEY UPDATE
	content_id = VALUES(content_id);

UPDATE xf_thread
SET discussion_state = 'deleted'
WHERE discussion_state = 'visible'
AND user_id = 2;

INSERT INTO xf_deletion_log (content_type, content_id, delete_date, delete_user_id, delete_username, delete_reason)
	SELECT 'thread', thread_id, UNIX_TIMESTAMP(), 1, 'admin', 'manually deleted via query'
	FROM xf_thread
	WHERE discussion_state = 'deleted'
	AND user_id = 2
ON DUPLICATE KEY UPDATE
	content_id = VALUES(content_id);

 

[MattW]

What is the max_execution_time value in your php.ini file?

 

[Puntocom]

It works nicely! thank you very much, Jake! can I safely apply this to my forum?

MattW: It was 30, I changed it to 600. It does the same.

One thing that worries me is that I can't rebuild caches. I hope to get this solved when I upgrade to XF 1.3.

--> UPDATE: I managed to rebuild caches using these mysql settings:

[mysqld]
innodb_additional_mem_pool_size=16M
innodb_buffer_pool_size=800M
innodb_file_per_table=1
innodb_log_buffer_size=4M
innodb_flush_log_at_trx_commit=2
myisam_sort_buffer_size=16M
expire_logs_days=7
query_cache_size=32M
thread_cache_size=16K
max_allowed_packet=16M
local-infile=0
table_open_cache=2K
table_definition_cache=3K
open_files_limit=2K
max_connections=25
wait_timeout=60
interactive_timeout=60
connect_timeout=10
read_buffer_size=1M
slow_query_log=1
slow_query_log_file="/var/log/slow_queries.log"
sort_buffer_size=1M
join_buffer_size=1M
key_buffer_size=32M
query_cache_size=128M
query_cache_limit=4M
query_cache_type=1
long_query_time=5
tmp_table_size=128M
max_heap_table_size=128M