1410 You are not allowed to create a user with GRANT

[bastcom]

Hi,

after updating to Froxlor 2.2.6, I encountered the following error when i try to create a new database.

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

This are the log:

==> /var/log/apache2/error.log <==
froxlor[2673501]: SQLSTATE[42000]: Syntax error or access violation: 1410 You are not allowed to create a user with GRANT
froxlor[2673501]: --- DEBUG: #0 /var/www/froxlor/lib/Froxlor/Database/Database.php(122): PDOStatement->execute(Array) 
#1 /var/www/froxlor/lib/Froxlor/Database/Manager/DbManagerMySQL.php(363): Froxlor\Database\Database::pexecute(Object(PDOStatement), Array) 
#2 /var/www/froxlor/lib/Froxlor/Database/DbManager.php(209): Froxlor\Database\Manager\DbManagerMySQL->grantCreateToDb('b---m', 'b---4', '127.0.0.1') 
#3 /var/www/froxlor/lib/Froxlor/Api/Commands/Mysqls.php(118): Froxlor\Database\DbManager->createDatabase('b---m', 'F8%k@PF((Dd5:b9...', 0, 3, 'b---m') 
#4 /var/www/froxlor/customer_mysql.php(151): Froxlor\Api\Commands\Mysqls->add() #5 {main}

Additionally, I recently updated my Ubuntu system. Here are the versions currently installed:

• MySQL: 8.0.41-0ubuntu0.24.04.1
• Froxlor: 2.2.6

Could you help me resolve this issue?

Thanks
Bastian

[d00p]

Using multiple database servers? Possibly try this: https://github.com/froxlor/Froxlor/commit/9253a94aadea8e99816fa9a3d7c927f071fc6eb4

[bastcom]

Hey,

I took another look but wasn’t sure about the solution, so I decided to do 2 fresh installations of Froxlor. Unfortunately, I ran into the same error. Could you take a look?

I tried setting it up with both a local and an external database:

• Local Database:

  • Ubuntu 22.04
  • MySQL 8.0.41-0ubuntu0.22.04.1

• External Database:

  • Ubuntu 24.04
  • MySQL 8.0.41-0ubuntu0.24.04.1

Let me know if you need any more details. Thanks!

[d00p]

Please give way more detailed info about the scenario. You said you have two fresh installations of froxlor, then something with local and external database....what are the exact steps to reproduce? 

[bastcom]

Hi,

yes sure no problem. 

I’ve got a new server running Ubuntu 24.04 and an older one with Ubuntu 22.04. Both are fully updated and running MySQL 8.0.41.

I followed the Froxlor documentation and installed it locally on the Ubuntu 22.04 server:
Installation Guide

After setting everything up, I created a new customer account and tried to add a database under that account. But I ran into this error:

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

Since that didn’t work, I removed everything and reinstalled Froxlor again on Ubuntu 22.04. This time, I tried using the database on the Ubuntu 24.04 server instead, but I got the same error again.

Not sure what’s going wrong—any ideas?

Thanks

[d00p]

and did you create the "privileged" mysql user as described in the docs? https://docs.froxlor.org/latest/general/installation/tarball.html#_3-create-privileged-database-user

Please show your `lib/userdata.inc.php` file (remove passwords of course).

[bastcom]

Yes i did, this is the userdata.inc.php:
 

<?php
// autogenerated froxlor file

$sql = [
        'debug' => false,
        'host' => 'MYSQLSERVER',
        'user' => 'froxlorweb22',
        'password' => <<<'EOT'
PASSWORT
EOT,
        'db' => 'froxlorweb22',
];
$sql_root = [
        '0' => [
                'caption' => 'Default',
                'host' => 'MYSQLSERVER',
                'user' => 'froxroot',
                'password' => <<<'EOT'
PASSWORT
EOT,
        ],
];

btw. i have another Froxlor installation, and after updating the database and Froxlor, I'm seeing the same error.

I found this online:

Quote

 

The error message “ERROR 1410 (42000) means you are not pemitted to create a user with GRANT” indicates that you attempted to create a user in MySQL 8.0 or later while also granting them privileges in the same statement.

This error also occurred when connecting to a remote MySQL database. Since MySQL 8, you cannot (implicitly) create a user with the GRANT command.

 

https://vishalvyas.com/mysql-error-1410-you-are-not-allowed-to-create-a-user-with-grant/

[d00p]

I can't really get where the issue comes from, from what I can see in the trace, we're just running a

GRANT ALL ON `" . $database . "`.* TO :username@:host

Which should only adjust/set privileges for an already existing user, not create it...

are you sure the "global mysql-user" for the customer has been created successfully when creating the customer?

[bastcom]

Each time a new database is created, a corresponding user is also generated, correct? For example, if I create DB1, the system will create User1; for DB2, it will create User2, and so on.

I can see that the system successfully creates both the database and the user, but something seems to go wrong in the process.

 

I tried the command you sent in mysql:

GRANT ALL ON `DB`.* TO USER@IP;

It worked fine.

I also attempted to modify DbManagerMySQL.php with chatgpt.

This code is working for me.

public function grantCreateToDb(string $username, string $database, string $access_host)
{
    // Manually construct the query (avoiding placeholders for database objects)
    $query = "GRANT ALL ON `" . $database . "`.* TO '" . $database . "'@'" . $access_host . "'";

    // Execute the statement directly
    Database::pexecute(Database::prepare($query));
}

Any idea? I know the second $database is wrong, but it was a workaround. 

[d00p]

No idea, maybe mysql8 does not allow placeholders for user@host in the query, need to check, works perfectly fine in mariadb

[bastcom]

Ok yes, would be great if you can take a look. For now, I have a workaround and can use the system. 

Thanks for the help

[bastcom]

Hi again,

today, I removed my Froxlor installation along with MySQL. I then installed MariaDB and set up a fresh Froxlor installation. Everything is working fine with MariaDB—just wanted to keep you informed.

Best
Bastian