[BF4] Stats webpage for XpKiller's Stats Logger Plugin

[ImportBot]

Originally Posted by ty_ger07*:

 

Code has been updated.

 

Changes:

- Removed server IP address from server banner image

- Added basic AdKats integration to highlight a player if they are banned or warned if running AdKats plugin

- Optimizations and improvements (combined queries where possible to reduce number of queries in pool)

 

 

Since it is a pretty big change, let me know if I accidentally broke anything. I tested with a Windows server this time, so let me know if there are any case-sensitive things I messed up on Linux servers.

[ImportBot]

Originally Posted by BuRockK*:

 

hi ty_ger, i know this was mentioned in one of the pages, but i dont think there was a way to fix this as i recall its a dice bug?

 

Still, how to fix the "Not Specified" weapon entries in stats pages?

 

 

EDIT:

 

Also just notice this show up on main page:

"No 'BF4' servers were found in this database! Please notify this website's administrator."

 

I checked and the db is fine, its there. Plugin is working and enabled etc.

[ImportBot]

Originally Posted by ty_ger07*:

 

hi ty_ger, i know this was mentioned in one of the pages, but i dont think there was a way to fix this as i recall its a dice bug?

 

Still, how to fix the "Not Specified" weapon entries in stats pages?

There is no way to fix it. You could ask Dice to fix their game server code, but good luck.

 

Here is the problem. More often than not, vehicle kills and stationary weapon kills are reported from the game server to procon as:

Friendlyname: Death

Fullname: Death

Damagetype: none

Slot: Primary

Kitrestriction: None

 

So, if you have a player with 200 vehicle kills or stationary weapon kills, how do you communicate that in a meaningful way? I chose "Not Specified" because there is no way to know if it is a vehicle kill or a stationary weapon kill and which vehicle or stationary weapon.

 

You could just choose to not display those kills at all. But then it gets worse because a player could have 200 vehicle kills and 20 pistol kills and then their player stats would say that they have 220 kills but their weapon stats would only display 20 pistol kills and people would ask where the other 200 un-displayed weapon kills are.

 

Also just notice this show up on main page:

"No 'BF4' servers were found in this database! Please notify this website's administrator."

 

I checked and the db is fine, its there. Plugin is working and enabled etc.

When did that start happening? What changed when it started happening? Is it still happening? Did you update to the new version of the stats page and then see it happen? Or are you using the old version of the stats page?

 

In phpmyadmin, what is the result if you run this query?

Code:

SELECT tg.`GameID`, ts.`ServerID`
FROM `tbl_games` tg
INNER JOIN `tbl_server` ts ON ts.`GameID` = tg.`GameID`
WHERE tg.`Name` = 'BF4'
AND (ts.`ConnectionState` IS NULL
OR ts.`ConnectionState` = 'on')

The result should be something like this:

Code:

GameID	ServerID
1		1
1		2
1		3
1		4
1		5

[ImportBot]

Originally Posted by BuRockK*:

 

Ok i understand now. "Not specified" is Death (one of the vehicles)

 

When did that start happening? What changed when it started happening? Is it still happening? Did you update to the new version of the stats page and then see it happen? Or are you using the old version of the stats page?

 

In phpmyadmin, what is the result if you run this query?

Code:

SELECT tg.`GameID`, ts.`ServerID`
FROM `tbl_games` tg
INNER JOIN `tbl_server` ts ON ts.`GameID` = tg.`GameID`
WHERE tg.`Name` = 'BF4'
AND (ts.`ConnectionState` IS NULL
OR ts.`ConnectionState` = 'on')

The result should be something like this:

Code:

GameID	ServerID
1		1
1		2
1		3
1		4
1		5

Im not sure how to run queries. I will test it if you walk me through. I saw the msg today. I was in stats page yesterday and it was fine (im mostly checking my stats page anyway).

 

I did not change anything in php or in plugin.

I dont think anything is changed with the way it is now.

Stats are there, the database is also there.

The newest version i have i think is the one you updated before your yesterdays post

[ImportBot]

Originally Posted by ty_ger07*:

 

It's hard for me to speculate about what has stopped working if nothing changed and it was working fine.

 

To run a query, first log into phpmyadmin (ask your web server host how, if you don't know). Then, select the stats database on the left. Then, click SQL at the top center. Then, paste the query, and run the query. What is the result?

Video:

[ImportBot]

Originally Posted by BuRockK*:

 

this is what i got:

 

 

MySQL returned an empty result set (ie zero rows). (The query lasted 0.0003 sec)

 

 

SELECT tg.`GameID`, ts.`ServerID`

FROM `tbl_games` tg

INNER JOIN `tbl_server` ts ON ts.`GameID` = tg.`GameID`

WHERE tg.`Name` = 'BF4'

AND (

Ts.`ConnectionState` IS NULL

OR ts.`ConnectionState` = 'on'

)

LIMIT 0, 30

[ImportBot]

Originally Posted by ty_ger07*:

 

this is what i got:

 

 

MySQL returned an empty result set (ie zero rows). (The query lasted 0.0003 sec)

 

 

SELECT tg.`GameID`, ts.`ServerID`

FROM `tbl_games` tg

INNER JOIN `tbl_server` ts ON ts.`GameID` = tg.`GameID`

WHERE tg.`Name` = 'BF4'

AND (

Ts.`ConnectionState` IS NULL

OR ts.`ConnectionState` = 'on'

)

LIMIT 0, 30

Hmm. Ok..?

 

Can you screenshot the data in tbl_games and tbl_server? Sounds like something was changed or deleted or ConnectionState was tampered with.

[ImportBot]

Originally Posted by BuRockK*:

 

Here they are:

 

tbl_games

tbl_games.jpg

 

tbl_server

tbl_server.jpg

[ImportBot]

Originally Posted by ty_ger07*:

 

In tbl_server, the GameID should be 1 according to the information in tbl_games. Not 0. What caused that to change?

Edit the server in tbl_server by changing GameID from 0 to 1. It should work then.

 

Your most recent stats are probably separate from your old stats as a result. You probably just didn't notice.

[ImportBot]

Originally Posted by BuRockK*:

 

Well, it is true some player names and stats are from my old server. But why this problem accurs now i have no idea. Its been over 2 months now that the new server is using that same db.

 

Edit: fixed now yes, thank you for your help ty_ger

 

Edit2: Its gone again. Seems like something is reseting it back to 0

 

I did a little test and it seems to reset GameID to 0 when someone views the website. I changed it to 1 and waited a bit and refreshed in db...was not changing to 0. After i visited stats page and clicked on any tab. I saw GameID change to 0

[ImportBot]

Originally Posted by ty_ger07*:

 

Well, it is true some player names and stats are from my old server. But why this problem accurs now i have no idea. Its been over 2 months now that the new server is using that same db.

 

Edit: fixed now yes, thank you for your help ty_ger

 

Edit2: Its gone again. Seems like something is reseting it back to 0

 

I did a little test and it seems to reset GameID to 0 when someone views the website. I changed it to 1 and waited a bit and refreshed in db...was not changing to 0. After i visited stats page and clicked on any tab. I saw GameID change to 0

I don't know why it would change to 0, but I know that the stats page can't be causing it to change. The stats page doesn't have a single query which alters or updates any of the stats tables. For the stats page to change any value in the stats tables, it would need a query which starts with "UPDATE", "INSERT", "ALTER TABLE", etc. and reference a stats table. Every query for the stats tables in the stats page is "SELECT" which is a read-only query. And, it should have no effect or conflict with XpKiller's stats plugin, because InnoDB tables are non-locking on SELECT. The only things I know of which could change those values are XpKiller's plugin, maybe AdKats, and maybe BFAdminCP; or anything or anyone else you may have connecting to the database.

 

I think that the problem is that I forgot to tell you that you need to turn off XpKiller's plugin while you change the GameID to 1. If XpKiller's plugin is running while it is changed, I don't think the change will last long since XpKiller's plugin has 0 in its running memory. I think that XpKiller's plugin will try to change the GameID back to 0 at least once every round if the GameID was 0 in XpKiller's plugin's running memory.

 

While XpKiller's plugin is disabled, you might want to optimize all the tables (which rebuilds and repairs InnoDB tables) while you are at it. Rebuilding the tables can fix indexing errors. It's a simple process. One check box, one button click, and wait a little bit. Google phpmyadmin optimize tables.

 

Why did it ever change in the first place? That's a weird one.

[ImportBot]

Originally Posted by BuRockK*:

 

I don't know why it would change to 0, but I know that the stats page can't be causing it to change. The stats page doesn't have a single query which alters or updates any of the stats tables. For the stats page to change any value in the stats tables, it would need a query which starts with "UPDATE" or "INSERT" and reference a stats table. Every query for the stats tables in the stats page is "SELECT" which is a read-only query. And, it should have no effect or conflict with XpKiller's stats plugin, because InnoDB tables are non-locking on SELECT. The only things I know of which could chnage those values are XpKiller's plugin, AdKats, and maybe BFAdminCP.

 

I think that the problem is that I forgot to tell you that you need to turn off XpKiller's plugin while you change the GameID to 1. If XpKiller's plugin is running while it is changed, I don't think the change will last long since XpKiller's plugin has 0 in its running memory.

 

While XpKiller's plugin is disabled, you might want to optimize all the tables (which rebuilds and repairs them) while you are at it. Rebuilding the tables can fix indexing errors. It's a simple process. One check box, one button click, and wait a little bit. Google phpmyadmin optimize tables.

 

Why did it ever change in the first place? That's a weird one.

I dont really know why it changed. But before this happened, plugin gave an error in plugin console while i was asleep couple of days ago (i saw the error after i woke up and thankfully server was empty at the time so the console didnt fill up with anything else until i was awake). I dont know why but i thought maybe if i turn "instant chat loggin" off it would maybe slow down the plugin (i had these errors before and i read they were just errors that shows when its somehow lagging to communicate with the db and its harmless as long as everything works_). After i turned that option off...(didnt notice any difference) i turned it back on. After that i visited stats page to see instant chat logging was working again and there when i saw stats page was giving database error on main page.

 

I will try change GameID again with the plugin disabled...didnt tried that my bad

[ImportBot]

Originally Posted by GR101*:

 

Code has been updated.

 

Changes:

- Removed server IP address from server banner image

- Added basic AdKats integration to highlight a player if they are banned or warned if running AdKats plugin

- Optimizations and improvements (combined queries where possible to reduce number of queries in pool)

 

 

Since it is a pretty big change, let me know if I accidentally broke anything. I tested with a Windows server this time, so let me know if there are any case-sensitive things I messed up on Linux servers.

Thanks ty_ger07 for the update.

[ImportBot]

Originally Posted by SmackDown*:

 

Great update. I liked the Adkats integration and hope you can do more with Adkats.

 

Thank you for your effort and great work!

[ImportBot]

Originally Posted by ty_ger07*:

 

I am glad that you like it!

[ImportBot]

Originally Posted by ty_ger07*:

 

I am thinking that excluding bots from the pages does a small disservice to the gaming clan/community. It is good to exclude bots in terms of performance, but maybe limiting instead of excluding is a better approach. "Limiting" as in: provide the bot with data to crawl, but make sure that the data is simple/basic information which doesn't require large queries to produce.

[ImportBot]

Originally Posted by SmackDown*:

 

I have a question regarding the latest update. With the adkats connection, is that creating more ban width usage or putting a heavy resource usage on the server? Sorry I am not an expert in this area so I might not be explaining myself correctly.

[ImportBot]

Originally Posted by ty_ger07*:

 

I have a question regarding the latest update. With the adkats connection, is that creating more ban width usage or putting a heavy resource usage on the server? Sorry I am not an expert in this area so I might not be explaining myself correctly.

It makes no change to the game server. It should make no measurable change to the web server. The database server, yes, it makes a small increase in load, but barely measurable difference in bandwidth.

 

Why do you ask?

[ImportBot]

Originally Posted by ty_ger07*:

 

Code updated. Change:

 

- Unbanned bot access and made bot access restricted instead. This allows search engines to crawl the stats page and get more clan/community attention while also restricting bot database performance impact to some slower features.

[ImportBot]

Originally Posted by Sp0rAdiC*:

 

Do I just overwrite the current files with the new ones?

[ImportBot]

Originally Posted by ty_ger07*:

 

Yup. Copy and paste. It's meant to be that easy. Just don't copy and past and overwrite the /config/config.php file (unless you want to edit in your database connection details and adjustments again).

[ImportBot]

Originally Posted by Sp0rAdiC*:

 

Yup

Cool, thank you! Works like a charm.

[ImportBot]

Originally Posted by SmackDown*:

 

It makes no change to the game server. It should make no measurable change to the web server. The database server, yes, it makes a small increase in load, but barely measurable difference in bandwidth.

 

Why do you ask?

Sorry for the late response to your question.

 

I asked this because after installing the updated webpage, every thing worked fine. About 23 hours later, our database went down and I had no connection to it for about 6 hours. I was getting a 1040 error code stating that it cannot connect and to many connections.

 

I turned in a repair ticket to my database provider and the fixed it and stated they raised my connection limit which corrected the issue.

 

So while waiting for a response back from my database provider, I reverted your program back to a previous version hoping that it would fix the problem. But it didn't, and I had to wait for about 6 hours for it to come back on-line. With my database down, I had no admin control because BFadminCp was not working. This caused my and my 12 other admins a huge problem when trying to admin our servers.

 

Do you think that my issue was related to the update? I am hesitant to revert back to the newer version at this time.

[ImportBot]

Originally Posted by ty_ger07*:

 

The newer version has less number of SQL query connections than before. Some of the queries are a little bigger/slower if you use AdKats (since it is selecting one more column from one more table), but the AdKats queries are all inside existing queries (except for one which checks if AdKats is installed) and therefore the number of connections is less than before because I was able to remove/combine some other queries in other places.

 

So, if anything, if you are bumping up on a connection limit, the newer versions with reduced number of queries is exactly what you want.

 

For example:

 

SELECT cp.`Soldiername`, cp.`Score`, cp.`Kills`, cp.`Deaths`, cp.`TeamID`, cp.`SquadID`, cp.`CountryCode`, sub.`PlayerID`, sub.`ban_status`

FROM `tbl_currentplayers` cp

LEFT JOIN

(

SELECT tpd.`PlayerID`, tpd.`SoldierName`, adk.`ban_status`

FROM `tbl_playerdata` tpd

INNER JOIN `tbl_server_player` tsp ON tsp.`PlayerID` = tpd.`PlayerID`

INNER JOIN `tbl_playerstats` tps ON tps.`StatsID` = tsp.`StatsID`

LEFT JOIN `adkats_bans` adk ON adk.`player_id` = tpd.`PlayerID`

WHERE tpd.`GameID` = {$GameID}

AND tsp.`ServerID` = {$ServerID}

) sub ON sub.`SoldierName` = cp.`SoldierName`

WHERE cp.`ServerID` = {$ServerID}

AND cp.`TeamID` = {$this_team}

GROUP BY cp.`SoldierName`

ORDER BY {$rank} {$order};

 

 

All of that was being done anyway. I didn't add a query or increase the number of connections. I just made the query a little larger.

[ImportBot]

Originally Posted by SmackDown*:

 

Thanks for the response! I will revert back to the new version.

[ImportBot]

Originally Posted by ty_ger07*:

 

Let me know how it goes.

[ImportBot]

Originally Posted by Sp0rAdiC*:

 

I searched the thread and seems like this was mentioned but is it possible for the server banner to display offline if the server or Procon layer goes down instead of just showing the last update?

[ImportBot]

Originally Posted by ty_ger07*:

 

The stats plugin doesn't provide a timestamp and heartbeat to the stats database; which makes it hard to know if the server or layer is offline.

 

If I reviewed the game server rcon and procon layer documentation, there theoretically could be a way to determine if either is offline, but it may not be reliable due to access denied, trusted host, and firewall settings at either the game server, layer server, or web server. I would rather not attempt to go down that path because I don't think the information would be reliable (for reasons stated) and it would require modifying config.php to add those IP addresses and passwords (which some people may not do properly, may not update to a newer config.php file, or may not want to do for security purposes (or whatever) regarding people who may have read access of the config.php file on their web server FTP).

 

That being said, yes, it is possible to know if the game server or layer server has gone offline using information in the stats database. You can determine that the server has gone offline if:

#1: the game server or layer server went offline while players were in the server

#2: map stats are enabled

#3: you wait a substantial amount of time (maybe an hour is a good benchmark) to determine that the players who are supposedly "in the server" are not updating their stats and the map stats aren't updating like they should be

 

The caveat is that if the game server or layer server goes offline while no players are in the server, I don't know of any way to measure that condition, using the stats database alone.

 

I will look into it. It woukd not be a real-time measurement, but could be added as a feature which detects extended server interruptions.

 

The problem is: if someone disables map stats logging after map stats logging has been enabled at a previous time, it may always make the server look offline, even when it really isn't.

[ImportBot]

Originally Posted by Sp0rAdiC*:

 

Thanks for the detailed reply. It's not that big of a deal as the server is usually online. Just thought it would be a cool feature to add if it was fairly easy to implement.

[ImportBot]

Originally Posted by ty_ger07*:

 

I have found a potential process related to the topic of detecting when a game server or layer server has gone offline. This process would not rely on anything other than the current players table and player stats table of the stats database.

 

The logic:

1) First determine if there are players in the server according to the current players table or server table.

2) Execute the following query to detect that at least one of those players has a `LastSeenOnServer` player stats time within this hour.

Code:

SELECT sub.`LastSeenOnServer`
FROM `tbl_currentplayers` cp
INNER JOIN
(
	SELECT tpd.`SoldierName`, tps.`LastSeenOnServer`
	FROM `tbl_playerdata` tpd
	INNER JOIN `tbl_server_player` tsp ON tsp.`PlayerID` = tpd.`PlayerID`
	INNER JOIN `tbl_playerstats` tps ON tps.`StatsID` = tsp.`StatsID`
	WHERE tpd.`GameID` = {$GameID}
	AND tsp.`ServerID` = {$ServerID}
) sub ON sub.`SoldierName` = cp.`SoldierName`
WHERE cp.`ServerID` = {$ServerID}
AND sub.`LastSeenOnServer` > NOW() - INTERVAL 1 HOUR AND NOW()

3) If no rows are returned, that means that every player who is "in the server" according to the current players table is actually not in the server or at least not recording their stats properly.

4) Trigger a solution such as emptying out the current players table, adjusting the current player server count to 0, and/or display a notification of the issue detected.

 

PROBLEM: if player(s) idle or seed a server for at least an hour while the server is otherwise idle, it would trigger a false detection of server issue.

 

EDIT: Keeping with my earlier convictions, I should not empty the current player table or change the server table's number of players. I haven't modified the stats plugin's data in the past and I shouldn't start now. Instead, a simple alert above the scoreboard stating something like "Alert: It appears that there are players in server but their stats aren't updating properly." should suffice.