Access denied for user to database

Post here your questions about SFS2X. Here we discuss all server-side matters. For client API questions see the dedicated forums.

Moderators: Lapo, Bax

moonlife
Posts: 45
Joined: 26 Sep 2016, 08:22

Access denied for user to database

Postby moonlife » 18 Jan 2022, 19:04

Hi,
We are using SFS2X 2.17.0 and MySQL 8.0.28.
We are migrating our game to another server. We installed SFS2X and Mysql database. Put out mysql connector named 'mysql-connector-java-8.0.23.jar' in jre/lib/ext folder (also placed under SFS2X/extensions/__lib__ and still no luck, but in our previous server we didn't need to do even that), and set all settings just like as in our previous server. Our Database Manager settings in Zone Configurator screen are attached to this post.
sfs_db.png
Our Database Manager settings
(37.65 KiB) Not downloaded yet


But we are getting this error message in smartflox.log when SFS starts:

Code: Select all

18 Jan 2022 | 21:49:00,240 | ERROR | main | v2.db.SFSDBManager |     | java.sql.SQLSyntaxErrorException:
::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
Exception: java.sql.SQLSyntaxErrorException
Message: Access denied for user 'mysql_user'@'127.0.0.1' to database 'my_db'
Description: The DBManager Test SQL failed: SELECT id FROM test LIMIT 1
Please double check your SQL code and make sure that Database server is running.


We tried to log in with our given Mysql user and password in Mysql Terminal Console to run our test query, it runs without a problem. How can we detect the reason of this error message and make SFS to connect our database?
moonlife
Posts: 45
Joined: 26 Sep 2016, 08:22

Re: Access denied for user to database

Postby moonlife » 19 Jan 2022, 12:32

I couldn't make it work with my current database user, so I created a new full privileged Mysql user via Mysql console, and it worked with this new user:
mysql> CREATE USER 'mysql_user2'@'127.0.0.1' IDENTIFIED BY 'password';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'mysql_user2'@'127.0.0.1' WITH GRANT OPTION;

I guess my first Mysql user (which I created via cPanel) was not bound to localhost.
User avatar
Lapo
Site Admin
Posts: 23008
Joined: 21 Mar 2005, 09:50
Location: Italy

Re: Access denied for user to database

Postby Lapo » 20 Jan 2022, 08:43

Hi,
yes the error reported by SFS2X indicates that there was a User permission problem in MySQL.
I am glad you were able to solve the problem.

Just one thing: I would recommend removing the mysql .jar file from SFS2X/extensions/__lib__ if you already have copied it in are/lib/ext, or viceversa. In other words it's best to just keep one copy.

This is because if you upgrade the mysql jar at a later time your classpath will be pointing both at the old and recent versions at the same time. Which can cause problems.

Cheers
Lapo
--
gotoAndPlay()
...addicted to flash games

Return to “SFS2X Questions”

Who is online

Users browsing this forum: No registered users and 60 guests