Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-23822

Deleting unique constraint with column in fk breaks inserts

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Duplicate
    • 10.5.5
    • 10.2.35, 10.3.26, 10.4.16, 10.5.7
    • None

    Description

      Upgraded to 10.5.5 from 10.2.2 because of MDEV-20723 . Still having an issue, though, with the same tables.

      Haven't checked this on MySQL. Checked just the 10.5.5 version in the docker image and debian system.

      Kinda minimal code to reproduce:

      DROP DATABASE testmaria;
      CREATE DATABASE IF NOT EXISTS testmaria;
      SELECT VERSION();
      USE testmaria;
      create table organization
      (
          id          int auto_increment comment '#|hidden'
              primary key
      )
          comment 'Organizations' charset = utf8;
      create table menu
      (
          id               int auto_increment comment '#|hidden'
              primary key,
          organization_id  int  null comment 'Organization',
          date_realization date null comment 'Date realization',
          constraint `add-menu`
              unique (organization_id, date_realization),
          constraint `menu-fk`
              foreign key (organization_id) references organization (id)
                  on update cascade on delete cascade
      )
          comment 'Menu' charset = utf8;
      INSERT INTO organization (id)
          VALUES (5);
      INSERT INTO `menu` (`organization_id`, `date_realization`)
          VALUES (5, '2020-09-27');
      SET foreign_key_checks = 0;
      ALTER TABLE menu
          DROP INDEX `add-menu`;
      SET foreign_key_checks = 1;
      -- Neither this works
      INSERT INTO `menu` (`organization_id`, `date_realization`)
          VALUES (5, '2020-09-27');
      -- Nor this
      INSERT INTO `menu` (`organization_id`, `date_realization`)
          VALUES (5, '2020-09-28');
      

      Version output and errors, logs for successful statements skipped:

      10.5.5-MariaDB-1:10.5.5+maria~focal
      testmaria> INSERT INTO `menu` (`organization_id`, `date_realization`)
                     VALUES (5, '2020-09-27')
      [2020-09-25 20:07:04] [23000][1452] (conn=21) Cannot add or update a child row: a foreign key constraint fails (`testmaria`.`menu`, CONSTRAINT `menu-fk` FOREIGN KEY (`organization_id`) REFERENCES `organization` (`id`) ON DELETE CASCADE ON UPDATE CASCADE)
      testmaria> INSERT INTO `menu` (`organization_id`, `date_realization`)
                     VALUES (5, '2020-09-28')
      [2020-09-25 20:07:11] [23000][1452] (conn=21) Cannot add or update a child row: a foreign key constraint fails (`testmaria`.`menu`, CONSTRAINT `menu-fk` FOREIGN KEY (`organization_id`) REFERENCES `organization` (`id`) ON DELETE CASCADE ON UPDATE CASCADE)
      

      What i'd like to see:

      last two queries successfully insert data

      What helps:

      Recreating the table with new structure and the data. After executing the statements from above, doing this fixes the inserts:

      SET foreign_key_checks = 0;
      DROP TABLE menu;
      create table menu
      (
          id               int auto_increment comment '#|hidden'
              primary key,
          organization_id  int  null comment 'Organization',
          date_realization date null comment 'Date realization',
          constraint `menu-fk`
              foreign key (organization_id) references organization (id)
                  on update cascade on delete cascade
      )
          comment 'Menu' charset = utf8;
      INSERT INTO `menu` (`organization_id`, `date_realization`)
      VALUES (5, '2020-09-27');
      SET foreign_key_checks = 1;
      -- Now this works
      INSERT INTO `menu` (`organization_id`, `date_realization`)
      VALUES (5, '2020-09-27');
      -- And this
      INSERT INTO `menu` (`organization_id`, `date_realization`)
      VALUES (5, '2020-09-28');
      

      How I fix this in production:

      For those who stumbles upon and doesn't know how to fix.
      NB! I have a pretty small table with the problem - just several Kbs, if you have big problematic table - consider checking that you have enough space.

      1. check that you have the corresponding CREATE TABLE statement for the current database state for the problematic table.
      1. disconnect apps, make backups
      1. set foreign_key_checks = 0; select * from [TABLE] into outfile '/tmp/DB_TABLE.csv'; drop table [TABLE]; create table [TABLE] [here goes the DDL you should have from previous lines]; load data infile '/tmp/DB_TABLE.csv' into table TABLE;

      Attachments

        Issue Links

          Activity

            People

              Unassigned Unassigned
              sheershoff Ilya Sheershoff
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.