Need help with KZStats Local on kz server

Discussion in 'Support & Feedback' started by ConfusedReality, Nov 25, 2016.

  1. ConfusedReality

    ConfusedReality NEW

    Joined:
    Nov 25, 2016
    Messages:
    13
    Likes Received:
    1
    Hello!

    I started with kz recently and set up my own KZ Timer server. It's working just fine and runs very smooth. However I would like to integrate it with the local KZStats API for my website. I have set it up and the webpage is running like it should. But, I can't seem to find out how to use MySQL with kztimer, or more precisely how you integrate the current kztimer sqlite setup with MySQL. (not much knowledge on databases sorry). I have tried to change the databases.cfg file accordingly to mysql without much luck.
    The webserver where KZStats is setup has access to the mysql database on the csgo server so there are no connectivity issues between the two. The KZStats webpage itself is connected to the csgo server but indicates: Server data unavailable. I'm guessing this is because the kztimer database I created in MySQL has no tables/data.

    Many thanks!
     
  2. Funk

    Funk Administrator

    Joined:
    Oct 9, 2015
    Messages:
    154
    Likes Received:
    1,000
    So are you currently using SQLite or MySQL for your server stats? KZStats local wont work with SQLite, you'd have to convert your SQLite db to MySQL.
     
  3. ConfusedReality

    ConfusedReality NEW

    Joined:
    Nov 25, 2016
    Messages:
    13
    Likes Received:
    1
    Hello!

    I have set up a MySQL database on the webserver which KZstats Local is running on. I also created a database user with full permission to the kztimer database in MySQL. The webserver is on the same local network as the cs go server, and the cs go server can access the kztimer database locally but seems to not be able to externally. I have opened ports for mysql but somehow it's still not accessible from anywhere other than the local network.

    phpmyadmin - user permissions to the kztimer database:
    upload_2016-11-30_12-13-24.png

    Databases.cfg is adjusted accordingly:
    }

    "kztimer"
    {
    "driver" "mysql"
    "host" "192.168.2.20" /IP of server running mysql (the webserver) - I have also tried using the public IP but that doesn't work either.
    "database" "kztimer"
    "user" "kzuser"
    "pass" "*******"
    }
    }
     
  4. Funk

    Funk Administrator

    Joined:
    Oct 9, 2015
    Messages:
    154
    Likes Received:
    1,000
    So your webserver (i.e. your website and your MySQL db) and your gameserver are all on the same box? (you seem to contradict yourself so I'm not sure)
     
  5. ConfusedReality

    ConfusedReality NEW

    Joined:
    Nov 25, 2016
    Messages:
    13
    Likes Received:
    1
    Everything is set up on one dedicated physical machine and there are two virtual Ubuntu machines running on that host. Webserver at 192.168.2.20 and the cs go server at 192.168.2.11. Both having the same public IP just different ports.
     
  6. Funk

    Funk Administrator

    Joined:
    Oct 9, 2015
    Messages:
    154
    Likes Received:
    1,000
    Have you tried using localhost?
     
  7. ConfusedReality

    ConfusedReality NEW

    Joined:
    Nov 25, 2016
    Messages:
    13
    Likes Received:
    1
    Localhost in the databases.cfg? Wouldn't it then use the ip of the cs go server and not the mysql database/webserver.
     
    Last edited: Nov 30, 2016
  8. Funk

    Funk Administrator

    Joined:
    Oct 9, 2015
    Messages:
    154
    Likes Received:
    1,000
    If your website and your MySQL install are on the same VM then localhost should work.
     
  9. ConfusedReality

    ConfusedReality NEW

    Joined:
    Nov 25, 2016
    Messages:
    13
    Likes Received:
    1
    Tried localhost and it's not working. Website and MySQL are installed on the same VM, the csgo server is installed on another one.
     
  10. Café

    Café CASUAL

    Joined:
    Jan 21, 2016
    Messages:
    76
    Likes Received:
    32
    Is your mysql server on port 3306?
     
  11. ConfusedReality

    ConfusedReality NEW

    Joined:
    Nov 25, 2016
    Messages:
    13
    Likes Received:
    1
    Yes.

    Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name
    tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 27642/mysqld
     
  12. pano

    pano #KZGER Donor KZ Server Owner

    Joined:
    Feb 5, 2015
    Messages:
    516
    Likes Received:
    159
    Not sure how it is done on Ubuntu since I use Debian but try this (actual config file name and/or path could vary!):

    vi /etc/mysql/my.cnf

    search for this line and change it to this:

    bind-address = 0.0.0.0

    Maybe that helps. Restart your mysql service afterwards.
    If this is not the right solution for your problem check in the sourcemod errorlogs if their is some socket error.

    If so try this:

    vi /etc/init.d/mysql

    add this at sanity checks()

    ln -s /var/run/mysqld/mysqld.sock /tmp/mysql.sock

    Again, paths and actual filenames could vary in your case.
     
  13. ConfusedReality

    ConfusedReality NEW

    Joined:
    Nov 25, 2016
    Messages:
    13
    Likes Received:
    1
    bind-address was set to 0.0.0.0 already from previous tries, and netstat says it's listening on all addresses and port 3306.

    I added the lines you mentioned inside the sanity checks under document at vi /etc/init.d/mysql and restarted mysql with no luck. I checked the paths and they are exactly the same at my build.

    I wish "host" "LAN IP of mysql/webserver" would work in the databases.cfg... Since I can connect to it with this command: mysql -u kzuser -p -h 192.168.2.20(IP of mysql/webserver). While connecting to it via public IP fails.
     
  14. pano

    pano #KZGER Donor KZ Server Owner

    Joined:
    Feb 5, 2015
    Messages:
    516
    Likes Received:
    159
    Does the connection work with root? The db user seems configured correctly, maybe some access rights are missing.
     
  15. ConfusedReality

    ConfusedReality NEW

    Joined:
    Nov 25, 2016
    Messages:
    13
    Likes Received:
    1
    Tried to change user in databases.cfg to root, still no success. Works fine to use mysql -u root -p -h 192.168.2.20.

    I looked up the error log in the sourcemod directory and found this:


    L 12/02/2016 - 19:27:19: SourceMod error session started
    L 12/02/2016 - 19:27:19: Info (map "de_dust") (file "errors_20161202.log")
    L 12/02/2016 - 19:27:19: [SM] Unable to load extension "cleaner.ext": Failed to find signature. Please contact the author.
    L 12/02/2016 - 19:27:20: [SM] Unable to load extension "dbi.mysql.ext": libz.so.1: cannot open shared object file: No such file or directory
    L 12/02/2016 - 19:27:20: [SM] Exception reported: [KZTimer] Unable to connect to database (Driver "mysql" not found)
    L 12/02/2016 - 19:27:20: [SM] Blaming: KZTimerGlobal.smx
    L 12/02/2016 - 19:27:20: [SM] Call stack trace:
    L 12/02/2016 - 19:27:20: [SM] [0] SetFailState
    L 12/02/2016 - 19:27:20: [SM] [1] Line 337, kztimerGlobal/sql.sp::db_setupDatabase
    L 12/02/2016 - 19:27:20: [SM] [2] Line 837, KZTimerGlobal.sp::OnPluginStart
    L 12/02/2016 - 19:27:20: [SM] Unable to load plugin "KZTimerGlobal.smx": Error detected in plugin startup (see error logs)
    L 12/02/2016 - 19:27:20: [admin-flatfile.smx] Error(s) detected parsing addons/sourcemod/configs/admins_simple.ini
    L 12/02/2016 - 19:27:20: [admin-flatfile.smx] (line 39) Invalid flag detected: Z
    L 12/02/2016 - 19:27:32: Error log file session closed.
    L 12/02/2016 - 19:27:32: SourceMod error session started
    L 12/02/2016 - 19:27:32: Info (map "workshop/632673660/kz_zxp_final4") (file "errors_20161202.log")
    L 12/02/2016 - 19:27:32: [admin-flatfile.smx] Error(s) detected parsing addons/sourcemod/configs/admins_simple.ini
    L 12/02/2016 - 19:27:32: [admin-flatfile.smx] (line 39) Invalid flag detected: Z
    L 12/02/2016 - 19:28:28: Error log file session closed.
     
  16. pano

    pano #KZGER Donor KZ Server Owner

    Joined:
    Feb 5, 2015
    Messages:
    516
    Likes Received:
    159
    [SM] Unable to load extension "dbi.mysql.ext": libz.so.1: cannot open shared object file: No such file or directory

    It seems there is a source package missing on your gameserver vm.

    Check this: https://idleserver.com/threads/sour...libz-so-1-cannot-open-shared-object-file.701/

    Ubuntu should use a similiar package. Install it on your server.

    EDIT:

    apt-get install lib32z1

    or

    apt-get install lib32z1 --reinstall

    if it is broken.


    [admin-flatfile.smx] (line 39) Invalid flag detected: Z

    admin_simple.ini seems case sensitive, use 99:z as flag for your admin acc, like this:

    "yoursteamid" "99:z"
     
    PTKFGS!? likes this.
  17. ConfusedReality

    ConfusedReality NEW

    Joined:
    Nov 25, 2016
    Messages:
    13
    Likes Received:
    1
    The lib32z1 package was already installed, running "apt-get install lib32z1 --reinstall" and restarting the VM and cs go server seems to have fixed the issue! Thanks alot for all the help!
     
    PTKFGS!? likes this.
  18. pano

    pano #KZGER Donor KZ Server Owner

    Joined:
    Feb 5, 2015
    Messages:
    516
    Likes Received:
    159
    you're welcome
     
    PTKFGS!? likes this.
  19. ConfusedReality

    ConfusedReality NEW

    Joined:
    Nov 25, 2016
    Messages:
    13
    Likes Received:
    1
    Hi

    There seems to still be minor issues with the kztimer plugin when using the mysql server over sqlite.
    Country tags and player ranks/scores are not showing in the scoreboard or chat the same way they do with sqlite.
    Photos as example:
    playerranktag.PNG
    scoreboardtag.PNG
    Every other stats from player jumps to map finishes etc looks to be working fine, except for the personal player ranks and points, newb, trainee and so on

    Here is what the error log says:
    L 12/11/2016 - 06:49:55: Info (map "workshop/408339325/bkz_apricity") (file "errors_20161211.log")
    L 12/11/2016 - 06:49:55: [SM] Exception reported: Invalid query Handle 0 (error: 4)
    L 12/11/2016 - 06:49:55: [SM] Blaming: KZTimerGlobal.smx
    L 12/11/2016 - 06:49:55: [SM] Call stack trace:
    L 12/11/2016 - 06:49:55: [SM] [0] SQL_HasResultSet
    L 12/11/2016 - 06:49:55: [SM] [1] Line 5456, kztimerGlobal/sql.sp::sql_selectRankedPlayerCallback
    L 12/11/2016 - 06:50:02: Error log file session closed.
    [SM] Unable to load extension "cleaner.ext": Failed to find signature. Please contact the author.

    Thanks!
     
  20. pano

    pano #KZGER Donor KZ Server Owner

    Joined:
    Feb 5, 2015
    Messages:
    516
    Likes Received:
    159

Share This Page