3

I have a database with around 150 tables, and many relations between them.

Now sometimes the foreign key relation of some tables becomes invalid. So while the foreign keys and indexes are set, and also set to cascade on update/delete; I get invalid indexes.

As example Table A has as row with a value in the foreign key relation 1. So Table B should have 1 as its primary key. Yet it's another value, usually 0 (not null).

Now I am not sure when this does happen, that is why I am asking here. I thought setting a foreign key, enforces the constraint to be valid. But apparently there must be some exception to it.

Things that I suppose could be the cause (I hope it's one of those, if these problems can happen during "normal" usage .. I will be scared):

  • rebuild / optimize table
  • export & import of the database through HeidiSQL or phpMyAdmin

The biggest problem with this is, that the database looks like it's running fine, as most queries work, only a few inserts/updates fail. So usually it gets by unnoticed, till some bug arises.

The actual questions:

  • Why does this happen?
  • How can I prevent this from happening?
  • If it can't be prevented, what are the "best practice" workarounds?

shop_award_product.shop_product_id is referenced with shop_product.id.

And well, shop_award_product.shop_product_id had 0 while shop_product.id was different 1 or 3.

CREATE TABLE `shop_award_product` (
    `shop_award_id` INT(11) UNSIGNED NOT NULL,
    `shop_product_id` INT(11) UNSIGNED NOT NULL,
    `sort_order` INT(11) UNSIGNED NOT NULL DEFAULT '1000',
    `created_at` DATETIME NOT NULL,
    `created_by` INT(11) UNSIGNED NOT NULL,
    `updated_at` DATETIME NOT NULL,
    `updated_by` INT(11) UNSIGNED NOT NULL,
    PRIMARY KEY (`shop_award_id`, `shop_product_id`),
    INDEX `shop_product_id` (`shop_product_id`),
    CONSTRAINT `fk_shop_awardproduct$shop_product` FOREIGN KEY (`shop_product_id`) REFERENCES `shop_product` (`id`) ON UPDATE CASCADE ON DELETE CASCADE
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
;

CREATE TABLE `shop_award` (
    `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `website_id` INT(11) UNSIGNED NOT NULL,
    `created_at` DATETIME NOT NULL,
    `created_by` INT(11) UNSIGNED NOT NULL,
    `updated_at` DATETIME NOT NULL,
    `updated_by` INT(11) UNSIGNED NOT NULL,
    PRIMARY KEY (`id`),
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=4
;

Additional information:
MySQL version: 5.7.15-0ubuntu0.16.04.1

According to a comment, the problem has to be with import and disabling the check for foreign keys. But I am not sure yet that is the case, as I have always exported a db, and imported it again without actually modifying the SQL code. And the source DB is apparently ok, while the new DB has the problem (which I discover weeks later).

I am glad I got no response like "this stuff just happens from time to time". But still have to figure out why it happens, I suppose it has to be when an export & import is being done, but I never edit the export. Still it's the only moment when FOREIGN_KEY_CHECKS is disabled.

No change of collation or character set during the import. I am not the only person able to edit the tables, but the chance that someone else changed that is very small. It has happened to me like 4 times in the past 6 months, and I have imported/exported about 100 db's in that time. This time, the tables involved and the project involved it is IMHO below 1% chance that someone else caused it.

  • 2
    export & import ... Importing into tables with foreign keys carries an implicit assumption that You Know Exactly What You're Doingᵀᴹ. Your exported scripts almost certainly temporarily SET FOREIGN_KEY_CHECKS = 0; so that you don't have to deal with ordered (or circular) dependencies or the increased restoration times that foreign key checking would cause. If you manipulate the exported data before importing, and thereby invalidate it then yes MySQL assumes the foreign keys are all valid and happily imports them without verification, because the script asked. Could this be related? – Michael - sqlbot Oct 24 '16 at 20:31
2
+100

I have solution to some of your problems

Q) Why does this happen?

A) because invalid or missing values in data-change statements such as INSERT or UPDATE, ...etc

Note disabling FOREIGN_KEY_CHECKS one of the main causes of this violation.

Q) Preventing or workaround There is some restriction you can add to your database, I'm not sure if this practical (DBA should be able to decide)

SET sql_mode = 'STRICT_TRANS_TABLES';
SET sql_mode = 'STRICT_ALL_TABLES';

you can also use the following script https://github.com/michaelirey/mysql-foreign-key-checker to check foreign key problems and fix them.

Regarding rebuild / optimize table this may cause problem, it happened to me once on SQL server. not sure about MySQL.

my bad since the answer is not organized enough, I will try to re-organize it soon.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.