Useful queries for managing your extDB MySQL Database

  • Offline JoSchaap
  • Developer
  • Mercenary
  • ******
  • Posts: 479
  • Had a life.. Got a modem.. (~1996)

Useful queries for managing your extDB MySQL Database

« posted: Feb 01, 2015, 01:49 PM »
Sooner or later someone will most likely build a web interface for this as DayZ mod had.

Until then, here are some useful query's for managing your ExtDB MySQL database :)


it includes examples of partially / fully flushing your db and some of the many things possible in player/vehicle/object management :)

you can run SQL query's using MySQL Management software such as the toolbox, navicat, phpMyAdmin or whatever floats your MySQL boat :)

Code: [Select]
/* show the ammount of players in your database */
select count(*) as 'players in database' from playerinfo;

/* show  the ammount of vehicles in the datbase */
select count(*) as 'vehicles in database' from servervehicles;

/* show  the ammount of objects in the datbase */
select count(*) as 'objects in database' from serverobjects;


/* delete all player info, saves and stats
!! ONLY DO THIS WITH THE SERVER OFFLINE !!  */
delete from playerinfo;
delete from playersave;
delete from playerstats;
delete from playerstatsmap;
delete from banktransferlog;

/* delete all server objects and vehicles
!! ONLY DO THIS WITH THE SERVER OFFLINE !!  */
delete from serverobjects;
delete from servervehicles;

/* whipe the entire database (recreates tables, faster then deleting)
!! ONLY DO THIS WITH THE SERVER OFFLINE !!  */
truncate table adminlog;
truncate table antihacklog;
truncate table banktransferlog;
truncate table playerinfo;
truncate table playersave;
truncate table playerstats;
truncate table playerstatsmap;
truncate table serverinstance;
truncate table servermap;
truncate table serverobjects;
truncate table servervehicles;


/* find player UID based on (part of) his/her ingame name */
select UID from playerinfo where Name like '%JoSchaap%';

/* find all vehicles 'owned' by a specific player UID */
select * from servervehicles where variables like '%76561197960482553%';

/* find all objects 'locked' by a specific player UID */
select * from serverobjects where variables like '%76561197960482553%';


/* example of resetting a certain players base and vehicles so they won't be deleted
due to his vacation/brokenpc or some other reason
DO THIS WITH THE SERVER OFFLINE OR WHILE RESTARTING */
update serverobjects set LastInteraction = NOW(), Damage=0 where Variables like '%76561197960482553%';
update servervehicles set LastUsed = NOW(), Damage=0 where Variables like '%76561197960482553%';

also available here: https://gist.github.com/JoSchaap/09248dd6f123830650cf

If you need help building a specific query feel free to ask here :)

Re: Useful querys for managing your ExtDB MySQL Database

« Reply #1 posted: Feb 08, 2015, 08:32 PM »
Thanks. Can you give me a query that lists the top 10 cases with  the largest amount of money?
  • Offline hobart
  • First Blood
  • ***
  • Posts: 84

Re: Useful querys for managing your ExtDB MySQL Database

« Reply #2 posted: Feb 09, 2015, 05:43 AM »
Thanks alot this is very handy, I have a question. How would one add the ability to have a custom loadout table working similar to dayzCC had?
  • Offline JoSchaap
  • Developer
  • Mercenary
  • ******
  • Posts: 479
  • Had a life.. Got a modem.. (~1996)

Re: Useful querys for managing your ExtDB MySQL Database

« Reply #3 posted: Feb 09, 2015, 02:41 PM »
that's abit of a tough nut to crack, I'm more fammilair with MSSQL then with MySQL

but i think this should work :)

*EDIT* fixed it so it also sorts correctly :)

Code: [Select]
select
ID as 'serverobjects-id',
CAST(REPLACE(LTRIM(RTRIM(SUBSTRING_INDEX(SUBSTRING(Variables,(LOCATE('cmoney',Variables)+8),(LOCATE('cmoney',Variables)+24)),'],',1))),']]','') as decimal) as 'chest-value'
from
serverobjects
where Variables like '%cmoney%'
AND Variables NOT LIKE '%cmoney",0%'
order by
CAST(REPLACE(LTRIM(RTRIM(SUBSTRING_INDEX(SUBSTRING(Variables,(LOCATE('cmoney',Variables)+8),(LOCATE('cmoney',Variables)+24)),'],',1))),']]','') as decimal)
desc;

  • Offline hobart
  • First Blood
  • ***
  • Posts: 84

Re: Useful querys for managing your ExtDB MySQL Database

« Reply #4 posted: Mar 14, 2015, 10:55 PM »
Oh wow Jo thank you!
Edit: I'm getting error 1146 table does not exist.

Re: Useful querys for managing your ExtDB MySQL Database

« Reply #5 posted: Apr 11, 2015, 11:11 AM »
How do I delete a server object if I know its ID? Do I have to shut down the server first?
  • Online AgentRev
  • Developer
  • Veteran
  • ******
  • Posts: 2347

Re: Useful querys for managing your ExtDB MySQL Database

« Reply #6 posted: Apr 11, 2015, 05:50 PM »
Yes, you can shutdown the server and delete the entry from the database. You can also hunt it down live and use deleteVehicle. Either way should work.
  • Offline peden
  • Fresh Spawn
  • *
  • Posts: 14

Re: Useful querys for managing your ExtDB MySQL Database

« Reply #7 posted: May 16, 2015, 06:11 PM »
Is there a way deleting everything from the database without deleting peoples bank account?
  • Online AgentRev
  • Developer
  • Veteran
  • ******
  • Posts: 2347

Re: Useful querys for managing your ExtDB MySQL Database

« Reply #8 posted: May 16, 2015, 06:30 PM »
Is there a way deleting everything from the database without deleting peoples bank account?

Well it depends on what you mean by "everything".
  • Offline peden
  • Fresh Spawn
  • *
  • Posts: 14

Re: Useful querys for managing your ExtDB MySQL Database

« Reply #9 posted: May 16, 2015, 06:37 PM »
Something like this, but without deleting the money in peoples bankaccount

/* whipe the entire database (recreates tables, faster then deleting)
      !! ONLY DO THIS WITH THE SERVER OFFLINE !!  */
truncate table adminlog;
truncate table antihacklog;
truncate table banktransferlog;
truncate table playerinfo;
truncate table playersave;
truncate table playerstats;
truncate table playerstatsmap;
truncate table serverinstance;
truncate table servermap;
truncate table serverobjects;
truncate table servervehicles;
  • Online AgentRev
  • Developer
  • Veteran
  • ******
  • Posts: 2347

Re: Useful querys for managing your ExtDB MySQL Database

« Reply #10 posted: May 16, 2015, 06:38 PM »
Remove the playerinfo line from the query.
  • Offline Matt76
  • Mercenary
  • *****
  • Posts: 408
  • co founder of customcombatgaming.com

Re: Useful querys for managing your ExtDB MySQL Database

« Reply #11 posted: Jun 07, 2015, 09:25 PM »
If you want to monitor your top players you can run this query which shows the last 7 days


Code: [Select]
SELECT playerinfo.Name, playerstats.PlayerKills, playerstats.AIKills, playerstats.DeathCount, (playerstats.PlayerKills / playerstats.DeathCount) AS KDR
FROM playerstats
INNER JOIN playerinfo
ON playerstats.PlayerUID=playerinfo.UID WHERE LastModified > Date_Sub(Current_Timestamp, INTERVAL 7 DAY)
ORDER BY PlayerKills DESC
LIMIT 15

Example result

Re: Useful querys for managing your ExtDB MySQL Database

« Reply #12 posted: Jun 12, 2015, 10:20 AM »
How optimized is the structure of this database? All my college classes on MySQL stressed properly optimizing the database structure to get the best performance and efficiency out of it. I would assume we can use the MySQL binary drop-in replacement MariaDB to further improve Database performance.

Can the database be run on the same server machine as the game server?
  • Online AgentRev
  • Developer
  • Veteran
  • ******
  • Posts: 2347

Re: Useful querys for managing your ExtDB MySQL Database

« Reply #13 posted: Jun 12, 2015, 10:40 AM »
I built the DB to the best of my ability, there isn't supposed to be any performance issues. Anyway, most if not all the queries made by the mission only target single tables, so overall it shouldn't be very demanding. I've never used MariaDB, if you want to try it then go ahead.

Re: Useful querys for managing your ExtDB MySQL Database

« Reply #14 posted: Jun 13, 2015, 04:41 PM »
I built the DB to the best of my ability, there isn't supposed to be any performance issues. Anyway, most if not all the queries made by the mission only target single tables, so overall it shouldn't be very demanding. I've never used MariaDB, if you want to try it then go ahead.

Seems to be working flawlessly!! MariaDB really gives the DB a boost in performance. Especially since it is a Binary drop in replacement for MySQL.