I recently moved my server to a new system and the SMF migration had no troubles. Until I checked the logs, and they were overflowing with errors because PHP 7.4 isn't officially supported.
So I installed PHP 7.3 and told the forum to use that (using nginx location)... But then nothing works, I just get an unspecified database error. I found a tip (https://github.com/SimpleMachines/SMF2.1/issues/5504#issuecomment-472296454) that gave me a more detailed error message:
Undefined variable: db_connnection in /var/www/forum/Sources/Subs-Db-mysql.php on line 93
The old server ran PHP 7.3, no trouble. The new one ran 7.4 without any apparent trouble, but cannot run on 7.3? There's something going on here that I can't figure out. I'd love some suggestions.
Thanks for any clues.
What version of 2.1 are you running? Off github or 2.1 RC2?
Currently running RC2.
I would try the latest version off github they probably added some fixes for 7.3 and 7.4
I have used:
https://www.simplemachines.org/community/index.php?topic=558451.0 (https://www.simplemachines.org/community/index.php?topic=558451.0)
To be clear, it's been running for ages on 7.3, it only failed to run on 7.3 after I tried it on 7.4. I went through the install docs for SMF and made sure I had all the appropriate PHP modules installed. If I go back to 7.4, it all works fine (but for the error logs and whatever other unnoticed havoc it causes).
So either PHP 7.4 changed my files or DB, or my PHP 7.3 setup is insufficient somehow.
That suggested tweak had one too many 'n's in it...
Try using this instead:
die(mysqli_error($db_connection));
And let's see if you get a better error msg...
Nicely spotted, @shawnb61 - but after fixing that, the error is the same (minus one N):
Notice: Undefined variable: db_connection in /var/www/forum/Sources/Subs-Db-mysql.php on line 93
Just to be sure nothing was broken with the database, I backed up the old server again (running 7.3), dropped the schema on the new server and re-imported the DB into the new server again (running 7.3) and it still fails.
As a sanity check, I re-copied all the PHP files from /forum/ and the whole of Packages and Sources, and it still fails. I checked the PHP.INI on both machines, they seem to be the same. I checked the nginx config, they're identical on both machines.
The problem seems to be in PHP7.3 but for the life of me I can't figure out what it might be.
Ack...
Use $connection... (No db_)
Thanks @shawnb61.
Use of undefined constant connection - assumed 'connection' (this will throw an Error in a future version of PHP) in /var/www/web/forum2/Sources/Subs-Db-mysql.php on line 93
Did you include the $? $connection?
Oops, dumb error on my part. I did not include the $. Now that I have, the error is:
Warning: mysqli_error(): invalid object or resource mysqli in /var/www/web/forum2/Sources/Subs-Db-mysql.php on line 93
Aha, here we go. I removed the @ in the mysqli_real_connect and got this:
mysqli_real_connect(): The server requested authentication method unknown to the client [caching_sha2_password]
Finally, a real error. I'll dig into that. =)
UPDATE: fixed! Altered the forum MySQL user to use the default password mechanism.
mysql> ALTER USER root IDENTIFIED WITH mysql_native_password BY 'PASSWORD';
Are you on mysql 8.0?
This appears to be a known issue with mysql 8.0 & php's mysqli. Discussion picked up here:
https://github.com/SimpleMachines/SMF2.1/issues/6242
Well that's the funny thing, both servers are on MySQL 8. The one that always worked is 8.0.20, the new one is 8.0.21.
The best I can guess is that MySQL was updated at some point? But... I'm pretty sure I built the old server after MySQL 8 was released. I just checked and all the users on the old server use the standard auth method. Which suggests that yeah, it was running an older version of MySQL somehow, and was later upgraded without changing that auth method.
There are a few conditions that must be met for this to affect you:
- mysql 8
- php 7.3 or prior
- user created with an authentication plugin other than mysql_native_password
The worst part of this particular issue is that the real error was hidden. If it weren't, this would have been resolved much sooner.
Yeah, that's absolutely true. I fixed it on my own in less than half an hour once I knew what the problem was. A bit unfortunate, really.
I appreciate your help.
Quote from: shawnb61 on August 30, 2020, 06:53:37 PM
The worst part of this particular issue is that the real error was hidden. If it weren't, this would have been resolved much sooner.
Did something change in RC2 to hide this? I've seen it in RC1 when I upgraded my MySQL and PHP before I was aware of the password encryption changes. It stopped the forum from loading altogether, and displayed the message very clearly to me.
Quote from: SugarD-x on August 31, 2020, 10:17:14 PM
Did something change in RC2 to hide this? I've seen it in RC1 when I upgraded my MySQL and PHP before I was aware of the password encryption changes. It stopped the forum from loading altogether, and displayed the message very clearly to me.
Lots of changes since RC1...
But the big generic "Connection Problems Sorry, SMF was unable to connect to the database" when there are authentication problems has been around since 2.0. You see the exact same issues in 2.0.17 when the DB user specified in Settings.php meets the criteria specified above.