User can't be bound correctly to mysql-server if multiple mysql-servers are configured

[Smith4545]

Describe the bug If multiple MySQL-servers are configured (admin interface > Resources > MySQL Server) and if a customer has only one mysql-server configured (as "Usable mysql-server") new databases of this customer will always be configured on the "Default"-mysql-server, which also contains Froxlor's database.

System information

• Froxlor version: 2.0.7
• Web server: nginx
• DNS server: none
• POP/IMAP server: Dovecot
• SMTP server: postfix
• FTP server: proftpd
• OS/Version: Debian 11

To Reproduce Steps to reproduce the behavior:

1. (On the Admin-Interface) go to "Resources" > "MySQL Server"
2. Via "Add new MySQL server" add at least one additional MySQL-server to the "Default" one.
3. (On the Admin-Interface) go to "Resources" > "Customers"
4. Add a customer with more than 0 possible "MySQL-databases" and only the additional MySQL-server (as configured in step 2) allowed.
5. (On the Admin-Interface) go to "Resources" > "Customers"
6. Click the customer username to perform a takeover.
7. Click "MySQL" > "Databases"
8. Click "Create database"
9. Create the database with some password and note down the name of the newly created database.
10. Run SHOW DATABASES; on the additional mysql-server (as configured in step 2) and the "Default" mysql-server, which contains Froxlor's database. The newly created database (as noted down in step 9) will appear in the list of databases of the "Default" mysql-server.

Expected behavior The newly created database and the corresponding user should not be present on the "Default" mysql-server, but on the additional mysql-server (as configured in step 2 of above steps).

Additional context

• If more than one mysql-server is allowed for a given customer, a drop-down will appear for the customer. The creation of the database will then happen on whatever database server the user picks.
• system.mysql_access_host is set to %
• On adding the additional mysql-server Test connection when saving was enabled.

[d00p]

identified, fix follows

[d00p]

It would be most helpful if you could verify that the following patch fixes this issue:

diff --git a/lib/Froxlor/Api/Commands/Mysqls.php b/lib/Froxlor/Api/Commands/Mysqls.php
index 334fce27..c5d78501 100644
--- a/lib/Froxlor/Api/Commands/Mysqls.php
+++ b/lib/Froxlor/Api/Commands/Mysqls.php
@@ -73,12 +73,12 @@ class Mysqls extends ApiCommand implements ResourceEntity
                        $password = $this->getParam('mysql_password');

                        // parameters
-                       $dbserver = $this->getParam('mysql_server', true, 0);
                        $databasedescription = $this->getParam('description', true, '');
                        $databasename = $this->getParam('custom_suffix', true, '');
                        $sendinfomail = $this->getBoolParam('sendinfomail', true, 0);
                        // get needed customer info to reduce the mysql-usage-counter by one
                        $customer = $this->getCustomerData('mysqls');
+                       $dbserver = $this->getParam('mysql_server', true, $this->getDefaultMySqlServer($customer));

                        // validation
                        $password = Validate::validate($password, 'password', '', '', [], true);
@@ -558,4 +558,13 @@ class Mysqls extends ApiCommand implements ResourceEntity
                $this->logger()->logAction($this->isAdmin() ? FroxlorLogger::ADM_ACTION : FroxlorLogger::USR_ACTION, LOG_WARNING, "[API] deleted database '" . $result['databasename'] . "'");
                return $this->response($result);
        }
+
+       private function getDefaultMySqlServer(array $customer) {
+               $allowed_mysqlservers = json_decode($customer['allowed_mysqlserver'] ?? '[]', true);
+               asort($allowed_mysqlservers, SORT_NUMERIC);
+               if (count($allowed_mysqlservers) == 1 && $allowed_mysqlservers[0] != 0) {
+                       return (int) $allowed_mysqlservers[0];
+               }
+               return (int) array_shift($allowed_mysqlservers);
+       }
 }

[Smith4545]

Yup I can confirm, that databases, the users and their GRANTs are being created on the "additional" mysql-server and not on the "Default" mysql-server anymore, if there is only the "additional" mysql-server allowed for the customer. The respective row in panel_databases also reflects that.

Seems to work. Thank you!