SQL: Survivor History

Peep

Peep

New Member
This is an SQL table that will exist within your dayz database. It will save every update a survivor makes and store it in the table with a timestamp. This is very useful for catching teleporters and gear injection. It's also very useful for restoring individual players to any point in time. This is something we've used heavily over at BMRF for months, and I would like to extend it's usefulness to other servers as well.

In the code for the procedures, you may need to change the definer to another user if you do not use dayz as your user.

Create the table:

Code: 
DROP TABLE IF EXISTS `survivor_history`;
/*!40101 SET @saved_cs_client    = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `survivor_history` (
  `player_id` varchar(128) NOT NULL,
  `character_id` int(8) NOT NULL,
  `inventory` varchar(2048) NOT NULL,
  `backpack` varchar(2048) NOT NULL,
  `state` varchar(128) NOT NULL,
  `medical` varchar(255) NOT NULL,
  `worldspace` varchar(60) NOT NULL,
  `dead` int(3) NOT NULL,
  `action` enum('insert','update','delete','') NOT NULL,
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY `player_id` (`player_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

Create the procedure to copy data from survivor into survivor_history when a survivor is created:

Code: 
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`dayz`@`localhost`*/ /*!50003 TRIGGER `proc_insertHistory` AFTER INSERT ON `survivor` FOR EACH ROW insert into survivor_history
 
    values(NEW.unique_id, NEW.id, NEW.inventory, NEW.backpack, NEW.state, NEW.medical, NEW.worldspace, NEW.is_dead, 'insert', now()) */;;
DELIMITER ;

Create the procedure to copy data from survivor into survivor_history when a survivor is updated:

Code: 
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`dayz`@`localhost`*/ /*!50003 TRIGGER `proc_updateHistory` AFTER UPDATE ON `survivor` FOR EACH ROW begin
 
    insert into survivor_history
 
    values(NEW.unique_id, NEW.id, NEW.inventory, NEW.backpack, NEW.state, NEW.medical, NEW.worldspace, NEW.is_dead, 'update', now()); --
 
end */;;
DELIMITER ;

Create the procedure to mirror deleted tables in survivor:

Code: 
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`dayz`@`localhost`*/ /*!50003 TRIGGER `proc_deleteHistory` AFTER DELETE ON `survivor` FOR EACH ROW insert into survivor_history
 
    values(OLD.unique_id, OLD.id, OLD.inventory, OLD.backpack, OLD.state, OLD.medical, OLD.worldspace, OLD.is_dead, 'delete', now()) */;;
DELIMITER ;

Create the event to delete entries older than 3 days to prevent the table from growing very large:

Code: 
CREATE DEFINER=`dayz`@`localhost` EVENT `event_cleanHistory` ON SCHEDULE EVERY 4 HOUR STARTS '2013-01-25 15:47:10' ON COMPLETION NOT PRESERVE ENABLE DO delete from survivor_history where date_sub(now(), interval 3 day) > `timestamp`

Let me know if this has helped you, and if you think there could be any improvements made to this.
 
Here is for Stapo's servers:
Create the table:
Code: 
DROP TABLE IF EXISTS `character_history`;
/*!40101 SET @saved_cs_client    = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `survivor_history` (
  `PlayerUID` varchar(128) NOT NULL,
  `CharacterID` int(8) NOT NULL,
  `Inventory` varchar(2048) NOT NULL,
  `Backpack` varchar(2048) NOT NULL,
  `currentState` varchar(128) NOT NULL,
  `Medical` varchar(255) NOT NULL,
  `Worldspace` varchar(60) NOT NULL,
  `Alive` int(3) NOT NULL,
  `Action` enum('insert','update','delete','') NOT NULL,
  `Timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY `PlayerUID` (`PlayerUID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

Create the procedure to copy data from survivor into character_history when a survivor is created:
Code: 
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`dayz`@`%`*/ /*!50003 TRIGGER `proc_insertHistory` AFTER INSERT ON `character_data` FOR EACH ROW insert into character_history
 
    values(NEW.PlayerUID, NEW.CharacterID, NEW.Inventory, NEW.Backpack, NEW.currentState, NEW.Medical, NEW.Worldspace, NEW.Alive, 'insert', now()) */;;
DELIMITER ;

Create the procedure to copy data from survivor into character_history when a survivor is updated:
Code: 
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`dayz`@`%`*/ /*!50003 TRIGGER `proc_updateHistory` AFTER UPDATE ON `character_data` FOR EACH ROW begin
 
    insert into character_history
 
    values(NEW.PlayerUID, NEW.CharacterID, NEW.Inventory, NEW.Backpack, NEW.currentState, NEW.Medical, NEW.Worldspace, NEW.Alive, 'update', now()); --
 
end */;;
DELIMITER ;

Create the procedure to delete entries over older than 24 hours to prevent the table from growing very large:
Code: 
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`dayz`@`%`*/ /*!50003 TRIGGER `proc_deleteHistory` AFTER DELETE ON `character_data` FOR EACH ROW insert into character_history
 
    values(OLD.PlayerUID, OLD.CharacterID, OLD.Inventory, OLD.Backpack, OLD.currentState, OLD.Medical, OLD.Worldspace, OLD.Alive, 'delete', now()) */;;
DELIMITER ;
 
Very nice Peep. I always wondered how you created this feature. I will try out himjacks version for my pwnoz0r server tomorrow.
 
so I am getting this error running it on Chive, or directly on the console "A SQL error occured (1235): This version of MySQL doesn't yet support 'multiple triggers with the same action time and event for one table'" any ideas? I am also running the whitelister app, with the tables and procedures.
 
linkinx said:
so I am getting this error running it on Chive, or directly on the console "A SQL error occured (1235): This version of MySQL doesn't yet support 'multiple triggers with the same action time and event for one table'" any ideas?
Click to expand...

Are you sure you didn't run it twice? Check your triggers to make sure it isn't already in there.
 
unless I am looking
Peep said:
Are you sure you didn't run it twice? Check your triggers to make sure it isn't already in there.
Click to expand...
Unless I am looking in the wrong place, DB mysql Table proc , I don't see anything called proc_updatehistory or the other two.
 
linkinx said:
unless I am looking
Unless I am looking in the wrong place, DB mysql Table proc , I don't see anything called proc_updatehistory or the other two.
Click to expand...
Nevermind , I was looking in the wrong place, is under information_schema > Triggers
 
Is not possible to create this kind of stuff with a dayz hoster?

I try on my private hive (dayz.st)

I have this error when i create the first procedure

#1227 - Access denied; you need (at least one of) the SUPER privilege(s) for this operation
 
nicquehen said:
Is not possible to create this kind of stuff with a dayz hoster?

I try on my private hive (dayz.st)

I have this error when i create the first procedure

#1227 - Access denied; you need (at least one of) the SUPER privilege(s) for this operation
Click to expand...

Pretty much your account does not have access to create the trigger.
 
himjack said:
Here is for Stapo's servers:
Create the table:
Code: 
DROP TABLE IF EXISTS `character_history`;
/*!40101 SET @saved_cs_client    = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `survivor_history` (
  `PlayerUID` varchar(128) NOT NULL,
  `CharacterID` int(8) NOT NULL,
  `Inventory` varchar(2048) NOT NULL,
  `Backpack` varchar(2048) NOT NULL,
  `currentState` varchar(128) NOT NULL,
  `Medical` varchar(255) NOT NULL,
  `Worldspace` varchar(60) NOT NULL,
  `Alive` int(3) NOT NULL,
  `Action` enum('insert','update','delete','') NOT NULL,
  `Timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY `PlayerUID` (`PlayerUID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

Create the procedure to copy data from survivor into character_history when a survivor is created:
Code: 
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`dayz`@`%`*/ /*!50003 TRIGGER `proc_insertHistory` AFTER INSERT ON `character_data` FOR EACH ROW insert into character_history
 
    values(NEW.PlayerUID, NEW.CharacterID, NEW.Inventory, NEW.Backpack, NEW.currentState, NEW.Medical, NEW.Worldspace, NEW.Alive, 'insert', now()) */;;
DELIMITER ;

Create the procedure to copy data from survivor into character_history when a survivor is updated:
Code: 
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`dayz`@`%`*/ /*!50003 TRIGGER `proc_updateHistory` AFTER UPDATE ON `character_data` FOR EACH ROW begin
 
    insert into character_history
 
    values(NEW.PlayerUID, NEW.CharacterID, NEW.Inventory, NEW.Backpack, NEW.currentState, NEW.Medical, NEW.Worldspace, NEW.Alive, 'update', now()); --
 
end */;;
DELIMITER ;

Create the procedure to delete entries over older than 24 hours to prevent the table from growing very large:
Code: 
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`dayz`@`%`*/ /*!50003 TRIGGER `proc_deleteHistory` AFTER DELETE ON `character_data` FOR EACH ROW insert into character_history
 
    values(OLD.PlayerUID, OLD.CharacterID, OLD.Inventory, OLD.Backpack, OLD.currentState, OLD.Medical, OLD.Worldspace, OLD.Alive, 'delete', now()) */;;
DELIMITER ;
Click to expand...

How exactly do I execute all of this in Navicat? Please help a SQL Newb....
 
ZAadmin said:
How exactly do I execute all of this in Navicat? Please help a SQL Newb....
Click to expand...
Run it as a query, I had to remove the delimeter part, also you might want to change dayz'@'% to be dayz'@'localhost if the account only has localhost access.
 
  • Like
Reactions: Doc
You must log in or register to reply here.
Back
Top