1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

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

Discussion in 'Troubleshooting and Problems' started by Puntocom, Jul 1, 2012.

  1. Puntocom

    Puntocom Well-Known Member

    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.
  2. Jake Bunce

    Jake Bunce XenForo Moderator Staff Member

    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)?
  3. Puntocom

    Puntocom Well-Known Member

    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
  4. Puntocom

    Puntocom Well-Known Member

    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?
  5. craigiri

    craigiri Well-Known Member

    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.
  6. Puntocom

    Puntocom Well-Known Member

    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.
  7. Jake Bunce

    Jake Bunce XenForo Moderator Staff Member

    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.

    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:


    I will look at this tomorrow.
  8. Puntocom

    Puntocom Well-Known Member

    Interesting. Thank you!

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

    Jake Bunce XenForo Moderator Staff Member

    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
    	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
    	content_id = VALUES(content_id);
    Puntocom likes this.
  10. MattW

    MattW Well-Known Member

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

    Puntocom Well-Known Member

    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:

Share This Page