Gr8 Posted October 11, 2014 Report Share Posted October 11, 2014 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] Link to comment Share on other sites More sharing options...
0 jahangir13 Posted October 11, 2014 Report Share Posted October 11, 2014 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. Link to comment Share on other sites More sharing options...
0 Gr8 Posted October 11, 2014 Author Report Share Posted October 11, 2014 I have created tempchartable with the same rows as character_data. And when i execute the script, this doesnt not fill up the tempchartable. Link to comment Share on other sites More sharing options...
0 jahangir13 Posted October 11, 2014 Report Share Posted October 11, 2014 ? 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. Link to comment Share on other sites More sharing options...
0 Gr8 Posted October 12, 2014 Author Report Share Posted October 12, 2014 got it, I never knew about virtual tables. I can get my way about the rest. I execute my scripts using batch files Thanks Link to comment Share on other sites More sharing options...
0 jahangir13 Posted October 12, 2014 Report Share Posted October 12, 2014 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! Link to comment Share on other sites More sharing options...
Question
Gr8
Hello,
This is a simple script for cleaning up dead.
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:
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]
Link to comment
Share on other sites
5 answers to this question
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now