Resolved How to access MySQL customer tables on Windows?

[AlexFye]

Server operating system version

Microsoft Windows Server 2019

Plesk version and microupdate number

Version 18.0.49 Update #2

Hello,

We are developing an extension for Plesk in PHP and we have need of interacting with WordPress databases on Customer domains.

On Linux, we have been using the pm_Bootstrap::getDbAdapter() to make all of our requests using PDO. On Windows, however, the adapter could not interact with the database servers, erroring out saying that the table or view could not be found. After some debugging and research, I found this article (https://support.plesk.com/hc/en-us/articles/213928465-How-to-access-the-Plesk-psa-database-in-MySQL) explaining that Plesk on Windows Servers have two separate database servers that it uses. The PDO server can access the one on port 8306 no problem, but the Customers' databases that we need to access are on port 3306.

We tried using shell_exec, exec, and system functions with the command shell_exec("\"%plesk_dir%MySQL\\bin\\mysql.exe\" -P3306 \"-u$dbuser\" \"-p$dbpassword\" -e \"$sql\""); , but there has been no change and no error results, except result code 1 from the system function. We verified that the server does recognize the %plesk_dir% coming from Program Files.

How does one properly interact with the MySQL database server in PHP?

Thanks in advance.

 

[Peter Debik]

The default mysqli library in PHP should connect to 3306 "automatically" when no port is given, but you can also change the port on creating the object in case it does not. Would using mysqli be an option for you? Like

$_SESSION['conn'] = new mysqli($config_vars['dbhost'], $config_vars['dbuser'], $config_vars['dbpass'], $config_vars['dbname']); 
if (($_SESSION['conn'] === false) || ($_SESSION['conn'] == '')) {
    // handle exception here
} else {
    // continue on success here
}

Nothing speaks against addressing MySQL through PHP PDO library either. But is there a specific reason why you are trying to run this through the shell instead of using built-in PHP functions for it?

 

[AlexFye]

Hello, Peter.

We have no preference on what we use to interact with the Customers' database. In short, we want the ability to INSERT, UPDATE and DELETE from the wp_options table, whether its through the adapter, mysqli, or anything else. We used the pm_Bootstrap::getDbAdapter because that was part of the Extension API that we used for the rest of the extension development. For example:

pm_Bootstrap::init();
$pdo = pm_Bootstrap::getDbAdapter();
$sth = $pdo->prepare("SELECT vendor_id FROM domains WHERE id = ?");
...

Shell is what we considered using if we wanted to connect to the specific server on port 3306. Looking back, we probably could have just used MySQLi. Then as a last comment, are the following variables correct?

• $hostname = 'localhost'
• $username = 'xxxxxxx' (customer database username)
• $password = 'xxxxxxx' (customer database password)
• $database = 'xxxxxxx' (customer database name)

Thanks again.

 

[Kaspar]

Then as a last comment, are the following variables correct?

• $hostname = 'localhost'
• $username = 'xxxxxxx' (customer database username)
• $password = 'xxxxxxx' (customer database password)
• $database = 'xxxxxxx' (customer database name)

For use with MySQLi? Those look correct to me.

 

[AlexFye]

Thanks for your assistance. Using mysqli, we were able to properly interact with the database on Windows and were able to do the necessary changes. It all works