set combo lock to 0000 after x days

[Coffee]

Hey there,

 

 

currently i set safes and lockbox's to 0000 after 14 days on our server, i'm wanting to do this so it also sets combo locks on doors the same for those abandoned bases we all dread on servers,

 however we have LockboxStorageLocked  &  VaultStorageLocked  combo locks being ItemComboLock   but i cannot find any class name for a combolock being locked does anyone know this ? 

 

 

 

Kind regards

 

 

 

[gopostal]

It doesn't have a class per se, it's used to modify something else like CinderWallDoorLocked_DZ or CinderWallDoorSmallLocked_DZ. There is where you'll find the combolock once it is "locked".

[Coffee]

Hello thanks for the reply,

 

 so if i just search them them two then it should return the results thank you very much :) 

[vbawol]

What is your cleanup set to? an abandoned base by default will only last 6 days.

[Coffee]

What is your cleanup set to? an abandoned base by default will only last 6 days.

iv got it set to -1  not ideal i know but for some reason i was having issues with parts of bases de spawning  randomly even when maintained i have indestructible bases on, so figure this was the best way to go forward this way old bases can be recycled so to speak or if found by an admin deleted manually if the lock is 000 

 

Would you be able to release the script once done? WOuld be awesome ;)

 

xBowBii

It's not a script as such more just a sql event that i used from this post   i did not create it just modifying it Sandbird is the original creator from what i can see  just have a read of that thread it's quite intresting.

 

 

hope this helps

[WGC GeekGarage]

I've disabled epoch cleanup system since from my experience is some what unreliable.

 

Instead of this i'm using two SQL events, read the comment on each to see what they do

DELIMITER ;
DROP EVENT IF EXISTS threeHourEvent;
DROP EVENT IF EXISTS oneDayEvent;
DELIMITER //
CREATE EVENT threeHourEvent
	ON SCHEDULE EVERY 3 HOUR
	COMMENT 'Rebuild vehicle table from trader. Set object damage 0,1 if buildable. Reset Safe lockbox codes. Restock Traders'
	DO
		BEGIN
			DROP TABLE vehicle_test;
			create table vehicle_test as select item from traders_data where  AFILE LIKE 'trade_any_vehicle';
			UPDATE vehicle_test SET item=SUBSTRING(item,1,LENGTH(item)-4);
			UPDATE vehicle_test SET item = replace(item,'[','');
			UPDATE vehicle_test SET item = replace(item,'"','');
			UPDATE object_data SET Damage = 0.1 WHERE Damage = 0 AND NOT (Classname LIKE '%vault%' OR Classname LIKE '%box%' OR Classname LIKE '%LightPole%') and classname NOT IN (select * from vehicle_test);
			UPDATE object_data SET CharacterID = 0 WHERE `LastUpdated` <= date_sub(CURRENT_TIMESTAMP, INTERVAL 7 DAY) AND (Classname LIKE '%vault%');
			UPDATE object_data SET CharacterID = 10000 WHERE `LastUpdated` <= date_sub(CURRENT_TIMESTAMP, INTERVAL 7 DAY) AND (Classname LIKE '%box%');
			UPDATE `traders_data` SET qty=20 WHERE qty<>20;
		END //
CREATE EVENT oneDayEvent
	ON SCHEDULE EVERY 1 DAY
	COMMENT 'Remove 1 day old dead players. Remove buildables every 7 day if not maintained except vault and lockbox. Remove old player login data'
	DO
		BEGIN
			DELETE FROM `character_data` USING character_data, character_data AS tmpcharacter_data WHERE NOT character_data.CharacterID=tmpcharacter_data.CharacterID AND character_data.CharacterID<tmpcharacter_data.CharacterID AND (character_data.PlayerUID=tmpcharacter_data.PlayerUID) AND character_data.LastLogin <= date_sub(CURRENT_TIMESTAMP, INTERVAL 1 DAY);
			DELETE FROM `object_data` WHERE `LastUpdated` < DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 7 DAY) AND NOT (Classname LIKE '%vault%' OR Classname LIKE '%box%' OR Classname LIKE '%LightPole%') and classname NOT IN (select * from vehicle_test);
			DELETE FROM `character_data` WHERE `LastLogin` <= date_sub(CURRENT_TIMESTAMP, INTERVAL 30 DAY);
			DELETE FROM `player_login` WHERE `Datestamp` <= date_sub(CURRENT_TIMESTAMP, INTERVAL 14 DAY);
		END //
DELIMITER ;

Thx to Cen for his post further down regarding character deletion if dead, i did see the flaw in my version.

Updated 04/01-14

[calamity]

this wont make player loose humanity ???

I really need a sql to delete almost all character_data but leaves the latest one soo they keep humanity

 

            DELETE FROM `character_data` WHERE `Alive` = 0 AND `LastLogin` <= date_sub(CURRENT_TIMESTAMP, INTERVAL 1 DAY);
 
            DELETE FROM `character_data` WHERE `LastLogin` <= date_sub(CURRENT_TIMESTAMP, INTERVAL 30 DAY);
        END //
DELIMITER ;

[cen]

As long as you don't delete the last record for that player they won't lose humanity.

[calamity]

As long as you don't delete the last record for that player they won't lose humanity.

will this that delete all or just most of them ??? should this work without deleting humanity ??

I just dont understand what its actually delete

 

have one that will work for me ??? 

[cen]

Just run this:

delete FROM `character_data`
USING character_data, character_data AS tmpcharacter_data
WHERE NOT character_data.CharacterID=tmpcharacter_data.CharacterID
AND character_data.CharacterID<tmpcharacter_data.CharacterID
AND (character_data.PlayerUID=tmpcharacter_data.PlayerUID)

[gopostal]

Cen nailed it. I use that all the time to trim the c_data.

[WGC GeekGarage]

Runs every 3 hour:

DROP TABLE vehicle_test;
create table vehicle_test as select item from traders_data where  AFILE LIKE 'trade_any_vehicle';
UPDATE vehicle_test SET item=SUBSTRING(item,1,LENGTH(item)-4);
UPDATE vehicle_test SET item = replace(item,'[','');
UPDATE vehicle_test SET item = replace(item,'"','');

This will create a new table and copy over all the vehicles from trader as this is needed for comparing later, we run this every 3 hour in case we updated the trader with new vehicles (THis one i got from my buddy Gazeroth from my community, awesome dude that works with SQL every day)

UPDATE object_data SET Damage = 0.1 WHERE Damage = 0 AND NOT (Classname LIKE '%vault%' OR Classname LIKE '%box%' OR Classname LIKE '%LightPole%') and classname NOT IN (select * from vehicle_test);

This will set damage to 0.1 so we can maintain. tho it will not set damage on vehicles  that you can buy or if the same classname is spawned in by server, and it will not set damage on safes and lockboxes and light poles. Reason that we don't give damage to light polesn is that the slightest damage they getn the lightbulb will get destroyed.

UPDATE object_data SET CharacterID = 0 WHERE `LastUpdated` <= date_sub(CURRENT_TIMESTAMP, INTERVAL 7 DAY) AND (Classname LIKE '%vault%');

Resetting safe code to "0000" after 7 days

UPDATE object_data SET CharacterID = 10000 WHERE `LastUpdated` <= date_sub(CURRENT_TIMESTAMP, INTERVAL 7 DAY) AND (Classname LIKE '%box%');

Resetting Lockbox code to RED 00 after 7 days

UPDATE `traders_data` SET qty=20 WHERE qty<>20;

Restocking Traders if quantity is lower or higher than 20. this is a very simple version, i would recommend to expand it so it will do a dafault and then add in some more trader updates that depended on the class names

 

 

Runs once a day:

 

Single Character Support

DELETE FROM `character_data` USING character_data, character_data AS tmpcharacter_data
WHERE NOT character_data.CharacterID=tmpcharacter_data.CharacterID
AND character_data.CharacterID<tmpcharacter_data.CharacterID
AND (character_data.PlayerUID=tmpcharacter_data.PlayerUID)
AND character_data.LastLogin <= date_sub(CURRENT_TIMESTAMP, INTERVAL 1 DAY);

Multiple Character Support

DELETE FROM `character_data` USING character_data, character_data AS tmpcharacter_data
WHERE NOT character_data.CharacterID=tmpcharacter_data.CharacterID
AND character_data.CharacterID<tmpcharacter_data.CharacterID
AND (character_data.PlayerUID=tmpcharacter_data.PlayerUID)
AND (character_data.Slot=tmpcharacter_data.Slot)
AND character_data.LastLogin <= date_sub(CURRENT_TIMESTAMP, INTERVAL 1 DAY); 

this will delete any record where the character isn't alive and have been dead for more than a day. Thx to Cen for his post. 

DELETE FROM `object_data` WHERE `LastUpdated` < DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 7 DAY) AND NOT (Classname LIKE '%vault%' OR Classname LIKE '%box%' OR Classname LIKE '%LightPole%') and classname NOT IN (select * from vehicle_test);

This will delete old objects from the database thats older than 7 days. it will not delete safes, lockboxes, lightpoles (as we can't maintain them since we can't add damage to them) and vehicles (Air / Land / Sea) that is in taht table we created with our 3 hour event.

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

This will delete any character that haven't logged in 30 days or more

DELETE FROM `player_login` WHERE `Datestamp` <= date_sub(CURRENT_TIMESTAMP, INTERVAL 14 DAY);

Will delete login records if older than 14 days

 

(I updated my other post with the small modifications)

[calamity]

thank you guyz soo much I just deleted over 70000 lines.   should that reduce lag at all ???

I ran

DELETE FROM `player_login` WHERE `Datestamp` <= date_sub(CURRENT_TIMESTAMP, INTERVAL 14 DAY);

 

and

 

delete FROM `character_data`
USING character_data, character_data AS tmpcharacter_data
WHERE NOT character_data.CharacterID=tmpcharacter_data.CharacterID
AND character_data.CharacterID<tmpcharacter_data.CharacterID
AND (character_data.PlayerUID=tmpcharacter_data.PlayerUID)

 

 

 

Now if I could just get up the nerve to clean object data.

a question about this  command

 

DELETE FROM `object_data` WHERE `LastUpdated` < DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 7 DAY) AND NOT (Classname LIKE '%vault%' OR Classname LIKE '%box%' OR Classname LIKE '%LightPole%') and classname NOT IN (select * from vehicle_test);

 

 

I dont require maintenance on my server  I set object data back to  fresh every few days, soo this wont work for me right ??

 

this will delete walls,floors,sandbags and even vehicles from object data right ?? 

 

I would like to clean my object data but I just feel like I cannot due to the no maintenance needed on my server

[WGC GeekGarage]

The long but important answer:

well this issue is since you don't have maintenance on your server, and run update on all objects now and then, you have no way of knowing what is old objects and not in use and what is current and being used by your players. You as a server owner have to take a stand and say "this is how we are doing it on this server" and tell them to do maintenance. Just explain them why it's important and how it will benefit them in the long run (less laggy server as there will be fewer objects to load in). Some times the easy way for the player in game will fuck up your server and eventually make your players leave.

 

The short version:

you are correct that will not work as you need maintenance on your server

 

Regarding vehicles

Any SQL code above this post that refer to "vehicle_test" table is depended on that you have run this first at least once!

DROP TABLE vehicle_test;
create table vehicle_test as select item from traders_data where  AFILE LIKE 'trade_any_vehicle';
UPDATE vehicle_test SET item=SUBSTRING(item,1,LENGTH(item)-4);
UPDATE vehicle_test SET item = replace(item,'[','');
UPDATE vehicle_test SET item = replace(item,'"','');

as it will compare the object that it want to delete and is making sure that it's not in the "vehicle_test" table, this way it won't delete vehicles, hence the code

and classname NOT IN

Side Note:

Always run a dump of your SQL database before running new query!

[Asian Kid]

how can i get this to work
Dont want it every 7 days 

Want it unused (unlocked) in the last 7 days set it to 000

 

How would i change this?

UPDATE object_data SET CharacterID = 0 WHERE `LastUpdated` <= date_sub(CURRENT_TIMESTAMP, INTERVAL 7 DAY) AND (Classname LIKE '%vault%');

[WGC GeekGarage]

 

how can i get this to work

Dont want it every 7 days 

Want it unused (unlocked) in the last 7 days set it to 000

 

How would i change this?

UPDATE object_data SET CharacterID = 0 WHERE `LastUpdated` <= date_sub(CURRENT_TIMESTAMP, INTERVAL 7 DAY) AND (Classname LIKE '%vault%');

that is what it does mate. thats why it checks "LastUpdated" as that field will update if any one adds or remove items from it

[Buck0]

geek what do you run to cleanup vehicles?

[WGC GeekGarage]

geek what do you run to cleanup vehicles?

DELETE FROM `object_data` WHERE object_data.CharacterID = 0 AND (Inventory = '[]' OR Inventory = '[[[],[]],[[],[]],[[],[]]]');

[calamity]

thankz for that GeekGarage

I changed it abit to repair and or refuel

 

UPDATE `Object_DATA` SET fuel=1 WHERE object_data.CharacterID = 0 AND (Inventory = '[]' OR Inventory = '[[[],[]],[[],[]],[[],[]]]');

 

 

UPDATE `Object_DATA` SET `damage` = '0.00000' WHERE Object_DATA.CharacterID = 0 AND (Inventory = '[]' OR Inventory = '[[[],[]],[[],[]],[[],[]]]');

[Buck0]

Only problem is with that part, I changed that to 21 days as we dont want safes to unlock for 3 weeks

So I assume that stays the same for vehicles aswell

Could I use a sql query to delete vehicles referenced from the vehicle test and last updated 3 days?

Or something like that?

[Buck0]

DELETE FROM `object_data` WHERE `LastUpdated` < DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 7 DAY) AND classname IN (select * from vehicle_test);

Would that work to clean up 7 day old vehicles? Allowing me to leave safes set @ 21 days?

[Dismal]

What is the difference between single character and multiple character support?

 

is it players that use two GUIDs?

[Dismal]

can anyone help?

[cen]

AxeCop created a script that lets you have 3 characters on a single server

[Dismal]

ahhh ok, so if it doesn't have that script then single character support is fine.

[skunkdave]

edited for not searching forums thoroughly enough to the answer to my question.
 :P