Laravel tests don't rollback transaction after each test

Question

My Tests are using the trait RefreshDatabase to "migrate fresh" before starting tests and use transactions for each testing method.

The migration works fine, but the transactions are not working at all.

I try to expose my setup:

• MariaDB 10.1 running in a docker container (I already proofed that all tables being used in my tests are in InnoDB, so transactions are supported)
• The base test class is using the RefreshDatabase
• I tried a separate connection for testing together with $connectionsToTransact and also using the default connection for testing. Transaction do not work either

My setUp method:

protected function setUp()
{
    parent::setUp();

    Queue::fake();
}

You find the complete Test Class and Test base class in this Gist: https://gist.github.com/patriziotomato/e25de1e160dace08edefa682b64bd150

I tried to debug already and also came down to PDO something starting and rolling back a transaction, so it seems like the laravel code attempt to transact and rollback, but it does not have any affect in my tests.

I need ideas what else could go wrong

Answer 1

You are probably using Model::truncate().

Unfortunately truncate() is not "compatible" with transactions since MySQL 5.1.32. You can DROP the table but can't truncate() inside a transaction.

http://dev.mysql.com/doc/refman/5.1/en/truncate-table.html

According to this URL, as of MySQL 5.1.32, TRUNCATE TABLE is DDL and NOT DML like DELETE. This means that TRUNCATE TABLE will cause an implicit COMMIT in the middle of a transaction block. So, use DELETE FROM on a table you need to empty instead of TRUNCATE TABLE.

Related answers from StackOverflow and Laracasts:

• https://laracasts.com/discuss/channels/eloquent/db-transaction-doesnt-appear-to-rollback-properly
• https://stackoverflow.com/a/5972738/667773

Answer 2

I had the same issue myself with a similar MySQL setup . I also tried Anthony's solution from above, and I saw the same ...1305 SAVEPOINT trans2 does not exist... error just as well.

In my case the culprit was a Model::truncate() operation within the code (for a reimport command). Somehow that seems to have upset Laravel's transaction/rollback handling (or MySQL's?) resulting in the above error. Using Model::all()->each->delete() instead has solved my problem. (After some further testing, it seems I cannot reset the auto_increment value either, so that's where the problem must lie...)

It is worth noting that it probably wouldn't have occurred with an in-memory database but with a MySQL setup for example if a rogue entry remains intact that could easily mess with the upcoming tests resulting in hard-to-debug errors, so just be careful... :)

UPDATE The best answer on this Laracast thread actually explains that the transaction operation has an implicit commit during the operation and that throws the transaction stack during testing.

Answer 3

I have the same problem. Never found the exact cause, but have a workaround - start and rollback transactions manually:

public function setUp()
{
    parent::setUp();
    DB::beginTransaction();
}

public function tearDown()
{
    DB::rollback();
    parent::tearDown();
}

Answer 4

To rollback a transaction made in the your test files you could use DatabaseTransactions:

...
use Illuminate\Foundation\Testing\DatabaseTransactions;
...

class SomeTest extends TestCase {

    use DatabaseTransactions;

public some_assertion_method()
{
    ...
}

Answer 5

Change file phpunit.xml

<php>
        <env name="DB_CONNECTION" value="mysq"/>
        <env name="APP_ENV" value="local"/>
        <env name="CACHE_DRIVER" value="array"/>
        <env name="SESSION_DRIVER" value="array"/>
        <env name="QUEUE_DRIVER" value="sync"/>
        <env name="MAIL_DRIVER" value="array"/>
        <env name="SMS_DRIVER" value="array"/>
</php>

Add this trait to test file:

use DatabaseTransactions;