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

[gandra]

• 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

[safoorsafdar]

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

[mrliptontea]

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

[gandra]

@mrliptontea ,
yes, I am using DatabaseTransactions trait.

[mrliptontea]

So what's happening here is Laravel is trying to simulate nested transactions using savepoints but, from my experience, they don't quite work like that. Not sure what best practices are in such cases but it is not framework's error.

One option, and that's something which I have done in the past to test code that was using transactions, is to remove DatabaseTransactions and empty/rebuild the test database before each test in setUp.

[ssmulders]

This was working just fine in 5.3, somehow the transactions stopped working for a custom database in 5.4 :-/

[arondeparon]

Experiencing the same issue. Is there a way this could somehow be supported?

[mundomatt]

@mrliptontea I have been using nested transactions for quite some time and they've worked successfully. I can confirm they are working on my local dev box. I start getting the same error messages only when the server load is high.

Laravel 5.3.31
PHP 7.0.17
MySQL 5.6.34

[mrliptontea]

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.

[mundomatt]

@mrliptontea Thank you for your quick response! I read that in the documentation but it didn't make sense to me since I have been using nested transactions successfully for so long. I thought Eloquent was using some kind of method to solve this, as I also do not have any implicit commit issues when calling beginTransaction in nested transactions.

What I can gather from this thread, and correct me if I'm wrong, is that nested transactions should be avoided as there isn't any proper solution at the moment. Even though Eloquent uses savepoints to simulate nested transactions there are still issues that arise periodically.

[mrliptontea]

That's basically what I meant.

[coogle]

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...

[pixelpeter]

I have this problem too after manually upgrading from Laravel 5.5 to 5.6 on homestead (7.0.1) running the application with php7.1

[pixelpeter]

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.

[oceanapplications]

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

[antonkomarev]

@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.

[victormacedo]

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

I have the same issue and scenario (AWS Aurora instance). Did you find a way to fix it?

[dele454]

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.

[amcsi]

I got this issue too, but it was because I forgot to run php artisan migrate for the tests.

[ty3sc]

I was experiencing this problem as well. My migrations worked fine when run manually, but when running the test suite I got the same error as the OP.

I was trying to do a complex migration and couldn't do that using the Schema facade, so instead I used the DB facade to write my SQL by hand.

In the end I solved it by swapping out a DB::raw() call in a migration for a DB::statement() call instead. I'm not sure why but DB::statement() worked much better with my test suite.

I hope this helps someone.

[aspirantzhang]

In my case, it works in my local environment, but it fails in CI.
Finally, I found the point.
Local MySQL
sql_mode: NO_ZERO_IN_DATE, NO_ZERO_DATE, NO_ENGINE_SUBSTITUTION
CI MySQL
sql_mode: STRICT_TRANS_TABLES, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION
I found that when I created the table, I didn't give the default value of the varchar type, and I forgot to write it when I assigned the value.
Therefore, in my case, the main reason is mysql strict mode.
May be helpful to you.
(By the way, the framework I am using is not laravel. )

[ribeiroplinio]

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
}

[amrshakya]

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 I'm also having the same issue where I get this error SQLSTATE[42000]: Syntax error or access violation: 1305 SAVEPOINT trans2 does not exist and also for committing I use explicit DB::commit() yet I'm still getting the same error.

For your reference here is my code screenshot
and btw I'm using laravel lumen framework
laravel version: 8

[GNUXDAR]

I solve with docker, this my docker-file.com, a old project in laravel.

version: '3.4'

services:
  php-apache:
    container_name: my_container_name
    build:
      context: .
      dockerfile: Dockerfile
    ports:
      - "8000:80"
    working_dir: /var/www/app
    volumes:
      - ../:/var/www/app
  database:
      image: mysql:5.7
      restart: no #always
      container_name: my_container_name_to_db
      environment:
        MYSQL_USER: test
        MYSQL_PASSWORD: example
        MYSQL_ROOT_PASSWORD: example
        MYSQL_DATABASE: my_db
      ports:
        - '3306:3306'
      volumes:
        - db-data:/var/lib/mysql
volumes:
  db-data:

[EricMcWinNer]

I recently had this issue when running tests on Laravel 10; PHP 8.3, and in my case it was caused by using DB::table('table')->truncate() in one of my seeders to automatically re-seed the table on migration.

I solved it by replacing DB::table('table')->truncate with DB::table('table')->delete() like @dele454 noted above.

[michaelklopf]

@EricMcWinNer What about the id? With delete you loose resetting the auto increment. I have the same problem within a Laravel Nova action now. I guess that Nova does some implicit wrapping in DB::transaction. I also had this in another place of my app, but it helped to move the truncation out of the DB::transaction block.