Configuration > Notifications = Error

a10 · June 12, 2023, 02:18:42 PM

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 · June 12, 2023, 04:06:59 PM

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

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 · June 12, 2023, 04:32:52 PM

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 · June 12, 2023, 04:45:09 PM

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 · June 12, 2023, 05:05:46 PM

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 · June 12, 2023, 05:16:13 PM

Nice tool :O)

1 red

shawnb61 · June 12, 2023, 05:32:03 PM

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

Syntax will look something like:

Code Select

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 · June 12, 2023, 05:35:59 PM

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 · June 12, 2023, 05:57:26 PM

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 · June 12, 2023, 06:08:07 PM

The Profile-Modify.php line 3002 error?

a10 · June 12, 2023, 06:29:18 PM

^^^ yes, no change.

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

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

shawnb61 · June 12, 2023, 10:43:16 PM

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 · June 12, 2023, 11:31:18 PM

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

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

ALTER TABLE smf_log_notify
ADD INDEX idx_id_board (id_board);

a10 · June 13, 2023, 10:43:41 AM

Quote from: shawnb61 on June 12, 2023, 10:43:16 PMA 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 · June 13, 2023, 10:49:26 AM

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 · June 13, 2023, 03:34:27 PM

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

How many rows in the themes table?

a10 · June 13, 2023, 05:29:42 PM

Quote from: shawnb61 on June 13, 2023, 03:34:27 PMI think that themes table needs to be repaired...

How many rows in the themes table?

6,254 rows

shawnb61 · June 13, 2023, 05:36:14 PM

Did you try repairing the table?

a10 · June 13, 2023, 05:54:38 PM

Quote from: shawnb61 on June 13, 2023, 05:36:14 PMDid 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 · June 13, 2023, 06:24:21 PM

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?

News:

Configuration > Notifications = Error