Users cannot sign up

TidbitSoftware

Experiencing a new issue where user's cannot sign up. I've updated Flarum, to no avail. The front end message is "Oops! Something went wrong. Please reload the page and try again."

Note,

Apache2 server is operating normally
Existing users can log in
Test email sends successfully from admin panel

shows,

flarum.ERROR: PDOException: SQLSTATE[HY000]: General error: 1712 Index users_email_unique is corrupted in /home/www-ssl/flarum/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php:117

and,
Next Doctrine\DBAL\Driver\PDO\Exception: SQLSTATE[HY000]: General error: 1712 Index users_email_unique is corrupted in /home/www-ssl/flarum/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDO/Exception.php:18

and,

Next Illuminate\Database\QueryException: SQLSTATE[HY000]: General error: 1712 Index users_email_unique is corrupted (SQL: select count(*) as aggregate from<USERS_TABLE>whereemail= <TEST_EMAIL_ADDRESS>) in /home/www-ssl/flarum/vendor/illuminate/database/Connection.php:712

System info,

$ php flarum info
Flarum core: 1.8.10
PHP version: 8.0.30
MySQL version: 8.0.41-0ubuntu0.20.04.1
Loaded extensions: Core, date, libxml, openssl, pcre, zlib, filter, hash, json, pcntl, Reflection, SPL, session, standard, sodium, mysqlnd, PDO, xml, calendar, ctype, curl, dom, mbstring, FFI, fileinfo, ftp, gd, gettext, gmp, iconv, exif, mysqli, pdo_mysql, Phar, posix, readline, shmop, SimpleXML, sockets, sysvmsg, sysvsem, sysvshm, tokenizer, xmlreader, xmlrpc, xmlwriter, xsl, zip, Zend OPcache
+-------------------------------+---------+--------+
| Flarum Extensions             |         |        |
+-------------------------------+---------+--------+
| ID                            | Version | Commit |
+-------------------------------+---------+--------+
| flarum-flags                  | v1.8.2  |        |
| flarum-tags                   | v1.8.4  |        |
| flarum-approval               | v1.8.2  |        |
| flarum-mentions               | v1.8.5  |        |
| flarum-subscriptions          | v1.8.1  |        |
| fof-follow-tags               | 1.2.9   |        |
| flarum-suspend                | v1.8.4  |        |
| fof-username-request          | 1.2.4   |        |
| fof-upload                    | 1.6.1   |        |
| fof-subscribed                | 1.1.4   |        |
| fof-split                     | 1.1.1   |        |
| fof-reactions                 | 1.4.1   |        |
| fof-pages                     | 1.0.8   |        |
| fof-nightmode                 | 1.6.0   |        |
| fof-links                     | 1.3.0   |        |
| fof-gamification              | 1.6.8   |        |
| fof-disposable-emails         | 1.0.0   |        |
| fof-default-user-preferences  | 1.2.1   |        |
| fof-cookie-consent            | 1.1.3   |        |
| fof-best-answer               | 1.6.4   |        |
| fof-anti-spam                 | 1.1.3   |        |
| flarum-sticky                 | v1.8.2  |        |
| flarum-statistics             | v1.8.1  |        |
| flarum-markdown               | v1.8.1  |        |
| flarum-lock                   | v1.8.2  |        |
| flarum-likes                  | v1.8.1  |        |
| flarum-lang-english           | v1.8.0  |        |
| flarum-emoji                  | v1.8.1  |        |
| flarum-bbcode                 | v1.8.0  |        |
| clarkwinkelmann-author-change | 1.0.3   |        |
+-------------------------------+---------+--------+
Base URL: <URL>
Installation path: /home/www-ssl/flarum
Queue driver: sync
Session driver: file
Scheduler status: Never run
Mail driver: smtp
Debug mode: off

clarkwinkelmann

I have never seen this issue with Flarum and don't know exactly how to fix it, but from the error message it seems clear something went wrong with your MySQL database, likely completely independently from Flarum or extensions.

Doing a quick search about MySQL index corruption yields this thread https://dba.stackexchange.com/questions/31701/finding-and-fixing-innodb-index-corruption It seems like you could try to run the OPTIMIZE command on your users table. If you have access to phpMyAdmin, MySQL Workbench or similar, there will be a button for this in the database management UI. Otherwise you could try connecting to the MySQL CLI and running the SQL directly. Best to make a backup before trying anything, in case it breaks things even more! It's possible that simply making and restoring a backup also fixes the issue, as it would rebuild the indexes.

As to what caused the issue originally, do you have any idea what the last change could have been before the bug occurred? Is the MySQL server managed or self-hosted? Did you recently make or restore a backup? Is the server low on disk space or memory?

TidbitSoftware

clarkwinkelmann Hey, I appreciate the thorough response and insights. Sounds like it's likely not a Flarum issue, which removes one big variable at least. The installation lives on a remote server without GUI, but I can dump the database, explore locally in phpMyAdmin and try some of what you suggested, then rebuild the database on the server. I do backup the database any time I update Flarum, so it doesn't appear that a restoration from backup has corrected the issue.

Regarding possible other causes, I can only think of periodic updates via APT. Disk usage is at just under 90%, and even though that's on the "inefficient" side of things, it shouldn't result in this (especially, I would think, if login works for existing users).

Again, thank you, and I'll update this if it turns out to be related to Flarum in anyway.

TidbitSoftware

clarkwinkelmann Can confirm that a simple OPTIMIZE TABLE worked to correct the issue for anyone in the same boat running Flarum on an Ubuntu Linux OS via MySQL.

Gorm

I am not too familiar with Flarum and his database structure, but the error point to not unique user email. Theoretically it is possible while users can changing their emails. Can you check user emails? Is it contain duplicates? If it was then you are finding the bug, except you are edit the database manually.

krvam

TidbitSoftware Hello sir, i have the same problem, how you fixed the error? what steps i need to do?

clarkwinkelmann

Glad to hear the problem was resolved!

The fact other features still work now is probably irrelevant to troubleshooting this. The original problem must have happened at a single point in time, while MySQL was writing data or performing a backup, leading to the index getting corrupted. After that point, the corruption won't solve itself without human intervention. In the meantime everything that doesn't rely on that index will continue to work, particularly if the original problem was temporary and now gone. Perhaps if the problem had lasted for more than a second, more of the database would have been corrupted.

On the "login still works" side, I'm actually wondering if/how it would have worked depending on the amount of corruption. If only the email index is corrupted, then login with username should work no matter what. But login with email would likely fail. Unless the corruption only affected writes operations and the index reading was still working 🤔

There's probably no need to dig too deep into the original cause if the problem happened a single time and does not happen again in the future. If it does happen again, I'd suggest just wiping the server and creating a new one, it will probably be the option that takes the least time.

90% disk space used is probably still something worth looking into. I don't know how many gigabytes that is in your situation, but it's best to be certain the server will never hit 100%.

TidbitSoftware

krvam If you are comfortable with command line SQL, you have to run OPTIMIZE TABLE <TABLE_NAME>, where <TABLE_NAME> is the name of the table given in your error message. Otherwise, you can use a GUI like phpMyAdmin to run the same operation.