framework: PDOException: SQLSTATE[42000]: Syntax error or access violation: 1305 SAVEPOINT trans2 does not exist

Laravel Version: v5.4.15
PHP Version: 7.1
Database Driver & Version: MySql 5.7.17

Description:

Error when runninng phpunit tests:

PDOException: SQLSTATE[42000]: Syntax error or access violation: 1305 SAVEPOINT trans2 does not exist

/home/vagrant/Code/my-api-project/vendor/laravel/framework/src/Illuminate/Database/Concerns/ManagesTransactions.php:205
/home/vagrant/Code/my-api-project/vendor/laravel/framework/src/Illuminate/Database/Concerns/ManagesTransactions.php:186
/home/vagrant/Code/my-api-project/vendor/laravel/framework/src/Illuminate/Database/DatabaseManager.php:322
/home/vagrant/Code/my-api-project/vendor/laravel/framework/src/Illuminate/Support/Facades/Facade.php:221
/home/vagrant/Code/my-api-project/app/Services/ProcessService.php:44
/home/vagrant/Code/my-api-project/app/Http/Controllers/Api/ProcessController.php:128
/home/vagrant/Code/my-api-project/vendor/laravel/framework/src/Illuminate/Routing/Controller.php:55
/home/vagrant/Code/my-api-project/vendor/laravel/framework/src/Illuminate/Routing/ControllerDispatcher.php:44
/home/vagrant/Code/my-api-project/vendor/laravel/framework/src/Illuminate/Routing/Route.php:203
/home/vagrant/Code/my-api-project/vendor/laravel/framework/src/Illuminate/Routing/Route.php:160
/home/vagrant/Code/my-api-project/vendor/laravel/framework/src/Illuminate/Routing/Router.php:559
/home/vagrant/Code/my-api-project/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php:30
/home/vagrant/Code/my-api-project/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php:102
/home/vagrant/Code/my-api-project/vendor/laravel/framework/src/Illuminate/Routing/Router.php:561
/home/vagrant/Code/my-api-project/vendor/laravel/framework/src/Illuminate/Routing/Router.php:520
/home/vagrant/Code/my-api-project/vendor/laravel/framework/src/Illuminate/Routing/Router.php:498
/home/vagrant/Code/my-api-project/vendor/laravel/framework/src/Illuminate/Foundation/Http/Kernel.php:174
/home/vagrant/Code/my-api-project/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php:30
/home/vagrant/Code/my-api-project/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php:102
/home/vagrant/Code/my-api-project/vendor/laravel/framework/src/Illuminate/Foundation/Http/Kernel.php:149
/home/vagrant/Code/my-api-project/vendor/laravel/framework/src/Illuminate/Foundation/Http/Kernel.php:116
/home/vagrant/Code/my-api-project/vendor/laravel/framework/src/Illuminate/Foundation/Testing/Concerns/MakesHttpRequests.php:234
/home/vagrant/Code/my-api-project/vendor/laravel/framework/src/Illuminate/Foundation/Testing/Concerns/MakesHttpRequests.php:206
/home/vagrant/Code/my-api-project/vendor/laravel/framework/src/Illuminate/Foundation/Testing/Concerns/MakesHttpRequests.php:97
/home/vagrant/Code/my-api-project/tests/Rest/ProcessTest.php:52

Steps To Reproduce:

Create some service class which use transaction. Put code something like this:

    public function createProcess(CreateProcessRequest $request)
    {
        DB::beginTransaction();
        try{
            if ($request->get('active', false))
            {
                $this->processRepository->desactivateAll();
            }
            $process = $this->processRepository->create($request->all());
            DB::commit();
            return $process;
        }
        catch(\Exception $e)
        {
            Log::error('createProcess() ERROR: ' . $e->getMessage(), $request->all());
            DB::rollBack();
        }

    }

then use it from controller:

    public function store(CreateProcessRequest $request)
    {
        $process = $this->processService->createProcess($request);
        $result = fractal()
            ->item($process)
            ->transformWith(new ProcessTransformer())
            ->toArray();
        return $this->sendResponseOk($result, 201);
    }

then make test for it like this:

    public function test_create_process()
    {
        $this->loginWithFakeUser();
        $process = $this->fakeProcessRequestData()->all();
        $response = $this->postJson('/api/processes', $process);
        $this->assertOkResponse($response, 201);
        $this->assertResponseJsonStructure($response);
    }

finally execute test:

phpunit  tests/Rest/ProcessTest.php

About this issue

Original URL
State: closed
Created 7 years ago
Comments: 25 (2 by maintainers)

Most upvoted comments

I know the issue is closed but for anyone looking for a solution. I had a similar problem and the following resolved it for me.

I switched from using an Eloquent model to using the DB fisade for operations within the transaction.
From MySQL docs: Truncate operations causes an implicit commit, and so cannot be rolled back.
From above I used the the delete func rather -> DB::table($table)->delete();
Write some sort of tests to back your implementation up.

When an exception got thrown within any db operation, it got rolled back to the previous state and any committed db ops was discarded.

dele454 on Apr 25, 2019

I also have the same problem if I use MyModel::truncate() in a test with the DatabaseTransactions trait.

oceanapplications on Feb 24, 2018

I have ran into this problem on MySQL as well and it’s really thrown a wrench in trying to write Unit tests… It is also not a consistent problem.

I use RefreshDatabase in my tests with MySQL as the backend (so it creates a transaction right off the bat)… In the course of my tests, I have found that calls to DB::transaction() sometimes fail and sometimes succeed depending on the actions taken WITHIN the transaction. Specifically it seemed to be related to if I was saving relationships instead of top-level models:

I.e.

/* ... */

$bool = false;

DB::transaction(function() use ($bool, $model, $bar) {
    $model->save();

    if($bool) {
        $model->relationship()->save($bar);
    }
}

If $bool == false there was no savepoint error, however if it was $bool == true it caused this savepoint exception to occur.

This feels like a bug, I think it should be reopened and investigated a bit further…

coogle on Nov 5, 2017

I assume your yest class uses DatabaseTransactions trait? Unfortunately, MySQL doesn’t support nested transactions.

mrliptontea on Mar 21, 2017

Maybe it’s helpful to somebody.

I found out what caused the issue for me when running my tests.

I’ve written an api-to-database importer and I’m using truncate() on my Model in this ServiceClass. Removing this line (MyModel::truncate()) fixed the error for me.

pixelpeter on Feb 18, 2018

From the documentation:

Transactions cannot be nested. This is a consequence of the implicit commit performed for any current transaction when you issue a START TRANSACTION statement or one of its synonyms.

Eloquent is trying to mitigate this limitation by using savepoints, which are only available in InnoDB.

It’s hard for me to say how savepoints are different from nested transactions, but they do behave differently. I also experienced unexpected ‘savepoint does not exist’ errors in the past, using different ORMs, hence I try to avoid nesting and don’t think there’s anything that can be done to Laravel’s code to fix this.

mrliptontea on Sep 15, 2017

Facing the same error on Laravel Framework version 5.1.45 (LTS) with AWS aurora instance. and not even getting any logs.

app("db")->transaction(function () use (
                $company,
                $company_detail,
                $user
            ) {
                $company            = $this->company->create($company,
                    $company_detail);
                $user['company_id'] = $company->id;
                app('Illuminate\Contracts\Console\Kernel')->call('tenant:new',
                    ['id' => $company->id]);
                $this->users->create($user);
            });

safoorsafdar on Mar 20, 2017

When having the same issue, explicitly committing the transaction worked for me:

try {
    DB::beginTransaction();

    //do stuff
    
    DB::commit();

    //return something
} catch (\Exception $e) {
    DB::rollback();

    //do something else
}

ribeiroplinio on May 19, 2021

@pixelpeter @oceanapplications I recommend to create a new issue and link it with this one. Continue conversation in issues closed long time ago isn’t productive.

antonkomarev on Feb 25, 2018

That’s basically what I meant.

mrliptontea on Sep 15, 2017