Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Duplicate
-
10.5.5
-
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
- duplicates
-
-
- Closed
-