Bug #47855 TRUNCATE TABLE having effect on transactional blocks
Submitted: 6 Oct 2009 10:09 Modified: 6 Oct 2009 11:03
Reporter: Hector . Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: General Severity:S2 (Serious)
Version:5.1.39 Community OS:Windows
Assigned to: CPU Architecture:Any
Tags: NET, transactions, TransactionScope, truncate

[6 Oct 2009 10:09] Hector . 
Description:
On a .Net application, using latest Connector (6.1.2), I'm trying to recreate some tables in a database, first making a TRUNCATE TABLE and then inserting the corresponding data.

All the operations are made inside a TransactionScope block, but the TRUNCATE operations are commited even if the transaction has not, and the following operations are therefore also executed. However, if the insertions are done in its own block they work as expected.

My application is using provider agnostic code, and I've also seen the problem on previous versions of MySQL.

Maybe a connector bug? Maybe not a bug?

How to repeat:
Just open some connection to a database and try to truncate some table inside a TransactionScope block, set a breakpoint before commiting the transaction, and the table will be already empty. Rolling back the operation won't recover the table.
[6 Oct 2009 10:20] Valeriy Kravchuk 
As far as I know, TRUNCATE TABLE perform implicit commit both before and after TRUNCATE (see http://dev.mysql.com/doc/refman/5.1/en/truncate.html). Does this explain the results you get?
[6 Oct 2009 11:00] Hector . 
Thanks for the reply, very informative since I wasn't aware of some of all the info I've found thanks to it.

I guess this explains it all. A shame TRUNCATE cannot be rolled back, but I'll live with it heh.

So not a bug after all.
[6 Oct 2009 11:03] Susanne Ebrecht 
Many thanks for the feedback. I will close the bug now.