XF 2.1 Too many MySQL Processes "UPDATE ( SELECT content_id FROM xf_reaction_content WHERE content_type = ? AND reaction_"

[Recep Baltaş]

I have a bunch of mySQL processes that looks like they are stuck and this is causing a high load on the server. Any idea?

 

[Ozzy47]

Could be an addon, what's the full info line?

 

[Recep Baltaş]

This was all I could see. I believe this was due to user merge. We have decided to never merge users until a version without user merge problem comes out. Since 2.1, it is nearly impossible to perform it, sometimes stopping in the halfway process.

I've restarted mySQL to solve the issue.

 

[nocte]

I see this query on user deletion:

            UPDATE (
                SELECT content_id FROM xf_reaction_content
                WHERE content_type = ?
                AND reaction_user_id = ?
            ) AS temp
            INNER JOIN xf_post AS reaction_table ON (reaction_table.`post_id` = temp.content_id)
            SET reaction_table.`reaction_users` = REPLACE(reaction_table.`reaction_users`, ?, ?)

Sometimes it causes server errors:

• XF\Db\Exception: Job XF:UserDeleteCleanUp: MySQL query error [1205]: Lock wait timeout exceeded; try restarting transaction

The query comes form XF\Reaction\AbstractHandler::updateRecentCacheForUserChange() (wich may also run on user merge).