Description:
When trying to insert a row, a FK constraint says no parent row exists. The parent row does exist.
Exact error message:
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test/reporttemplatesubreport`, CONSTRAINT `reporttemplatesubreport_ibfk_2` FOREIGN KEY (`subreport`) REFERENCES `subreport` (`subreport`))
There may be something magical about the string 'PlatformsByWeekmargnonbrand' because we can insert other strings okay.
The contents of the two parent tables after the successful inserts:
+-----------------------------+-------------------------------+
| subreport | module |
+-----------------------------+-------------------------------+
| PlatformsByWeekmargnonbrand | PlatformsByWeek_marg_nonbrand |
+-----------------------------+-------------------------------+
1 row in set (0.00 sec)
+----------------+---------------------+
| reporttemplate | name |
+----------------+---------------------+
| 6 | nonbrandstandardDPS |
+----------------+---------------------+
1 row in set (0.00 sec)
(this bug also repeatable on 5.0.21-log on Gentoo)
How to repeat:
drop table if exists reporttemplatesubreport;
drop table if exists subreport;
drop table if exists reporttemplate;
CREATE TABLE `subreport` (
`subreport` varchar(50) NOT NULL,
`module` varchar(60) NOT NULL,
PRIMARY KEY (`subreport`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `reporttemplate` (
`reporttemplate` int(11) NOT NULL auto_increment,
`name` varchar(20) NOT NULL,
PRIMARY KEY (`reporttemplate`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `reporttemplatesubreport` (
`reporttemplate` int(11) NOT NULL,
`tab` smallint(6) NOT NULL,
`subreport` varchar(25) NOT NULL,
PRIMARY KEY (`reporttemplate`,`tab`),
KEY `subreport` (`subreport`),
CONSTRAINT `reporttemplatesubreport_ibfk_1` FOREIGN KEY (`reporttemplate`) REFERENCES `reporttemplate` (`reporttemplate`),
CONSTRAINT `reporttemplatesubreport_ibfk_2` FOREIGN KEY (`subreport`) REFERENCES `subreport` (`subreport`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into subreport(subreport, module)
values ('PlatformsByWeekmargnonbrand', 'PlatformsByWeek_marg_nonbrand');
insert into reporttemplate(reporttemplate, name)
values (6, 'nonbrandstandardDPS');
select * from subreport;
select * from reporttemplate;
insert into reporttemplatesubreport(reporttemplate, tab, subreport)
values (6, 25, 'PlatformsByWeekmargnonbrand');
Description: When trying to insert a row, a FK constraint says no parent row exists. The parent row does exist. Exact error message: ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test/reporttemplatesubreport`, CONSTRAINT `reporttemplatesubreport_ibfk_2` FOREIGN KEY (`subreport`) REFERENCES `subreport` (`subreport`)) There may be something magical about the string 'PlatformsByWeekmargnonbrand' because we can insert other strings okay. The contents of the two parent tables after the successful inserts: +-----------------------------+-------------------------------+ | subreport | module | +-----------------------------+-------------------------------+ | PlatformsByWeekmargnonbrand | PlatformsByWeek_marg_nonbrand | +-----------------------------+-------------------------------+ 1 row in set (0.00 sec) +----------------+---------------------+ | reporttemplate | name | +----------------+---------------------+ | 6 | nonbrandstandardDPS | +----------------+---------------------+ 1 row in set (0.00 sec) (this bug also repeatable on 5.0.21-log on Gentoo) How to repeat: drop table if exists reporttemplatesubreport; drop table if exists subreport; drop table if exists reporttemplate; CREATE TABLE `subreport` ( `subreport` varchar(50) NOT NULL, `module` varchar(60) NOT NULL, PRIMARY KEY (`subreport`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE `reporttemplate` ( `reporttemplate` int(11) NOT NULL auto_increment, `name` varchar(20) NOT NULL, PRIMARY KEY (`reporttemplate`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE `reporttemplatesubreport` ( `reporttemplate` int(11) NOT NULL, `tab` smallint(6) NOT NULL, `subreport` varchar(25) NOT NULL, PRIMARY KEY (`reporttemplate`,`tab`), KEY `subreport` (`subreport`), CONSTRAINT `reporttemplatesubreport_ibfk_1` FOREIGN KEY (`reporttemplate`) REFERENCES `reporttemplate` (`reporttemplate`), CONSTRAINT `reporttemplatesubreport_ibfk_2` FOREIGN KEY (`subreport`) REFERENCES `subreport` (`subreport`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; insert into subreport(subreport, module) values ('PlatformsByWeekmargnonbrand', 'PlatformsByWeek_marg_nonbrand'); insert into reporttemplate(reporttemplate, name) values (6, 'nonbrandstandardDPS'); select * from subreport; select * from reporttemplate; insert into reporttemplatesubreport(reporttemplate, tab, subreport) values (6, 25, 'PlatformsByWeekmargnonbrand');