Database access with php doesnt work

[Lasslos05]

Hey Guys,

I'm building my own website with flutter. It needs a database, so I set up one on strato.de and tried to set up a php connection. See here: db.lh0.eu/db_connection.php The code: 

<?php
$db_server = 'rdbms.strato.de';
$db_benutzer = 'DBU******';
$db_passwort = '******************';
$db_name = 'DBS********'; # Verbindungsaufbau
if(mysql_connect($db_server, $db_benutzer, $db_passwort)) {
echo 'Server-Verbindung erfolgreich, wähle Datenbank aus...';
if(mysql_select_db($db_name)) {
echo 'Datenbank erfolgreich ausgewält, alle Tests abgeschlossen.';
}
else {
echo 'Die angegebene Datenbank konnte nicht ausgewählt werden, bitte die Eingabe prüfen!'; }
}
else {
echo 'Verbindung nicht möglich, bitte Daten prüfen! ';
echo 'MYSQL-Fehler: '.mysql_error();
}
?>

Anything I did wrong? The documentation how to use PHP to access the database is here: https://www.strato-hosting.co.uk/faq/hosting/this-is-how-you-can-use-our-mysql-database/

[cyberRobot]

You'll want to switch to PDO or MySQLi for working with the database. The mysql_ functions were removed from PHP in version 7.0. More information can be found in the warning box in the PHP manual here:
https://www.php.net/manual/en/function.mysql-connect.php

[Lasslos05]

Thanks for the reply. I changed the code: 

<?php
$host = 'rdbms.strato.de';
$db = 'DBS*********';
$user = 'DBU******';
$password = '******************';

$dsn = "mysql:host=$host;dbname=$db;charset=UTF8";

try {
   $db = new PDO($dsn, $user, $password);
   echo 'connected';
} catch (PDOException $e) {
   echo $e->getMessage();
}
?>

It still doesn't work. Is there something else wrong? I'm pretty sure the login data is correct. Error Message:

SQLSTATE[HY000] [2003] Can't connect to MySQL server on 'rdbms.webmailer.de' (113)

 

Edited September 9, 2021 by Lasslos05

[Barand]

Does it require you to specify the port number?

[Lasslos05]

I don't think so. The tutorial from my hosting service (strato.de) uses mysql_connect($db_server, $db_user, $db_password). If there is a port added automatically when using mysql_, then that could be the issue.

 

The code from the offical tutorial from strato:

# Access data
$db_server = 'rdbms.strato.de';
$db_user = '';
$db_passwort = '';
$db_name = ''; # Connection establishment
if(mysql_connect($db_server, $db_user, $db_password)) {
echo 'Server connection successful, select database...
';
if(mysql_select_db($db_name)) {
echo 'Server connection successful, select database ...
}
else {
echo 'The specified database could not be selected, please check the information you have entered!'; }
}
else {
echo 'Connection not possible, please check data! ';
echo 'MYSQL-error: '.mysql_error();
}
?>

 

Edited September 9, 2021 by Lasslos05

[kicken]

Could be a firewall problem (error 113 = no route to host).  Talk to your host, or check your firewall if you're managing your own hosting.

[gizmola]

Did you try and access it via ssh using the instructions they provided?  There is also a mention of a security administration package that could be interfering.  

Just to be clear, the default mysql port is 3306, and since they provide a hosted/shared mysql server, there is no problem with the default port.  

According to some people, there was an odd bug in regards to the DSN string and remote databases.  I have never encountered this myself but perhaps you have an old(er) php stack in your hosting environment?

Try this code, cribbed directly from the Best PDO Tutorial

$host = 'rdbms.strato.de';
$db   = 'DBS*********';
$user = 'DBS*********';
$pass = '***';
$charset = 'utf8mb4';

$dsn = "mysql: host=$host;dbname=$db;charset=$charset";
$options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false,
];
try {
     $pdo = new PDO($dsn, $user, $pass, $options);
} catch (\PDOException $e) {
     throw new \PDOException($e->getMessage(), (int)$e->getCode());
}

The space between the scheme 'mysql: ' and host= is deliberate in this case.  Between that and checking out if you can ssh & the security settings you can change, any other issue probably will require some support from your ISP.

[Lasslos05]

Hey Guys, 

first of all, thank you for the reply's. I taught I would be subscribed to that topic automatically. Apparently, I wasn't. I ran the code of @gizmola exactly how he wrote it except for the error thrown in the catch block, and replaced it with 

     echo $e->getMessage();

I have a good message and a bad message:

Good message: It shows something different!
Bad message: It's an error.

The error is: 

SQLSTATE[HY000] [2002] Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)

And if I try it with SSH, as @kicken suggested, it throws:

ERROR 2003: (HY000): Can't connect to MySQL server on 'rdbms.webmailer.de' (113)

I've tried it twice, and I'm pretty sure there was no typo. I could not find any security settings that could block access. 

Edited September 12, 2021 by Lasslos05

[Lasslos05]

And btw, I tried to change the PHP Version to 8.0. Now it says

SQLSTATE[HY000] [2002] No such file or directory

I obviously can change it back but I would appreciate if someone knew a new way in PHP 8.0, mabye that will solve the problem.

[Lasslos05]

I don't know how, but I solved it. I just tried a random script from the internet that looked a little bit different. It's that one:

<?php
    $user = 'DBu*******';
    $pass = '****************';
    $host = 'rdbms.strato.de';
    $db   = 'DBs**********';
    
$conn = new mysqli($host, $user, $pass, $dbdb);
	if ($conn->connect_error) {
    	echo("Connection failed: " . $conn->connect_error);
	} else {
		echo "Success";
	}

?>

Thanks so much to all of you who tried to help me.

[Barand]

It may be worth just checking a couple of basic settings.

Run this script

<?php
phpinfo();
?>

Scroll down the output to the PDO section

• check that there is one
• check that "mysql" is one of the installed drivers

If not, enable them in your php.ini file

[Lasslos05]

Hey Guys, its me again. Long story short: It wasn't the solution. It seemed to be it, but only because the output of ->connect_error is a string and cannot be used in an if, statement. I found the real solution, however. Here is a part of an email i wrote to my provider:

1. quoting from their article:
 "Please pay attention to the capitalization of the letters "DB" when specifying the database name. A connect using "dbxxxx" will fail.". 
This is wrong. In fact, any access made with uppercase letters will fail. These letters must be lowercase.

2. your example script uses the mysql_connect() command. This is deprecated since PHP 5.0 and has been removed in PHP 7.0. Therefore it does not work. Please change it to mysqli_connect, mysql or PDO().

3. your article always says that the user would be something like Uxxxxxx, and the database something like DBxxxxxxxx. This is wrong. In fact, the database is something like dbsXXXXXXX, and the user name is dbuXXXXXX. 

Translated with www.DeepL.com/Translator (free version).

That is why it didn't work.

 

Also, yes, mysql driver is installed.

Edited September 30, 2021 by Lasslos05

[ginerjm]

So are you working or not?  I'm not sure from your last post.

Something that you are doing that I do not have in my connect logic.  I am on a paid-for hosting service and the host spec is this:

        $host="mysql:host=localhost;dbname=$l_dbname;charset=utf8";

I have never used any actual server name for localhost.

PS - you will note that the dbname is a variable here which is preset to null.  When I do make a connection I usually supply the dbname that I plan on using as part of my call to my connection module.

[gizmola]

On 9/30/2021 at 12:44 PM, Lasslos05 said:

Hey Guys, its me again. Long story short: It wasn't the solution. It seemed to be it, but only because the output of ->connect_error is a string and cannot be used in an if, statement. I found the real solution, however. Here is a part of an email i wrote to my provider:

1. quoting from their article:
 "Please pay attention to the capitalization of the letters "DB" when specifying the database name. A connect using "dbxxxx" will fail.". 
This is wrong. In fact, any access made with uppercase letters will fail. These letters must be lowercase

Also, yes, mysql driver is installed.

So in a nutshell, there was some confusion or misunderstanding on your part having to do with the credentials.   

If there is value in this for you, and anyone else, it is this:

• mysql_ functions are long deprecated, and actually removed in all supported versions of php
• use PDO.  It's a much better api than mysqli

There are still mysteries to be found in this, including why you got a 2003 error while trying to connect using "ssh".  Just to be clear, what was suggested wasn't clear.  The suggestion was to ssh into your server, and then use the mysql command line client to attempt to connect using something like:

mysql -u username -h rdbms.strato.de -p

If you tried this and got a 2003 error, then there was a firewall issue either going out port 3306 or connecting to the mysql server on 3306, or perhaps even connecting from your server at all.  Having a password or username rejected should have produced different errors.  If these errors went away, it's very likely that they changed firewall settings to allow the connection.  Using the mysql command line client uses the same underlying client libraries, so it's a good double check, not to mention a useful administration tool to have access to.