Continuous error messages, sent to email "SMF could not connect to the database"

[parsa]

I'm getting tons of error messages and I also get the following message sent to my email:

There has been a problem with the database!
This is a notice email to let you know that SMF could not connect to the database, contact your host if this continues.

I'm using the latest SMF 2.1.4. I believe my webserver is using PHP version: 8.1.32 and libmysql—mysqlnd 8.1.32. Forum is at: American Roads Forum
I'm trying to get this forum usable again after it's been quiescent for awhile. However, I seem to be trolled by hundreds of "guests", likely bots, that are causing these errors. I also get hundreds (265 now) of requests that look like potential spammers that are appearing. I thought I had taken care to ensure a pretty robust spam control when I moved to my new server, but I can't go through and check that each and every one is not a dangerous user. I'm almost tempted to give up and close it all down.
This is the pair of error messages that I'm getting every few minutes in the error logs:


I'd love to get rid of these error messages and somehow get the forum so that only legitimate users are making membership requests.

[vbgamer45]

Maybe try the free plan on cloudflare for their dns proxying which would filter out more bots.

I would also suggest on your registration page use either questions or answers or a more advanced captcha mod like recaptcha, hcatpcha or turnstile.

[parsa]

I added a couple questions. That would likely help a bit with the foreign spammers and bots. AI is getting pretty good at figuring out stuff though.

I still have the issue of all those errors related to the database as shown in the image. It shows some line error, but I have no idea what to do with that information.

[Sir Osis of Liver]

You should have 20-30 questions minimum, rotating three.  This mod works well -

https://custom.simplemachines.org/index.php?mod=4278

Database connection errors are usually server side issues, you have to contact host support for that.

What is in row 1 of settings table?

[shawnb61]

If your forum is getting inundated with crawlers, this might help:
https://github.com/sbulen/SMF-bot-hygiene

Note that 0xE0AE9AE0AEBF is valid UTF8.  It looks like your forum is running some weekly maintenance & updating the TLDs from iana.org.  (0xE0AE0AEOAEBF is familiar as it is part of one of the first TLDs.)

So the question is why is it not accepting valid UTF8?  First guess is that you haven't been properly converted to UTF8...

What is the collation of all your tables?
What is the collation of all the columns in your settings table?

[parsa]

My charset and collation were set to Latin-1 for some reason, so I had the host correct it. They changed them to:
utf8mb4 and utf8mb4_unicode_ci respectively
However, the problem is persisting with these error messages. Subs_Db_mysql.php seems to handle connections with the database. Looking in Subs.db, this seems to have something to do with guests trying to register perhaps. My code seems to be identical to the original 2.1.4 code in these Source files. I have no idea what to do now.

[parsa]

These seem to be the referenced problems in Subs.php and Subs_Db_mysql.php —

[Kindred]

1- thr collation has no association with the "can not connect to database" message.

2- can not connect to database indicates that you have either a bad database connection details or your host is unstable.

3- mb4 is not the correct collation. Smf uses mb3... and just changing the collation is not enough... it needs to be transformed properly...

[Sesquipedalian]

3- mb4 is not the correct collation. Smf uses mb3... and just changing the collation is not enough... it needs to be transformed properly...

Using utf8mb4 is fine and actually a good idea when available. SMF 2.1 defaults to utf8mb3 only because it still supports ancient versions of MySQL that might not have mb4 support. SMF 3.0 will require mb4 support.

[shawnb61]

Using utf8mb4 is fine and actually a good idea when available. SMF 2.1 defaults to utf8mb3 only because it still supports ancient versions of MySQL that might not have mb4 support. SMF 3.0 will require mb4 support.

It's possible to manually convert to mb4 one some current DBs.

But...  It won't work on all DBs, which is why I tell folks to stick to mb3.  E.g., it won't work if your forum is still running MyISAM without other DB changes.  Even DBs installed on earlier versions of InnoDB will have issues, depending on the row format.

So for many (I'd bet most forums that have been around a while...) it's not as simple as changing collation.  You'd have to cut over to InnoDB and ensure you are on a more modern row format also.

And even if you were to do so, SMF 2.x would still only use mb3 and entity encode mb4 characters.  So there's no point.

[Sesquipedalian]

True, but for this situation, where the database has already been successfully converted to utf8mb4, there's no harm in that and no need to change it back.

[shawnb61]

The entity logic was never really exercised in that configuration.  I would not assume it works without a lot of testing.

I do know if you convert smf 2.x to mb4 & set the db_mb4 switch to true nothing works at all.  Even just editing posts errors out.

It's better if you leave the db_mb4 switch to null (despite the fact that this is exactly what that switch was for...)

I'm risk averse.  I would never advise putting a production forum in a non-standard & completely untested configuration.

[shawnb61]

Back to the original problem...  "Table" collations are just defaults - you have to tell it to convert the columns as well.  So although the table may be set to utf8mb4, that doesn't necessarily mean the actual data in the columns is utf8mb4.

So...  I suggest looking at a few tables in detail, e.g., smf_settings and smf_messages.
- What is the collation of the columns (not the tables, the columns)?
- In settings.php, what is the value of db_mb4?  (As noted above, setting this to true causes lots of problems like this.  It's possible the host did this thinking they were helping...)

I am fairly sure the above issue is being caused by the process that updates tlds in the background.  It has no issues with true utf8 columns.  So either the columns aren't utf8 or something else is causing an issue.

[parsa]

Back to the original problem...  "Table" collations are just defaults - you have to tell it to convert the columns as well.  So although the table may be set to utf8mb4, that doesn't necessarily mean the actual data in the columns is utf8mb4.

So...  I suggest looking at a few tables in detail, e.g., smf_settings and smf_messages.
- What is the collation of the columns (not the tables, the columns)?
- In settings.php, what is the value of db_mb4?  (As noted above, setting this to true causes lots of problems like this.  It's possible the host did this thinking they were helping...)

I am fairly sure the above issue is being caused by the process that updates tlds in the background.  It has no issues with true utf8 columns.  So either the columns aren't utf8 or something else is causing an issue.

Sorry, I'm not great with the nuts and bolts of these forum files. I'm going to need more direction. I can handle html, css, etc., but I'm not familiar with the way these php files work or are supposed to be formatted. Heck, I'm not even sure what the tables and columns are here. My files are barely changed from the original downloaded SMF files, so perhaps you can suggest where and what I should be looking for.

My host was pretty much giving up in the last ticket exchange. This is all they suggested. Keep in mind that I'm now not really running much of anything beside the base forum. Default theme, etc.

Would it be possible for you to contact SMF support regarding this? It seems this has something to do with their software and maybe the PHP version you're using. https://www.simplemachines.org/community/index.php?topic=577747.0

Please try try:
- a different PHP version (DirectAdmin > Extra Features > Select PHP Version)
- disabling mods/plugins one by one until you figure out which ones are problematic

[Sir Osis of Liver]

Tables and columns are not files, they're in your forum database.  You can view them with phpmyadmin, which should be available in the databases section of your host control panel.

[parsa]

Ok, thanks. I did that, and they were not set to "utf8mb4_unicode_ci" throughout the database, either in the files or the backup files. The majority were still set to "latin1_swedish_ci", a few were "utf8mb3_general_ci".
1) Is there a batch way to change all these throughout the database? If I can do it with an SQL Query to the whole database, I would need a walkthrough on how to do that.
2) Does character set appear anywhere in the columns, and would it need to be changed to "utf8mb4"?

smf_settings:


smf_messages:


smf_admin_info:


smf_pm_labels:

[shawnb61]

Something went very wrong with your UTF8 implementation...

To fix, I would take the following steps by hand.  This task will be two, maybe three, glasses of wine in duration.

Suggested steps going forward:
1) DB & file system BACKUPS.   Before doing anything, back it all up.  If something bad happens, it's better to get back to where you are today than to lose stuff.  If you don't know how to do backups, have your host do them.  If you don't have usable backups and something bad happens, that's on you.
2) FOR EACH TABLE, change the table & column collation to utf8mb3_general_ci.  You can use phpmyadmin to do this.  Under the "Operations" tab, you can change the table collation.  It is very important to check the "Change all column collations" box.
3) FOR EACH TABLE, *confirm* the column collations were changed.  Review the structure of each in phpmyadmin.  All text columns should now be utf8mb3_general_ci.
4) In your settings table, make sure the 'global_character_set' entry is set to 'UTF-8'.
5) In Settings.php, make sure $db_character_set is set to 'utf8'.
6) In Settings.php, make sure $db_mb4 is set to null.

If you complete the above steps, you should be 100% properly converted to utf8, and the invalid character sequence errors should go away.

You should review all your posts, make sure things look OK.  Pay special attention to any existing non-English text where multi-byte characters are involved - Greek, Turkish, Cyrillic, CJK, etc.  Make sure things look OK. 

[Sir Osis of Liver]

phpmyadmin allows you to change all table collations in one go, in Operations > Collations.  There's a checkbox for 'Change all tables collations', I believe that also changes column collations.

[shawnb61]

phpmyadmin allows you to change all table collations in one go, in Operations > Collations.  There's a checkbox for 'Change all tables collations', I believe that also changes column collations.

Yep...  Never saw that before.  Just tried it, & it seems to work, too...

Note the 2nd checkbox doesn't appear until after you check the first checkbox.  Both must be checked.

You cannot view this attachment.

[parsa]

Thanks. Too bad I didn't know about that general operations tab earlier. However, it wouldn't have worked as two of the tables returned errors (see below).

For procedures #4–6, those already had those settings.

Everything went fine for all but two tables. Since the database was set to "utf8mb4_unicode_ci", I set everything to that. I actually used a query of the form:

Code Select Expand

ALTER TABLE smf_tablename CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;That worked the same as using the Operations tab for each table.
Two tables persistently wanted to stay in "latin1_swedish_ci": smf_members and backup_smf_messages.
I managed to change them to "latin1_general_ci".
Then after they were in that form, it was possible to change them to "utf8mb3_unicode_ci"
However, when I tried to change them to utf8mb4_unicode_ci", I would get the following messgae for both tables:



It did the same thing when I tried to use the Operations tab. I could actually change individual rows to utf8mb4_unicode_ci, but the "real_name" column (in smf_members) would not change. It just gave the error message above. The same happened with about three rows in the backup_smf_messages table.
I'm not sure if having one regular table as utf8mb3, while all the others are utf8mb4, is going to cause a problem....