0

I have an empty table amasty_audit_log_details with the following constraint:

enter image description here

(The table in the screenshot is "amasty_audit_log_entry")

I also tried to change "On Update" and "On Delete" to NO UPDATE but I get the same error.

Error:

SQLSTATE[23000]: Integrity constraint violation: 
1452 Cannot add or update a child row: a foreign key constraint fails

    `xxxxxxx_28072022`.`amasty_audit_log_details`,
    CONSTRAINT `AMASTY_AUDIT_LOG_DETAILS_LOG_ID_AMASTY_AUDIT_LOG_ENTRY_ID`
    FOREIGN KEY (`log_id`)
    REFERENCES `amasty_audit_log_entry`

query was: 

    INSERT INTO `amasty_audit_log_details` (`log_id`, `name`, `old_value`, `new_value`, `model`) VALUES (?, ?, ?, ?, ?)

The table amasty_audit_log_entry is also empty.

What might be the cause?

More Details:

amasty_audit_log_details:

CREATE TABLE IF NOT EXISTS `amasty_audit_log_details` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Log Detail ID',
  `log_id` int(10) unsigned NOT NULL COMMENT 'Log Entry ID',
  `name` text DEFAULT NULL COMMENT 'Name',
  `old_value` text DEFAULT NULL COMMENT 'Old Value',
  `new_value` text DEFAULT NULL COMMENT 'New Value',
  `model` text DEFAULT NULL COMMENT 'Model',
  PRIMARY KEY (`id`),
  KEY `idx_amasty_amaudit_details_log_id` (`log_id`),
  KEY `AMASTY_AUDIT_LOG_DETAILS_LOG_ID` (`log_id`),
  CONSTRAINT `AMASTY_AUDIT_LOG_DETAILS_LOG_ID_AMASTY_AUDIT_LOG_ENTRY_ID` FOREIGN KEY (`log_id`) REFERENCES `amasty_audit_log_entry` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8 COMMENT='Amasty Admin Actions Log Log Detail Table';

amasty_audit_log_entry:

CREATE TABLE IF NOT EXISTS `amasty_audit_log_entry` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Log Entry ID',
  `date` datetime DEFAULT NULL COMMENT 'Log Entry Date',
  `username` varchar(40) DEFAULT NULL COMMENT 'Log Entry Username',
  `type` varchar(70) DEFAULT NULL COMMENT 'Log Entry Type',
  `category` varchar(255) DEFAULT NULL COMMENT 'Log Entry Category',
  `category_name` varchar(255) DEFAULT NULL COMMENT 'Log Entry Category Name',
  `parameter_name` varchar(255) DEFAULT NULL COMMENT 'Log Entry Parameter Name',
  `element_id` int(10) unsigned NOT NULL COMMENT 'Log Entry Element ID',
  `item` text DEFAULT NULL COMMENT 'Log Entry Item',
  `ip` varchar(30) DEFAULT NULL COMMENT 'Log Entry Item',
  `store_id` int(10) unsigned DEFAULT NULL COMMENT 'Log Entry Store ID',
  `additional_data` blob DEFAULT NULL COMMENT 'Additional serialized data',
  PRIMARY KEY (`id`),
  KEY `AMASTY_AUDIT_LOG_ENTRY_USERNAME` (`username`),
  KEY `AMASTY_AUDIT_LOG_ENTRY_CATEGORY` (`category`),
  KEY `AMASTY_AUDIT_LOG_ENTRY_ELEMENT_ID` (`element_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Amasty Admin Actions Log Entry Table';
Black
  • 18,150
  • 39
  • 158
  • 271
  • `REFERENCE amasty_audit_log_entry` - shouldn't there by any log entry when you add a row to the details table? Why do you want to add a row to the details table first when it needs a log ID? – Nico Haase Dec 15 '22 at 12:06
  • I can't add any row to the details table, I tried this: `INSERT INTO `amasty_audit_log_details` (`log_id`, `name`, `old_value`, `new_value`, `model`) VALUES (1, 'test', 'x', 'y', 'test2')` but I get that constraint error. – Black Dec 15 '22 at 12:08
  • But there **is** an entry in the table `amasty_audit_log_entry` with ID 1? – Nico Haase Dec 15 '22 at 12:09
  • No, there is no data at all in any table. – Black Dec 15 '22 at 12:10
  • And why do you reference a log entry with ID 1 then? If that does not exist and should not exist, remove the foreign key constraint – Nico Haase Dec 15 '22 at 12:12
  • Where do I reference and entry with ID 1 ? In the example in the comment I try to add a row with ID 1. – Black Dec 15 '22 at 12:12
  • 1
    If you insert something into the log_details table, you reference a row in the log_entry table by setting `log_id` to 1. If no row with that ID exists, the foreign key constraint fails – Nico Haase Dec 15 '22 at 12:16
  • I see. Thank you! ... So the problem is caused because the logic thinks that an entry exists in `amasty_audit_log_entry` and tries to add an entry to `amasty_audit_log_entry_details` ... Then the problem is a bug in the code. The code should first check if the entry even exists before trying to add something to the child table. – Black Dec 15 '22 at 12:18
  • "because the logic thinks" - no, because whoever configured these tables added a foreign key constraint. There's no bug in the code you've shared so far. – Nico Haase Dec 15 '22 at 12:59
  • I mean the PHP code which triggers the SQL insert. It should check the table before the insert, to check if the parent table even has the entry. Otherwise it makes no sense to insert, because it will fail. This could have been prevented with code logic. – Black Dec 16 '22 at 12:06

0 Answers0