Mysqli_connect(): (HY000/2002): Connection refused in Website Building ON SYNOLOGY NAS

Question

I transferred a small service site from the AlterVista portal to my NAS Synology, the same code that ran fine now no longer runs on my NAS. From connection error to MariaDB. I have already read your forums about this problem but this is another system (not a pc but a nas with its own applications). I am using port 3307 because mariadb10 is listening on that port. (While mariadb 5 is listening on port 3306 You can also check)

On the NAS I have already installed MariaDB, PHP, PHP admin (which connects well to mariadb), Apache HTTP Server 2.2, and all the drivers and options that require the installation of PHP 5,7 (mysqli, pdo driver, and other options).

<?php
error_reporting(E_ALL);

$USERNAME = "root";
$PASSWORD = "xxxxxxx";  //null  
$DBSERVER = "127.0.0.1:3307";   //127.0.0.1:3306  mariadb5
$DBNAME = "miodb";

if(!($db_connection = new mysqli($DBSERVER, $USERNAME, $PASSWORD, $DBNAME)))
   die('Connect Error (' . $db_connection->connect_errno . ') '. $db_connection->connect_error);

if(!($db_selection = $db_connection->select_db($DBNAME)))
   die ("Errore nella selezione del DB.");

$query = $db_connection->query("SELECT * FROM MATERIALE");
while($cicle=$query->fetch_array(MYSQLI_ASSOC)){
    $url=$cicle['FOTO'];
    echo "<tr>
              <td>".$cicle['CODICEARTICOLO']."</td>
              <td><img src='".$url."' width='50' height='50' /> </td>
              <td>".$cicle['DESCRIZIONE']."</td>
              <td>".$cicle['PREZZO']." Euro</td>
          </tr>";
}
$query->close();
$db_connection->close();
?>

Warning: mysqli::__construct(): (HY000/2002): Connection refused in >/volume1/web/listino.php on line 111 Warning: mysqli::select_db(): Couldn't fetch mysqli >in /volume1/web/listino.php on line 114 Errore nella selezione del DB.

Answer 1

I modified in my graphic console Synology Nas:

1. on (PHP core setting port 3307 instead of 3306).

2. in PHP code port 3307 value.

3. enabled on maria db10 flag on TCP/IP port setting on 3307(default)

    <?php
   
    error_reporting(E_ALL);
   
    $USERNAME = "root";
    $PASSWORD = "password"; //null X
    $DBSERVER = "127.0.0.1";    //127.0.0.1:3306
    $DBNAME = "miodb";
    $DB_PORT = '3307';
    $SOCKET = "/run/mysqld/mysqld10.sock";   //not necessary
   
    $db_connection = new mysqli($DBSERVER, $USERNAME, $PASSWORD, $DBNAME, $DB_PORT);
   
    $query = $db_connection->query("SELECT * FROM MATERIALE");
    while ($cicle = $query->fetch_array(MYSQLI_ASSOC)) {
        $url = $cicle['FOTO'];
        echo "<tr>
                <td>" . $cicle['CODICEARTICOLO'] . "</td>
                <td><img src='" . $url . "' width='50' height='50' /> </td>
                <td>" . $cicle['DESCRIZIONE'] . "</td>
                <td>" . $cicle['PREZZO'] . " Euro</td>
            </tr>";
    }
    $query->close();
    $db_connection->close();
   

Answer 2

Please Try this

$db_host        = '127.0.0.1';
$db_user        = 'root';
$db_pass        = '';
$db_database    = 'database_name';
$db_port        = '3306';

$connection = mysqli_connect($db_host,$db_user,$db_pass,$db_database,$db_port);

Answer 3

You need to provide all six arguments when connecting to MariaDB on your Synology NAS. The sixth parameter is the socket name which you should use.

The correct code for mysqli connection would be this:

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
// Please do not connect as root!
$mysqli = new mysqli('localhost', 'root', 'password', 'miodb', 3307, '/run/mysqld/mysqld10.sock');
$mysqli->set_charset('utf8mb4'); // always set the charset

If you are only starting with PHP development then I recommend learning PDO instead of mysqli. Using PDO you would connect this way:

$pdo = new PDO("mysql:host=localhost;dbname=miodb;charset=utf8mb4;unix_socket=/run/mysqld/mysqld10.sock", 'root', 'password', [
    \PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION,
    \PDO::ATTR_EMULATE_PREPARES => false
]);