prue420

SQL Vehicle Cleanup Query?

19 posts in this topic

If you follow Dwarfers guide and ad a updated filed it would recommend also set the code to 0000:

CREATE DEFINER=`exile`@`localhost` EVENT `Delete vanished vehicles` 
ON SCHEDULE EVERY 1 DAY STARTS '2015-09-10 14:25:02' 
ON COMPLETION NOT PRESERVE ENABLE 
COMMENT 'Delete vehicles from inactive users' 
DO UPDATE `vehicle` SET `is_locked` = '0', `pin_code` = '0000' WHERE `updated` < now() - INTERVAL 14 DAY)
Edited by Flow
1 person likes this

Share this post


Link to post
Share on other sites
Advertisement

Well you would just switch the delete from to something like.

update `vehicle` set is_locked=0 WHERE `updated` < now() - INTERVAL 14 DAY)

I have note tested so make a backup !!

Naturally when you run this the updated coloum will have the new time stamp on it..

However I must stress doing this while the server is running is BAD VERY BAD.. Best bet is to add some script todo this when the server restart.

Test servers FTW Will test

 

Share this post


Link to post
Share on other sites

You can always change DELETE or UPDATE to SELECT * FROM to return a count first :)

Share this post


Link to post
Share on other sites
Got this error.
 
 
 CREATE DEFINER=`exile`@`localhost` EVENT `Delete vanished vehicles`
ON SCHEDULE EVERY 1 DAY STARTS '2015-09-10 14:25:02'
ON COMPLETION NOT PRESERVE ENABLE
COMMENT 'Delete vehicles from inactive users'
DO UPDATE `vehicle` SET `is_locked` = '0', `pin_code` = '0000' WHERE `updated` < now() - INTERVAL 14 DAY)
 
[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 5
 
 
deleted ) after day and looked to of worked.
Edited by BZerk

Share this post


Link to post
Share on other sites

You have a Problem with ")"

This should fix it

CREATE DEFINER=`exile`@`localhost` EVENT `Delete vanished vehicles`
ON SCHEDULE EVERY 1 DAY STARTS '2015-09-10 14:25:02'
ON COMPLETION NOT PRESERVE ENABLE
COMMENT 'Delete vehicles from inactive users'
DO UPDATE `vehicle` SET `is_locked` = '0', `pin_code` = '0000' WHERE `updated` < (now() - INTERVAL 14 DAY);

You can also have a look at this -> http://www.exilemod.com/topic/750-database-cleanup-scripts/

There i gathered it all together.

Share this post


Link to post
Share on other sites

hey, 

 

here you go just change the date at the end to fit your needs.

SELECT
vehicle.id,
vehicle.class,
vehicle.spawned_at,
vehicle.account_uid,
vehicle.is_locked,
vehicle.fuel,
vehicle.damage,
vehicle.hitpoints,
vehicle.position_x,
vehicle.position_y,
vehicle.position_z,
vehicle.direction_x,
vehicle.direction_y,
vehicle.direction_z,
vehicle.up_x,
vehicle.up_y,
vehicle.up_z,
vehicle.cargo_items,
vehicle.cargo_magazines,
vehicle.cargo_weapons,
vehicle.cargo_container,
vehicle.pin_code
FROM
vehicle
INNER JOIN account ON account.uid = vehicle.account_uid
WHERE last_connect_at < '2015-09-05 *:*:*'

 

Unfortunately our Host does not allow scheduled querys, so I have to do it as a one time thing.

I'm able to pull out the vehicles that haven't been accessed since that date, but then I don't see the option to delete those rows on MySQL Workbench.

NP3VFpP.jpeg

How do I execute a deletion to matching rows?

Thanks in advance.

Share this post


Link to post
Share on other sites

How bout

 

delete  *

from vehicle t1 where t1.account_uid 
in (select t2.uid from account t2 where t2.uid = t1.account_uid and t2.last_connect_at <= '2015-09-17 00:00:00')

 

just change the date to whatever you want?

Share this post


Link to post
Share on other sites

Hello all

I am trying to setup my database to delete vehicles after 14 days, however it does not seem to be working.

here is what I have for my query.

CREATE DEFINER=`exile`@`localhost` EVENT `Delete vanished vehicles`
ON SCHEDULE EVERY 1 DAY STARTS '2017-12-29 14:25:02'
ON COMPLETION NOT PRESERVE ENABLE
COMMENT 'Delete vehicles from inactive users'
DO DELETE FROM `vehicle` WHERE `account_uid` IN (SELECT `uid` FROM `account` WHERE `last_connect_at` < NOW() - INTERVAL 14 DAY)

 

Here is the error I get when I click on Explain before running the query...

EXPLAIN CREATE DEFINER=`exile`@`localhost` EVENT `Delete vanished vehicles`
ON SCHEDULE EVERY 1 DAY STARTS '2017-12-29 14:25:02'
ON COMPLETION NOT PRESERVE ENABLE
COMMENT 'Delete vehicles from inactive users'
DO DELETE FROM `vehicle` WHERE `account_uid` IN (SELECT `uid` FROM `account` WHERE `last_connect_at` < NOW() - INTERVAL 14 DAY)
> 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CREATE DEFINER=`exile`@`localhost` EVENT `Delete vanished vehicles`
  ON SCHEDULE' at line 1
> Time: 0s

I am sure there is an issue however I am not sure what to fix. could someone assist me?

Thank you.

Hammer

Share this post


Link to post
Share on other sites
Advertisement

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

  • Recently Browsing   0 members

    No registered users viewing this page.