Jump to content
MaxCheaters Official Group on Discord
Top.MaxCheaters.com is now OPEN – Lineage 2 Top Listing

[SQL]Question - Answer

EdenEternal
 Share

Recommended Posts

EdenEternal Iron Initiate

EdenEternal

Posted
Posted

Hello Maxcheaters!

I decide to open topic where users can post their sql question.

 

Example:

Q.How to Delete from spawnlist my GK(7777)?

A. SQL Script:

DELETE FROM spawnlist WHERE npc_templateid="7777";

 

i think is usefull topic for Newbie's and not only :)

EdenEternal Iron Initiate

EdenEternal

Posted
  • Author
Posted

Q. How can I delete player that is not active more then 1 month.

I mean complete delete - quests/items/shortcuts/skills/subclasses of that player?

DELETE FROM characters WHERE lastAccess < 155520000;
DELETE FROM character_quests WHERE char_id=(SELECT obj_id FROM characters WHERE lastAccess < 155520000);
DELETE FROM items WHERE owner_id=(SELECT obj_id FROM characters WHERE lastAccess < 155520000);
DELETE FROM character_friends WHERE char_id=(SELECT obj_id FROM characters WHERE lastAccess < 155520000);
DELETE FROM character_shortcuts WHERE char_obj_id=(SELECT obj_id FROM characters WHERE lastAccess < 155520000);
DELETE FROM character_hennas WHERE char_obj_id=(SELECT obj_id FROM characters WHERE lastAccess < 155520000);
DELETE FROM character_macroses WHERE char_obj_id=(SELECT obj_id FROM characters WHERE lastAccess < 155520000);
DELETE FROM character_recipebook WHERE char_id=(SELECT obj_id FROM characters WHERE lastAccess < 155520000);
DELETE FROM character_recommends WHERE char_id=(SELECT obj_id FROM characters WHERE lastAccess < 155520000);
DELETE FROM character_skills WHERE char_obj_id=(SELECT obj_id FROM characters WHERE lastAccess < 155520000);
DELETE FROM character_skills_save WHERE char_obj_id=(SELECT obj_id FROM characters WHERE lastAccess < 155520000);
DELETE FROM character_subclasses WHERE char_obj_id=(SELECT obj_id FROM characters WHERE lastAccess < 155520000);
DELETE FROM clan_data WHERE leader_id=(SELECT obj_id FROM characters WHERE lastAccess < 155520000);
DELETE FROM heroes WHERE char_id=(SELECT obj_id FROM characters WHERE lastAccess < 155520000);
DELETE FROM olympiad_nobles WHERE char_id=(SELECT obj_id FROM characters WHERE lastAccess < 155520000);
DELETE FROM augmentations WHERE item_id=(SELECT item_id FROM items WHERE owner_id=(SELECT obj_id FROM characters WHERE lastAccess < 155520000));
DELETE FROM auction_bid WHERE clan_name=(SELECT clan_name FROM clan_data WHERE leader_id=(SELECT obj_id FROM characters WHERE lastAccess < 155520000));
DELETE FROM auction_watch WHERE charObjId=(SELECT obj_id FROM characters WHERE lastAccess < 155520000);
DELETE FROM clan_privs WHERE clan_id=(SELECT clan_id FROM clan_data WHERE leader_id=(SELECT obj_id FROM characters WHERE lastAccess < 155520000));
DELETE FROM clan_subpledges WHERE clan_id=(SELECT clan_id FROM clan_data WHERE leader_id=(SELECT obj_id FROM characters WHERE lastAccess < 155520000));
DELETE FROM clan_wars WHERE clan1=(SELECT clan_id FROM clan_data WHERE leader_id=(SELECT obj_id FROM characters WHERE lastAccess < 155520000));
DELETE FROM clan_wars WHERE clan2=(SELECT clan_id FROM clan_data WHERE leader_id=(SELECT obj_id FROM characters WHERE lastAccess < 155520000));
UPDATE clanhall SET ownerId="0" WHERE ownerId=(SELECT obj_id FROM characters WHERE lastAccess < 155520000);
DELETE FROM pets WHERE item_obj_id=(SELECT object_id FROM items WHERE owner_id=(SELECT obj_id FROM characters WHERE lastAccess < 155520000));
DELETE FROM seven_signs WHERE char_obj_id=(SELECT obj_id FROM characters WHERE lastAccess < 155520000);
DELETE FROM siege_clans WHERE clan_id=(SELECT clan_id FROM clan_data WHERE leader_id=(SELECT obj_id FROM characters WHERE lastAccess < 155520000));

EdenEternal Iron Initiate

EdenEternal

Posted
  • Author
Posted

Q. How can I delete player that is not active more then 1 month.

I mean complete delete - quests/items/shortcuts/skills/subclasses of that player?

Updated

 

DELETE FROM characters WHERE lastAccess < 155520000;
DELETE FROM character_quests WHERE char_id=(SELECT obj_id FROM characters WHERE lastAccess < 155520000);
DELETE FROM items WHERE owner_id=(SELECT obj_id FROM characters WHERE lastAccess < 155520000);
DELETE FROM character_friends WHERE char_id=(SELECT obj_id FROM characters WHERE lastAccess < 155520000);
DELETE FROM character_shortcuts WHERE char_obj_id=(SELECT obj_id FROM characters WHERE lastAccess < 155520000);
DELETE FROM character_hennas WHERE char_obj_id=(SELECT obj_id FROM characters WHERE lastAccess < 155520000);
DELETE FROM character_macroses WHERE char_obj_id=(SELECT obj_id FROM characters WHERE lastAccess < 155520000);
DELETE FROM character_recipebook WHERE char_id=(SELECT obj_id FROM characters WHERE lastAccess < 155520000);
DELETE FROM character_recommends WHERE char_id=(SELECT obj_id FROM characters WHERE lastAccess < 155520000);
DELETE FROM character_skills WHERE char_obj_id=(SELECT obj_id FROM characters WHERE lastAccess < 155520000);
DELETE FROM character_skills_save WHERE char_obj_id=(SELECT obj_id FROM characters WHERE lastAccess < 155520000);
DELETE FROM character_subclasses WHERE char_obj_id=(SELECT obj_id FROM characters WHERE lastAccess < 155520000);
DELETE FROM clan_data WHERE leader_id=(SELECT obj_id FROM characters WHERE lastAccess < 155520000);
DELETE FROM heroes WHERE char_id=(SELECT obj_id FROM characters WHERE lastAccess < 155520000);
DELETE FROM olympiad_nobles WHERE char_id=(SELECT obj_id FROM characters WHERE lastAccess < 155520000);
DELETE FROM augmentations WHERE item_id=(SELECT item_id FROM items WHERE owner_id=(SELECT obj_id FROM characters WHERE lastAccess < 155520000));
DELETE FROM auction_bid WHERE clan_name=(SELECT clan_name FROM clan_data WHERE leader_id=(SELECT obj_id FROM characters WHERE lastAccess < 155520000));
DELETE FROM auction_watch WHERE charObjId=(SELECT obj_id FROM characters WHERE lastAccess < 155520000);
DELETE FROM clan_privs WHERE clan_id=(SELECT clan_id FROM clan_data WHERE leader_id=(SELECT obj_id FROM characters WHERE lastAccess < 155520000));
DELETE FROM clan_subpledges WHERE clan_id=(SELECT clan_id FROM clan_data WHERE leader_id=(SELECT obj_id FROM characters WHERE lastAccess < 155520000));
DELETE FROM clan_wars WHERE clan1=(SELECT clan_id FROM clan_data WHERE leader_id=(SELECT obj_id FROM characters WHERE lastAccess < 155520000));
DELETE FROM clan_wars WHERE clan2=(SELECT clan_id FROM clan_data WHERE leader_id=(SELECT obj_id FROM characters WHERE lastAccess < 155520000));
UPDATE clanhall SET ownerId="0" WHERE ownerId=(SELECT obj_id FROM characters WHERE lastAccess < 155520000);
DELETE FROM pets WHERE item_obj_id=(SELECT object_id FROM items WHERE owner_id=(SELECT obj_id FROM characters WHERE lastAccess < 155520000));
DELETE FROM seven_signs WHERE char_obj_id=(SELECT obj_id FROM characters WHERE lastAccess < 155520000);
DELETE FROM siege_clans WHERE clan_id=(SELECT clan_id FROM clan_data WHERE leader_id=(SELECT obj_id FROM characters WHERE lastAccess < 155520000));

Realtek2 Iron Initiate

Realtek2

Posted
Posted 

ERROR 1054 : Unknown column 'char_id' in 'where clause'
ERROR 1054 : Unknown column 'obj_id' in 'field list'
ERROR 1146 : Table 'test-server.character_friends' doesn't exist
ERROR 1054 : Unknown column 'char_obj_id' in 'where clause'
ERROR 1054 : Unknown column 'char_obj_id' in 'where clause'
ERROR 1054 : Unknown column 'char_obj_id' in 'where clause'
ERROR 1054 : Unknown column 'char_id' in 'where clause'
ERROR 1054 : Unknown column 'char_id' in 'where clause'
ERROR 1054 : Unknown column 'char_obj_id' in 'where clause'
ERROR 1054 : Unknown column 'char_obj_id' in 'where clause'
ERROR 1054 : Unknown column 'char_obj_id' in 'where clause'
ERROR 1054 : Unknown column 'obj_id' in 'field list'
ERROR 1054 : Unknown column 'char_id' in 'where clause'
ERROR 1054 : Unknown column 'char_id' in 'where clause'
ERROR 1146 : Table 'test-server.augmentations' doesn't exist
ERROR 1054 : Unknown column 'obj_id' in 'field list'
ERROR 1054 : Unknown column 'obj_id' in 'field list'
ERROR 1054 : Unknown column 'obj_id' in 'field list'
ERROR 1054 : Unknown column 'obj_id' in 'field list'
ERROR 1054 : Unknown column 'obj_id' in 'field list'
ERROR 1054 : Unknown column 'obj_id' in 'field list'
ERROR 1054 : Unknown column 'obj_id' in 'field list'
ERROR 1054 : Unknown column 'obj_id' in 'field list'
ERROR 1054 : Unknown column 'char_obj_id' in 'where clause'
ERROR 1054 : Unknown column 'obj_id' in 'field list'

 

thats what I get :S

minideed Iron Initiate

minideed

Posted
Posted

All you need to do is execute this line:

 

DELETE FROM characters WHERE lastAccess < 155520000;

 

When you reboot the server most java cores will automatically delete all information in the database for whatever character(s) were deleted. No need for all those lines of code, too much wasted time and since not all L2j database tables are the same it probably will return errors.

EdenEternal Iron Initiate

EdenEternal

Posted
  • Author
Posted 

ERROR 1054 : Unknown column 'char_id' in 'where clause'
ERROR 1054 : Unknown column 'obj_id' in 'field list'
ERROR 1146 : Table 'test-server.character_friends' doesn't exist
ERROR 1054 : Unknown column 'char_obj_id' in 'where clause'
ERROR 1054 : Unknown column 'char_obj_id' in 'where clause'
ERROR 1054 : Unknown column 'char_obj_id' in 'where clause'
ERROR 1054 : Unknown column 'char_id' in 'where clause'
ERROR 1054 : Unknown column 'char_id' in 'where clause'
ERROR 1054 : Unknown column 'char_obj_id' in 'where clause'
ERROR 1054 : Unknown column 'char_obj_id' in 'where clause'
ERROR 1054 : Unknown column 'char_obj_id' in 'where clause'
ERROR 1054 : Unknown column 'obj_id' in 'field list'
ERROR 1054 : Unknown column 'char_id' in 'where clause'
ERROR 1054 : Unknown column 'char_id' in 'where clause'
ERROR 1146 : Table 'test-server.augmentations' doesn't exist
ERROR 1054 : Unknown column 'obj_id' in 'field list'
ERROR 1054 : Unknown column 'obj_id' in 'field list'
ERROR 1054 : Unknown column 'obj_id' in 'field list'
ERROR 1054 : Unknown column 'obj_id' in 'field list'
ERROR 1054 : Unknown column 'obj_id' in 'field list'
ERROR 1054 : Unknown column 'obj_id' in 'field list'
ERROR 1054 : Unknown column 'obj_id' in 'field list'
ERROR 1054 : Unknown column 'obj_id' in 'field list'
ERROR 1054 : Unknown column 'char_obj_id' in 'where clause'
ERROR 1054 : Unknown column 'obj_id' in 'field list'

 

thats what I get :S

 

it's because we have different packs. i have la2base IL, and that sql query is working for me. which pack you have?

removed_001 Iron Initiate

removed_001

Posted
Posted

Hello Maxcheaters!

I decide to open topic where users can post their sql question.

 

Example:

Q.How to Delete from spawnlist my GK(7777)?

A. SQL Script:

DELETE FROM spawnlist WHERE npc_templateid="7777";

 

i think is usefull topic for Newbie's and not only :)

 

come on only 3 lines share???? COME ON its pretty easy with right click delete record better than writing these facking things

EdenEternal Iron Initiate

EdenEternal

Posted
  • Author
Posted

come on only 3 lines share???? COME ON its pretty easy with right click delete record better than writing these facking things

for example you have monster with id 99999

and it's spawned 5000 Places. you will delete all manual?

or with sql script?

removed_001 Iron Initiate

removed_001

Posted
Posted

for example you have monster with id 99999

and it's spawned 5000 Places. you will delete all manual?

or with sql script?

u can just select all the lines come on its pretty easy (set the begining and the ending and delete records

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 account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share
Go to topic listing


  • Posts

    • Mobius
      aCis - another CRAPPY interlude server

      By Mobius · Posted

      L2jMobius has data oriented pathfinder since May. Already have "less memory" used for geodata since 3 weeks ago. Avoiding 1 GB less memory in geodata on latest clients. Not to mention rolling with an actual geodata generator since 3 weeks ago again. We also did actual benchmarks, tests with actual players, and decided to not use AI slop navmesh. I guess working only on a 10 year old client gives you a lot of free time and need to kill your boredom somehow... Need to show some progress somehow... Kinda get it... And unlike aCis we do release our full work every 4 months. So expect to actually see our work on this Christmas release.
    • mjst
      L2-Scripts Packs: (HF, IT, Essence, Classic, GOD) [15/05/26]

      By mjst · Posted

      anti-bot 🤣
    • l2-scripts
      L2-Scripts Packs: (HF, IT, Essence, Classic, GOD) [15/05/26]

      By l2-scripts · Posted

      🎉 L2-SCRIPTS TURNS 20 🎉 This year marks our studio's 20th anniversary!   ▬▬▬▬▬▬▬▬▬▬   Two decades in the Lineage 2 private server market is a long run — we've seen chronicles, engines and trends come and go. We keep doing what we do best: builds, technical support, turnkey projects, website and engine development, personal account systems, anti-cheat and anti-bot protection, auto-donation systems, unique events, geodata — basically anything your project might need.   🎨 To mark the occasion, we figured it was the perfect time for a fresh look. Honestly, the haters deserve some credit too — they never sleep, and they gave us that extra push to finally update the visuals 😉 We kept the familiar structure and sections intact so nobody gets lost — just made everything more modern and easier on the eyes.   ▬▬▬▬▬▬▬▬▬▬   ⚠️ Spot any bugs or rough edges on the updated site? Let us know and we'll fix it right away. Thanks for being with us for 20 years. It only gets better from here 🚀   🌐 https://l2-scripts.ru   |   https://l2-scripts.com ✈️ https://t.me/L2scripts
    • 0vert
      Aden Chronicles old Lineage 2

      By 0vert · Posted

      A full MMORPG that runs in your browser. No download, no launcher, no account with anyone else — click the link and you are in the world. ▶ Play free: https://l2d.one Aden Chronicles is an isometric fantasy MMORPG built in the spirit of the old Lineage 2 servers: classes that branch as you grow, castles that change hands, and world bosses that the whole server has to deal with together. Not a single-player game with a leaderboard attached — one shared world, where the same monsters die once and the same castle has one lord. WHAT IS IN IT 7 regions and 245 hunting grounds, scaling from level 1 to 80 6 classes that branch into 24 professions, then Awakening at 60 201 skills, enchantable past their normal ceiling 174 monster types, raid bosses, and grand bosses shared by the entire server Castle sieges, clans with reputation and wars, the Grand Olympiad 23 armour sets across 6 grades, enchanting, augmentation with life stones Caves and mines you walk down into — tunnels, ore veins, a guardian at the bottom 14 islands reached by ship, 10 pets, from a wolf to a dragon hatchling THE TWO THAT EVERYONE FIGHTS Valakas the World-Flame — level 78, 900,000 HP Aetheron, Sky Sovereign — level 82, 1,400,000 HP They do not respawn for you. They respawn for everybody.   Video -->  
    • aguy
      Fermata - A Modern Lineage 2 Interlude Client

      By aguy · Posted

      I personally like it a lot. I see this not as a replacement but as a supplementary client that can fill different type of gameplay. 

  • Topics

×
×
  • Create New...

Important Information

This community uses essential cookies to function properly. Non-essential cookies and third-party services are used only with your consent. Read our Privacy Policy and We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue..