Clean-up Dead People

[Gr8]

Hello,

 

This is a simple script for cleaning up dead.

DELETE FROM character_data WHERE Alive = 0 

I have that as an event every restart.

 

But a problem with that is, if a player dies first restart and all his character_data is dead then it deletes it all. thus deleting the humanity

So i did this:

DELETE FROM character_data WHERE Alive = 0 AND Datestamp < NOW() - INTERVAL 5 DAY

For Busy servers, that piles up a lot of dead characters in the database.

Is there a sql script that can look for dead characters and delete them, but only leaving the last dead character to save the humanity.

This is cleanup all the dead and have 1 dead and 1 alive per restart.

 

Help Appreciated,

[GG]

[jahangir13]

You could extend you delete statement to something like this:

 

DELETE from Character_DATA
USING Character_DATA, Character_DATA as tempchartable
WHERE (Character_DATA.PlayerUID = tempchartable.PlayerUID)
AND (NOT Character_DATA.alive = tempchartable.alive)
AND (Character_DATA.alive = 0)

 

You may need to change the table names to fit to your names. In the linux server character_data is Character_DATA (the db will complain for the wrong case).

 

1.) You delete from char table

2.) using an temporary table tempchartable which contains the same lines to compare

3.) where playeruid in character_data table is same as in tempchartable

4.) and alive field is different in both tables

4.) and alive = 0 in character_data (so you keep alive = 1)

 

I'v tested this quickly. Maybe you copy your table to a new one and test if this does it with more data rows ;)

 

http://s1.postimg.org/6111o7433/image.jpg

 

So in the picture in the lower screen are the lines before and above in the DB are the lines after execution of the command.

It keeps the rows where there is Alive = 0 but no row with the same PlayerUID with Alive = 1 is available.

It delete all rows with the same playerUID if there is a line with Alive = 1 for this player.

 

Looks ok to me. Don't know if there are much better ways.

[Gr8]

I have created tempchartable with the same rows as character_data. And when i execute the script, this doesnt not fill up the tempchartable.

[jahangir13]

? temchartable is a temporary table the command just uses. NOTHING which needs to be created.

Execute my sql just in whatever tool you use and just adapt the character_table to the exact case it's named in your database. This is enough.

The temporary table is created by the database in memory just while executing the statement. It's used to compare row for row in the REAL character_data table with a the same rows in the VIRTUAL temp table.

 

If you wat to execute this statement manually in e.g. phpMyAdmin (each other tool should also have a way to execute statements) you go to the sql tab and just execute it. If you want to execute it automatically e.g. each day once...I guess you need to create an mysql event which does that scheduled for you.

[Gr8]

got it, I never knew about virtual tables. I can get my way about the rest. I execute my scripts using batch files

 

Thanks

[jahangir13]

Me too. I had a look in google and just tried it. Seems today is my support/learn day ,) (I think the right word is shadow-table here)

 

Let me know if it works for you!