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

21 answers to this question

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
31 minutes ago, d00p said:

Are you on latest git-version?

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

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

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



×