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

Schaffhausen

Are you calling setUp() and tearDown() where needed? – Latvia

Added my setUp() method to my original question – Schaffhausen

Are you developing a package? And why don't u use sqlite instead of a proper db? – Urano

I cannot use sqlite because my code include specific features that are not supported by sqlite – Schaffhausen

in this case I am not testing a package – Schaffhausen

Can you update your question and include one test method? – Urano

Updated my question and added a gist – Schaffhausen

Are you giving the db user the right privileges to remove & create tables – Idolah

yes, since the usual migrations are all running under same user – Schaffhausen

There was a bug fixed in the latest version of Laravel 5.6 relating to RefreshDatabase in tests. Try upgrading to the latest version of 5.6 – Indigested

Which fix are you refering to? – Schaffhausen

@Schaffhausen were you able to test a service that uses database transactions? while using the RefreshDatabase trait? – Marcel

@Marcel at least the problem described above has been fixed in the meantime – Schaffhausen

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://mcmap.net/q/369449/-mysql-truncate-table-within-transaction

Pelson

Denes you are a hero – Abnegate

Does DELETE FROM reset the ID column like truncate? – Sleeve

delete is compatible with transactions, only truncate is not compatible – Pelson

Answer 2

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();
}

Awaken

In my case, i guess because of RefreshDatabase this does not work: Doctrine\DBAL\Driver\PDOException : SQLSTATE[42000]: Syntax error or access violation: 1305 SAVEPOINT trans2 does not exist – Schaffhausen

Answer 3

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.

Goodfornothing

It might depend on the Laravel version, but I don't think Model::all()->each->delete() would work. Specifically, each is a function that takes a callback, not a property, unless I'm mistaken. – Salley

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()
{
    ...
}

Balneology

Hi. RefreshDatabase does this as well but they don’t work in my case. I tried out your idea as well, but transactions don’t wanna work here :( – Schaffhausen

Answer 5

I fix this bug creating these keys in phpunit.xml

</php>
    <env name="DB_CONNECTION" value="sqlite"/>
    <env name="DB_DATABASE" value=":memory:"/>
</php>

And using RefreshDatabase.

Embroideress

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center. – Cowberry

Answer 6

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;

Emphasis