[FIXED] transaction doesn't work on truncate

Issue

I'm trying Laravel transaction for the first time... I do most of my queries with Eloquent and since there no transaction there I have to do a mixture of Eloquent and query builder.

Here is my code:

DB::beginTransaction();

try{
    Setting::truncate();
    Setting::insert($data);
    DB::commit();
    jok('all ok');

}
catch (\Exception $e)
{
    DB::rollback();
    jerror('some error accorded! ');
}

So I've tied to add some invalid data to Settings and i got the some error accorded error as expected but the query before INSERT Setting::truncate(); was executed anyway and I ended up with a empty table.

So either I'm doing something wrong or transaction doesn't work on truncate.

Solution

TRUNCATE TABLE in a database transaction will cause a implicit COMMIT action. DELETE does not have that same behavior, so you can safely run

DELETE FROM tblname

This will clear your table and still keep the desired rollback feature of a transaction.

See the MySQL documentation on TRUNCATE. The bullet points explain this functionality http://dev.mysql.com/doc/refman/5.7/en/truncate-table.html

Answered By - Rob Fonseca