Configuration > Notifications = Error

[a10]

2.1.4

Upon clicking Notifications, error appears immediately.

https://www.*/forum/index.php?action=admin;area=featuresettings;sa=alerts;aa0c66092d=1e63a70ae405f105f63d87b8569e71c2
/customers/*.*/httpd.www/forum/Sources/Profile-Modify.php (Line 3002)

Database Error: 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

What more info should I provide ?

[shawnb61]

Two questions & a theory...

What is the query around line 3002 in your source?  The query ~line 3002 in vanilla 2.1.4 doesn't make sense for that issue, I suspect the source was modified.  (I want to confirm the query you are having issues with is this one: https://github.com/SimpleMachines/SMF/blob/4ce67fd23cda6f4ad34f2c565048d5ff5a7ce9e6/Sources/Profile-Modify.php#L2952)

What is your MAX_JOIN_SIZE setting? Run this query:

Code Select Expand

SHOW VARIABLES LIKE 'MAX_JOIN_SIZE'

My theory is that your host has a relatively low MAX_JOIN_SIZE set.  Not a huge problem normally, but given that query likely needs tuning, you have become the canary in the coal mine...  I believe a quick solution would be to add an index to the smf_log_notify table on id_board.  More here:
https://github.com/SimpleMachines/SMF/issues/7661

[a10]

max_join_size    67108864

If it matters in any way, forum not a clean install.
Upgrade from 2.0.19 > 2.1.3 > 2.1.4, no errors in any upgrade process.

[a10]

And a new one, immediately after clicking 'Send' in PM.
Also, mails seems stuck until clicking 'send mq now'. Then arrives fine at destination.

https://www.*/forum/index.php?action=suggest;suggest_type=member;search=*;be9500dba=eca795e37d99e6276bf3814e7a682a9a;xml;time=1686602119824
/customers/0/*/httpd.www/forum/Sources/Subs-Editor.php (Line 2344)
Backtrace information


Database Error: 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

[shawnb61]

Hmmm... So it's not the query I was thinking of.  But those symptoms point to something a little broader.  Like some of your indexes are missing. 

I would look very closely at all of the indexes on all the key tables used in those queries (members, log_notify, etc.).  And make sure they match what would be on a vanilla forum. 

To do the compare, it may help to download this utility.  Right click, download it, then copy it to your forum's root folder.  Then run it. 

Share anything missing - in red.

https://raw.githubusercontent.com/sbulen/sjrbTools/master/smf_db_compare.php

[a10]

Nice tool :O)

1 red

[shawnb61]

Oh...  You're gonna need to add that. 

Syntax will look something like:

Code Select Expand

ALTER TABLE smf_boards
ADD INDEX idx_member_groups (member_groups(48));
Run the utility afterwards & see if all the red went away.  Then retest.

Delete the utility when done.

[a10]

Ran the compare tool on the 2.0.19 that became 2.1.4

2x red, one was the same as in post above if that could matter.

[a10]

No reds after the ALTER TABLE.
But "Database Error: The SELECT would examine etc" appears exactly as reported before.

btw, it's a 2.1.4 upgrade test on a clone of the production 2.0.19.
Can install a clean set of files, or if useful continue testing things.

[shawnb61]

The Profile-Modify.php line 3002 error?

[a10]

^^^ yes, no change.

clicking Notifications > Profile-Modify.php (Line 3002)

sending pm > Subs-Editor.php (Line 2344) (and emails not leaving queue)

[shawnb61]

A couple of quick questions, before diving in too deep on individual queries...

Do all of your tables have the same collation now?
Do all of your tables have the same engine (ISAM/InnoDB) now?

Spanning engines/collations can impact queries.  Usually not this bad, but just checking...

[shawnb61]

Focusing on the line 3002 issue for now...  I really don't think the query on line 3002 can possibly result with a join issue...  But just to check, try a query like this:

Code Select Expand

SELECT id_member, variable, value
FROM smf_themes
WHERE id_theme IN (1, 2, 3, 4, 5)
AND id_member IN (-1, 1, 2, 3, 4, 5)
I suspect we're getting thrown a curve somehow, and it's actually the prior query having an issue.  To confirm/deny, try adding an index on id_board on smf_log_notify and retest:

Code Select Expand

ALTER TABLE smf_log_notify
ADD INDEX idx_id_board (id_board);


[a10]

A couple of quick questions, before diving in too deep on individual queries...

Do all of your tables have the same collation now?
Do all of your tables have the same engine (ISAM/InnoDB) now?

Spanning engines/collations can impact queries.  Usually not this bad, but just checking...

utf8_general_ci  ok
MyISAM \ InnoDB ok

[a10]

SELECT id_member, variable, value
FROM smf_themes
WHERE id_theme IN (1, 2, 3, 4, 5)
AND id_member IN (-1, 1, 2, 3, 4, 5) LIMIT 0, 25

MySQL said: Documentation
#1104 - 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

------------------

 MySQL returned an empty result set (i.e. zero rows). (Query took 0.0160 seconds.)
ALTER TABLE smf_log_notify ADD INDEX idx_id_board (id_board);

[shawnb61]

I think that themes table needs to be repaired...

How many rows in the themes table?

[a10]

I think that themes table needs to be repaired...

How many rows in the themes table?

6,254 rows

[shawnb61]

Did you try repairing the table?

[a10]

Did you try repairing the table?

Now done. Lo & behold! No "Line 3002" error, Configuration > Notifications opens fine & accepts\saves settings.
Consider this fixed ?

Shall we have a look at PM send \ mail queue (#3), or is that a new topic?

[a10]

Regarding PM send.

Send pm to my own account > no error !?
Send pm to any other member:
index.php?action=suggest;suggest_type=member;search=a10;ac1b0769f2=941177e4628c06e8b840aa5ff6f13e2b;xml;time=1686693957447
/customers/*/httpd.www/forum/Sources/Subs-Editor.php (Line 2344)
+ the "Database Error: The SELECT would examine more etc"

Remark, all errors resulting from PM send shows the same "search=a10;ac1b0769f2=941177e4628c06e8b840aa5ff6f13e2b;" present whoever the pm was adressed to, something stuck?