Connecting to SQL database

Post Reply
killabotcz
Compulsive poster
Compulsive poster
Posts: 66
Joined: Fri Nov 07, 2014 8:58 am

Connecting to SQL database

Post by killabotcz » Sat Jan 17, 2015 3:42 pm

So, I've installed amxbans on my server and for some reason it just will not connect to the DB.

I have an exact replica of the server at home and when I used the same config it works fine.

Here are my settings. (relevant parts only)

amxbans.cfg
// AMXBans tries to get the address from the server automatically.
// You can set a different one here - ex. "<ip>:<port>"
amxbans_server_address "64.94.238.61:27015"

plugins.ini
amxbans_core.amxx
amxbans_main.amxx
variable-friendly.amxx

; AMX Mod X plugins

; Admin Base - Always one has to be activated
;admin.amxx ; admin base (required for any admin-related)
admin_sql.amxx ; admin base - SQL version (comment admin.amxx)

sql.cfg

// SQL configuration file
// File location: $moddir/addons/amxmodx/configs/sql.cfg

// *NOTE* Linux users may encounter problems if they specify "localhost" instead of "127.0.0.1"
// We recommend using your server IP address instead of its name

// *NOTE* amx_sql_type specifies the DEFAULT database type which admin.sma will use.

amx_sql_host "74.220.215.100"
amx_sql_user "therobo2_kab"
amx_sql_pass "*****"
amx_sql_db "therobo2_amxbansdb"
amx_sql_table "admins"
amx_sql_type "mysql"

I've added the nfo server to the whitelist in my DB hosting cpanel, this is the ip I used 64.94.238.61

I'm at a loss as what to do next.

Thanks

killabotcz
Compulsive poster
Compulsive poster
Posts: 66
Joined: Fri Nov 07, 2014 8:58 am

Re: Connecting to SQL database

Post by killabotcz » Sat Jan 17, 2015 4:04 pm

This is the error I'm seeing

L 01/17/2015 - 18:56:21: [amxbans_main.amxx] [AMXBans] Connection failed!
L 01/17/2015 - 18:56:21: [amxbans_main.amxx] [AMXBans] Threaded Query Error, Place: 17
L 01/17/2015 - 18:56:21: [amxbans_main.amxx] [AMXBans] Message: Can't connect to MySQL server on '74.220.215.100' (10060) (2003)

User avatar
soja
This is my homepage
This is my homepage
Posts: 2389
Joined: Fri May 18, 2012 3:20 pm

Re: Connecting to SQL database

Post by soja » Sat Jan 17, 2015 4:39 pm

Looks like you are using shared hosting for your database.

Have you double checked that the game server IP is white listed in remote mysql? Even if you have the game server IP there, it is likely that your game server is using the primary machine IP to try to connect(which you wouldn't know). You can check the mysql server logs to find failed logins from NFO IPs, review game server logs to see if you have an error like this:
Connection to SQL database has failed, Reason: [1045]: Access denied for user 'mysql_user'@'c-66-150-188-219.internap-la.nfoservers.com' (using password: YES)

in your amx logs(above is sourcemod error logs).

If you still can't find the IP, open a ticket and ask for the primary machine IP. The ticket may need to be escalated to John.
Not a NFO employee

killabotcz
Compulsive poster
Compulsive poster
Posts: 66
Joined: Fri Nov 07, 2014 8:58 am

Re: Connecting to SQL database

Post by killabotcz » Sun Jan 18, 2015 9:04 am

Thanks Soja,

This is immensely useful!

They're contacting the CEO to get the primary machine ID.

I'm also going to install the DB on one of NFO webservers as I'm going to assume that they've whitelisted themselves

Thanks again

User avatar
soja
This is my homepage
This is my homepage
Posts: 2389
Joined: Fri May 18, 2012 3:20 pm

Re: Connecting to SQL database

Post by soja » Sun Jan 18, 2015 5:18 pm

NFOs web/sql is configured a little differently from cPanel, NFO has a checkbox to "allow external hosts" which will allow all remote hosts to connect.

You're welcome :)
Not a NFO employee

killabotcz
Compulsive poster
Compulsive poster
Posts: 66
Joined: Fri Nov 07, 2014 8:58 am

Re: Connecting to SQL database

Post by killabotcz » Wed Jan 21, 2015 3:03 pm

I used the nfoservers web SQL db, worked a treat.

Funny thing is that my home servers are not populating data the same way the nfo server is (although they're both working great)

Any ideas?

Thanks

Image

User avatar
soja
This is my homepage
This is my homepage
Posts: 2389
Joined: Fri May 18, 2012 3:20 pm

Re: Connecting to SQL database

Post by soja » Wed Jan 21, 2015 3:04 pm

Is that on your home or the NFO SQL?
Not a NFO employee

killabotcz
Compulsive poster
Compulsive poster
Posts: 66
Joined: Fri Nov 07, 2014 8:58 am

Re: Connecting to SQL database

Post by killabotcz » Thu Jan 22, 2015 5:11 am

the SQL db is the nfo one, and one (the working one) of the game servers is nfo but the other two (connecting but not showing details) are my home/test servers.

User avatar
soja
This is my homepage
This is my homepage
Posts: 2389
Joined: Fri May 18, 2012 3:20 pm

Re: Connecting to SQL database

Post by soja » Thu Jan 22, 2015 6:01 am

Have you port forwarded correctly?
Not a NFO employee

killabotcz
Compulsive poster
Compulsive poster
Posts: 66
Joined: Fri Nov 07, 2014 8:58 am

Re: Connecting to SQL database

Post by killabotcz » Thu Jan 22, 2015 11:36 am

wait, you might be right (again), any idea what port I should be forwarding?

Thanks

User avatar
soja
This is my homepage
This is my homepage
Posts: 2389
Joined: Fri May 18, 2012 3:20 pm

Re: Connecting to SQL database

Post by soja » Thu Jan 22, 2015 11:56 am

27015 UDP and TCP
Not a NFO employee

killabotcz
Compulsive poster
Compulsive poster
Posts: 66
Joined: Fri Nov 07, 2014 8:58 am

Re: Connecting to SQL database

Post by killabotcz » Thu Jan 22, 2015 8:20 pm

I already have those ports forwarded so players can connect from the net.
I thought you meant different ports. I'll keep messing with the settings see what I can come up with
Thanks again

killabotcz
Compulsive poster
Compulsive poster
Posts: 66
Joined: Fri Nov 07, 2014 8:58 am

Re: Connecting to SQL database

Post by killabotcz » Sun Jan 25, 2015 7:34 am

Sorted it


I had my DDNS in the amxbans.cfg file, when I swapped it out for the actual IP it worked.
Shouldn't make a difference but I guess it did :)

Post Reply