Corruption of admin user permissions?

AndyS

The issue

Logging in with an administrator account used to display the All Discussions page with access to the admin pages via the drop down menu displayed by clicking on the user name at top right of the page.

After erasing a user account admin users can still login and get access to the admin pages but nothing is displayed on the All Discussions page apart from the header and its contents. Also Profile and Settings do nothing.

Non-admin users still have access to All Discussions page and see all discussions and tags and can access their Profile and Settings.

Steps leading up to the problem

Launched forum site from my browser favourites (Landing Page extension is enabled so first page is just to force log in)
Logged in as Admin_user1 - All Discussions page correctly displayed as usual.
3 Clicked on Administration - Administration page launched in new tab.
Selected Users and clicked on username User_X to Visit User_X's profile
User X's profile page opened in a new browser tab,
On User_X's profile clicked Controls > Erase and then confirmed erase when prompted.
Closed browser tab for User_X's profile.
Refreshed the Users page in admin. Erase User_X is now listed as Former Member 1.
Switched to browser tab opened at step 2 for All Discussions for Admin_user1. Problem observed.

Things I've tried to fix the problem, without success.

Refresh browser page.
Log Admin_user1 out of forum, close browser tabs, close browser and relaunch.
Log in as Admin_user1 and make User_Y an administrator. Log out Admin_user1. Log in as User_Y. Same problem as for Admin_user1.
Log out as User_Y, log in as Admin_user1 and remove administrator role from User_Y. Problem still exists for User_Y.
Cleared the cache. Logged in as Admin_user1. Problem still exists.

### Flarum log message

I see error message:

flarum.ERROR: PDOException: SQLSTATE[42000]: Syntax error or access violation: 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 in /home/fellfixe/public_html/dev_forum/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php:117

I discovered that this error message gets generated even before a user logs in i.e. on opening of the landing page.

This looks to be the same message mentioned in this support thread and one or two others but the context is totally different.

I have GDPR extension installed so my hunch is that because I ran the account erase as an administrator 'visiting' the user profile of the account to be erased (rather than logging in as the user account itself) this has in some way corrupted the access of all administrator accounts to the forum discussion pages and functionality. And as mentioned above the problem the problem persists even if the administration role is revoked from a user.

Flarum environment

Flarum 1.8.5
PHP 8.2.21
MySQL 10.6.18-MariaDB-cll-lve

Loaded extensions: Core, date, libxml, openssl, pcre, sqlite3, zlib, bz2, calendar, ctype, curl, hash, filter, ftp, gettext, json, iconv, SPL, pcntl, random, readline, Reflection, session, standard, mbstring, shmop, SimpleXML, tokenizer, xml, i360, gmp, exif, bcmath, dom, gd, posix, sockets, xmlreader, xmlwriter, xmlrpc, zip, fileinfo, intl, PDO, mysqlnd, pdo_mysql, mysqli, pdo_sqlite, mcrypt, imap, imagick, pgsql, soap, xsl, Phar, clos_ssa, Zend OPcache
+------------------------------+-----------------+--------+
| Flarum Extensions | | |
+------------------------------+-----------------+--------+
| ID | Version | Commit |
+------------------------------+-----------------+--------+
| flarum-flags | v1.8.0 | |
| flarum-tags | v1.8.0 | |
| flarum-approval | v1.8.1 | |
| blomstra-gdpr | 0.1.0-beta.20 | |
| flarum-mentions | v1.8.3 | |
| flarum-subscriptions | v1.8.0 | |
| flarum-markdown | v1.8.0 | |
| flarum-suspend | v1.8.1 | |
| fof-follow-tags | 1.2.5 | |
| romanshul-editor | 1.0.3 | |
| katosdev-signature | 1.6 | |
| gtdxyz-discussion-paginator | 1.0.3 | |
| fof-user-directory | 1.3.3 | |
| fof-user-bio | 1.4.0 | |
| fof-subscribed | 1.1.4 | |
| fof-reactions | 1.4.1 | |
| fof-profile-image-crop | 1.1.6 | |
| fof-polls | 2.2.4 | |
| fof-default-user-preferences | 1.2.1 | |
| fof-byobu | 1.3.6 | |
| flarum-sticky | v1.8.0 | |
| flarum-statistics | v1.8.0 | |
| flarum-lock | v1.8.0 | |
| flarum-likes | v1.8.0 | |
| flarum-lang-english | v1.8.0 | |
| flarum-extension-manager | v1.0.3 | |
| flarum-emoji | v1.8.0 | |
| flarum-bbcode | v1.8.0 | |
| ecnu-im-sticky-sidenav | v1.1.0 | |
| datlechin-landing-page | 0.1.0 | |
| blomstra-sort-order-toggle | 0.2.0 | |
| blomstra-mark-unread | 0.2.0 | |
| blomstra-fontawesome | 0.1.5 | |
| blomstra-digest | 0.1.0-beta.11.7 | |
| blomstra-conversations | 0.1.2 | |
| becod-backtotop | v1.1 | |
| askvortsov-rich-text | v2.1.7 | |
| acpl-my-tags | 1.4.0 | |
+------------------------------+-----------------+--------+

All ideas what else I can do to debug and fix this will be gratefully received.

luceos

AndyS MySQL 10.6.18-MariaDB-cll-lve

Gonna save this.

Try this fix in the meantime it seems more people are encountering this: https://discuss.flarum.org/d/35028-cannot-have-more-than-one-discussion-in-a-tag/17

clarkwinkelmann

You should fix that MySQL error first.

It's very likely that this has nothing to do with having deleted a user, just that an admin account loads more information on page load and results in a larger SQL query, and your host apparently isn't configured to handle queries of that size.

If you still have issues only for specific users, also check the browser javascript console for any javascript console. It's possible an extension crashes the discussion list only when some admin features are attempted to load.

AndyS

luceos Try this fix in the meantime it seems more people are encountering this: https://discuss.flarum.org/d/35028-cannot-have-more-than-one-discussion-in-a-tag/17

Well that seems to have fixed it. The All Discussions page displays as it used to with all tags visible in Side Nav and I'm able to go to Profile and Settings pages and get to the Admin page. So thanks very much for your help.

clarkwinkelmann It's very likely that this has nothing to do with having deleted a user, just that an admin account loads more information on page load and results in a larger SQL query, and your host apparently isn't configured to handle queries of that size.

I'm still a little puzzled as to why this happened in the first place. My forum is a really small evaluation forum - just 3 users, 14 discussions and 17 posts, and 33 tags - so I would have thought not likely to be challenging in terms of the numbers of rows returned by a SELECT clause even if MAX_JOIN_SIZE is set on the low side. Plus, as I mentioned, the problem did not occur before the user account deletion, and after the deletion the number of posts, discussions and tags was unchanged from before the deletion. The only difference was one less user account (although of course I realise that the deletion is basically a 'soft' deletion with the original user account still existing but anonymised). So while on the face of it I totally agree that a soft deletion of a user seems like an unlikely cause, but as the problem occurred immediately after that action it seemed reasonable to at least suspect it as a possible cause. I mean, is it possible that the anonymisation of the data led to some circular references in the data which resulted in a lot of duplicate rows being returned thus blowing the MAX_JOIN_SIZE limit?

I'm afraid I just don't have the in depth technical skills and detailed knowledge of Flarum code and database internals to undertake some further investigations but am happy to take instruction from either @luceos or @clarkwinkelmann if I can be of assistance in investigating an issue which seems to have occurred in a number of different situations.

Thanks again for steering me to a solution even if the root cause is still a mystery (to me, at least).

luceos

AndyS I assume your hosting provider updated cpanel which includes this broken database version, see https://discuss.flarum.org/d/32901-staff-diary-v20-cycle/190 for my analysis from yesterday.

AndyS

luceos I assume your hosting provider updated cpanel which includes this broken database version

I checked in with my hosting provider. They confirmed I have been running on the same database version since I started out with them on 7 August. They sent me this link to a changelog which seems to suggest that version was installed about a month before I even took out a contract on their service: https://changelog.cloudlinux.com/cl-MariaDB106

I'm not expecting any specific action on this. Just throwing this info into the mix so you can keep it in mind if the problem keeps occurring.