The transaction operation cannot be performed because there are pending requests working on this transaction.

[mleko64]

Hello! I have some problem to run this bundle in my project. I'm using MSSQL server to store data. I installed bundle, configured and always I got error from database: SQLSTATE[42000]: [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]The transaction operation cannot be performed because there are pending requests working on this transaction.

I spent few hours to figure out what is going on and notice two things: 1) If in test will be execute flush method more than once , I got an error. For example:

This produces an error:

public function testFoo(): void
{
  $currency = CurrencyFactory::new()->create(); // using Foundry
  $country = CountryFactory::new()->create(); // using Foundry
}

This works fine:

public function testFoo(): void
{
  Factory::delayFlush(function() {
    $currency = CurrencyFactory::new()->create(); // using Foundry
    $country = CountryFactory::new()->create(); // using Foundry
  });
}

This produces an error:

public function testFoo(): void
{
  $currency = new Currency('EUR'); // Ordinary Entity
  $this->em->persist($currency);
  $this->em->flush();

  $country = new Country('DE'); // Ordinary Entity
  $this->em->persist($country);
  $this->em->flush();
}

This works fine:

public function testFoo(): void
{
  $currency = new Currency('EUR'); // Ordinary Entity
  $this->em->persist($currency);
  //$this->em->flush();

  $country = new Country('PL'); // Ordinary Entity
  $this->em->persist($country);
  $this->em->flush();
}

2) If some error occurs on database, I got an error. For example:

This works fine:

public function testFoo(): void
{
  $country = new Country('DE');
  $country->setDescription('Germany'); // Let's assume "description" field is required in Database

  $this->em->persist($country);
  $this->em->flush();
}

This produces an error:

public function testFoo(): void
{
  $country = new Country('DE');
  //$country->setDescription('Germany'); // Let's assume "description" field is required in Database

  $this->em->persist($country);
  $this->em->flush();
}

If some error occurs on database (in this case I forgot set description) I don't get error that field "description" in database is NOT NULL, etc, but I got the same error which I mentioned before.

Of course in my project I'm using Foundry bundle and main problem is with case number 1, but case number 2 is annoying, because if some error occurs on database I don't know what is going on and is difficult to find the bug.

Maybe I doing some wrong?

Thanks for yours help!

[dmaicher]

Hi. Hm that's very hard to tell whats happening there. I never used MSSQL server.

If you find a way to provide a small reproducer (including docker compose config to run that MSSQL server) I can take a look. If not then I'm afraid you will need to dig into it yourself.

[dmaicher]

Which version of the bundle are you using? Latest v8?

[mleko64]

doctrine/doctrine-bundle: 2.10.1 doctrine/orm: 2.17.3 dama/doctrine-test-bundle: 8.0.2 zenstruck/foundry: 1.36.1 Symfony: 6.3

I'll try to prepare a sample project for you tonight. This will make it easier for you to check what's going on. Thanks a lot.

[mleko64]

I found the problem. While I was preparing a sample project for you, I started stripping it of unnecessary files and noticed that I had a custom database driver. In this driver, I have overwritten methods "prepare" and "query" where I add another query (SELECT) to the base query. This additional query while inserting the record is what causes the error:

public function prepare(string $sql): StatementInterface
{
    if (preg_match('/insert into/i', $sql)) {
        $sql = "$sql; SELECT COALESCE(SCOPE_IDENTITY(), @@IDENTITY) AS 'Identity';";

        try {
            $stmt = $this->getNativeConnection()->prepare($sql);
            assert($stmt instanceof \PDOStatement);
            $this->insertStmt = $stmt;

            return new Statement(new \Doctrine\DBAL\Driver\PDO\Statement($stmt));
        } catch (\PDOException $exception) {
            throw Exception::new($exception);
        }
    }

    return new Statement(
        $this->connection->prepare($sql)
    );
}

After removing the modifications, everything works fine. So, thank you for your effort and time.