Invision Community 4: SEO, prepare for v5 and dormant account notifications Matt November 11, 2024Nov 11
Posted January 27Jan 27 Hello, I have one user who can not access/view his own profile. Neither can he view “unread content” when clicking this link in the header. I’ve checked for misconfigurations etc. but can’t find anything wrong. I’m on the latest beta for version 5. He’s getting error code EX1104 when trying to access his own profile or trying the “unread content” link. Any suggestions?
January 27Jan 27 10 minutes ago, Jan Reidar Storelvmo said:He’s getting error code EX1104 when trying to access his own profile or trying the “unread content” link.What is the full error message?
January 27Jan 27 You’ll want to look in ACP → Support → System Log to find the resulting error message.
January 28Jan 28 Just a quick check on that indicates it might be your mysql configuration, and the MAX_JOIN_SIZE config item
January 28Jan 28 Author Sure, I’ve copy/pasted the recurring ones into a text file (from today):Recurring_errors
January 28Jan 28 Author SELECT class_type_id_hash, total_rep FROM ( SELECT class_type_id_hash, SUM(rep_rating) as total_rep FROM core_reputation_index AS x WHERE rep_date BETWEEN 1735340400 AND 1738074770 AND ( rep_class IN('IPS\\calendar\\Event','IPS\\calendar\\Event\\Comment','IPS\\gallery\\Image','IPS\\gallery\\Image\\Comment','IPS\\gallery\\Album\\Item','IPS\\gallery\\Album\\Comment','IPS\\cms\\Records1','IPS\\cms\\Records\\Comment1','IPS\\downloads\\File','IPS\\downloads\\File\\Comment','IPS\\downloads\\File\\Review','IPS\\blog\\Entry','IPS\\blog\\Entry\\Comment','IPS\\forums\\Topic\\Post') ) GROUP BY class_type_id_hash ) AS x ORDER BY x.total_rep desc LIMIT 0,500IPS\Db\Exception: The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET MAX_JOIN_SIZE=# if the SELECT is okay (1104)#0 /customers/8/e/6/cqxhtznpz/webroots/r1169389/system/Db/Select.php(402): IPS\Db->preparedQuery('/*cqxhtznpz_db1...', Array, true)#1 /customers/8/e/6/cqxhtznpz/webroots/r1169389/system/Db/Select.php(300): IPS\Db\Select->runQuery()#2 /customers/8/e/6/cqxhtznpz/webroots/r1169389/applications/core/modules/front/discover/popular.php(405): IPS\Db\Select->setKeyField('class_type_id_h...')#3 /customers/8/e/6/cqxhtznpz/webroots/r1169389/applications/core/modules/front/discover/popular.php(96): IPS\core\modules\front\discover\popular->leaderboard()#4 /customers/8/e/6/cqxhtznpz/webroots/r1169389/system/Dispatcher/Dispatcher.php(169): IPS\core\modules\front\discover\popular->execute()#5 /customers/8/e/6/cqxhtznpz/webroots/r1169389/index.php(16): IPS\Dispatcher->run()#6 {main}------------------------------------------------SELECT forums_topics.*, author.*, last_commenter.*, core_tags_cache.tag_cache_text FROM forums_topics LEFT JOIN forums_forums ON forums_topics.forum_id=forums_forums.id LEFT JOIN core_tags_cache ON tag_cache_key=MD5(CONCAT('forums;forums;',forums_topics.tid)) LEFT JOIN core_members AS author ON author.member_id = forums_topics.starter_id LEFT JOIN core_members AS last_commenter ON last_commenter.member_id = forums_topics.last_poster_id WHERE tid IN(83918,84133,84263,84276,84284,84435,87812,88179,88379,88495,88613,88820) AND ( forums_forums.can_view_others=1 OR forums_topics.starter_id=5370 ) AND ( forums_forums.password IS NULL OR ( FIND_IN_SET(3,forums_forums.password_override) OR FIND_IN_SET(12,forums_forums.password_override) ) ) AND ( forums_topics.approved=1 OR ( forums_topics.approved=0 AND ( forums_topics.starter_id=5370 ) ) ) AND ( forums_topics.is_future_entry=0 OR ( forums_topics.is_future_entry=1 AND forums_topics.starter_id=5370 ) ) AND ( NULLIF(forums_topics.moved_to, '') IS NULL ) AND forums_topics.forum_id NOT IN (11,6,3,13,2,5,14,7,59,66,15,32,31,25,45,44,40,36,43,42,39,26,18,9,10,12) ORDER BY forums_topics.start_date DESCIPS\Db\Exception: The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET MAX_JOIN_SIZE=# if the SELECT is okay (1104)#0 /customers/8/e/6/cqxhtznpz/webroots/r1169389/system/Db/Select.php(402): IPS\Db->preparedQuery('/*cqxhtznpz_db1...', Array, true)#1 /customers/8/e/6/cqxhtznpz/webroots/r1169389/system/Db/Select.php(460): IPS\Db\Select->runQuery()#2 [internal function]: IPS\Db\Select->rewind()#3 /customers/8/e/6/cqxhtznpz/webroots/r1169389/system/Content/Solvable.php(182): IteratorIterator->rewind()#4 /customers/8/e/6/cqxhtznpz/webroots/r1169389/system/Content/Search/Results.php(215): IPS\forums\Topic::searchResultExtraData(Array)#5 /customers/8/e/6/cqxhtznpz/webroots/r1169389/applications/core/modules/front/members/profile.php(262): IPS\Content\Search\Results->init()#6 /customers/8/e/6/cqxhtznpz/webroots/r1169389/system/Dispatcher/Controller.php(139): IPS\core\modules\front\members\profile->manage()#7 /customers/8/e/6/cqxhtznpz/webroots/r1169389/applications/core/modules/front/members/profile.php(130): IPS\Dispatcher\Controller->execute()#8 /customers/8/e/6/cqxhtznpz/webroots/r1169389/system/Dispatcher/Dispatcher.php(169): IPS\core\modules\front\members\profile->execute()#9 /customers/8/e/6/cqxhtznpz/webroots/r1169389/index.php(16): IPS\Dispatcher->run()#10 {main}----------------------------------------------------SELECT themember, rep FROM (SELECT core_reputation_index.member_received as themember, SUM(rep_rating) as rep FROM core_reputation_index WHERE member_received>0 AND ( rep_class IN('IPS\\calendar\\Event','IPS\\calendar\\Event\\Comment','IPS\\gallery\\Image','IPS\\gallery\\Image\\Comment','IPS\\gallery\\Album\\Item','IPS\\gallery\\Album\\Comment','IPS\\cms\\Records1','IPS\\cms\\Records\\Comment1','IPS\\downloads\\File','IPS\\downloads\\File\\Comment','IPS\\downloads\\File\\Review','IPS\\blog\\Entry','IPS\\blog\\Entry\\Comment','IPS\\forums\\Topic\\Post') ) and rep_date BETWEEN 1735340400 AND 1738071032 GROUP BY themember) AS in ORDER BY rep DESC LIMIT 4IPS\Db\Exception: The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET MAX_JOIN_SIZE=# if the SELECT is okay (1104)#0 /customers/8/e/6/cqxhtznpz/webroots/r1169389/system/Db/Select.php(402): IPS\Db->preparedQuery('/*cqxhtznpz_db1...', Array, true)#1 /customers/8/e/6/cqxhtznpz/webroots/r1169389/system/Db/Select.php(300): IPS\Db\Select->runQuery()#2 /customers/8/e/6/cqxhtznpz/webroots/r1169389/applications/core/modules/front/discover/popular.php(483): IPS\Db\Select->setKeyField('themember')#3 /customers/8/e/6/cqxhtznpz/webroots/r1169389/applications/core/modules/front/discover/popular.php(96): IPS\core\modules\front\discover\popular->leaderboard()#4 /customers/8/e/6/cqxhtznpz/webroots/r1169389/system/Dispatcher/Dispatcher.php(169): IPS\core\modules\front\discover\popular->execute()#5 /customers/8/e/6/cqxhtznpz/webroots/r1169389/index.php(16): IPS\Dispatcher->run()#6 {main}-----------------------------------------------------
January 28Jan 28 The error there is what I mentioned above, and appears to point to your mysql configuration
January 28Jan 28 31 minutes ago, Jan Reidar Storelvmo said:The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET MAX_JOIN_SIZE=# if the SELECT is okay (1104)Can you set the SQL_BIG_SELECTS=1 and see if it would help? Doing that will cause the value for MAX_JOIN_SIZE to be ignored.
January 28Jan 28 Author Thanks for the suggestion. Looks like I don't have access to run this myself so I will have to contact my hosting provider. Will report back.
February 1Feb 1 Author Update: I've been in contact with my hosting provider and they were not willing to let me use SET SQL_BIG_SELECTS=1. They suggested I contact you for a different solution. Do you have any other suggestions on how to fix this issue?
February 1Feb 1 Either your hosting has a really low value for MAX_JOIN_SIZE or your reputation table is extremely big. It seems shared hostings (?) use this a lot to limit long queries, but it's the first time I have ever seen this error.Leaving SQL_BIG_SELECTS aside, if your hosting is unwilling to raise the MAX_JOIN_SIZE value, your only real option is to change hosting. 🤷♂️ Edited February 1Feb 1 by teraßyte
February 2Feb 2 Author Just checked the value for MAX_JOIN_SIZE which is set to 67108864 if this makes any sense.
February 2Feb 2 Author SELECT class_type_id_hash, total_rep FROM ( SELECT class_type_id_hash, SUM(rep_rating) as total_rep FROM core_reputation_index AS x WHERE rep_date BETWEEN 1735340400 AND 1738074770 AND ( rep_class IN('IPS\\calendar\\Event','IPS\\calendar\\Event\\Comment','IPS\\gallery\\Image','IPS\\gallery\\Image\\Comment','IPS\\gallery\\Album\\Item','IPS\\gallery\\Album\\Comment','IPS\\cms\\Records1','IPS\\cms\\Records\\Comment1','IPS\\downloads\\File','IPS\\downloads\\File\\Comment','IPS\\downloads\\File\\Review','IPS\\blog\\Entry','IPS\\blog\\Entry\\Comment','IPS\\forums\\Topic\\Post') ) GROUP BY class_type_id_hash ) AS x ORDER BY x.total_rep desc LIMIT 0,500IPS\Db\Exception: The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET MAX_JOIN_SIZE=# if the SELECT is okay (1104)#0 /customers/8/e/6/cqxhtznpz/webroots/r1169389/system/Db/Select.php(402): IPS\Db->preparedQuery('/*cqxhtznpz_db1...', Array, true)#1 /customers/8/e/6/cqxhtznpz/webroots/r1169389/system/Db/Select.php(300): IPS\Db\Select->runQuery()#2 /customers/8/e/6/cqxhtznpz/webroots/r1169389/applications/core/modules/front/discover/popular.php(405): IPS\Db\Select->setKeyField('class_type_id_h...')#3 /customers/8/e/6/cqxhtznpz/webroots/r1169389/applications/core/modules/front/discover/popular.php(96): IPS\core\modules\front\discover\popular->leaderboard()#4 /customers/8/e/6/cqxhtznpz/webroots/r1169389/system/Dispatcher/Dispatcher.php(169): IPS\core\modules\front\discover\popular->execute()#5 /customers/8/e/6/cqxhtznpz/webroots/r1169389/index.php(16): IPS\Dispatcher->run()#6 {main}Been looking through some of the errors I'm getting and the date in the beginning of the SQL query does seem a bit weird. What does it refer to? (see bold text in query).
February 2Feb 2 Been looking through some of the errors I'm getting and the date in the beginning of the SQL query does seem a bit weird. What does it refer to? (see bold text in query).It's unix epoch, nothing weird ;)
February 2Feb 2 IPS\Db\Exception: The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET MAX_JOIN_SIZE=# if the SELECT is okay (1104)If you're hosting provider isn't helping you resolve the issue, the only option is to change hosting as you've outgrown them.
February 5Feb 5 Author Update:The issues I had seem to be solved now after my hosting company came up with a fix/walkaround.