Reset server sql queries (don't delete players nor accounts)

[Snusarn Thor]

I don't want to remove the current players or accounts, I just want all the players and accounts to be reseted to the values that my vocation samples,inventories,houses,bank balance,premium points,premium days. You name it.

If anyone could help me with this I would be forever greatful!

 

[Codex NG]

distro?
Or just post your sql schema

 

[Snusarn Thor]

distro?
Or just post your sql schema

Tfs 0.4

 

[Codex NG]

Make sure you backup your database before you attempt this...
Also you need to change the town_id & posx, posy, posz to your temple's position and town id

UPDATE `accounts` SET `premdays` = 0, `lastday` = 0, `warnings` = 0;

UPDATE `players` SET `level` = 1, `vocation` = 0, `health` = 150, `healthmax` = 150, `experience` = 0, `lookaddons` = 0, `maglevel` = 0, `mana` = 0, `manamax` = 0, `manaspent` = 0, `soul` = 0, `town_id` = 0, `posx` = 50, `posy` = 50, `posz` = 7, `conditions` = '', `cap` = 400, `skull` = 0, `skulltime` = 0, `rank_id` = 0, `guildnick` = '', `blessings` = 0, `balance` = 0, `stamina` = 201660000, `loss_experience` = 100, `loss_mana` = 100, `loss_skills` = 100, `loss_containers` = 100, `loss_items` = 100, `marriage` = 0, `promotion` = 0, `description` = '';

DELETE * FROM `player_deaths`;

DELETE * FROM `player_depotitems`;

DELETE * FROM `player_items`;

UPDATE `player_skills` SET `value` = 10, `count` = 0 WHERE `skillid` >= 0;

DELETE * FROM `player_spells`;

DELETE * FROM `player_storage`;

DELETE * FROM `killers`;

DELETE * FROM `player_killers`;

DELETE * FROM `environment_killers`

UPDATE `houses` SET `owner` = 0, `paid` = 0, `warnings` = 0, `lastwarning` = 0;

DELETE * FROM `house_auctions`;

DELETE * FROM `house_lists`;

DELETE * FROM `house_data`;

DELETE * FROM `guilds`;

DELETE * FROM `guild_invites`;

DELETE * FROM `guild_ranks`;

DELETE * FROM `bans`;

DELETE * FROM `global_storage`;

DELETE * FROM `server_record`;

DELETE * FROM `server_reports`;

 

[Snusarn Thor]

Thanks dude, this is awesome. Although I would need for the players to get the vocation samples values. I reckon people would be weirded out if their character stood in thais without vocation in level 1

INSERT INTO `players` (`id`, `name`, `world_id`, `group_id`, `account_id`, `level`, `vocation`, `health`, `healthmax`, `experience`, `lookbody`, `lookfeet`, `lookhead`, `looklegs`, `looktype`, `lookaddons`, `lookmount`, `maglevel`, `mana`, `manamax`, `manaspent`, `soul`, `town_id`, `posx`, `posy`, `posz`, `conditions`, `cap`, `sex`, `lastlogin`, `lastip`, `save`, `skull`, `skulltime`, `rank_id`, `guildnick`, `lastlogout`, `blessings`, `pvp_blessing`, `balance`, `stamina`, `direction`, `loss_experience`, `loss_mana`, `loss_skills`, `loss_containers`, `loss_items`, `premend`, `online`, `marriage`, `marrystatus`, `promotion`, `deleted`, `description`, `exphist_lastexp`, `exphist1`, `exphist2`, `exphist3`, `exphist4`, `exphist5`, `exphist6`, `exphist7`, `onlinetimetoday`, `onlinetime1`, `onlinetime2`, `onlinetime3`, `onlinetime4`, `onlinetime5`, `onlinetime6`, `onlinetime7`, `onlinetimeall`, `auction_balance`, `created`, `nick_verify`, `old_name`, `hide_char`, `comment`, `show_outfit`, `show_eq`, `show_bars`, `show_skills`, `show_quests`, `stars`, `create_ip`, `create_date`, `signature`, `cast`, `castViewers`, `castDescription`, `offlinetraining_time`, `offlinetraining_skill`, `broadcasting`, `viewers`, `ip`) VALUES
(3, 'Sorcerer Sample', 0, 1, 1, 8, 1, 185, 185, 4200, 0, 88, 88, 0, 136, 0, 0, 0, 35, 35, 0, 100, 2, 32369, 32241, 7, '', 850, 1, 0, 0, 1, 0, 0, 0, '', 0, 0, 0, 0, 151200000, 0, 100, 100, 100, 100, 100, 0, 0, 0, 0, 0, 0, '', 4200, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1299890460, '1', '', 0, '', 0, 0, 0, 0, 0, 0, 0, 0, '', 0, 0, '', 43200, -1, 0, 0, '0'),

(4, 'Druid Sample', 0, 1, 1, 8, 2, 185, 185, 4200, 0, 88, 88, 0, 136, 0, 0, 0, 35, 35, 0, 100, 2, 32369, 32241, 7, '', 850, 1, 0, 0, 1, 0, 0, 0, '', 0, 0, 0, 0, 151200000, 0, 100, 100, 100, 100, 100, 0, 0, 0, 0, 0, 0, '', 4200, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1299890460, '1', '', 0, '', 0, 0, 0, 0, 0, 0, 0, 0, '', 0, 0, '', 43200, -1, 0, 0, '0'),

(5, 'Paladin Sample', 0, 1, 1, 8, 3, 185, 185, 4200, 0, 88, 88, 0, 136, 0, 0, 0, 35, 35, 0, 100, 2, 32369, 32241, 7, '', 850, 1, 0, 0, 1, 0, 0, 0, '', 0, 0, 0, 0, 151200000, 0, 100, 100, 100, 100, 100, 0, 0, 0, 0, 0, 0, '', 4200, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1299890460, '1', '', 0, '', 0, 0, 0, 0, 0, 0, 0, 0, '', 0, 0, '', 43200, -1, 0, 0, '0'),

(6, 'Knight Sample', 0, 1, 1, 8, 4, 185, 185, 4200, 0, 88, 88, 0, 136, 0, 0, 0, 35, 35, 0, 100, 2, 32369, 32241, 7, '', 850, 1, 0, 0, 1, 0, 0, 0, '', 0, 0, 0, 0, 151200000, 0, 100, 100, 100, 100, 100, 0, 0, 0, 0, 0, 0, '', 4200, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1299890461, '1', '', 0, '', 0, 0, 0, 0, 0, 0, 0, 0, '', 0, 0, '', 43200, -1, 0, 0, '0'),

I know the names of each value isn't there, I'll try to see if I can get it.

 

[Codex NG]

The column `id` the number associated with it is automatically generated by the database, so its value shouldn't be altered by a query.
The reason we know this is because of this tid bit of code in the schema

CREATE TABLE `players`
(
   `id` INT NOT NULL AUTO_INCREMENT,

This tells us that the database automatically increases the value of id during creation of an entry even if we delete it, it will continue to increment from the previous value.

So if id were 6 and we deleted 6 for whatever reason and created a new entry id would become 7 even tho entry 6 no longer exists.

 

[Codex NG]

Also I want to point out this aswell

INSERT INTO `players` (`id`, `name`,

VALUES
(3, 'Sorcerer Sample',

This is a bad idea, what your doing here is renaming every player in your database Sorcerer Sample, Druid Sample etc..

If you want to change the name of the vocations, this can be handled in the \data\XML\vocations.xml file

 

[Codex NG]

For what your trying to accomplish, using INSERT is a bad idea to tackle this problem, because with INSERT you need to name every column in the table and whatever information you pass to INSERT will alter the entry and I don't think that is what you want to do here.

Since all the values being passed to players are same we just need 1 query to handle this

UPDATE `players` SET `level` = 8, `health` = 185, `healthmax` = 185, `experience` = 4200, `lookbody` = 0, `lookfeet` = 88, `lookhead` = 88, `looklegs` = 0, `looktype` = 136, `lookaddons` = 0, `lookmount` = 0, `maglevel` = 0, `mana` = 35, `manamax` = 35, `manaspent` = 0, `soul` = 100, `town_id` = 2, `posx` = 32369, `posy` = 32241, `posz` = 32241, `conditions` = '', `cap` = 850, `sex` = 1, `lastlogin` = 0, `lastip` = 0, `save` = 1, `skull` = 0, `skulltime` = 0, `rank_id` = 0, `guildnick` = '', `lastlogout` = 0, `blessings` = 0, `pvp_blessing` = 0, `balance` = 0, `stamina` = 151200000, `direction` = 0, `loss_experience` = 100, `loss_mana` = 100, `loss_skills` = 100, `loss_containers` = 100, `loss_items` = 100, `premend` = 0, `online` = 0, `marriage` = 0, `marrystatus` = 0, `promotion` = 0, `deleted` = 0, `description` = '', `exphist_lastexp = 4200`, `exphist1` = 0, `exphist2` = 0, `exphist3` = 0, `exphist4` = 0, `exphist5` = 0, `exphist6` = 0, `exphist7` = 0, `onlinetimetoday` = 0, `onlinetime1` = 0, `onlinetime2` = 0, `onlinetime3` = 0, `onlinetime4` = 0, `onlinetime5` = 0, `onlinetime6` = 0, `onlinetime7` = 0, `onlinetimeall` = 0, `auction_balance` = 0, `created` = 1299890460, `nick_verify` = '1', `old_name` = '', `hide_char` = 0, `comment` = '', `show_outfit` = 0, `show_eq` = 0, `show_bars` = 0, `show_skills` = 0, `show_quests` = 0, `stars` = 0, `create_ip` = 0, `create_date` = 0, `signature` = '', `cast` = 0, `castViewers` = 0, `castDescription` = '', `offlinetraining_time` = 43200, `offlinetraining_skill` = -1, `broadcasting` = 0, `viewers` = 0, `ip` = '0';

Now if lets say you wanted to alter each vocation and give them different values like say a health, mana cap etc.. You would use a WHERE statement at the end of the query to only effect entries that met the condition.

Example :

UPDATE `players` SET `level` = 8, `health` = 185, `healthmax` = 185, `mana` = 35, `manamax` = 35 WHERE `vocation` == 1;

`vocation` == 1 means only to make changes to players with an id of 1 e.g. a sorcerer.

Good Luck

 

[53701688]

@Snusarn Thor add me on skype maybe I can fix up some php script for you to tackle this problem skype -> iron-smith | from the netherlands

 

[Snusarn Thor]

Thanks @Codex NG for the help! I finally resetted my server without any issues!

 

[Snusarn Thor]

How do I reset the house tiles though? Houses are good but the items are still there till someone buys it.