SQL Clean Up

[Nasdero]

This one should work:

 

 

delete FROM `character_data`
USING character_data, character_data AS tmpcharacter_data
WHERE NOT character_data.CharacterID=tmpcharacter_data.CharacterID
AND character_data.CharacterID<tmpcharacter_data.CharacterID
AND (character_data.PlayerUID=tmpcharacter_data.PlayerUID)

 

 

Use on your own risk ;).

EDIT: Hmm, when we got only not alive player, it will delete both :(
EDIT2: now it works for me

Edited December 2, 2013 by Nasdero

[cen]

Use this:

DELETE FROM `character_data` WHERE `LastLogin` < DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 5 DAY) AND `Generation` > 1 AND `Alive` = 0;

[MGT]

 

Use this:

DELETE FROM `character_data` WHERE `LastLogin` < DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 5 DAY) AND `Generation` > 1 AND `Alive` = 0;

 

If a player dies and doesn't log back in for 7 days, when they respawn how will MySQL remember their humanity from previous life if you delete all their dead characters?

[Nasdero]

Use this http://dayzepoch.com/forum/index.php?/topic/521-sql-clean-up/?p=27249

I did edit it, this one is working!

[cen]

If a player dies and doesn't log back in for 7 days, when they respawn how will MySQL remember their humanity from previous life if you delete all their dead characters?

 

Then they should be more active on the server :)

[Nasdero]

If you believe or not, sometimes ppl go for vacation and then you reset their humanity? Take this and they will keep their humanity:

delete FROM `character_data`

USING character_data, character_data AS tmpcharacter_data

WHERE NOT character_data.CharacterID=tmpcharacter_data.CharacterID

AND character_data.CharacterID<tmpcharacter_data.CharacterID

AND (character_data.PlayerUID=tmpcharacter_data.PlayerUID)[/php]

[MGT]

If you believe or not, sometimes ppl go for vacation and then you reset their humanity? Take this and they will keep their humanity:

delete FROM `character_data`

USING character_data, character_data AS tmpcharacter_data

WHERE NOT character_data.CharacterID=tmpcharacter_data.CharacterID

AND character_data.CharacterID<tmpcharacter_data.CharacterID

AND (character_data.PlayerUID=tmpcharacter_data.PlayerUID)[/php]

 

Nice work

[Nasdero]

Thank you, I hope it will help you guys to keep the DB smal :D.

[Fuchs]

hey guys !

 

How do i schedule this ?

 

In the past i deleted stuff by hands .... please anyone take me in the right direction !

 

I have navicat ,but i prefer php myadmin via xampp

Edited December 3, 2013 by Fuchs

[Nasdero]

In an batch file:

 

mysql -e "delete FROM `character_data` USING character_data, character_data AS tmpcharacter_data WHERE NOT character_data.CharacterID=tmpcharacter_data.CharacterID AND character_data.CharacterID<tmpcharacter_data.CharacterID AND (character_data.PlayerUID=tmpcharacter_data.PlayerUID)" dayz_epoch -h localhost -u dayz --password=YOUR_PW

 

or have a lock here: http://dayzepoch.com/wiki/index.php/Server_Configuration_Instructions " CREATE EVENT"

[Fuchs]

thx man ! saved my day !

 

btw is this the right code for a bat file with the trader stocks

mysql -e "UPDATE `traders_data` SET qty=10 WHERE qty=0 AND afile<>'trade_any_vehicle' AND afile<>'trade_any_boat';" dayz_epoch -h localhost -u xxx --password=xxx

[neiljb]

If you believe or not, sometimes ppl go for vacation and then you reset their humanity? Take this and they will keep their humanity:

delete FROM `character_data`

USING character_data, character_data AS tmpcharacter_data

WHERE NOT character_data.CharacterID=tmpcharacter_data.CharacterID

AND character_data.CharacterID<tmpcharacter_data.CharacterID

AND (character_data.PlayerUID=tmpcharacter_data.PlayerUID)[/php]

 

 

Very nice work there. :)

[Fuchs]

Hey guys.

Is this Method below also working since the Scheduled Method doesn't work for me so far...i'm getting out of ideas to restock traders.

Found this here http://opendayz.net/threads/dayz-epoch-easy-way-to-restock-items.10327/#post-44401

restock_traders.bat
"C:\Program Files (x86)\MySQL\MySQL Server 5.5\bin\mysql" -h localhost -uUSERNAME -pPASSWORD dayz_epoch < restock_traders.sql

cls
@exit
restock_traders.sql
UPDATE `traders_data` SET qty=50 WHERE qty<=49;
Of course you can also doubleclick the .bat file to update the traders stock.
But then again, you might need full access to it`s desktop.

[antartis]

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

DELETE FROM player_login WHERE DATE (`Datestamp`) < CURDATE() - INTERVAL 3 DAY

[Luna]

Hi, I’m kind of new to server management and I was hoping for a little help J

 

There are a few tables in my database that is getting quit big and I want to do some clean up, with some SQL’s – This is want I am thing about running on a schedule but I am nervous it will all go…….hmmm bad am I delete something wrong L

 

So please tell me if this looks reasonable – the tables that I want to manage are:

 

·        Character_DATA
I think I get what his table shows (what you have when you log in or died). So doing this SQL I’ll keep what they had and they will not lose humanity the first 14 days, right ?

 

DELETE FROM `character_data` WHERE `LastLogin` < DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 14 DAY) AND `Generation` > 1 AND `Alive` = 0;

 

·        Object_DATA

I know what this table shows, I would like to have an SQL that deletes object that has not been in use for 14 days, but my concern is that I do not want to delete bases where the player is active with in the last 14 days and I am guessing that e.g. the cinderblock last used does not update every time a player is in his/her base?? I found this one – But it WILL NOT leave the players bases alone will it? Any other surgestions?:
 

DELETE FROM `Object_DATA` WHERE `LastUpdated` < DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 14 DAY) AND `Datestamp` < DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 24 DAY) AND ( (`Inventory` IS NULL) OR (`Inventory` = '[]') OR (`Inventory` = '[[[],[]],[[],[]],[[],[]]]') )

 

·        Player_LOGIN

This one is a bit tricky, this is by far the biggest table and I don’t know what is shows? All I have been able to find is this sql from the above post, what is the consequenses of running it?
 

DELETE FROM Player_LOGIN WHERE DATE (`Datestamp`) < CURDATE() - INTERVAL 14 DAY

 

Thank you very much for you help, greatly appreciated.

Luna

[Randomness]

I use the query posted earlier, to update timestamps of objects from players that logged in recently, and then delete everything else that didnt get updated. The annoying part is that you have to keep all the character data this way

[Turtle]

So can someone post the right way to do this with out loosing humanity and also how to update the traders? Step by step please :D  (I can use HeidiSQL or Navicat)

[Hooch]

Hi guys,

 

I am looking to do a little house cleaning of our DB, I wanted to remove anything built by players who have not logged in within the last 3-4 weeks.

 

I found this event:

 

DO

UPDATE Object_DATA
SET Datestamp = CURRENT_TIMESTAMP
WHERE ObjectUID <> 0
AND CharacterID <> 0
AND ((Inventory IS NULL) OR (Inventory = '[]'))
AND EXISTS (SELECT 1
FROM Character_DATA c
WHERE c.PlayerUID = (SELECT p.PlayerUID
FROM Player_DATA p,
Character_DATA c2
WHERE p.PlayerUID = c2.PlayerUID
AND c2.CharacterID = Object_DATA.CharacterID)
AND c.Alive = 1
AND c.LastLogin > NOW() - INTERVAL 1 WEEK)

 

Would this work for me if I changed the LastLogin > NOW () - INTERVAL 1 WEEK to INTERVAL 3 or 4 WEEKS?

 

Anyone able to help?

 

Thanks in advance,

Hooch.

[Turtle]

If you believe or not, sometimes ppl go for vacation and then you reset their humanity? Take this and they will keep their humanity:

delete FROM `character_data`

USING character_data, character_data AS tmpcharacter_data

WHERE NOT character_data.CharacterID=tmpcharacter_data.CharacterID

AND character_data.CharacterID<tmpcharacter_data.CharacterID

AND (character_data.PlayerUID=tmpcharacter_data.PlayerUID)[/php]

So just paste this into a new function, then do the original steps for the .bat file?

[Randomness]

So just paste this into a new function, then do the original steps for the .bat file?

i just made the query and do it manual from time to time

[Turtle]

i just made the query and do it manual from time to time

I have NO idea what so ever how to do sql stuff, can you explain like, step by step how to do this query thing. 

[Randomness]

Using navicat you  can create query's instead of events.  Just save the query and run it when needed. Make sure you have the proper database selected if you have multiple, since it adds the query to the database, not the program ::: )

[LancerSolurus]

Just wondering why anyone hasn't done this, set your trader stock to 1 million, doubt they would run out anytime soon. Mine haven't :)

[Turtle]

or why do they just not have a quantity

[LancerSolurus]

Well it allows the option of trader guilds, certain clans keep the traders stocked otherwise there are no items to buy. I've seen this in action on a few youtube videos. An option to disable the quantity requirement in 1.0.5 maybe?