[Share]Clean Tables

[xAddytzu]

 

Delete characters with level 20(or less) and 2 months of inactive

DELETE FROM character_friends WHERE charId in (select charId from characters where lastaccess<((UNIX_TIMESTAMP(CURRENT_TIMESTAMP)-(51840000))*1000) and accesslevel<=0 and level<=20);
DELETE FROM character_subclasses WHERE charId in (select charId from characters where lastaccess<((UNIX_TIMESTAMP(CURRENT_TIMESTAMP)-(51840000))*1000) and accesslevel<=0 and level<=20);
DELETE FROM character_hennas WHERE charId in (select charId from characters where lastaccess<((UNIX_TIMESTAMP(CURRENT_TIMESTAMP)-(51840000))*1000) and accesslevel<=0 and level<=20);
DELETE FROM character_macroses WHERE charId in (select charId from characters where lastaccess<((UNIX_TIMESTAMP(CURRENT_TIMESTAMP)-(51840000))*1000) and accesslevel<=0 and level<=20);
DELETE FROM character_quests WHERE charId in (select charId from characters where lastaccess<((UNIX_TIMESTAMP(CURRENT_TIMESTAMP)-(51840000))*1000) and accesslevel<=0 and level<=20);
DELETE FROM character_recipebook WHERE charId in (select charId from characters where lastaccess<((UNIX_TIMESTAMP(CURRENT_TIMESTAMP)-(51840000))*1000) and accesslevel<=0 and level<=20);
DELETE FROM character_shortcuts WHERE charId in (select charId from characters where lastaccess<((UNIX_TIMESTAMP(CURRENT_TIMESTAMP)-(51840000))*1000) and accesslevel<=0 and level<=20);
DELETE FROM character_skills WHERE charId in (select charId from characters where lastaccess<((UNIX_TIMESTAMP(CURRENT_TIMESTAMP)-(51840000))*1000) and accesslevel<=0 and level<=20);
DELETE FROM character_skills_save WHERE charId in (select charId from characters where lastaccess<((UNIX_TIMESTAMP(CURRENT_TIMESTAMP)-(51840000))*1000) and accesslevel<=0 and level<=20);
DELETE FROM character_subclasses WHERE charId in (select charId from characters where lastaccess<((UNIX_TIMESTAMP(CURRENT_TIMESTAMP)-(51840000))*1000) and accesslevel<=0 and level<=20);
DELETE FROM items WHERE owner_id in (select charId from characters where lastaccess<((UNIX_TIMESTAMP(CURRENT_TIMESTAMP)-(51840000))*1000) and accesslevel<=0 and level<=20);
DELETE FROM characters WHERE lastaccess<((UNIX_TIMESTAMP(CURRENT_TIMESTAMP)-(51840000))*1000) and accesslevel<=0 and level<=20;

 

Delete banned accounts

UPDATE characters SET `accesslevel` = '-99' where account_name in (select login from accounts WHERE `accessLevel` < 0) AND account_name NOT IN (SELECT `account` FROM `l2_bannedontime`);
DELETE FROM character_friends WHERE charId in (select charId from characters where lastaccess<((UNIX_TIMESTAMP(CURRENT_TIMESTAMP)-(30*24*3600))*1000) and accesslevel < 0);
DELETE FROM character_subclasses WHERE charId in (select charId from characters where lastaccess<((UNIX_TIMESTAMP(CURRENT_TIMESTAMP)-(30*24*3600))*1000) and accesslevel<0);
DELETE FROM character_hennas WHERE charId in (select charId from characters where lastaccess<((UNIX_TIMESTAMP(CURRENT_TIMESTAMP)-(30*24*3600))*1000) and accesslevel<0);
DELETE FROM character_macroses WHERE charId in (select charId from characters where lastaccess<((UNIX_TIMESTAMP(CURRENT_TIMESTAMP)-(30*24*3600))*1000) and accesslevel<0);
DELETE FROM character_quests WHERE charId in (select charId from characters where lastaccess<((UNIX_TIMESTAMP(CURRENT_TIMESTAMP)-(30*24*3600))*1000) and accesslevel<0);
DELETE FROM character_recipebook WHERE charId in (select charId from characters where lastaccess<((UNIX_TIMESTAMP(CURRENT_TIMESTAMP)-(30*24*3600))*1000) and accesslevel<0);
DELETE FROM character_shortcuts WHERE charId in (select charId from characters where lastaccess<((UNIX_TIMESTAMP(CURRENT_TIMESTAMP)-(30*24*3600))*1000) and accesslevel<0);
DELETE FROM character_skills WHERE charId in (select charId from characters where lastaccess<((UNIX_TIMESTAMP(CURRENT_TIMESTAMP)-(30*24*3600))*1000) and accesslevel<0);
DELETE FROM character_skills_save WHERE charId in (select charId from characters where lastaccess<((UNIX_TIMESTAMP(CURRENT_TIMESTAMP)-(30*24*3600))*1000) and accesslevel<0);
DELETE FROM character_subclasses WHERE charId in (select charId from characters where lastaccess<((UNIX_TIMESTAMP(CURRENT_TIMESTAMP)-(30*24*3600))*1000) and accesslevel<0);
DELETE FROM items WHERE owner_id in (select charId from characters where lastaccess<((UNIX_TIMESTAMP(CURRENT_TIMESTAMP)-(30*24*3600))*1000) and accesslevel<0);
DELETE FROM characters WHERE lastaccess<((UNIX_TIMESTAMP(CURRENT_TIMESTAMP)-(30*24*3600))*1000) and accesslevel<0;

[i˚Ozõ]

only the first is good. the second is useless . why? because maybe you want to give second chance to banned members or they pay to get unbanned ..!

anyway thnx

[Onix]

awesome and usefull share addytzu thanks a lot .

[Cod3x]

I was searching for this a long time, thank you very much !

[Lelouch]

Thanks for share, but I'll pass.

only the first is good. the second is useless . why? because maybe you want to give second chance to banned members or they pay to get unbanned ..!

anyway thnx

 

+

[BooT★]

Indeed is useful. Good job `xAddytzu`

[~Ge0rge~]

for 1 more time xAddytzu a usefull share from you.i like this o yea....both are usefull for me.

[HellRuLeR]

thx for share!

[Cherub]

This is ok but I think there will be errors with the items. I mean these characters that are deleted own some items so there will be errors in the gameserver (i think).

 

The same happens if you delete NPC's without deleting their spawns.

[SoRa]

I was searching for this a long time, thank you very much !

Me too.

[minideed]

This is ok but I think there will be errors with the items. I mean these characters that are deleted own some items so there will be errors in the gameserver (i think).

 

The same happens if you delete NPC's without deleting their spawns.

 

Wrong, when you delete a character from the database everything associated with it will be deleted from the database next time you reboot the server. L2j cores were written to check the player ID's against the other tables, when it does not find an active one it removes it from the database.

 

On some if you watch the server starting up you will see something like "X Items cleaned from database." or something like that.

 

Deleting NPC's is not the same as deleting characters, NPC's have set ID's that the server needs when loading. If you delete an NPC from the NPC table then yes, you will have problems as many NPC's have their own skill lists, drops, html texts, etc.

 

As for the script to delete banned character I think it a bad idea as a players can once again create the same character. However if you do this and then restart the server then anything associated with the deleted character(s) will be deleted upon server load so even if they make the same character they will start from scratch (or however you have your server set).

[Grim.]

the first one is good bad the second not,you may change your mind and unbann the chars

[JustClicK]

Realy i need first, good job mengz ;dty

[brisioksss]

wrong section sorryy...:D