SQL Clean Up

[cen]

move to config based traders and you don't have to worry about it :D

[MGT]

On a busy server you can't have large quantities of items in stock like cinder blocks as the database objects would increase too much. Our sql refills with 25 if stock is less than 10 at restart.

[ThePotatoeTree]

Help!

Where do i put this?!

Whats this SQL you speak of?!

[Turtle]

Help!

Where do i put this?!

Whats this SQL you speak of?!

Open up navicat or heidiSQL or whatever you have and open a new querey, paste it in there and then hit run.

[ThePotatoeTree]

Open up navicat or heidiSQL or whatever you have and open a new querey, paste it in there and then hit run.

Can i get link to it and and all i need to do for it please?!

[Shizweak]

Posted this in another thread, but someone else might find it useful

 

This SQL converts buy/sell prices to numerical values, and checks if a sell price is greater than a buy price - you can then adjust accordingly:

SELECT
	CASE MID(buy, 5, LENGTH(buy)-8)
		WHEN 'ItemGoldBar' THEN 100*MID(buy, 2, 1)
		WHEN 'ItemGoldBar10oz' THEN 1000*MID(buy, 2, 1)
		WHEN 'ItemBriefcase100oz' THEN 10000*MID(buy, 2, 1)
		WHEN 'ItemSilverBar' THEN 1*MID(buy, 2, 1)
		WHEN 'ItemSilverBar10oz' THEN 10*MID(buy, 2, 1)
		ELSE 0
	END as buy_n,
	CASE MID(sell, 5, LENGTH(sell)-8)
		WHEN 'ItemGoldBar' THEN 100*MID(sell, 2, 1)
		WHEN 'ItemGoldBar10oz' THEN 1000*MID(sell, 2, 1)
		WHEN 'ItemBriefcase100oz' THEN 10000*MID(sell, 2, 1)
		WHEN 'ItemSilverBar' THEN 1*MID(sell, 2, 1)
		WHEN 'ItemSilverBar10oz' THEN 10*MID(sell, 2, 1)
		ELSE 0
	END as sell_n,
traders_data.* FROM traders_data
HAVING sell_n>buy_n;

[aaaman]

Is there a routine to make an event to clean the player_data table, because it has no timestamp row to sort or delete  :(

[RimBlock]

Not at my own MySQL db so only writing pesudo sql (i.e. not actual runnable code but more logical text).

 

Deleting old characters;

 

Problem: Needing to housekeep the Character_Data table and remove all alive=0 characters where the player has respawned

 

Solution: Delete all but the newest entry for each UID.  Could be setup as an event for the MySQL schedular.

 

delete characters where generation < (max(generation) where UIDs match)

 

Downside: Does not clean out character_data for players who leave the server and don't come back.  A clause to delete if last_login > 30 days (or more) could also be added.

 

Destroyed vehicles.

 

Problem: Destroyed vehicles loose all loot - A hacker destroyed vehicle would be hard to recover with its loot (DB restore or scanning through backup sql to last recorded details).

 

Solution: Create a trigger on the object table to copy a vehicles data from the object table when an update occurs to the object_data table and the update sets damage =1 and the object has loot.

 

This would allow the vehicle to be rolled back from the backup table the trigger saves a pre-update copy of the vehicel data too.

 

The update table would need to be housekept or it will keep on growing though.

 

Downside: Triggers will potentially have an affect on updates to the object_data table depending on server specs and object_data being updated at any particular time.

 

RB

 

Update:

Potentially you could also transfer all your buildables to the new spawns ownership with sql like this as well although I guess it would only work after a server reboot re-reads the DB.

 

All code is tested on my own server.  I would advise testing yourself before using it on a live server.  Use at your own risk.

 

Cleardown all but the last login for each PlayerUID in the Player_Login table.

 

DROP TABLE IF EXISTS `temp_table`;

 

CREATE TEMPORARY TABLE IF NOT EXISTS temp_table AS 

 (select playerUID, max(loginID) as "LoginID"

from Player_login 

where action = 0

group by PlayerUID);

 

delete from Player_login 

where LoginID < (select temp_table.LoginID from temp_table where temp_table.PlayerUID = Player_login.PlayerUID);

 

 

What it does:

It puts the max loginid for logins (action = 0) for each playerUID in to a temp table and then deletes all entries in the player_login table that have a PlayerUID matching the entry in the temp table and a loginid less than the max loginid for that playeruid in the temp table.

 

More to come.

[Donnovan]

I need to restart the server for the changes on object_data and character_data tables reflect in the game?

 

Thankyou.

[cen]

object_data yes

character_data no but the player has to be in lobby or disconnected

[Donnovan]

Thankyou for the reply, cen.

 

About character_data:

 

I understand this behavior is aplicable to changes on character_data, without deletion of the line.

 

If i delete a line of a dead body from the character_data table, the dead body disapears instantly in the game?

[cen]

no it wont

[shan1784]

Our server started getting popular so we needed to find a way to remove bases and objects no longer being used by their owners... Created teh queries below. The first on is to test and see what is currenty out there, the second will remove the objects.. Could be set as an event and ran daily...

Note - on our server we have the PlayerUID being added to the Object_DATA.CharacterID field...

Find Objects from people that haven’t logged in in 30 days

SELECT o.`Classname`, p.PlayerName, o.`CharacterID`, c.`LastLogin` FROM `Object_DATA` o, `Player_DATA` p, `Character_DATA` c WHERE p.`PlayerUID`=o.`CharacterID` and c.`PlayerUID`=p.`PlayerUID` and c.`Alive`=1 and o.`CharacterID` IN (SELECT `PlayerUID` FROM `Character_DATA` WHERE `Alive`='1' and DATE(`LastLogin`) < CURDATE() - INTERVAL 30 DAY)

 

 

Remove Objects from people that haven’t logged in in 30 days

DELETE FROM `Object_DATA` WHERE `CharacterID` IN (SELECT `PlayerUID` FROM `Character_DATA` WHERE `Alive`='1' and DATE(`LastLogin`) < CURDATE() - INTERVAL 30 DAY)

[Taszi]

Note - on our server we have the PlayerUID being added to the Object_DATA.CharacterID field...

 

Hello, 

 

How did you set the Object_DATA contains Player UID? is it done in the server_pbo? And refreshing the playeruid when someone operate with the object. I mean if the lastupdated is changes, will refresh the playeruid in database as well?

[Donnovan]

Taszi,

 

Try to find a way to assing objects on ground to a player, i believe there is no way to do that, like a stone in the middle os the road in front of my home does not have a owner.

 

I can think of owner objects as the ones inside player gear, and i believe those objects are not in the object_data table.

 

So, using the oportunity to ask: Objects into the player gear are in the table object_data?

[Pro_Speedy]

ANyway to properly cleanup Character_data mines over 4.6 MB

[fr1nk]

ANyway to properly cleanup Character_data mines over 4.6 MB

 

Probably a lot of dead characters in there you can clear out.

DELETE FROM character_data WHERE alive = '0'

[fr1nk]

 

Probably a lot of dead characters in there you can clear out.

DELETE FROM character_data WHERE alive = '0'

 

Re-read earlier parts of this thread and it's probably best to do what's said to prevent folks from potentially losing their humanity when deleting dead characters.

[Pro_Speedy]

 

Re-read earlier parts of this thread and it's probably best to do what's said to prevent folks from potentially losing their humanity when deleting dead characters.

 

Will the one you linked to break my multichars?

[RimBlock]

 

Re-read earlier parts of this thread and it's probably best to do what's said to prevent folks from potentially losing their humanity when deleting dead characters.

 

Update: they are not recycled.  Removed incorrect info.

 

The sql is still more robust though in the links below.

 

 I posted a solution a page back based on the last updated date Put it in a stored procedure and fire it from a batch file or sql timed event (like the other Epoch housekeeping sql).

[fr1nk]

Didn't know the character ID's were recycled, what you posted makes sense.

[RimBlock]

Ok, actually, let me confirm tonight.  I def have it in my head that they are but the details are a little fuzzy about the testing as it was a while ago.  I will recheck.

 

Personally I would rather remove based on last updated than the numeric value as if they are not reused then they should be else the number is just going to keep going up.

[Logan]

So after reading all of this I'm still not entirely sure what the best way to go about deleting object data for players that haven't logged in in more than 30 days.

[Buck0]

Anyone care to share their general cleanup events?

Ive been having some trouble with mine of late and are looking for some improvements

[cen]

So after reading all of this I'm still not entirely sure what the best way to go about deleting object data for players that haven't logged in in more than 30 days.

DELETE FROM `character_data` WHERE `LastLogin` < DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 30 DAY);