Not a bug Xenforo Relation returning All rows from left table causing conversation messages to disappear

K a M a L

Member
I've encountered an odd issue today after I've moved one of my XF 2.1 Sites to a new server , after the move I got complaints from users that conversations aren't showing all messages , checking database I found all data is there
turning on debug mode I found
Code:
SELECT `xf_conversation_message`.*, `xf_user_User_1`.*, `xf_user_profile_Profile_2`.*, `xf_user_privacy_Privacy_3`.*, `xf_session_activity_Activity_4`.*, `xf_reaction_content_Reactions_5`.*
FROM `xf_conversation_message`
LEFT JOIN `xf_user` AS `xf_user_User_1` ON (`xf_user_User_1`.`user_id` = `xf_conversation_message`.`user_id`)
LEFT JOIN `xf_user_profile` AS `xf_user_profile_Profile_2` ON (`xf_user_profile_Profile_2`.`user_id` = `xf_user_User_1`.`user_id`)
LEFT JOIN `xf_user_privacy` AS `xf_user_privacy_Privacy_3` ON (`xf_user_privacy_Privacy_3`.`user_id` = `xf_user_User_1`.`user_id`)
LEFT JOIN `xf_session_activity` AS `xf_session_activity_Activity_4` ON (`xf_session_activity_Activity_4`.`user_id` = `xf_user_User_1`.`user_id` AND `xf_session_activity_Activity_4`.`unique_key` = CONCAT(`xf_user_User_1`.`user_id`, ''))
LEFT JOIN `xf_reaction_content` AS `xf_reaction_content_Reactions_5` ON (`xf_reaction_content_Reactions_5`.`content_type` = 'conversation_message' AND `xf_reaction_content_Reactions_5`.`content_id` = `xf_conversation_message`.`message_id` AND `xf_reaction_content_Reactions_5`.`reaction_user_id` = '1619')
WHERE (`xf_conversation_message`.`conversation_id` = 85754)
ORDER BY `xf_conversation_message`.`message_date` ASC

LIMIT 20
Explain Query Shows
Run Time: 0.079549
Select TypeTableTypePossible KeysKeyKey LenRefRowsExtra
SIMPLExf_conversation_messagerefconversation_id_message_dateconversation_id_message_date4const8Using temporary; Using filesort
SIMPLExf_user_User_1eq_refPRIMARYPRIMARY4quadraphonicquad_xf.xf_conversation_message.user_id1
SIMPLExf_user_profile_Profile_2eq_refPRIMARYPRIMARY4quadraphonicquad_xf.xf_user_User_1.user_id1
SIMPLExf_user_privacy_Privacy_3eq_refPRIMARYPRIMARY4quadraphonicquad_xf.xf_user_User_1.user_id1
SIMPLExf_session_activity_Activity_4ALL6290Using where; Using join buffer (Block Nested Loop)
SIMPLExf_reaction_content_Reactions_5eq_refcontent_type_id_user_id,content_type_id_reaction_datecontent_type_id_user_id35const,quadraphonicquad_xf.xf_conversation_message.message_id,const1Using where

  1. XF\Db\Mysqli\Statement->execute() in src/XF/Db/AbstractAdapter.php at line 94
  2. XF\Db\AbstractAdapter->query() in src/XF/Mvc/Entity/Finder.php at line 1294
  3. XF\Mvc\Entity\Finder->fetch() in src/XF/Pub/Controller/Conversation.php at line 184
  4. XF\Pub\Controller\Conversation->actionView() in src/XF/Mvc/Dispatcher.php at line 350
  5. XF\Mvc\Dispatcher->dispatchClass() in src/XF/Mvc/Dispatcher.php at line 257
  6. XF\Mvc\Dispatcher->dispatchFromMatch() in src/XF/Mvc/Dispatcher.php at line 113
  7. XF\Mvc\Dispatcher->dispatchLoop() in src/XF/Mvc/Dispatcher.php at line 55
  8. XF\Mvc\Dispatcher->run() in src/XF/App.php at line 2184
  9. XF\App->run() in src/XF.php at line 391
  10. XF::runApp() in index.php at line 20
I tried emptying xf_session_activity table that solved the issue for few minutes until the table was filled again
I temporarily fixed the issue by disabling \XF::eek:ptions()->showMessageOnlineStatus
This removed the session activity table join and solved the issue
 
Last edited:

Chris D

XenForo developer
Staff member
This would really require more troubleshooting. Left joins wouldn’t usually behave in that way - it shouldn’t matter if the data is there or not.

And I’m sure you acknowledge this isn't a known or widespread issue as this would be affecting many installs. We’ve not had any other reports of this and none of the things you’ve described would need to happen in normal usage in order for it to work properly.

I don’t have any immediate suggestions Though I think I’ve seen “(Block Nested Loop)” mentioned before and I vaguely recall that coming up in relation to some other MySQL issue - possibly a particular config or option that needs to be changed.
 

kick

Active member
I don’t have any immediate suggestions Though I think I’ve seen “(Block Nested Loop)” mentioned before and I vaguely recall that coming up in relation to some other MySQL issue - possibly a particular config or option that needs to be changed.
He is in the same gallery and still exists.
This add-on fixes more than half of the mess created by the engine. And reproduced with attitude and other entities
And not only with the gallery you can catch "Block Nested Loop"
 
Top