[RELEASE]Lots of MySQL Commands

[mikethemak]

Well, to stop people from releasing single MySQL commands..that most people already know. Ill just keep updating this 1 with random crap lolz .

Joen's MySQL Helped. Great program like the "GM handbooks", based off the scripts in this thread, download it

To view the content, you need to sign in or register

Table Of Contents:

1. Logged in glitch
2. Adding/Setting Nx
3. Setting all shop prices
4. Removing an item sold in all shops
5. Setting an account as a GM
6. Removing a ban
7. Adding guild points to a guild
8. Removing skills from ALL players (in case of buggy)
9. Deleting chars
10. Setting a chars stats
11. Manual ban (account only)
12. Fixing inventory glitch (0 of an item)
13. Reg and Ranking Fix
14. Wedding Drops
15. Char wipe
16. Inventory wipe
17. Adding mesos to EVERYONES storage
18. Negative exp fix
19. Negative meso fix.
20. Added/Subtracting slots in storages.
21. Spirits and Scales drops
22. Setting price of one item to X
23. Setting price of a whole shop to X.
24. Deleting a item from droping from all monsters
25. Negative mesos in storage
26. Hp/Mp bug

Press ctrl + f, then type the script you want to go to for faster finding.




Scripts:


Logged in glitch:

Update accounts set loggedin = 0

Added/Setting nx:

To ADD: update accounts set nxcash = nxcash + [amount]

To SET: update accounts set nxcash = [amount]

Setting all shop prices:

update shopitems set price = [amount]

Removing an item sold in all shops
CREDITS: Joen

DELETE FROM shopitems WHERE itemid = [id]

Setting an account as a GM:
CREDITS FOR SECOND PART: NamePlawks

update accounts where accountid = [id you want to be gm] set gm = 1

and

update characters where name = '[name]' set gm = 1

Removing a ban:
This will unban ONLY an account, the ip and MAC will still be banned.

update accounts where accountid = [id] set banned = 0

Adding guild points to a guild:

update guilds where guildid = [id] set GP = [amount]

Removing skills from ALL players (in case of buggy)

delete from skills where skillid = [id of skill to delete]

Deleting chars:

delete from characters where name = '[name]'

Setting a chars stats:
Can do with hp/mp/maxhp/maxmp

update characters where name = '[name]' set [stat (str, int, luk, dex)] = [amount]

Manual ban:

update accounts where accountid = [id] set banned = 1

Fixing inventory glitch (0 of items in inv)
CREDITS:jasonpwns
This will make it so stars arent deleted.

First:
update inventoryitems SET quantity = 1 where itemid = 2070006
and 2070005
and 2070016
and 2070004
and 2070007

Then

delete from inventoryitems where quantity = 0

For reg and rankings problems execute these 3 codes:
CREDITS: Joen

ALTER TABLE `accounts` MODIFY COLUMN `macs` TINYTEXT NULL DEFAULT NULL;
ALTER TABLE `characters` ADD `rebirth` INTEGER UNSIGNED NOT NULL DEFAULT 0
ALTER TABLE `accounts` MODIFY COLUMN `banreason` TEXT NULL DEFAULT NULL;

Wedding Drops:
CREDITS: Joen

INSERT INTO monsterdrops
(`monsterid`, `itemid`, `chance`)
VALUES
(9300098, 2020020,2000);

INSERT INTO monsterdrops
(`monsterid`, `itemid`, `chance`)
VALUES
(9300098, 5251003,2000);

INSERT INTO monsterdrops
(`monsterid`, `itemid`, `chance`)
VALUES
(9300097, 5251003,2000);

INSERT INTO monsterdrops
(`monsterid`, `itemid`, `chance`)
VALUES
(9300097, 2020020,2000);

Char wipe
This will wipe all chars except ones with GM set to 1, good for beta wipe, ect.

delete from characters where gm = 0

Inventory Wipe
Should clear your whole `inventoryitems` table, good for beta wipe, ect.

delete from inventoryitems where inventorytype = -1;
delete from inventoryitems where inventorytype = 0;
delete from inventoryitems where inventorytype = 1;
delete from inventoryitems where inventorytype = 2;
delete from inventoryitems where inventorytype = 3;
delete from inventoryitems where inventorytype = 4;
delete from inventoryitems where inventorytype = 5;

Adding mesos to everyones storage
Credits Mussii

UPDATE storages SET meso = meso + [amount]

Negative exp fix
Credits Mussii

UPDATE characters SET exp = 0 WHERE exp < 0

Negative Meso Fix
Credits Mussii

UPDATE storages SET meso = 0 WHERE meso < 0

Adding/Subtracting slots in storage accounts
Meaning you can have more then 16 items in your storage

For EVERYONE: update storages set slots = [amount]
For a SINGLE person: update storages where accountid = [id] set slots = [amount]

Spirits and Scales Drops
CREDITS: MesoRanger
This will make it so you can get the items for the lvl 110 weapons quest.

INSERT INTO monsterdrops
(`monsterid`, `itemid`, `chance`)
VALUES
(8140701, 4000244, 10);

INSERT INTO monsterdrops
(`monsterid`, `itemid`, `chance`)
VALUES
(8140701, 4000245, 10);

INSERT INTO monsterdrops
(`monsterid`, `itemid`, `chance`)
VALUES
(8140702, 4000244, 10);

INSERT INTO monsterdrops
(`monsterid`, `itemid`, `chance`)
VALUES
(8140702, 4000245, 10);

INSERT INTO monsterdrops
(`monsterid`, `itemid`, `chance`)
VALUES
(8140703, 4000244, 20);

INSERT INTO monsterdrops
(`monsterid`, `itemid`, `chance`)
VALUES
(8140703, 4000245, 20);

INSERT INTO monsterdrops
(`monsterid`, `itemid`, `chance`)
VALUES
(8150300, 4000244, 15);

INSERT INTO monsterdrops
(`monsterid`, `itemid`, `chance`)
VALUES
(8150300, 4000245, 15);

INSERT INTO monsterdrops
(`monsterid`, `itemid`, `chance`)
VALUES
(8150200, 4000244, 15);

INSERT INTO monsterdrops
(`monsterid`, `itemid`, `chance`)
VALUES
(8150200, 4000245, 15);

INSERT INTO monsterdrops
(`monsterid`, `itemid`, `chance`)
VALUES
(8150301, 4000244, 15);

INSERT INTO monsterdrops
(`monsterid`, `itemid`, `chance`)
VALUES
(8150301, 4000245, 15);

INSERT INTO monsterdrops
(`monsterid`, `itemid`, `chance`)
VALUES
(8150302, 4000244, 20);

INSERT INTO monsterdrops
(`monsterid`, `itemid`, `chance`)
VALUES
(8150302, 4000245, 20);

INSERT INTO monsterdrops
(`monsterid`, `itemid`, `chance`)
VALUES
(8150201, 4000244, 20);

INSERT INTO monsterdrops
(`monsterid`, `itemid`, `chance`)
VALUES
(8150201, 4000245, 20);

INSERT INTO monsterdrops
(`monsterid`, `itemid`, `chance`)
VALUES
(8190000, 4000244, 15);

INSERT INTO monsterdrops
(`monsterid`, `itemid`, `chance`)
VALUES
(8190000, 4000245, 15);

INSERT INTO monsterdrops
(`monsterid`, `itemid`, `chance`)
VALUES
(8190005, 4000244, 20);

INSERT INTO monsterdrops
(`monsterid`, `itemid`, `chance`)
VALUES
(8190005, 4000245, 20);

INSERT INTO monsterdrops
(`monsterid`, `itemid`, `chance`)
VALUES
(8190003, 4000244, 15);

INSERT INTO monsterdrops
(`monsterid`, `itemid`, `chance`)
VALUES
(8190003, 4000245, 15);

INSERT INTO monsterdrops
(`monsterid`, `itemid`, `chance`)
VALUES
(8190004, 4000244, 20);

INSERT INTO monsterdrops
(`monsterid`, `itemid`, `chance`)
VALUES
(8190004, 4000245, 20);

Setting price of one item to X
CREDITS: NamePlawks

update shopitems  where itemid = [id] set price = [amount]

Setting price of a whole shop to X
CREDITS: NamePlawks

update shopitems where shopid = [id] set price = [amount]

Deleting a item from droping from all monsters
CREDITS: NamePlawks

DELETE FROM monsterdrops WHERE itemid = [id]

Negative Mesos In Storage
CREDITS: LolHamachi

UPDATE storages WHERE meso < 0 SET meso = [amount]

Bugged hp/mp Fix
LolHamachi

UPDATE characters WHERE maxhp > 30000 SET maxhp = 30000
UPDATE characters WHERE maxmp > 30000 SET maxmp=30000

Updates:

7/23: Added: Hp/Mp bug, fixed unban script, added neg meso in storage fix
7/22: Added: Deleting a item from droping from all monsters
7/22: Added: Setting price of a whole shop to X, Setting price of one item to X
7/22: Added: MySQL Helper Tool Link
7/22: Added: Table of contents, Removing item sold in ALL shops, storage slots
7/22: Added: Neg exp fix, Neg meso fix, adding meso to storage, inventory wipe.
7/21: Added char wipe.
7/21: Made thread, added: reg/rank fix, wedding drops, and inv glitch. Fixed 'name' problems.




Credits:
Ill just list off everyone who contributed:

Me
NamePlawks
Joen
Trait0r
Jasonpwns
Mussii
LolHamachi





Ill add more if i think of more lol...this is just a bunch of random codes that i used when i ran a server.

Hopefully this post will help some people, and stop people from posting random 1 line mysql codes

Enjoi
~Mike

 

[joen]

add those for site reg and rankings problems ;

ALTER TABLE `characters` ADD `rebirth` INTEGER UNSIGNED NOT NULL DEFAULT 0

ALTER TABLE `accounts` MODIFY COLUMN `banreason` TEXT NULL DEFAULT NULL;

ALTER TABLE `accounts` MODIFY COLUMN `macs` TINYTEXT NULL DEFAULT NULL;

 

[nadav64]

thx will use

 

[Traitor]

Removing a ban should be:

Removing a ban:

update accounts where accountid = [id] set banned = -1

 

[mikethemak]

if banned = 0 you arent banned...wuts -1 mean in banned column?

@joen

Will add, ty

 

[Traitor]

when banned = -1 and completely unbans the person (IE. the ip and mac bans)

 

[mikethemak]

added wedding drops, fixed ban.

Ty to joen and Trait0r

 

[Phoenix14]

Very useful. Thanks a lot

 

[mikethemak]

If you guys think anymore should be added, post em here. ill give u credits

 

[NamePlawks]

UPDATE characters
SET gm = '1'
where name = 'name';

update accounts
set gm = '1'
where name = 'name';

Yours just does accounts. And doesn't work unless you do it like the second one I said.
I mean, the quotes :O.

 

[mikethemak]

ooo forgot that, thanx

 

[eternalms]

delete from accounts where email = (email)

for those hackers that make random accounts using the same 1-x emails.

 

[NamePlawks]

For most values, you're missing the quotes, I'm pretty sure that matters o.o.
For

update accounts where accountid = [id you want to be gm] set gm = 1

that [below] would probably be better.

update accounts
set gm = '1'
where name = 'name'

 

[mikethemak]

delete from accounts where email = (name here without the())

for those hackers that make random accounts using the same 1-x emails.

Im not quite sure i understand this 1...delete the accounts where emails = their names?

For most values, you're missing the quotes, I'm pretty sure that matters o.o.
Never mind :X.

If your talking about the `s, you dont need them

 

[Traitor]

EDIT:
Never mind, I was corrected

 

[NamePlawks]

I mean

update accounts
set gm = 1
where name = Someone

Would say "Unknown column "Someone""

update accounts
set gm = 1
where name = 'Someone'

wouldn't.
@Above: I've tried it normally like
update accounts set gm = 1 where name = Someone
same thing.
And I changed accountid to name there. :X.

 

[mikethemak]

umm, not sure. probably, can someone test with a random account pls?

 

[joen]

if banned = 0 you arent banned...wuts -1 mean in banned column?

@joen

Will add, ty

np :happy: this thread should be stickied

 

[mikethemak]

Lol i know. It would save a lot of noobs a lot of questions...and save us from a million "[release?]ZoMg i made a 1 line SQL command.[whats a tag?]"

 

[Traitor]

umm, not sure. probably, can someone test with a random account pls?

What NamePlawks said is true