CODEWITHSUNDEEP
phpsqldatabasepdosynology
VâneVâne
VâneVâne

Reputation: 151

Getting Error SQLSTATE[HY000] [2002] Connection refused on NAS Synology

I am making an under construction page which is hosted on my Synology NAS.

Visitors can subscribe with their email and get informed when the website will be available.

I have trouble with the database and PHP code that add the email to the database.

If the server name is localhost, I get the following error:

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

When it is 127.0.0.1 or 127.0.0.1:3306, I get the error below:

SQLSTATE[HY000] [2002] Connection refused

I didn't find the solution yet on Stackoverflow.

Here the PHP code:

<?php

$servername = "localhost";
$username   = "id";
$password   = "password";
$dbname     = "dbname";

try {
    $conn = new PDO( "mysql:host=$servername;dbname=$dbname", $username, $password );
    $conn->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
    $sql = "INSERT INTO email ( email ) VALUES ( '$email' )";

    $conn->exec( $sql );
    echo "New record created successfully";
}

catch( PDOException $e )
{
    echo $sql . "<br>" . $e->getMessage();
}
$conn = null;

Why am I getting this error?

Upvotes: 4

Views: 30024

Answers (7)

Sławek
Sławek

Reputation: 11

Go to directory:

cd /var/packages/MariaDB10/etc

Rename your my.cnf file

mv my.cnf my_backup.cnf

Start the database server

/usr/syno/bin/synopkg start MariaDB10

Restore the previous name of the my.cnf file

mv my_backup.cnf my.cnf

Restart the database server

/usr/syno/bin/synopkg restart MariaDB10

Check if the database is running

Upvotes: 0

Flo H
Flo H

Reputation: 59

I didn't find a synthetic answer but managed to solve my issues with previous answers and other threads.

As a potential memo:

  • Synology (DSM >=7)
  • do enable the PHP extensions on: web station > PHP parameters
  • do enable TCP/IP on: Maria DB > enable
  • prefer ip address: 127.0.0.1 to localhost;

Here is a test Snippet,

<?PHP
  $dsn = "mysql:host=127.0.0.1:3307;dbname=XXX";
  $username = "XXX";
  $password = "XXXXX";


  try {
      $pdo = new PDO($dsn, $username, $password);
  } catch(PDOException $e) {
    die($e);
  }

  $stmt = $pdo->prepare("SELECT * FROM user WHERE XXX = 'XXX'");
  $stmt->execute();
  while($row = $stmt->fetch()) {
    print_r($row);
  }
 ?>

Upvotes: 0

Ben
Ben

Reputation: 11

Just had the same problem and here is how I fixed it. I'm running on DSM 6.2.2-24922 Synology diskstation, i installed webstation with Nginx, PHP 5.6, as well as MariaDB 10 (and phpmyadmin).

The fix I found was to first activate the pdo_mysql extension in the php configuration.

Then go in the Core tab and look for the pdo_mysql.default_socket.

Per default i had 

/tmp/mysql.sock
in it but I changed it with 
/run/mysqld/mysql10.sock
I found this in the mariadb10 properties. It misteriously worked for me so i hope it will help somebody else as well :)

Upvotes: 1

Strots
Strots

Reputation: 21

I had the same problem.

I ended up solving it by enabling TCP/IP connection with Port: 3307. You may do that by opening the MariaDB 10 options in the Synology Package Center.

Then instead of: mysqli_connect("127.0.0.1", "root", "", DB_NAME);

I appended a the port number to it as the 5th parameter: mysqli_connect("127.0.0.1", "root", "", DB_NAME, 3307);

Upvotes: 2

zeroprobe
zeroprobe

Reputation: 580

What fixed it for me.

Go to Synology Apps > MariaDB

Ensure 'Enable TCP/IP connection' is enabled above Port 3306.
Note down Domain Socket: /run/mysqld/mysqld10.sock

And as comment above.
In Web Station > PHP Settings > Edit > Core tab Search > socket

Enter domain socket example /run/mysqld/mysqld10.sock into default_sockets

Upvotes: 1

PassionPete
PassionPete

Reputation: 51

I had the same problem: 

SQLSTATE[HY000] [2002] Connection refused

when attempting to connect to the MariaDB database running on my Synology NAS from php. Providing just username and password 

$conn = new PDO( "mysql:host=$servername;dbname=$dbname", $username, $password );

didn't work, while specifying the port number, a solution found elsewhere, didn't work either:

$conn = new PDO( "mysql:host=$servername;port=3307;dbname=$dbname", $username, $password );

What does work (for me):

$conn = new PDO("mysql:host=$servername:3307;dbname=$database", $login, $password);

The port name is found when opening the MariaDB 10 properties window from the main menu on the Synology NAS. Apparently, the port=3307 specification does not have any effect, but does not give an error message either.

Upvotes: 5

V&#226;neV&#226;ne
V&#226;neV&#226;ne

Reputation: 151

I have the answer, the connection was refused because i installed mariaDB on my sinology witch change the default port for MySQL from 3306 to 3307.

Upvotes: 3

Related Questions