froxlor latest - mysql 8 support

bimmer · December 3, 2018

Hi All,

I just upgraded my server to mysql 8 with froxlor 0.9.38.7. It threw a couple mysql error during database creation so I decided to upgrade froxlor. However after the upgrade I can't even reach the login page due to a database error. Is it possible that froxlor latest is not compatible with mysql 8?? Or am I missing something?

Thanks a lot!

d00p · December 3, 2018

Output of the errors would be helpful.

bimmer · December 3, 2018

1 hour ago, d00p said:

Output of the errors would be helpful.

here you are:

A database error occurred

SQLSTATE[42000] [1231] Variable 'sql_mode' can't be set to the value of 'NO_AUTO_CREATE_USER'

d00p · December 3, 2018

Hm, okay, patch is on its way...if you do not use latest developer version (git) just remove every occurance of NO_AUTO_CREATE_USER in our code

d00p · December 3, 2018

additional info:

NO_AUTO_CREATE_USER is removed in MySQL 8.0.11
https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-11.html#mysqld-8-0-11-deprecation-removal

bimmer · December 3, 2018

1 hour ago, d00p said:

Hm, okay, patch is on its way...if you do not use latest developer version (git) just remove every occurance of NO_AUTO_CREATE_USER in our code

thanks, removing it solved the problem. However with the old and the new version when I try to create a new database for a user this error occurs:

A database error occurred

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IDENTIFIED BY 'password'' at line 2

d00p · December 4, 2018

Can you try this patch in lib/classes/database/manager/class.DbManangerMySQL.php:

diff --git a/lib/classes/database/manager/class.DbManagerMySQL.php b/lib/classes/database/manager/class.DbManagerMySQL.php
index b9a8d3ec..d4b9fed3 100644
--- a/lib/classes/database/manager/class.DbManagerMySQL.php
+++ b/lib/classes/database/manager/class.DbManagerMySQL.php
@@ -66,12 +66,25 @@ class DbManagerMySQL {
         * @param bool $p_encrypted optional, whether the password is encrypted or not, default false
         */
        public function grantPrivilegesTo($username = null, $password = null, $access_host = null, $p_encrypted = false) {
-               // grant privileges
-               $stmt = Database::prepare("
+               if (Database::getAttribute(PDO::ATTR_SERVER_VERSION) >= '8.0.0') {
+                       // create user
+                       $stmt = Database::prepare("
+                               CREATE USER `" . $username . "`.`" . $access_host . "` IDENTIFIED BY 'password'
+                       ");
+                       Database::pexecute($stmt);
+                       // grant privileges
+                       $stmt = Database::prepare("
+                               GRANT ALL ON `" . $username . "`.* TO :username@:host
+                       ");
+                       Database::pexecute($stmt, array("username" => $username, "host" => $access_host));
+               } else {
+                       // grant privileges
+                       $stmt = Database::prepare("
                                GRANT ALL PRIVILEGES ON `" . $username . "`.*
                                TO :username@:host IDENTIFIED BY 'password'
-                               ");
-               Database::pexecute($stmt, array("username" => $username, "host" => $access_host));
+                       ");
+                       Database::pexecute($stmt, array("username" => $username, "host" => $access_host));
+               }
                // set passoword
                if ($p_encrypted) {
                        $stmt = Database::prepare("SET PASSWORD FOR :username@:host = :password");

bimmer · December 4, 2018

patch < db.patch
Hmm... Looks like a unified diff to me...
The text leading up to this was:
--------------------------
|
--------------------------
File to patch: lib/classes/database/manager/class.DbManagerMySQL.php
Patching file lib/classes/database/manager/class.DbManagerMySQL.php using Plan A...
Hunk #1 failed at 66.
1 out of 1 hunks failed--saving rejects to lib/classes/database/manager/class.DbManagerMySQL.php.rej
done

d00p · December 4, 2018

Are you on latest git-version?

bimmer · December 4, 2018

31 minutes ago, d00p said:

Are you on latest git-version?

sorry, forgot to mention, no git installed, production server

d00p · December 4, 2018

The best try to manually enter the changes to the file

bimmer · December 4, 2018

3 hours ago, d00p said:

The best try to manually enter the changes to the file

did it, this error occured:

Parse error: syntax error, unexpected '"host"' (T_CONSTANT_ENCAPSED_STRING), expecting ')' in /vhosts/ssl/lib/classes/database/manager/class.DbManagerMySQL.php on line 81

d00p · December 4, 2018

I've committed the changes and the unit tests run perfectly fine. So, maybe a typo or similar from your side?

bimmer · December 4, 2018

2 hours ago, d00p said:

I've committed the changes and the unit tests run perfectly fine. So, maybe a typo or similar from your side?

well I deleted these lines and inserted again, now I have a different error

A database error occurred

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.`127.0.0.1` IDENTIFIED BY 'password'' at line 1

d00p · December 4, 2018

Is the file 1:1 the same as currently in git?

bimmer · December 4, 2018

1 hour ago, d00p said:

Is the file 1:1 the same as currently in git?

Now I tried to select all lines and inserted in a new file, named it as class.DbManagerMySQL.php and tried again.

The error is the same:

A database error occurred

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.'127.0.0.1' IDENTIFIED BY 'password'' at line 1

Maybe there is a different way to create user in Mysql 8?

d00p · December 4, 2018

no, this is the way, see https://dev.mysql.com/doc/refman/8.0/en/create-user.html

Can you debug what username the query uses? Any special characters in it?

bimmer · December 4, 2018

doesn't contain anything special but I checked your code if user and host is connected with a dot and it seems it doesn't work with mysql 8:

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

root@localhost [(none)]> CREATE USER 'somebody'@'localhost' IDENTIFIED BY 'PASSWORD';
Query OK, 0 rows affected (0.02 sec)

d00p · December 4, 2018

oh stupid me, didnt see the obvious...thanks mate

bimmer · December 4, 2018

yeah, I fixed this but I still have a db error but can't figure out what's missing..

A database error occurred

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'PASSWORD('yoQhkgclUs')' at line 1

code:

$stmt = Database::prepare("
CREATE USER '" . $username . "'@'" . $access_host . "' IDENTIFIED BY 'password'
");

d00p · December 4, 2018

12 minutes ago, bimmer said:

'PASSWORD('yoQhkgclUs')' at line 1

that's not the "CREATE USER" part, it's the SET PASSWORD:

$stmt = Database::prepare("SET PASSWORD FOR :username@:host = PASSWORD(:password)");

So it seems mysql uses another recommended way for setting passwords since 5.7.6

$stmt = Database::prepare("ALTER USER :username@:host IDENTIFIED BY :password");

Could you try that quickly?

bimmer · December 4, 2018

cool, it works!

Thanks a lot mate!

CengizPirasa · August 21, 2019

Hi,

dont know if this is the right thread to post:

I had the same Problem with a freh installation of Froxlor @mysql v8.

Had to rewrite the Installer:

From: (Line 645 to 663)

foreach ($mysql_access_host_array as $mysql_access_host) {
			$_db = str_replace('`', '', $this->_data['mysql_database']);
			$stmt = $db_root->prepare("
					GRANT ALL PRIVILEGES ON `" . $_db . "`.*
					TO :username@:host
					IDENTIFIED BY 'password'");
			$stmt->execute(array(
				"username" => $this->_data['mysql_unpriv_user'],
				"host" => $mysql_access_host
			));
			$stmt = $db_root->prepare("SET PASSWORD FOR :username@:host = PASSWORD(:password)");
			$stmt->execute(array(
				"username" => $this->_data['mysql_unpriv_user'],
				"host" => $mysql_access_host,
				"password" => $this->_data['mysql_unpriv_pass']
			));
		}

		$db_root->query("FLUSH PRIVILEGES;");

Replace with:

foreach ($mysql_access_host_array as $mysql_access_host) {
					
			//Create User	
			if (version_compare($version_server, '8.0.11', '>=')) {
				
				$stmt = $db_root->prepare("CREATE USER :username@:host IDENTIFIED BY :password");
				$stmt->execute(array(
					"username" => $this->_data['mysql_unpriv_user'],
					"host" => $mysql_access_host,
					"password" => $this->_data['mysql_unpriv_pass']			
				));

			} else {						
				$stmt = $db_root->prepare("
					GRANT ALL PRIVILEGES ON `" . $_db . "`.*
					TO :username@:host
					IDENTIFIED BY 'password'");
				$stmt->execute(array(
					"username" => $this->_data['mysql_unpriv_user'],
					"host" => $mysql_access_host
			));
				$stmt = $db_root->prepare("SET PASSWORD FOR :username@:host = PASSWORD(:password)");
				$stmt->execute(array(
					"username" => $this->_data['mysql_unpriv_user'],
					"host" => $mysql_access_host,
					"password" => $this->_data['mysql_unpriv_pass']
					));
				}						
		}
		// Ab dafür (DB -> Rolle -> User)
		$stmt = $db_root->prepare("CREATE ROLE 'Froxlor_Role'");
		$stmt->execute();

		$stmt = $db_root->prepare("GRANT ALL ON `" . $_db . "`.* TO 'Froxlor_Role'");
		$stmt->execute();

		foreach ($mysql_access_host_array as $mysql_access_host) {
			$stmt = $db_root->prepare("GRANT Froxlor_Role TO :username@:host");	
			$stmt->execute(array(
					"username" => $this->_data['mysql_unpriv_user'],
					"host" => $mysql_access_host
					));
			}

		foreach ($mysql_access_host_array as $mysql_access_host) {
                        $stmt = $db_root->prepare("SET DEFAULT ROLE Froxlor_Role TO :username@:host");
                        $stmt->execute(array(
                                        "username" => $this->_data['mysql_unpriv_user'],
                                        "host" => $mysql_access_host
                                        ));
                        }	
		
		if (version_compare($version_server, '8.0.11', '<')) {
		$db_root->query("FLUSH PRIVILEGES;"); 
		}

Some Info´s:

- I have used MySQL Roles

- Add User with GRANT only does not work anymore

- FLUSH PRIVILEGES is also obsolete.

For sure there are some possibilities to finetune some of the code.

hf with it

d00p · August 21, 2019

A diff would be easier to read but thanks I'll look into it

d00p · August 22, 2019

Can you verify that the following changes work?

diff --git a/install/lib/class.FroxlorInstall.php b/install/lib/class.FroxlorInstall.php
index ba101c35..8da5328b 100644
--- a/install/lib/class.FroxlorInstall.php
+++ b/install/lib/class.FroxlorInstall.php
@@ -643,21 +643,8 @@ class FroxlorInstall
 
                $mysql_access_host_array[] = $this->_data['serverip'];
                foreach ($mysql_access_host_array as $mysql_access_host) {
-                       $_db = str_replace('`', '', $this->_data['mysql_database']);
-                       $stmt = $db_root->prepare("
-                                       GRANT ALL PRIVILEGES ON `" . $_db . "`.*
-                                       TO :username@:host
-                                       IDENTIFIED BY 'password'");
-                       $stmt->execute(array(
-                               "username" => $this->_data['mysql_unpriv_user'],
-                               "host" => $mysql_access_host
-                       ));
-                       $stmt = $db_root->prepare("SET PASSWORD FOR :username@:host = PASSWORD(:password)");
-                       $stmt->execute(array(
-                               "username" => $this->_data['mysql_unpriv_user'],
-                               "host" => $mysql_access_host,
-                               "password" => $this->_data['mysql_unpriv_pass']
-                       ));
+                       $frox_db = str_replace('`', '', $this->_data['mysql_database']);
+                       $this->_grantDbPrivilegesTo($db_root, $frox_db, $this->_data['mysql_unpriv_user'], $this->_data['mysql_unpriv_pass'], $mysql_access_host);
                }
 
                $db_root->query("FLUSH PRIVILEGES;");
@@ -667,6 +654,38 @@ class FroxlorInstall
                return $content;
        }
 
+       private function _grantDbPrivilegesTo(&$db_root, $database, $username, $password, $access_host)
+       {
+               // mysql8 compatibility
+               if (version_compare($db_root->getAttribute(\PDO::ATTR_SERVER_VERSION), '8.0.11', '>=')) {
+                       // create user
+                       $stmt = $db_root->prepare("
+                               CREATE USER '" . $username . "'@'" . $access_host . "' IDENTIFIED BY :password
+                       ");
+                       $stmt->execute(array(
+                               "password" => $password
+                       ));
+                       // grant privileges
+                       $stmt = $db_root->prepare("
+                               GRANT ALL ON `" . $database . "`.* TO :username@:host
+                       ");
+                       $stmt->execute(array(
+                               "username" => $username,
+                               "host" => $access_host
+                       ));
+               } else {
+                       // grant privileges
+                       $stmt = $db_root->prepare("
+                               GRANT ALL PRIVILEGES ON `" . $database . "`.* TO :username@:host IDENTIFIED BY :password
+                       ");
+                       $stmt->execute($stmt, array(
+                               "username" => $username,
+                               "host" => $access_host,
+                               "password" => $password
+                       ));
+               }
+       }
+
        /**
         * Check if an old database exists and back it up if necessary
         *

CengizPirasa · August 22, 2019

H,

going to test this as soon as possible.

Got some other errors going on with buster in a LXC-Container. App-Armor won`t let apache start.

I think you get the results at 1am.

Greets

Sign In

froxlor latest - mysql 8 support

Question

Link to comment

Share on other sites

30 answers to this question

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Create an account or sign in to comment

Create an account

Sign in