Bug #64648 | Doesnt allow to Truncate Parent Table | ||
---|---|---|---|
Submitted: | 14 Mar 2012 12:24 | Modified: | 14 Mar 2012 14:43 |
Reporter: | Flavian C | Email Updates: | |
Status: | Not a Bug | Impact on me: | |
Category: | MySQL Server: InnoDB storage engine | Severity: | S2 (Serious) |
Version: | 5.5.19 | OS: | Any |
Assigned to: | CPU Architecture: | Any |
[14 Mar 2012 12:24]
Flavian C
[14 Mar 2012 12:48]
Valeriy Kravchuk
Please, send complete, repeatable test case. Just copy/paste all statements executed and error messages you get, for example.
[14 Mar 2012 13:13]
Flavian C
create 2 table CREATE TABLE `parentTable` ( `col1` varchar(10) NOT NULL, `col2` varchar(80) DEFAULT NULL, `col3` varchar(100) DEFAULT NULL, `col4` varchar(200) DEFAULT NULL, `version` int(3) NOT NULL DEFAULT '0', PRIMARY KEY (`col1`))ENGINE=InnoDB DEFAULT CHARSET=latin1; insert into `parentTable`(`col1`,`col2`,`col3`,`col4`) values ('xxx','aaa','bbb','ddd'),('yyy','aaa','ccc','eee'); CREATE TABLE `childTable` ( `bol1` int(3) NOT NULL, `bol2` varchar(15) NOT NULL, `bol3` int(11) NOT NULL, `bol4` varchar(20) NOT NULL, `version` int(3) NOT NULL DEFAULT '0', PRIMARY KEY (`bol1`,`bol2`,`bol4`), KEY `FK_childTable` (`bol4`), CONSTRAINT `FK_childTable` FOREIGN KEY (`bol4`) REFERENCES `parentTable` (`col1`) ON UPDATE CASCADE, ) ENGINE=InnoDB DEFAULT CHARSET=latin1 PACK_KEYS=0; insert into `childTable`(`bol1`,`bol2`,`bol3`,`bol4`) values ('1','sadd','111','xxx'),('2','sada','222','yyy'),('3','fwfc','333','xxx'); Now I need to truncate both the tables. First I truncated the childTable and it works. Then when I tried to truncate parentTable, it shows error. Cannot truncate a table referenced in a foreign key constraint (`childtable`, CONSTRAINT `FK_childTable` FOREIGN KEY (`bol4`) REFERENCES `parentTable` (`col1`)) (0 ms taken) Even though there isn’t any data in childTable, why I couldn’t truncate the parentTable?
[14 Mar 2012 14:16]
Valeriy Kravchuk
This is a documented limitation, check http://dev.mysql.com/doc/refman/5.5/en/truncate-table.html: "TRUNCATE TABLE fails for an InnoDB table if there are any FOREIGN KEY constraints from other tables that reference the table. Foreign key constraints between columns of the same table are allowed." This is the case for a long time already. Check bug #54678 for the details why this was introduced.
[14 Mar 2012 14:43]
Flavian C
>>This is the case for a long time already. Check bug #54678 for the details why this was In 5.1.50 or 58 The ParentTable used to check if the data is present in childTable before it would drop and recreate the table when truncate command is issued. eg if the data is present in the childTable referencing the parentTable truncate table would give foreign key constraint. if data is truncated in childTable and then you try truncate table on parentTable it would go through.