Thanks for the report.
Apparently, our KB is outdated. Same happens on MySQL 5.5/5.6 (as opposed to MySQL 5.1), and it's been documented in MySQL manual accordingly. Compare:
If there are any FOREIGN KEY constraints that reference the table, InnoDB processes TRUNCATE TABLE by deleting rows one by one, processing the constraints as it proceeds. If the FOREIGN KEY constraint specifies DELETE CASCADE, rows from the child (referenced) table are deleted, and the truncated table becomes empty. If the FOREIGN KEY constraint does not specify CASCADE, the TRUNCATE TABLE statement deletes rows one by one and stops if it encounters a parent row that is referenced by the child, returning this error:
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 permitted.
Naturally, we inherited the same behavior in MariaDB 5.5 and then 10.0.
Hey,
so the current behavour is correct, just the docomentation is outdated? Hmm, maybe it would be better the other way around because the behavour described in the documentation sounds much more usefull.
Imagine you want to empty a table. You don't always know the table structure. So why not just delete it row by row if a direct truncate is not possible?
It would save users a lot of work.
Greetings
Leo
I don't know why the InnoDB team chose to do it this way. The only obvious thing is that it was intentional (hence the changes in documentation). I suppose you can ask around, e.g. on the mailing lists (MariaDB or MySQL), maybe somebody has information to share.
Meanwhile, I'm not sure it's a good idea to change this kind of behavior in MariaDB only (while MySQL and Percona still have what it is now), it will only bring more problems for replication and such. We can ask jplindst for the expert opinion.
I am not that interrested in asking such questions on the MySQL mailinglist. As soon as they realize that the question started on the MariaDB tracker they behave like idiots.
I don't see a problem with replication here because who uses replication between MariaDB<->MySQL?
I am not that interrested in asking such questions on the MySQL mailinglist. As soon as they realize that the question started on the MariaDB tracker they behave like idiots.
OK, lets see, maybe jplindst will be able to shed some light on the subject.
I don't see a problem with replication here because who uses replication between MariaDB<->MySQL?
Actually, many people use replication between MariaDB<->MySQL, it appears quite often in environment description.
Also, MySQL => MariaDB is a common upgrade/crossgrade path. For MariaDB => MySQL, we are trying not to break it more than absolutely necessary, so that people know there is a way back.
I just looked into the code and if a truncate would delete it row by row it still would trigger the delete events and so on. That would mean that, even in replication mode, it would be a normal delete and so no problem at all for the replication. Even against mysql.
The documentation has been updated
From 5.6 manual: "Logically, TRUNCATE TABLE is similar to a DELETE statement that deletes all rows, or a sequence of DROP TABLE and CREATE TABLE statements. To achieve high performance, it bypasses the DML method of deleting data. ". Note that foreign keys can create a directed network like A ->B -> C||D -> E. Thus, truncate on child table could trigger delete on parent table row that is required on other child table and what we should do then as truncate can't be rolled back ?
Is a rollback on a truncate possible? As far as i remember the code it is not possible to roll back a truncate. However deleting row by row would be possible to rollback.
If you know for sure the table relations are such that TRUNCATE would be performed as a row-by-row DELETE, then you can just run DELETE which would be possible to rollback.
If you don't know for sure whether it will be TRUNCATE or DELETE, you cannot rely on this possible rollback anyway.
Thats exactly my point. Rollbacks are not blocking this issue.
Remember that if we would follow foreign keys on truncate this could lead delete on child table that has ON DELETE RESTRICT: Rejects the delete or update operation for the parent table, thus we should roll back but we can't. I do not see any bug here, this is current limitation.
Just for the record. I disagree with your last statement. It's wrong and makes no sence. But since you closed that ticket there is nothing i can do about it 
Consider following:
create table parent(a int not null primary key) engine=innodb;
|
create table child1(a int not null primary key, b int,
|
foreign key (b) references parent(a) on delete cascade on update cascade) engine=innodb;
|
create table child2(a int not null primary key, b int,
|
foreign key (b) references parent(a) on delete restrict on update restrict) engine=innodb;
|
|
|
insert into parent values (1);
|
insert into child1 values (1,1);
|
insert into child2 values (1,1);
|
commit;
|
Now consider first case where we would do normal delete:
MariaDB [test]> delete from parent;
|
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test`.`child2`, CONSTRAINT `child2_ibfk_1` FOREIGN KEY (`b`) REFERENCES `parent` (`a`))
|
In this case we can naturally roll back the delete. However, consider
truncate table parent;
|
If we execute this row by row, firstly row from child1 is deleted (this case is fine) but row from child2 may not be deleted because we have specified ON DELETE RESTRICT and also parent row can't be then deleted, instead we should roll back the truncate. This is not currently possible. Not deleting rows that are needed for foreign key constraints on truncate is also not logical.
I still dont see the difference between truncate triggering a row by row delete and a user triggering a row by row delete. It's the same effect for all referenced tables. They have to delete there entry. But it does not matter, i have added a function called "Force Truncate" into my HeidiSQL Clone for Linux. If the truncate fails the client will do the row by row delete automaticly.
Difference is that delete is transactional (all or nothing is executed) but truncate is not (if table is found all rows are deleted). Thus, for truncate we need to first check can we do it and then execute. In middle of execution of the truncate there is no way to roll back all that we have done so far.