TFS 1.X+ [MYSQL] Connection going to localhost

[platano]

Hello dear forum, I have an issue with my DB connection. I have a webhost that has a cpanel, I decided to put the website on that webhost so I could connect to it instead of putting the OTServer and the website on the same place.

SO I have
1.- Webhost IP aka cPanel = 255.255.255.255
2.- VPS = 127.0.0.1

When I try to connect to my Webhost DB OTServer vomits some logs saying it can't connect because access is denied on my VPS because it's poiting my VPS IP instead of my WebHost IP I specified as "255.255.255.255" as an example, I specified the IP of my DB in config.lua probably I'm missing something can anyone help me out?

        -- Owner Data
        ownerName = "platano"
        ownerEmail = "[email protected]"
        url = "mywebsite.com"
        location = "USA"

        -- Messages
        motd = "Welcome to OT Chaos!"
        serverName = "OT Chaos"
        loginMessage = "Welcome to The Chaos Server!"
        displayGamemastersWithOnlineCommand = false

        -- MySql
        sqlType = "mysql"
        sqlHost = "255.255.255.255"
        sqlPort = 3306
        sqlUser = "user"
        sqlPass = "password"
        sqlDatabase = "dbName"
        sqlFile = ""
        sqlKeepAlive = 0
        mysqlReadTimeout = 10
        mysqlWriteTimeout = 10
        mysqlReconnectionAttempts = 5
        encryptionType = "sha1"
       -- World / Ip / Port
        worldId = 0
        ip = "127.0.0.1"
        worldType = "open"
        bindOnlyGlobalAddress = false
        loginPort = 7171
        gamePort = "7172"
        statusPort = 7171
        loginOnlyWithLoginServer = false

napalm911911@otchaos:~/otserv$ ./server
Compiled with GNU C++ version 5.4.0 20160609 for arch 64 Bits at Jun  3 2020 16:43:30

>> Loading config (config.lua)
>> Opening logs
>> Loading Password encryption:
>>> Using (SHA1) encryption ... (done).
>> Loading RSA key
>> Starting SQL connection

Failed connecting to database - MYSQL ERROR: Access denied for user 'user'@'127.0.0.1' (using password: YES) (1045)
server: databasemysql.cpp:39: DatabaseMySQL::DatabaseMySQL(): Assertion `connect(false)' failed.
Aborted (core dumped)

My IP is not that mask 255.255.255.255 I just rewrote it to avoid leaking my development IPs

Both 127.0.0.1 and 255.255.255.255 are real IPs I just changed their values to avoid leaking my IPs lets assume they're
35.123.34.59 and 39.34.12.213

 

[Merrok]

For starters this, again, is a question the internet and google can answer for you pretty quickly.

Magical tutorial on how to fix any error

Below you will find an instruction on how to fix 99% of the issues and errors posted on this forum. Not even clickbait. And not even a video. I might have misunderstood the concept of clickbait... During all those steps, 2 important key elements should always be kept in mind: Keep calm and use...

otland.net

Have fun reading that.

Now to your problem.
You apparently set your public IP to be your MySQL IP right?
By default a MySQL server, for security reasons, only listens on localhost (127.0.0.1). You can find and change that in the /etc/mysql/my.cnf file, if it's on a different server as your OT, but if it is, you should also encrypt that connection if it's outside your private subnet.
Now the second thing is that if you try to access MySQL with the root MySQL login, that won't work either.
The MySQL root user is limited to being only accessed by the system root user. Again for security reasons.
Also make sure that you set the IP correctly in the new user.
Per default your database IP should be 127.0.0.1 as well as the user's access IP, so 'otuser'@'127.0.0.1'
Again if it's a different server the @'ip' needs to be the IP of the client connecting.
After you created the user and granted him the privileges, sometimes it is necessary to "FLUSH PRIVILEGES;" so they take effect.
Those at least are the common causes of this issue.
Also make sure MySQL is actually running...
And try debugging, for example you can always try to manually connect to it with the data you gave in the config file for a more detailed error or to see if the problem is within the code or your config. But my guess is that if you try it manually you will get an error code 115
So assuming 39.34.12.213 is your public IP you used in the config (as 255.255.255.255) you would test mysql -u user -p -h 39.34.12.213.

I don't understand why a small OT (no matter what size) would separate server, webserver and database though...

Also please do me a favor and change that dumb "sha1" encryption (sha algorithms are hashing btw and not encryption) to "sha256" or any other supported sha2 or sha3 algorithm. sha1 is completely out of date and not supported or secure anymore. It's like using plain text.
I don't know why so many default options in tfs or any other distribution for OTs are still so old and bad...

 

[platano]

quote

I expected config.lua to actually work as it may appear to work, it asks for a parameter that I thought was gonna work, though there was some setting in the config.lua to actually make it work. Anyway pointing this out made me open my eyes to a new chance lol /etc/mysql/my.cnf

I did tried adding the website on a local enviroment but I just can't understand why I cant write data to the tables if the user created for mysql DB has all privileges on the damn DB.

Noticed in a post in stackoverflow that 127.0.0.1 and localhost are NOT the same for MySQL so I proceeded with this

CREATE USER 'forgottenserver'@'127.0.0.1' IDENTIFIED BY 'password';
CREATE USER 'forgottenserver'@'localhost' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON * . * TO 'forgottenserver'@'127.0.0.1';
GRANT ALL PRIVILEGES ON * . * TO 'forgottenserver'@'localhost';
FLUSH PRIVILEGES;

User it setup correctly in the config.lua of the server and the server actually runs and reads the DB just fine.

DB is fine and running I inserted some data manually and it worked but whenever I try to submit data using the website(localhost) it just wont write into the Table, I'm currently looking at apache error logs and it seems to be that there's something wrong and I'm trying to fix that.

Its super fine because right now the server is under development and I appreciate your help.

Lold at the Magical Tutorial xD, just want to be very clear that I expected config.lua > sqlHost = "255.255.255.255" to actually work as expected