Database creation not working

[papppeter]

As a rule of thumb: before reporting an issue

• issue not found
• not option an to try it with master

Describe the bug
Database creation is not working

Following error happens:
A database error occurred
SQLSTATE[42000]: Syntax error or access violation: 1410 You are not allowed to create a user with GRANT

System information

• Froxlor version: 2.2.6
• PHP sapi & version: php-fpm 8.2.27
• Web server: apache2
• MySQL server: 8.0.41-0ubuntu0.22.04.1

To Reproduce
Steps to reproduce the behavior:

1. switch to a customer
2. Click on create db
3. Fill form
4. Submit
5. See error

we use prefixes, and customer can enter any db name

Expected behavior
It should create a db

[d00p]

seems to be a "mysql" related issue. MariaDB works just fine. I'm already on it.

[papppeter]

seems to be a "mysql" related issue. MariaDB works just fine. I'm already on it.

if i comment out 209 line in DbManager than it works again. It is something about global user

[d00p]

Can you verify whether the "global mysql" user for that customer exists? It should have the name of the customer's loginname.
Also, is this a local database or are you using multiple database-servers and the customer uses the remote one?

[papppeter]

Can you verify whether the "global mysql" user for that customer exists? It should have the name of the customer's loginname. Also, is this a local database or are you using multiple database-servers and the customer uses the remote one?

it is a local database. we have only one database.
it does not exists if i see it correclty. did i missed a step to create it?

[d00p]

it usually gets created automatically on customer creation if the customer has mysql-resources. If I remember correctly there is no action yet for existing users, so this might be the issue, possibly try this:

diff --git a/lib/Froxlor/Database/Manager/DbManagerMySQL.php b/lib/Froxlor/Database/Manager/DbManagerMySQL.php
index 1cf3f61c..61cb90f1 100644
--- a/lib/Froxlor/Database/Manager/DbManagerMySQL.php
+++ b/lib/Froxlor/Database/Manager/DbManagerMySQL.php
@@ -361,12 +361,15 @@ class DbManagerMySQL
         */
        public function grantCreateToDb(string $username, string $database, string $access_host)
        {
-               $stmt = Database::prepare("
-                       GRANT ALL ON `" . $database . "`.* TO :username@:host
-               ");
-               Database::pexecute($stmt, [
-                       "username" => $username,
-                       "host" => $access_host
-               ]);
+               // only grant permission if the user exists
+               if ($this->userExistsOnHost($username, $access_host)) {
+                       $stmt = Database::prepare("
+                               GRANT ALL ON `" . $database . "`.* TO :username@:host
+                       ");
+                       Database::pexecute($stmt, [
+                               "username" => $username,
+                               "host" => $access_host
+                       ]);
+               }
        }
 }

[papppeter]

I will try it.

By the way i did checked the upgrade script, and it fealt like it has some user creation or upgrade in it.

Do you think if you release a new version it will include the global user creation?

[d00p]

Yeah the update only handled existing global-users to correct some issues with permissions, looks like we should add an option to "Create global mysql for existing customers" in the next update, I agree

[papppeter]

by the way i tried your suggested patch, and it is working as well. thanks for the quick response!