[Share] DataBase Cleanup SQL Script

[xMaylox]

ok, this will clean all account which are inactive for specified days. [glow=red,2,300]ALL CREDITS GOES TO NE@FLAX FROM 2JFREE.COM[/glow]

 

 

-- more than ... days
SET @dt = 60;

DELETE FROM accounts WHERE DATEDIFF( CURRENT_DATE( ) , FROM_UNIXTIME( `lastactive` /1000 ) ) > @dt;

DELETE FROM accounts WHERE login NOT IN (SELECT account_name FROM characters);
DELETE FROM account_data WHERE account_name NOT IN (SELECT login FROM accounts);
DELETE FROM characters WHERE account_name NOT IN (SELECT login FROM accounts);
DELETE FROM character_friends WHERE char_id NOT IN (SELECT obj_Id FROM characters);
DELETE FROM character_hennas WHERE char_obj_id NOT IN (SELECT obj_Id FROM characters);
DELETE FROM character_macroses WHERE char_obj_id NOT IN (SELECT obj_Id FROM characters);
DELETE FROM character_quests WHERE char_id NOT IN (SELECT obj_Id FROM characters);
DELETE FROM character_recipebook WHERE char_id NOT IN (SELECT obj_Id FROM characters);
DELETE FROM character_shortcuts WHERE char_obj_id NOT IN (SELECT obj_Id FROM characters);
DELETE FROM character_skills WHERE char_obj_id NOT IN (SELECT obj_Id FROM characters);
DELETE FROM character_skills_save WHERE char_obj_id NOT IN (SELECT obj_Id FROM characters);
DELETE FROM clan_data WHERE leader_id NOT IN (SELECT obj_Id FROM characters);
DELETE FROM clan_privs WHERE clan_id NOT IN (SELECT clan_id FROM clan_data);
DELETE FROM clan_skills WHERE clan_id NOT IN (SELECT clan_id FROM clan_data);
DELETE FROM pets WHERE item_obj_id NOT IN (SELECT object_id FROM items WHERE owner_id IN (SELECT obj_Id FROM characters));
DELETE FROM items WHERE owner_id NOT IN (SELECT obj_Id FROM characters) AND owner_id NOT IN (SELECT clan_id FROM clan_data);
DELETE FROM seven_signs WHERE char_obj_id NOT IN (SELECT obj_Id FROM characters);
DELETE FROM augmentations WHERE item_id NOT IN (SELECT item_id FROM items);
UPDATE characters SET clanid=0,title="",clan_privs=0 where clanid NOT IN (SELECT clan_id FROM clan_data);
UPDATE clanhall SET ownerID=0,paidUntil=0 where ownerID NOT IN (SELECT clan_id FROM clan_data);

[eKoIce]

nice, Title edited

[jossoo]

Awesome....

very nice my friend....all need that is usefull...tnx :)

 

[SaLeoTragem]

Verry cool thing :P

[GrisoM]

ok, this will clean all account which are inactive for specified days.

 

i read it over and over and over and i still cant see the specified days =/ call me a fool but i can see it xD

 

Is this line?

DELETE FROM accounts WHERE DATEDIFF( CURRENT_DATE( ) , FROM_UNIXTIME( `lastactive` /1000 ) ) > @dt;

Or

SET @dt = 60;

 

So after 60 days that account get deleted?

 

and then then , what is the rest?

 

Good work any way xD that could save much time from idiots creating usless accounts

[xMaylox]

set @dt :D specify there the days! and just run the bat file;)

[leoxxx]

oh thank you,that i'm searching for 2 weeks.

[Versus]

Dude plz say where your posts are working!This is not working on L2JFree for anyone who tried it.

 

I run it and it stucks at 2nd line.

 

EDIT:

I deleted these lines

-- more than ... days

and now the script is running.Thanks!

[Boxan]

That's very usefull. Thx for your share.

[Versus]

One question,this will clean your database the time you'll run it,it's not automated?For example if i run the script and a player is inactive for 59 days (;D) the next day he won't get auto deleted?I'll have to run the script again in order to get deleted?

 

And something else..In my database i have 40k accounts.Theoretically the script will run for ages to delete all the inactive and untill it's finished server will lag?

 

Thanks in advance

[xMaylox]

1st) you have to run the batch file its not automated. otherwise you have to make a SQL script to auto execute the batch file

 

2nd) i am not really sure, but i think this will not lag the server. (At least not that much)

[Versus]

I left it about 10 minutes and i had 2 java errors (the server was running) and it wasn't going any further than the 2nd line.

[xAddytzu]

I need a bat file .. or execute batch file in navicat ?

[Versus]

I need a bat file .. or execute batch file in navicat ?

I just execute it in navicat.

[striker]

i think this is useless