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

[K a M a L]

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

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 Type	Table	Type	Possible Keys	Key	Key Len	Ref	Rows	Extra
SIMPLE	xf_conversation_message	ref	conversation_id_message_date	conversation_id_message_date	4	const	8	Using temporary; Using filesort
SIMPLE	xf_user_User_1	eq_ref	PRIMARY	PRIMARY	4	quadraphonicquad_xf.xf_conversation_message.user_id	1
SIMPLE	xf_user_profile_Profile_2	eq_ref	PRIMARY	PRIMARY	4	quadraphonicquad_xf.xf_user_User_1.user_id	1
SIMPLE	xf_user_privacy_Privacy_3	eq_ref	PRIMARY	PRIMARY	4	quadraphonicquad_xf.xf_user_User_1.user_id	1
SIMPLE	xf_session_activity_Activity_4	ALL					6290	Using where; Using join buffer (Block Nested Loop)
SIMPLE	xf_reaction_content_Reactions_5	eq_ref	content_type_id_user_id,content_type_id_reaction_date	content_type_id_user_id	35	const,quadraphonicquad_xf.xf_conversation_message.message_id,const	1	Using 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:ptions()->showMessageOnlineStatus
This removed the session activity table join and solved the issue

 

[Chris D]

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.

 

[K a M a L]

@Chris , I'm sorry for bothering .. I just found the issue that xf_session_activity table has no unique key , seems it is not related to the software itself .. feel free to close this one

 

[kick]

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.

Implemented - XF Finder join index hints

The XenForo finder system allows index hints applied to the 'from' clause, but not when doing joins. ie; select * from a join b as c use index(d) on c.id = a.id This is desirable as at some optimization levels recent MySQL version do not correctly select the right index (or ignore it...

xenforo.com

This add-on fixes more than half of the mess created by the engine. And reproduced with attitude and other entities

Optimized List Queries by Xon

This add-on is aimed at large forum with tens of thousands to hundreds of thousands or more threads in a single forum. Smaller forum do not gain much benefit from this add-on. Member List/Forum/Conversation/Reactions/XFMG lists: MySQL/MariaDB...

xenforo.com

And not only with the gallery you can catch "Block Nested Loop"