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

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...





×
×
  • Create New...