Prestashop 9 on Azure - cannot connect to DB [Solved]

[rcitaliano]

Just trying to get prestashop running on azure, I've tried many times unfortunately without success, the issue is always the same:

Database Server is not found. Please verify the login, password and server fields (DbPDO)

I've tried:

• azure app service > web app + database
• azure app service > web app + database > then correcting the database collation to utf8mb4
• azure app service > web app + database > then creating a new database
• azure app service > web app + database > using service connectors

what works:

• I've created a simple PHP page that connects to the database and I can have a successful connections

now I'm trying to setup my own ubuntu server VM with LAMP stack, but I'm not an expert here so going through the process is really hard.

does anyone have any idea on solving the connection issue?

 

Solution:

• open azure portal https://portal.azure.com
• got to your resource group
• go to your database server, from here we are doing 2 things
  • update server wide settings
    • from the database server, on the left go to the "Server parameters"
    • make sure the following settings have these values:
    • character_set_server: UTF8MB4
    • collation_server: UTF8MB4_0900_AI_CI
    • require_secure_transport: OFF (THIS IS IMPORTANT)
  • update database specific settings
    • from the database server, on the left go to the "Databases" section
    • here you'll find the database that Azure created for you
    • be sure that:
      • Character Set is utf8mb4
      • Collation is:utf8mb4_general_ci
    • if the database created by azure doesn't have these settings:
      • write down the database name
      • delete the old database (you'll not be able to use it anyway)
      • create a new database clicking on "create" and create a new database with the settings above
        • use the same name used for the old database
        • if you didn't use the same name you choose to:
          • delete the database again, a create it again with the name used by azure
          • or give it a new name
            • if you give it a new name then now we need to update the web application to point to the new database:
              • go to your web application inside the resource group
              • on the left menu go to settings > configuration
              • on the top a message will appear: "View and edit your application settings and connection strings from Environment variables. Click here to go to Environment Variables menu"
              • click to see the environment variables
              • update the AZURE_MYSQL_DBNAME variable with the name of the new database
  • now on the prestashop installation page you MUST select to not use SSL
  • then you'll be able to connect

Edited July 26 by rcitaliano
found solution (see edit history)

[Paul C]

Certainly appears to be a network error. I assume you're 100% sure you've got the database config settings correct and the PDO extension for your database type (pdo_mysql for MySQL, pdo_sqlsrv for SQL Server) is configured for the php version you're using?

Docker would be easier to deploy than creating a VM.

 

Edited July 23 by Paul C
clarification of pdo extension (see edit history)

[rcitaliano]

thank you for the answer @Paul C!!

I think the issue is not on the network itself between the VMs created by azure, because testing the connection using a simple script, on the same folder where prestashop is installed, does work!

So I guess it's something about Prestashop that is not configured correctly.

I'm no PHP expert also so I'm not sure if I'm using the same extension

this is the sample script and it works

<?php
$host = getenv('AZURE_MYSQL_HOST');
$username = getenv('AZURE_MYSQL_USER');
$password = getenv('AZURE_MYSQL_PASSWORD');
$database = getenv('Azure_MYSQL_DBNAME');
$port = int(getenv('AZURE_MYSQL_PORT'));
# $flag = getenv('AZURE_MYSQL_FLAG');

$conn = mysqli_init();
# mysqli_ssl_set($conn,NULL,NULL,NULL,NULL,NULL);
mysqli_real_connect($conn, $host, $username, $password, $database, $port, NULL, MYSQLI_CLIENT_SSL);

if (mysqli_connect_errno($conn)) {
    die('Failed to connect to MySQL: ' . mysqli_connect_error());
}

echo 'Connected successfully to MySQL database!';
mysqli_close($conn);
?>

I've found, looking at prestashop's code that it uses a different approach, using some PDO class, I suppose it is an implementation of some sort of interface that then uses mysql... is this so?

I'll try to check how to use dockers on azure if I can't work out this issue

[rcitaliano]

thank you for the answer @Paul C!!

I think the issue is not on the network itself between the VMs created by azure, because testing the connection using a simple script, on the same folder where prestashop is installed, does work!

So I guess it's something about Prestashop that is not configured correctly.

I'm no PHP expert also so I'm not sure if I'm using the same extension

this is the sample script and it works

<?php
$host = getenv('AZURE_MYSQL_HOST');
$username = getenv('AZURE_MYSQL_USER');
$password = getenv('AZURE_MYSQL_PASSWORD');
$database = getenv('Azure_MYSQL_DBNAME');
$port = int(getenv('AZURE_MYSQL_PORT'));
# $flag = getenv('AZURE_MYSQL_FLAG');

$conn = mysqli_init();
# mysqli_ssl_set($conn,NULL,NULL,NULL,NULL,NULL);
mysqli_real_connect($conn, $host, $username, $password, $database, $port, NULL, MYSQLI_CLIENT_SSL);

if (mysqli_connect_errno($conn)) {
    die('Failed to connect to MySQL: ' . mysqli_connect_error());
}

echo 'Connected successfully to MySQL database!';
mysqli_close($conn);
?>

I've found, looking at prestashop's code that it uses a different approach, using some PDO class, I suppose it is an implementation of some sort of interface that then uses mysql... is this so?

I'll try to check how to use dockers on azure if I can't work out this issue

[Paul C]

19 hours ago, rcitaliano said:

I've found, looking at prestashop's code that it uses a different approach, using some PDO class, I suppose it is an implementation of some sort of interface that then uses mysql... is this so?

Yes. The fact that mysqli calls works but Prestashop fails indicates that you need the pdo_mysql extension added to your php config.

 

EDIT: Also make sure that the values returned by those environment variables match what you put in the Prestashop config (or use the environment variables themselves).

Edited July 24 by Paul C
added edit section for database config (see edit history)

[Knowband Plugins]

49 minutes ago, Paul C said:

Yes. The fact that mysqli calls works but Prestashop fails indicates that you need the pdo_mysql extension added to your php config.

 

If environment variables are working in the normal PHP script, then they will work on the PrestaShop configuration file as well. In that case, you can simply put the values i.e.

getenv('AZURE_MYSQL_HOST'), getenv('AZURE_MYSQL_USER') etc the Presashop configuration file i.e. app//config/parameters.php like

 

CHeck in the phpinfo() if the mysql_pdo extension is enabled on the server side ?

 

 

 

[rcitaliano]

it looks like PDO is enabled, is this correct?

I was also able to use a sample php file to connect to the database and it worked, I had to remove SSL from the server configuration

this is the php page

<?php
try {
        $host = getenv('AZURE_MYSQL_HOST');
        $port = intval(getenv('AZURE_MYSQL_PORT'));
        $database = getenv('AZURE_MYSQL_DBNAME');
        $password = getenv('AZURE_MYSQL_PASSWORD');
        $username = getenv('AZURE_MYSQL_USERNAME');
    $dsn = "mysql:host=$host;port=$port;dbname=$database";

    $db = new PDO($dsn, $username, $password);
    echo "Connected successfully";
} catch (PDOException $e) {
    echo "Connection failed: " . $e->getMessage();
}
?>

and the result was indeed: Connected successfully

now it looks like it worked and I'm continuing the installation

I'm worried tho about having disabled SSL, how can I keep it enabled?

[rcitaliano]

update... it failed the installation and it corrupted it :)

[Paul C]

23 hours ago, rcitaliano said:

update... it failed the installation and it corrupted it

If you echo out those environment variables you're using in your test script, do the values exactly match what you're entering in the database fields during installation?

[Knowband Plugins]

Can you share more details? What is corrupted?

If installation has started (As shared in the previous screenshot by you i.e. Creating tables with 10% progress), then it simply means the database connection is successful. There is something else. 

Share the details of errors that are on screen OR from the error log file.

 

[rcitaliano]

Hey People! thank you very much! I'll create a new thread for the new Issue, I guess it keeps the things organized!

so the solution for the original issue was:

• open azure portal https://portal.azure.com
• got to your resource group
• go to your database server, from here we are doing 2 things
  • update server wide settings
    • from the database server, on the left go to the "Server parameters"
    • make sure the following settings have these values:
      • character_set_server: UTF8MB4
      • collation_server: UTF8MB4_0900_AI_CI
      • require_secure_transport: OFF (THIS IS IMPORTANT)
  • update database specific settings
    • from the database server, on the left go to the "Databases" section
    • here you'll find the database that Azure created for you
    • be sure that:
      • Character Set is utf8mb4
      • Collation is:utf8mb4_general_ci
    • if the database created by azure doesn't have these settings:
      • delete the old database (you'll not be able to use it anyway)
      • create a new database clicking on "create" and create a new database with the settings above (call it prestashop, it's the default that prestashop uses)
      • now we need to update the web application to point to the new database:
        • go to your web application inside the resource group
        • on the left menu go to settings > configuration
        • on the top a message will appear: "View and edit your application settings and connection strings from Environment variables. Click here to go to Environment Variables menu"
        • click to see the environment variables
        • update the AZURE_MYSQL_DBNAME variable with the name of the new database
• now on the prestashop installation page you MUST select to not use SSL
• then you'll be able to connect