SQL Clean Up

[Nox]

Hello

 

Go to Navicat or something like that

 

Create a FUNCTION and make it like this

 

BEGIN
DELETE FROM character_data WHERE Alive = 0;
DELETE FROM object_data WHERE Damage = 1;
UPDATE traders_data SET qty = 10 WHERE qty = 0;
END

With this,

Clean all Dead Player

Clean All Destroyed Vehicles

Restore 10 Items to Trader who have 0 in Stock.

 

then After you need to call it with Batch

 

Create a Bat file and write this

echo Cleaning Server Database
cd\ 
cd C:\Program Files\MySQL\MySQL Server 5.6\bin\ mysql.exe -u YOURUSER -pYOURPASS dayz-epoch --execute="call NAMEOFFUNCTION()"

Enjoy

[ekroemer]

Hi Nox,

 

thank you for sharing, may I ask something?

 

Would something like

UPDATE object_data SET Damage = 0.95 WHERE Damage = 1; 

restore destroyed vehicles to a repairable status?

 

What I'd like to do would be

• delete all destroyed vehicles that are not player-owned while
• restoring the destroyed player-owned vehicles

 

What I really, ideally want to do is to restore (player owned) vehicles that were locked when destroyed into the status they were before someone killed them while the owner was logged out.

Do you see a way to do this?

 

Thanks in advance!

[Nox]

sure, but ....

 

- when you do this, all stuff inside stay :), if you know that become to 0.95 when server reboot, you don't scare to destroye your vehicles.

- Other Way, A camp with 10 cars, all destroy by a raid, when the server reboot, all is return, juste need to be repair .... :s (no need to protect us)

 

With Del Vehicles from Database,

 

-- Less charge for your database on boot

-- More New vehicules for new player.

 

This is my way of thinking. maybe i'm wrong :)

[ekroemer]

You're correct that this could be exploited, but as it is now, what happens all to often is that while  most of the players play the game as it is meant to be (yes, competively), some very few destroy what others have worked hard for without any risk to themselves simply by logging in when noone else is awake and wreaking havok.

 

That's basically the same discussion as 'why invest effort in building a base when a single player can destroy/remove the work of many others within minutes'.

 

Your pro-deletion points are valid, but only partially:

• all non-owned vehicles and those destroyed in battle (not locked) would be removed -> less database load
• the few owned/locked vehicles left won't make a big difference to the new players, they won't effect spawns, noticably
• you'll still have to be careful about how much to risk your ride in battle because then they aren't locked.

 

So, basically the database command I asked for would give an admin the chance to redress harm done outside of normal game parameters - just as he could ban dupers or give back gear to players who lost to someone nuke-hacking.

 

I feel there is a difference between a hard played game and one completely without rules/codes. There simply is no possibility to guard your gear as you could do in RL and as long as the game mechanics themselves don't enforce RL-logic there should be a method to 'cheat back'.

 

As for the method to do so, I don't know enough about the database structure to make more fitting suggestions.

The code example you gave indicated that there is an entry Damage that is set to 1 for a destroyed (exploded) vehicle.

I don't know what setting the damage to a value between 0 and 1 will to to the individual vehicle components.

I'd surely be happy to re-create the vehicle in a state that is just barely repairable (if 'all parts 100% destroyed' means 'vehicle explodes' then it could be feasible to set the body to only 95% damaged). But I imagine the database command to effect individual vehicle parts will be very complex.

[Achmed]

How would i go about clearing the player_login table while leaving the last 3 days worth of logins?

[Bungle]

What are the columns you have to work with? (I really need to check out the epoch Database sometime! haha)

 

Would be perhaps:

DELETE FROM player_login WHERE Something = something; 

 

[Nox]

carefull with 3 days cause

 

In holliday, maybe your users don't connect on your Dayz server during few days.

 

I thinks you can make it to 2 weeks (14 dayz)

[Achmed]

Is the player_login table not just a record of who has logged in and out?

[Nox]

You have the last login

[Achmed]

so its best to just leave them then even though last login is in the character_data table as well?

[Nox]

hop sorry my bad,

 

Last login is in character_data.

[Falcon911]

Nox using TOAD for SQL

 

http://www.toadworld.com/m/freeware/552.aspx

 

I think I got it but fail as usual... 

 

CREATE FUNCTION cleanup

BEGIN

DELETE FROM character_data WHERE Alive = 0;

DELETE FROM object_data WHERE Damage = 1;

UPDATE traders_data SET qty = 10 WHERE qty = 0;

END

 

This correct?

[Nox]

Fail for what, what's wrong ?

[Falcon911]

Trying to install/write the function within Toad. Maybe I am missing something. I copy and paste the function code you stated and from there I do not know what to do. There is no Save function from what I can see. 

[Nox]

you can make some SQL request ? and auto run it ?

 

If you can, you need to make 3 SQL queries

 

- Del Dead survivor

DELETE FROM character_data WHERE Alive = 0

 

- Del Destroyed vehicles

DELETE FROM object_data WHERE Damage = 1

 

- Give item to traders

UPDATE traders_data SET qty = 10 WHERE qty = 0

[GucciMane]

Keep in mind

UPDATE traders_data SET qty = 10 WHERE qty = 0

Sets vehicle traders to restock 10 of each currently out of stock vehicle.

 

Use

UPDATE `traders_data` SET qty=10 WHERE qty=0 AND afile<>'trade_any_vehicle' AND afile<>'trade_any_boat';

to update all items except vehicles. Also remember for anyone using 

DELETE FROM character_data WHERE Alive = 0

 

That if you have issues with a player dying from a (verifiable) glitch or even worse, a (verifiable) hacker, deleting their body means you can't tell what gear they had when they died in order to do a restore.

[Nox]

Right But

 

This is a Buggy Game and if every time you need to give stuff to dead by glitch, bug or somethings else .... you loose your time.

 

To remove this action, i've put on my server lot of stuff point, and in 30min or 1 hour you can have a full stuff.

 

My customer like this idea and when they die by glitch nobody said to me , Plz give me my stuff :(.

[Darth_Rogue]

What if my db doesn't reside on my game server and I have to connect to it remotely?  What would be the proper syntax through the batch file to connect to a remote server database and run those commands?   I've done some searching on it but can't find any specific directions on connecting to a database on a remote machine.  I know SQL pretty well on local connections, but remoting in is way different.  Thanks in advance!

[MGT]

If you delete dead players with status "0" then they will lose any humanity they've gained or lost

[Nox]

no Humanity stay

[Achmed]

Im not big on mysql but i dont think it matters if its local or not as the function is placed in the database on the mysql server and so is no diffrrent then any data etc.

[Sc0rc3d]

i tryed this over Navicat function:

 

BEGIN
DELETE FROM character_data WHERE Alive = 0;
DELETE FROM object_data WHERE Damage = 1;
UPDATE traders_data SET qty = 10 WHERE qty = 0;
END

 

but it does not seem to work. The trader Items still 0. But it looks like that some data will be deleted right because the Database is a bit smaler.

Any ideas?

 

 

Cheers

 

 

Sc0rc3d

[Codemanx]

With a dayz.st server, where would I put this information in the SQL?

 

Thanks

[Randomness]

What i dont understand is why this topic is a sticky. DayZ.st has a page about this, epoch has a page explaining this, why is the wheel being reinvented here?

 

http://dayz.st/w/Epoch_PBO_config_options_and_how_they_work#Server_Maintenance

http://dayzepoch.com/wiki/index.php?title=Server_Configuration_Instructions#Server_Maintenance

 

Also the Epoch server files include sql files that add events to the database to do this.

 

 

DELETE FROM character_data WHERE Alive = 0; Does infact make a player lose his humanity if he didnt respawn when the data is deleted.

[Nasdero]

If you delete dead players with status "0" then they will lose any humanity they've gained or lost

He is right, when a player died and leave the server without joining again and you run your DB-cleanup, then he will lost his humantiy, if he rejoin right after he died, then it is ok.