Useful queries for managing your extDB MySQL Database

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

Thanks. Can you give me a query that lists the top 10 cases with  the largest amount of money?

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?

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;


Oh wow Jo thank you!
Edit: I'm getting error 1146 table does not exist.

How do I delete a server object if I know its ID? Do I have to shut down the server first?

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.

Is there a way deleting everything from the database without deleting peoples bank account?

Is there a way deleting everything from the database without deleting peoples bank account?

Well it depends on what you mean by "everything".

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;

Remove the playerinfo line from the query.

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

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?

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.

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.