[Request] Clean Database

[gionys]

Gia sas re filoi exo ena server kai entaxi then ime kai o poio empiros admin.

An Kapoios xeri pos mporo na kano clean ta account pou then exoun xrisimopiithi gia 3s mines kapoios na mou pi.

Logika then nomiso na to kanis me to xeri :P gt ine pola tspa an kapios xeri iparxi kapoio programa ? pos akribos ginete kai

an then iparxi programa pite mou xeirokinita.

 

Exo MySQL 5.0 Server kai Navicat to poio kainourio..

 

Perimeno apantisi asap gt exo gemisi apo account pano apo 9.000 :/

 

 

-- 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);

 

Brika Afto alla then xero pos akribos doulebi :/

[gionys]

Come one nowone can help me :/

[jossoo]

kane execute batch file ayto to arxio ..

[Hax0r]

Μπες στο navicat, στην database σου...άνοιξε την l2jdb και πάτα Query.

Μετά πάτα New Query.

Στο παράθυρο που άνοιξε γράψε αυτό:

SET @dt = 90;

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);

Πάτα run.

[gionys]

kai na se rotiso kati filaraki afto gia poses meres xoris na exoun xrisimopiithi ine ego thelo na to kano na einai  2mines pos ? thx anyway..File molis to kano paste :/ the mou mavrisi to Run...ti na kano plsss :P

[sτrίkε-]

[GR]

SET @dt = 90;

eipes 3 mines

90 meres=3 mines

60 meres=2mines opote kanto 60..

[ENG]Anyway dont speak greek to English Section

and post to correct section...!

[gionys]

Ok i Find it guys i am thank you but it dont realy delete everything :/ thats it for this who dont know

 

-- 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 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);
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);

 

 

SET @dt = 60;  <--  This is how much weeks/days you want to delete inactive accounts.