Jump to content
Froxlor Forum
  • 0
bimmer

froxlor latest - mysql 8 support

Question

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!

Share this post


Link to post
Share on other sites

Recommended Posts

  • 0
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'

 

Share this post


Link to post
Share on other sites
  • 0

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

Share this post


Link to post
Share on other sites
  • 0
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

Share this post


Link to post
Share on other sites
  • 0
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

Share this post


Link to post
Share on other sites
  • 0

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

 

Share this post


Link to post
Share on other sites
  • 0

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

 

Share this post


Link to post
Share on other sites
  • 0
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

Share this post


Link to post
Share on other sites
  • 0

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

Share this post


Link to post
Share on other sites
  • 0
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

Share this post


Link to post
Share on other sites
  • 0
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?

Share this post


Link to post
Share on other sites
  • 0

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)

 

Share this post


Link to post
Share on other sites
  • 0

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

Share this post


Link to post
Share on other sites
  • 0
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?

Share this post


Link to post
Share on other sites
  • 0

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

 

 

Share this post


Link to post
Share on other sites
  • 0

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

Share this post


Link to post
Share on other sites
  • 0

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
         *

 

Share this post


Link to post
Share on other sites
  • 0

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

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×
×
  • Create New...