Jump to content

SQL Clean Up


Nox

Recommended Posts

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
Link to comment
Share on other sites

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!

Link to comment
Share on other sites

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 :)

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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 :(.

Link to comment
Share on other sites

  • 3 weeks later...

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!

Link to comment
Share on other sites

  • 3 months later...

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

Link to comment
Share on other sites

  • 2 weeks later...

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Advertisement
×
×
  • Create New...