Error code: MAX_JOIN_SIZE

[Aernout86]

Best,

I would like your advice with the next problem.

Many years ago we were two coin and banknote collectors who created a forum to discover the variants of the coins and banknotes of Belgium and Belgian Congo.

The roles were very well defined. One person did everything regarding the IT management of the web hosting and the forum. While I was concerned with the layout and content of the forum.

However, due to family/medical reasons, the other person can no longer be involved or provide advice on problems...

Unfortunately, I have to report that my IT knowledge is too limited to really make major adjustments or updates.
I cannot solve problems due to my lack of IT knowledge...

I have two questions here:
1) Is there a free manual course or webinar here somewhere, where the operation of the whole thing can be explained simply. It is especially important to be able to do updates and other essential things that need to be done...
2) The specific problem we are having is that some members, certainly not all, are no longer accessing the forum and are getting this message. Message: 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

Unfortunately I have no idea what to do nor where to start looking for the solution. For the time being I have been able to solve this by restoring yesterday's backup, now it works again, but I have no idea what is causing this as I have not changed anything in the settings.

Thanks in advance for your own suggestions

With kind regards,
Aernout86

[Doug Heffernan]

This isn't a bug. What is your Smf version so that this topic can be moved to the correct board?

[Kindred]

Url?

Mods installed?

How many users, how many messages?

What were the users doing when they got that message?

[Aernout86]

This isn't a bug. What is your Smf version so that this topic can be moved to the correct board?

Hi, Thanks:

Versie-informatie:
Forumversie: SMF 2.0.18
Huidige SMF-versie: SMF 2.0.19

mvg, Aernout86

[Aernout86]

Url?

Mods installed?

How many users, how many messages?

What were the users doing when they got that message?

Url? => hxxp:www.cbnu.be/forum/index.php [nonactive]

Mods installed? => ?

How many users, how many messages? => 255 users / 14904 messages in 5810 Topics

What were the users doing when they got that message? => Trying to enter the forum

thanks.

mvg, Aernout

[Sir Osis of Liver]

Assuming you have admin access:

- Go to Admin > Support and Credits and copy Version Information and post it here.

- Look in Admin > Error log and post the complete error message here.

- Go to Admin > Package Manager > Installed Packages and copy contents here.


Can a mod move this to 2.0 support?

[Doug Heffernan]

Hi, Thanks:

Versie-informatie:
Forumversie: SMF 2.0.18
Huidige SMF-versie: SMF 2.0.19

mvg, Aernout86

No problem. I moved this to the correct board.

Regarding the error, was there any particular code and file name mentioned?

[shawnb61]

This forum is not large... 

You might see this error if an index is missing, even on a small forum. 

Also - we have found some hosts have introduced relatively low values for MAX_JOIN_SIZE.

To figure out what the value is, run this query:

Code Select Expand

SHOW VARIABLES LIKE 'MAX_JOIN_SIZE';
If MAX_JOIN_SIZE has been overwritten, and all indexes are present, the specific query may need to be tuned; it may need a custom index.  We're seeing this on 2.1 with a key notification query for some users with large forums where the host set MAX_JOIN_SIZE a bit low. 

[Aernout86]

Assuming you have admin access:

- Go to Admin > Support and Credits and copy Version Information and post it here.

- Look in Admin > Error log and post the complete error message here.

- Go to Admin > Package Manager > Installed Packages and copy contents here.


Can a mod move this to 2.0 support?

- Go to Admin > Support and Credits and copy Version Information and post it here.

Versie-informatie:
Forumversie: SMF 2.0.18 (meer details)
Huidige SMF-versie: SMF 2.0.19
GD-versie: 2.3.0
Databaseserver: MariaDB
MySQL-versie: 10.6.14-MariaDB-1:10.6.14+maria~ubu2204
PHP: 8.0.30
Serverversie: Apache

- Look in Admin > Error log and post the complete error message here.

I suspect there are no errors in the log because I have to restore a backup to get back to the forum.

The error log:
Filter op type:  Alle fouten (0)
  Pagina's: [1]
Er zijn momenteel geen ingangen in het foutenlog.
  Pagina's: [1]

- Go to Admin > Package Manager > Installed Packages and copy contents here.

Bekijken en verwijderen van geïnstalleerde pakketten
Er zijn momenteel geen modificaties geïnstalleerd...

Thank you in advance for your help! I appreciate it !

mvg,
Aernout

[Aernout86]

This forum is not large... 

You might see this error if an index is missing, even on a small forum. 

Also - we have found some hosts have introduced relatively low values for MAX_JOIN_SIZE.

To figure out what the value is, run this query:

Code Select Expand

SHOW VARIABLES LIKE 'MAX_JOIN_SIZE';
If MAX_JOIN_SIZE has been overwritten, and all indexes are present, the specific query may need to be tuned; it may need a custom index.  We're seeing this on 2.1 with a key notification query for some users with large forums where the host set MAX_JOIN_SIZE a bit low. 

This is the result:
max_join_size  67108864

mvg, Aernout86

[Aernout86]

Some time has past...

Anyone an idee? 

gr,
Aernout

[Steve]

Moving this up so it doesn't get lost in the shuffle for our Support coders.

[shawnb61]

We never received the full error message.  We don't know which query is causing the issue.

If the error is still occurring, I'd run the db compare utility to see if any indexes are missing:
https://github.com/sbulen/sjrbTools/blob/master/smf_db_compare.php

This will list all tables, columns, and indexes in the db, and highlight differences from the vanilla db.

Items flagged green and yellow were added or changed, likely by a mod.  Usually ok.

Items in red are missing, and usually that's bad.  If an index is missing, that could explain the error.

[Aernout86]

Thank you for your message.
It took me quite a while to figure out how to do what was requested.
In the end, I had to ask for some help from a few ICT professionals, as I couldn't manage it on my own. Now that I've seen it, things are much clearer 😊

As requested, I ran the program, and one red line appeared, attachment 1


Additionally, we upgraded the version together from 2.0.18 to 2.0.19.
However, I wanted to ask why there is a difference in the versions, attachment 2


I can never consult an error log because when the error occurs, the screen goes completely white with the text. The only way to restore the forum is to use the backup from the previous day, but unfortunately, no error logs are visible then.

Thank you in advance for your advice.

Best regards,
Aernout

[Kindred]

how did you upgrade to 2.0.19?

[a10]

https://www.simplemachines.org/community/index.php?topic=590209.0

Same host, same error.

[Aernout86]

how did you upgrade to 2.0.19?

"More details can be found under 'support information.' I arrived at the following screen and clicked on a shortcut to upgrade from 2.0.18 to 2.0.19. I did this, but the other tasks were not completed. See the detailed photos. Thank you!"

[Kindred]

It looks like the upgrade did not complete,  especially in the sources directory...

You may have to apply a clean as set of files and then reinstall mods