Simple Machines Community Forum

SMF Support => SMF 2.0.x Support => Topic started by: Turrican3 on May 17, 2017, 06:59:46 AM

Title: Unable to connect to database after applying 2.0.14 patch
Post by: Turrican3 on May 17, 2017, 06:59:46 AM
Hi guys, I'm having issues with the 2.0.14 patch.

Installation reports no issues at all, but at first I got an error message regarding Load.php module at line 142.

I had a look at this section and discovered that, despite the README patch claiming the opposite, PHP 5.3 is *not* tha actual minimum required version (or at least has issues)

So, I set up my host to run on PHP 5.6.x branch and the Load.php error disappeared... but now I am stuck with the "cannot connect to database" error.

I checked Settings.php and everything seems fine BUT... repair_settings.php claims there are, infact, issues while connecting to the database despite showing correct values in the dedicated fields.

Right now I reverted to a 2.0.13 backup but I'd clearly like to stay up-to-date (also because due to a weird concidence I've just noticed my hosting just enabled https so I'd like to use it as well, and I understand that 2.0.14 also serves this purpose)

Any suggestions?
Title: Re: Unable to connect to database after applying 2.0.14 patch
Post by: vbgamer45 on May 17, 2017, 07:08:08 AM
The 2.0.14 patch moved the min to 5.4    I would stay 5.6 as well.
Do you have mysqi installed when upgrading php to 5.6 and check that mysql extensions are installed as well.
Title: Re: Unable to connect to database after applying 2.0.14 patch
Post by: Turrican3 on May 17, 2017, 07:19:57 AM
Thanks for the reply!

Unfortunately I have no terminal access to the server (it's a cheap, yet decent host so no complaints here), but I think a dump of phpinfo html would be enough to check everything needed is here, right?
Title: Re: Unable to connect to database after applying 2.0.14 patch
Post by: vbgamer45 on May 17, 2017, 07:22:07 AM
Yeah just check phpinfo and see if mysql or mysqli are installed
Title: Re: Unable to connect to database after applying 2.0.14 patch
Post by: Turrican3 on May 17, 2017, 07:26:42 AM
I see both mysql and mysqli sections, but I don't know about the specific values/parameters listed.

Anything special that might need to be verified there or do we have to look/check elsewhere?
Title: Re: Unable to connect to database after applying 2.0.14 patch
Post by: vbgamer45 on May 17, 2017, 07:27:42 AM
That should do it... I saw one post mention the user had 127.0.0.1 as the host try changing it to localhost:3306
Title: Re: Unable to connect to database after applying 2.0.14 patch
Post by: Turrican3 on May 17, 2017, 07:36:35 AM
I'll give it a try but I'm 99% confident that won't work, as I said this is a cheap, shared hosting so we (they) have mysql on a separate server :-\
Title: Re: Unable to connect to database after applying 2.0.14 patch
Post by: vbgamer45 on May 17, 2017, 07:42:56 AM
Yeah that won't work if separate serer then
Title: Re: Unable to connect to database after applying 2.0.14 patch
Post by: Turrican3 on May 17, 2017, 08:02:12 AM
Anything else worth checking like, I don't know, a log or something like that?

Would a little test like this (https://www.simplemachines.org/community/index.php?topic=553895.0) supply further information so we can track the issue down?
Title: Re: Unable to connect to database after applying 2.0.14 patch
Post by: SleePy on May 18, 2017, 06:38:10 PM
Turrican3,

Open Settings.php and add at the end before the ?>

$db_show_debug = true;


Save it and refresh your site.  It should give you a more detailed error message as to why its not connecting.
Title: Re: Unable to connect to database after applying 2.0.14 patch
Post by: Turrican3 on May 19, 2017, 04:35:39 AM
Great, didn't know about that option!

I'll give it a try, thanks.
Title: Re: Unable to connect to database after applying 2.0.14 patch
Post by: SleePy on May 24, 2017, 07:11:54 PM
Turrican3,

Any update here?
Title: Re: Unable to connect to database after applying 2.0.14 patch
Post by: Turrican3 on May 25, 2017, 03:31:31 AM
Sorry, it's been a very busy week at work and this prevented me from re-trying to apply the forum update/patch. :-\
Title: Re: Unable to connect to database after applying 2.0.14 patch
Post by: Turrican3 on May 25, 2017, 04:15:49 AM
Ok, tried adding the debug stuff but apparently nothing changed, I still get the same generic "unable to connect to the database" message.
Title: Re: Unable to connect to database after applying 2.0.14 patch
Post by: Turrican3 on May 25, 2017, 05:08:10 AM
I *think* I know where the issue lies, but I have no clue where exactly...

Tried to have a look at the PHP code (definitely not an expert), and to me it seems there are issues with the functions that connect to the database in Subs-db-mysql.php

As a matter of fact, as soon as I replaced the 2.0.14 version with the previous one, the forum immediately came online as usual!

Maybe it depends on the new MySQLi stuff?

PS: by the way, I don't really think this is a stable configuration... heck, I see that file basically doubled in size compared to the previous version, surely there's something in that added code that sooner or later will make the forum not working as intended or, even worse, damaging stuff on the database?!

PPS: with the "old" Subs-db-mysql.php I also do NOT get any error messages when using repair_settings.php
Title: Re: Unable to connect to database after applying 2.0.14 patch
Post by: Turrican3 on May 25, 2017, 06:09:08 AM
Additional info that *might* prove useful.

Tried to upload a very small PHP test file:

<?php
$mysqli 
= new mysqli("MYSERVER""MYUSERNAME""MYPASSWORD");

/* check connection */
if (mysqli_connect_errno()) {
    
printf("Connect failed: %s\n"mysqli_connect_error());
    exit();
}

/* print server version */
printf("Server version: %s\n"$mysqli->server_info);

/* close connection */
$mysqli->close();
?>


That gives this error message:

QuoteWarning: mysqli::mysqli(): (HY000/2000): mysqlnd cannot connect to MySQL 4.1+ using the old insecure authentication. Please use an administration tool to reset your password with the command SET PASSWORD = PASSWORD('your_existing_password'). This will store a new, and more secure, hash value in mysql.user. If this user is used in other scripts executed by PHP 5.2 or earlier you might need to remove the old-passwords flag from your my.cnf file in /web/htdocs/www.gamers4um.it/home/public/smf/prova.php on line 2
Connect failed: mysqlnd cannot connect to MySQL 4.1+ using the old insecure authentication. Please use an administration tool to reset your password with the command SET PASSWORD = PASSWORD('your_existing_password'). This will store a new, and more secure, hash value in mysql.user. If this user is used in other scripts executed by PHP 5.2 or earlier you might need to remove the old-passwords flag from your my.cnf file

I understand 2.0.14 uses mysqli so maybe this is the exact same reason why the upgraded SMF says it cannot connect to the database?!
Title: Re: Unable to connect to database after applying 2.0.14 patch
Post by: Kindred on May 25, 2017, 06:14:23 AM
ummm... are you still on php 5.2 and mySQL4?

If so...   yeah, that would be an issue.
Title: Re: Unable to connect to database after applying 2.0.14 patch
Post by: Turrican3 on May 25, 2017, 06:29:39 AM
Nope, I'm using MySQL 5.0.x and PHP 5.6.x right now.

Anyway, I solved the issue by running the SET PASSWORD command on the database (via phpmyadmin) as per previous quote, and now it's working!

I have a different issue right now but I guess I'll use another thread. Marking this as solved.
Title: Re: Unable to connect to database after applying 2.0.14 patch
Post by: SleePy on May 25, 2017, 06:13:19 PM
Well that is an issue that is difficult to test for as it has to do with outdated mysql passwords that need updated to the newer 4.1+ format.  Although setting that debug statement in your Settings.php should have revealed you that same error message.  Thanks for the update.
Title: Re: Unable to connect to database after applying 2.0.14 patch
Post by: Turrican3 on May 29, 2017, 05:31:58 AM
Just out of curiosity... isn't that actually an issue with mysqli (i.e. the new driver) or whatever?

I mean, I've been running on 5.0.x for a good while, but it was just this forum 2.0.14 update that triggered (well, basically forced should I say) that password/hash change.
Title: Re: Unable to connect to database after applying 2.0.14 patch
Post by: Arantor on May 29, 2017, 06:03:12 AM
No, it's not, it's about the underlying connector - mysql and mysqli both use underlying libraries to connect to MySQL itself, whether that's libmysqlclient or mysqlnd.

What happened here is that your database was set up such that it had a 4.0 (or older) style password set up and mysqlnd can't make use of that but libmysqlclient can. Reset the database password via cPanel or similar.
Title: Re: Unable to connect to database after applying 2.0.14 patch
Post by: Turrican3 on May 29, 2017, 12:47:58 PM
Ok got it, I stand corrected then.
(please understand it was an honest question by the way, didn't want to act pretentious and I apologize if I actually sounded like I did, wasn't my intention)
Title: Re: Unable to connect to database after applying 2.0.14 patch
Post by: Arantor on May 29, 2017, 12:54:36 PM
No, not pretentious, just wanted to make sure that the real cause was understood - because if not, it's all too easy to get hung up and spend time trying to fix a problem that isn't there.
Title: Re: Unable to connect to database after applying 2.0.14 patch
Post by: Turrican3 on May 29, 2017, 01:06:32 PM
Totally fine to me, thanks again. :D
Title: Re: Unable to connect to database after applying 2.0.14 patch
Post by: SleePy on May 29, 2017, 01:08:40 PM
Well I should note while the mysql client can tell us to that the password is not compatible and needs updated, we can't fix that on our side because the client (mysqlnd) can't make the old style password connections.  If it could do that, we could correct this by connecting across the old method and issuing a update.

I've fixed a few passwords that broke after the update by simply login in as that user and running:

SET password=PASSWORD('theDatabasePasswordHere');


This causes the password format to be updated to a 4.1+ compatible one.  Your hosts cPanel usually has to do this though if you can't connect across a cli interface to mysql yourself.
Title: Re: Unable to connect to database after applying 2.0.14 patch
Post by: Turrican3 on May 29, 2017, 01:34:12 PM
@SleePy

Yep that's exactly what I did (https://www.simplemachines.org/community/index.php?topic=553947.msg3927892#msg3927892) to fix the issue. :)