1

Okay, I've just inherited a project from a previous developer, before I came in they had problems of a particular table losing records and leaving exactly the same number of records each time, the records get erased completely. And I noticed that there are lots of DELETE statements in the code as well, but I can't find the script that deletes the records.

For now I run a CRON job twice a day to back up the database. I have checked for CASCADE DELETE using this SQL

USE information_schema;

SELECT 
    table_name
FROM
    referential_constraints
WHERE
    constraint_schema = 'my_database_name'
AND referenced_table_name IN 
   (SELECT table_name 
    FROM information_schema.tables 
    WHERE table_schema ='my_database_name')
 AND delete_rule = 'CASCADE'

It lists all the tables in my database and checks for any possibilities of a CASCADE DELETE, but so far it returns empty. I use SQL a lot because I'm a back-end developer but I'm not an expert at it. So I could really use some help because it's getting quite embarrassing each time it happens. It's a MySQL database. Thanks.

Devops-Paddy
  • 97
  • 1
  • 9
  • Would seem more productive to look in the code for `DELETE FROM ` – RiggsFolly Mar 28 '19 at 11:14
  • You may be in the terrible place of having to log ***everything*** and then trawl the log to find out what caused it. Either with your application's own log or using something like : https://stackoverflow.com/questions/303994/log-all-queries-in-mysql *(You do have application logs? If so, then add a brute-force check of how many rows are in the table in as many places as you can?)* – MatBailie Mar 28 '19 at 11:15
  • @MatBailie thanks i was just about to ask that – Devops-Paddy Mar 28 '19 at 11:19

1 Answers1

1

I onced faced a similar situation. I created an SQL TRIGGER statement that stored the rows into another table before they get deleted, that way:

  1. I was able to restore the lost data each time it happened.
  2. I was able to study the rows being deleted and the information helped in resolving the situation.

Here's a sample for backing up the records before they are deleted:

CREATE TABLE `history_table` LIKE `table`;

ALTER TABLE `history_table`
MODIFY COLUMN `id` INT UNSIGNED NOT NULL;

ALTER TABLE `history_table` DROP PRIMARY KEY;

DELIMITER $$

CREATE TRIGGER `deleted_table` BEFORE DELETE on `table`
FOR EACH ROW
BEGIN
    INSERT INTO history_table
    SELECT *
    FROM table
    WHERE id = OLD.id;
END$$

DELIMITER ;

For restoring the table

INSERT INTO table
    SELECT * FROM history_table
Ikechukwu
  • 1,135
  • 1
  • 13
  • 30