Strange error for 1 member, Modify Profile

[a10]

Hello, everything in 'Modify Profile' for 1 single member (all the others ok) returns this error. The other menus OK. Any clues ? See attach. BTW, it's the newest registration. Will try with registering a test account and see if it's stuck for any new reg. No change in php etc.

MySQL version: 10.6.18-MariaDB-ubu2204
PHP: 8.0.30

Thanks

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
File: /* forum/Sources/Profile-Modify.php
Line: 2186

Note: It appears that your database may require an upgrade etc.

[Aleksi "Lex" Kilpinen]

It sounds like you've hit a MySQL limit. If you can't change MySQL config yourself, you might need to ask your host about this.

[a10]

Registered a freash test account, everything works. So seems definitely to be one single account hit by this... !??

Anything the member could have entered in the profile causing some runaway ?
Like diesel :O) https://www.youtube.com/watch?v=PdSK8tYyWZo

[Doug Heffernan]

You have gotten this type of error before as well. Some of the advice given at your other topic apply for this situation too:

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

On a side note, it would be best to upgrade your forum to the latest version. If you can't, or don't want to upgrade to 2.1.4., then you should consider to upgrade it to 2.0.19 at least. You don't even have to run the upgrader, simply overwriting your forum files with those from the large 2.0.19 upgrade package will do.

[Illori]

You don't even have to run the upgrader, simply overwriting your forum files with those from the large 2.0.19 upgrade package will do.

if you do this you will remove any mods that changed the default files. it is better to use the package manager to upgrade to 2.0.19, it will not affect mods that are installed.

[a10]

You have gotten this type of error before as well.
https://www.simplemachines.org/community/index.php?topic=586123

That was a very experimental test install \ upgrade to 2.1.3 on another domain, I think we can forget whatever was happening there :O)

Postponed 2.1.4 as long as possible (.19 working so good).
Host unexpectedly still offers 8.0, that won't last, so...

Until then, as the error seems to affect 1 single account, will monitor any new registration. Maybe delete the problem-account, asking the member to re-register (as my fresh test-account is flawless) then see if it really was a one-off occurence > something went wrong at that exact registration moment on server \ whatever \ wherever.

Got many small tweaks and a few mods, would like to not touch anything until going for 2.1.4(5) (unless any errors really starts to pop-up, not just that (mysterious) single one).

Thanks for the feedback.

[a10]

^^^ Registered a test account, no errors in profile. Got some new members, all ok.
So it was all related to 1 single registration, complete mystery, never seen before or after.

Fixed:

Gathered all settings available for this account (username, email etc). Deleted the problem account, keeping posts. Registered a new account with the available settings from the problem account. Emailed new PW to member.

All well, no more errors. Finshed with re-attributing posts (became guest) to new account.

[a10]

Ok, the re-registration of an account seems to work fine, as do a fresh test account, but now seems to affect more accounts (which not long ago where ok, including my previously ok test account), newer accounts, older accounts not affected. Affects accounts made from 17 oct to 14 nov. Strange...

Otherwise, the forum works perfect with zero errors in log.

Contacted host who swiftly (good) responded with this, but no idea what to do with it :O) Where could I possibly find this MysqliDriver.php ??

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

According to our Senior Technical Team, it is advisable to edit the following file: libraries/vendor/joomla/database/src/Mysqli/MysqliDriver.php, and to insert the code  on the pastebin provided below at line #331:


mysqli_query($this->connection,'SET SQL_BIG_SELECTS = 1;' );
 
 
 
 it looks like this:
 
 if ($this->options['sqlModes'] !== [])
 {
 $this->connection->query('SET @@SESSION.sql_mode = \'' . implode(',', $this->options['sqlModes']) . '\';');
 mysqli_query($this->connection,'SET SQL_BIG_SELECTS = 1;' );
 }

[Aleksi "Lex" Kilpinen]

That looks like a lazy copy/paste answer aimed at Joomla specifically.

Usually, MySQL settings SQL_BIG_SELECTS or MAX_JOIN_SIZE would be the relevant places to look at - and on most shared hosts, you probably don't have direct access to change these yourself.

I may be wrong though, and you probably should just ask your host where they think you should find this file.
If I'm right though, the "Senior Technical Team" would have just made me look for a new host.

[a10]

^^^ Yes. But host has been very reliable for a dozen+ years, so looking for a fix.
And yes, no 'privileges' to do edits. Contacted them an awaiting a response.

[a10]

Some time ago got a clear answer form host (one.com). Like expected, an inexpensive shared account got some limits regarding what customers can change.

About the support, not immediately the good answers, but very easy & swift to get in contact + getting confirmations that someone was working on it ...at least they did not leave things unanswered, but initially provided some random \ no good fix proposals.  The (very friendly) groundfloor support should simply learn to forward special questions quicker to the right dept.

After consulting with our senior technicians, we regret to inform you that due to server limitations, there is nothing further we can do to resolve this specific issue.

    The SQL_BIG_SELECTS option is set to Off by default and Global value cannot be changed.
    The MAX_JOIN_SIZE limit is set to 67108864 and cannot be adjusted.

Anyway, the profile edit error disapeared just as mysteriously as it appeared. Some days with random server errors? updates? moved server? they actually fixed something? Whatever, at time of writing and for many days all is well. Won't complain, 1st database\forum error seen after approx 15+ years.

And should the error re-appear, won't bother with it, as one-of-these-days 2.0.19 will be left behind and forgotten.

[shawnb61]

That really shouldn't happen...  That MAX_JOIN_SIZE appears to be reasonable.

First suspicion is that an index is missing or corrupted somehow.

This utility would help identify if something is missing, pay careful attention to the indexes:
https://raw.githubusercontent.com/sbulen/sjrbTools/refs/heads/master/smf_db_compare.php

If you have an index highlighted in red, I'd go about adding it back.   (Green=added to SMF, usually by a mod or optional feature; Yellow=changed; Red=something was removed...  In general, Green/Yellow are OK, but red is usually bad...  Clicking on a header (blue bar) will collapse that section...)

If not, it would be helpful to know exactly which query/line of code produced that error.

[a10]

^^^ Thanks for info, if the error ever appears again will look up that post.

[a10]

Just an update.

After affecting profile settings for very recently registered members, all well for a long time now. Zero changes done to the forum, seemingly zero changes server side, either before or after the error occured.

So the ghost have left, now on the lookout for some other forum to haunt :O)