0

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.

leo73
  • 1
  • 1
  • 3
  • mysqli_connect(host, username, password, dbname, port, socket) please follow this – Waqar Akram Oct 12 '20 at 10:39
  • leave it empty follow below comment. also check that your connect should not be closed before querying or too early. i.e $db_connection->close(); – Waqar Akram Oct 12 '20 at 10:44
  • Please do not use PHP 5. You should also not be using Apache 2.2. There is Apache 2.4 available. – Dharman Oct 12 '20 at 12:24

3 Answers3

-1

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);
Waqar Akram
  • 107
  • 5
  • Warning: mysqli_connect(): (HY000/2002): Connection refused in >/volume1/web/listino.php on line 120 Fatal error: Uncaught Error: Call to >a member function select_db() on boolean in /volume1/web/listino.php:126 >Stack trace: #0 {main} thrown in /volume1/web/listino.php on line 126. could you use the same syntax as the code I posted above please? ;) this in order not to upset the code. – leo73 Oct 12 '20 at 11:31
  • does your connection is successful? Can you please share line 120 with me? – Waqar Akram Oct 12 '20 at 11:34
  • no it doesn't ;120 line: $db_connection = mysqli_connect($db_host,$db_user,$db_pass,$db_database,$db_port); FATAL ERROR because in this moment we are not manage the error connection. in my code it's managed !! – leo73 Oct 12 '20 at 11:39
  • have you change your db port accordingly? – Waqar Akram Oct 12 '20 at 11:41
  • mysqli_connect accepts two parameter first is db connection & second the name of db like this mysqli_select_db($db_connection , 'db_name') – Waqar Akram Oct 12 '20 at 11:43
  • no i did not changed port on mariaDB !!! could you explain me how make it in MariaDB10 software? (i know mariadb10 default port is 3307), but we need verify it !! – leo73 Oct 12 '20 at 11:45
  • Does your connection is successful? – Waqar Akram Oct 12 '20 at 11:47
  • please use this mysqli_select_db($db_connection , 'db_name') instead of select_db() for selecting database – Waqar Akram Oct 12 '20 at 11:48
  • Yes you can do !! do you have teamviewer or anydesk for connection? – leo73 Oct 12 '20 at 11:58
  • can you please provide me ftp account? – Waqar Akram Oct 12 '20 at 12:12
  • i prefare you enter by my laptop connected to my NAS. so i can see your fixed. – leo73 Oct 12 '20 at 12:16
  • Sorry Can't access via any desktop tool except filezilla – Waqar Akram Oct 12 '20 at 12:17
  • can you please tell me teamviewer id & password – Waqar Akram Oct 12 '20 at 12:20
  • 831 366 230 ; fuu178 – leo73 Oct 12 '20 at 12:23
  • Hello Akram i read you page, in synology nas the file my.cnf is in this path: /var/packages/MariaDB/etc , expecially in my nas the file is not present i created it using VI editor but i don't know what to put inside. (in your page you wrote in maria db 10 port and bind they not work). So what i must write in this file? – leo73 Oct 12 '20 at 13:20
-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();
Dharman
  • 30,962
  • 25
  • 85
  • 135
leo73
  • 1
  • 1
  • 3
  • Please take a moment to read through the [editing help](//stackoverflow.com/editing-help) in the [help]. Formatting on Stack Overflow is different than other sites. Also, take a look at [answer] – Dharman Oct 12 '20 at 14:22
  • You need to stop manually checking for errors. Please read: [Should we ever check for mysqli_connect() errors manually?](https://stackoverflow.com/q/58808332/1839439) and [Should I manually check for errors when calling “mysqli_stmt_prepare”?](https://stackoverflow.com/q/62216426/1839439) – Dharman Oct 12 '20 at 14:23
  • I fixed your answer a little bit, but I still think it's slightly unclear. I barely see any difference between this and the code in question. – Dharman Oct 12 '20 at 16:40
  • Also, word of advice: Don't use `while` loop. It can be quite confusing. Try `foreach($query as $cicle)` instead – Dharman Oct 12 '20 at 16:41
  • Why did you roll it back? – Dharman Oct 12 '20 at 20:50
-2

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
]);
Dharman
  • 30,962
  • 25
  • 85
  • 135