News:

Wondering if this will always be free?  See why free is better.

Main Menu

Configuration > Notifications = Error

Started by a10, June 12, 2023, 02:18:42 PM

Previous topic - Next topic

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 ?
2.0.19, php 8.0.30, MariaDB 10.6.18. Mods: Contact Page, Like Posts, Responsive Curve, Search Focus Dropdown, Add Join Date to Post.
Stand with 🇺🇦

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:
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
A question worth asking is born in experience & driven by necessity. - Fripp

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.
2.0.19, php 8.0.30, MariaDB 10.6.18. Mods: Contact Page, Like Posts, Responsive Curve, Search Focus Dropdown, Add Join Date to Post.
Stand with 🇺🇦

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
2.0.19, php 8.0.30, MariaDB 10.6.18. Mods: Contact Page, Like Posts, Responsive Curve, Search Focus Dropdown, Add Join Date to Post.
Stand with 🇺🇦

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

A question worth asking is born in experience & driven by necessity. - Fripp

a10

2.0.19, php 8.0.30, MariaDB 10.6.18. Mods: Contact Page, Like Posts, Responsive Curve, Search Focus Dropdown, Add Join Date to Post.
Stand with 🇺🇦

shawnb61

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

Syntax will look something like:
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.
A question worth asking is born in experience & driven by necessity. - Fripp

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.
2.0.19, php 8.0.30, MariaDB 10.6.18. Mods: Contact Page, Like Posts, Responsive Curve, Search Focus Dropdown, Add Join Date to Post.
Stand with 🇺🇦

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.
2.0.19, php 8.0.30, MariaDB 10.6.18. Mods: Contact Page, Like Posts, Responsive Curve, Search Focus Dropdown, Add Join Date to Post.
Stand with 🇺🇦

shawnb61

The Profile-Modify.php line 3002 error?
A question worth asking is born in experience & driven by necessity. - Fripp

a10

^^^ yes, no change.

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

sending pm > Subs-Editor.php (Line 2344) (and emails not leaving queue)
2.0.19, php 8.0.30, MariaDB 10.6.18. Mods: Contact Page, Like Posts, Responsive Curve, Search Focus Dropdown, Add Join Date to Post.
Stand with 🇺🇦

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...
A question worth asking is born in experience & driven by necessity. - Fripp

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:
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:
ALTER TABLE smf_log_notify
ADD INDEX idx_id_board (id_board);
A question worth asking is born in experience & driven by necessity. - Fripp

a10

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
2.0.19, php 8.0.30, MariaDB 10.6.18. Mods: Contact Page, Like Posts, Responsive Curve, Search Focus Dropdown, Add Join Date to Post.
Stand with 🇺🇦

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);

2.0.19, php 8.0.30, MariaDB 10.6.18. Mods: Contact Page, Like Posts, Responsive Curve, Search Focus Dropdown, Add Join Date to Post.
Stand with 🇺🇦

shawnb61

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

How many rows in the themes table?
A question worth asking is born in experience & driven by necessity. - Fripp

a10

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
2.0.19, php 8.0.30, MariaDB 10.6.18. Mods: Contact Page, Like Posts, Responsive Curve, Search Focus Dropdown, Add Join Date to Post.
Stand with 🇺🇦

shawnb61

A question worth asking is born in experience & driven by necessity. - Fripp

a10

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?


2.0.19, php 8.0.30, MariaDB 10.6.18. Mods: Contact Page, Like Posts, Responsive Curve, Search Focus Dropdown, Add Join Date to Post.
Stand with 🇺🇦

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?
2.0.19, php 8.0.30, MariaDB 10.6.18. Mods: Contact Page, Like Posts, Responsive Curve, Search Focus Dropdown, Add Join Date to Post.
Stand with 🇺🇦

Advertisement: