CentOS 7 - PHP 8.0 - MariaDB 10.5 - SQL syntax error during first setup

[Fanfurlio]

On an already existing server, CentOS 7 with PHP-FPM 8.0.16 and MariaDB 10.5.15

Downloaded latest Froxlor stable, following the documentation, and set up a virtualhost for it.

I start the setup process, all requirements check are green, then I insert a DB user and password for the "new" froxlor user, then I my (almost plaintext, no strange characters) MariaDB root password, and when clicking the continue button, I receive a blank page, and in the PHP-FPM log I have:

PHP Fatal error:  Uncaught PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'BY '[MY_FROXLOR_DBUSER_PASSWORD]'' at line 1 in /var/www/froxlor/install/lib/class.FroxlorInstall.php:744

Is it a problem with the MariaDB version or something else?

I also tried to manually create the user, but there's no way to tell the installer that it already exists.

[d00p]

The corresponding action that is run is:

$stmt = $db_root->prepare("
	CREATE USER '" . $username . "'@'" . $access_host . "' IDENTIFIED WITH mysql_native_password BY :password
");
$stmt->execute(array(
	"password" => $password
));

Does CentOS / MariaDB support the mysql_native_password? (would be strange if not I guess)

Also, the current Unittests for adding new users with this exact query succeed for mariadb-10.5 and php8 (see https://github.com/Froxlor/Froxlor/runs/5066376654?check_suite_focus=true)

[d00p]

 

I also tried to manually create the user, but there's no way to tell the installer that it already exists.

Yes that is currently correct, a pre-existing user as unprivilged user would be deleted and re-created.

[Fanfurlio]

You're spot on, if I try to execute the same query from the command line client, I get the same error:

MariaDB [(none)]> CREATE USER 'froxlor'@'localhost' IDENTIFIED WITH mysql_native_password BY 'random_garbage';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'BY 'random_garbage'' at line 1

The only way I can create an user on the CentOS standard MariaDB installation using old mysql SHA1 passwords is

MariaDB [(none)]> CREATE USER 'froxlor'@'localhost' IDENTIFIED VIA mysql_native_password USING PASSWORD('random_garbage');
Query OK, 0 rows affected (0.003 sec)

From here, what should I change in my configuration to allow Froxlor to create its user?

I tried setting old_passwords (both ways, also the default is 0), but that does not change anything.

EDIT: Just out of curiosity, is there a specific reason behind forcing mysql_native_passwords instead of using the DB server's default?

Edited February 18, 2022 by Fanfurlio

[d00p]

 

From here, what should I change in my configuration to allow Froxlor to create its user?

nothing in configuration but froxlor-code...need to check. have not used centos in any productive environment

 

EDIT: Just out of curiosity, is there a specific reason behind forcing mysql_native_passwords instead of using the DB server's default?

hm, unsure...might be because of unix_socket plugin possibly being default (or set as default) which would prevent the user from being able to connect (only from shell). But for mariadb, I've just read in the official docs: 

By default, when you create a user without specifying an authentication plugin, MariaDB uses the mysql_native_password plugin.

So it might really just be a syntax thing, need to check where this goes wrong exactly.

 

MariaDB [(none)]> CREATE USER 'froxlor'@'localhost' IDENTIFIED VIA mysql_native_password USING PASSWORD('random_garbage');
Query OK, 0 rows affected (0.003 sec)

can you verify that a login is then possible with user 'froxlor' and password 'random_garbage' ?

[Fanfurlio]

 

can you verify that a login is then possible with user 'froxlor' and password 'random_garbage' ?

Yes, I can login from the command line client using those credentials.

[major]

Hi,

I'm running into the same issue on Debian 11 with PHP 8 and MariaDB 10.5.

Installation Method "manual" (downloaded and extracted froxlor-latest.tar.gz, and ran the web-installer).

Debian 11.2
mariadb-server (1:10.5.12-0+deb11u1)
php8.0 (8.0.17-1+0~20220320.31+debian11~1.gbp34e045)

Happy to provide more information if needed, however the forum didn't let me post my terminal output due to spam-protection.

Best regards, Sven.

[d00p]

Has been fixed for the upcoming 0.10.34 release, see commit here: https://github.com/Froxlor/Froxlor/commit/a2f8d02b16bec815ce4d7996c9c2ff6b9bc62593

[major]

Perfect, thank you.