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:
Create the procedure to copy data from survivor into survivor_history when a survivor is created:
Create the procedure to copy data from survivor into survivor_history when a survivor is updated:
Create the procedure to mirror deleted tables in survivor:
Create the event to delete entries older than 3 days to prevent the table from growing very large:
Let me know if this has helped you, and if you think there could be any improvements made to this.
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.