Jump to content
Froxlor Forum
  • 0

froxlor latest - mysql 8 support


bimmer

Question

Posted

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!

Recommended Posts

Posted

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

 

 

Posted

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
         *

 

Posted

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

Posted

This Patchfile does not work.

 

root@ws1:/var/www/froxlor/install/lib# patch --dry-run -b -p1 < cls.diff
can't find file to patch at input line 5
Perhaps you used the wrong -p or --strip option?
The text leading up to this was:
--------------------------
|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
--------------------------
File to patch: class.FroxlorInstall.php
checking file class.FroxlorInstall.php
Hunk #1 FAILED at 643.
Hunk #2 FAILED at 667.
2 out of 2 hunks FAILED

Maybe I´m doins something wrong?

Greets

Posted
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'

 

Posted

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

Posted
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
Posted
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

Posted

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");

 

Posted

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

 

Posted
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

Posted
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

Posted
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?

Archived

This topic is now archived and is closed to further replies.



×
×
  • Create New...