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

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

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

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…

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.

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.

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

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:

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
}

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

That’s basically what I meant.