xMaylox Posted October 30, 2008 Posted October 30, 2008 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); 1 Quote
jossoo Posted November 1, 2008 Posted November 1, 2008 Awesome.... very nice my friend....all need that is usefull...tnx :) Quote
GrisoM Posted November 1, 2008 Posted November 1, 2008 On 10/30/2008 at 4:14 PM, xMaylox said: 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 Quote
xMaylox Posted November 1, 2008 Author Posted November 1, 2008 set @dt :D specify there the days! and just run the bat file;) Quote
leoxxx Posted November 1, 2008 Posted November 1, 2008 oh thank you,that i'm searching for 2 weeks. Quote
Versus Posted November 1, 2008 Posted November 1, 2008 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! Quote
Versus Posted November 2, 2008 Posted November 2, 2008 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 Quote
xMaylox Posted November 2, 2008 Author Posted November 2, 2008 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) Quote
Versus Posted November 2, 2008 Posted November 2, 2008 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. Quote
xAddytzu Posted November 2, 2008 Posted November 2, 2008 I need a bat file .. or execute batch file in navicat ? Quote
Versus Posted November 2, 2008 Posted November 2, 2008 On 11/2/2008 at 12:00 PM, xAddytzu said: I need a bat file .. or execute batch file in navicat ? I just execute it in navicat. Quote
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.