December 3, 20187 yr 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!
December 3, 20187 yr Author 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'
December 3, 20187 yr 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
December 3, 20187 yr 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
December 3, 20187 yr Author 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
December 4, 20187 yr 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");
December 4, 20187 yr Author 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
December 4, 20187 yr Author 31 minutes ago, d00p said: Are you on latest git-version? sorry, forgot to mention, no git installed, production server
December 4, 20187 yr Author 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
December 4, 20187 yr I've committed the changes and the unit tests run perfectly fine. So, maybe a typo or similar from your side?
December 4, 20187 yr Author 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
December 4, 20187 yr Author 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?
December 4, 20187 yr 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?
December 4, 20187 yr Author 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)
December 4, 20187 yr Author 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' ");
December 4, 20187 yr 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?
August 21, 20196 yr 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
August 22, 20196 yr 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 *
Archived
This topic is now archived and is closed to further replies.