There is no active transaction

[rela589n]

PHP version: 8.0.6 MySQL version: 5.7

After updating php from 7.0 to 8.0 this issue arises. All the fixtures successfully load their data into database, but after that process fails.

I think this is related to PHP's BC break:

PDO::inTransaction() now reports the actual transaction state of the connection, rather than an approximation maintained by PDO. If a query that is subject to "implicit commit" is executed, PDO::inTransaction() will subsequently return false, as a transaction is no longer active.

[kolobok22]

some error.

In Connection.php line 1840:
There is no active transaction 

After updating php 7.4 to php 8.0.3 doctrine/data-fixtures version: 1.5.0

[greg0ire]

As pointed out by @rela589n , it probably means that one of the queries run inside a transaction causes an implicit commit in MySQL. The next step would be to find which one (see https://dev.mysql.com/doc/refman/8.0/en/implicit-commit.html). TRUNCATE comes to mind.

You can read more on implicit commits at https://github.com/doctrine/migrations/blob/3.2.x/docs/en/explanation/implicit-commits.rst

[mcorteel-harel]

I get the exact same error with data-fixtures v1.5.1 (DoctrineFixturesBundle v3.4.1) after migrating from PHP 7.3 to PHP 8.0. Has anyone found a workaround?

[BafS]

Same issue here, doing an alter table to change the AUTO_INCREMENT will cause an implicit commit.

The ugly workaround is to open a new transaction at the end of the fixture.

[greg0ire]

How can you have a call to ALTER TABLE when loading fixtures? :thinking:

[BafS]

It's mixed with legacy logic but basically to change the AUTO_INCREMENT, so it doesn't start at 1

[greg0ire]

Is it Doctrine which performs this alter table for you?

[BafS]

No, it's a raw SQL query with a shared PDO connection

[greg0ire]

Ok. For all I know that's the case for everybody complaining in this thread, so I'm going to close this, but if somebody has evidence that some of the SQL causing implicit commits was generated by this library, post another message and I will reopen.

[rtek]

if somebody has evidence that some of the SQL causing implicit commits was generated by this library, post another message and I will reopen.

@greg0ire I came across this issue by using an ORMPurger with setPurgeMode(ORMPurger::PURGE_MODE_TRUNCATE) since TRUNCATE in mysql causes implicit commit.

[greg0ire]

Ok. Here is the calling code: https://github.com/doctrine/data-fixtures/blob/51c1890e8c5467c421c7cab4579f059ebf720278/lib/Doctrine/Common/DataFixtures/Executor/ORMExecutor.php#L68-L76

If you are using MySQL or Oracle, you cannot have transactions and use TRUNCATE, and we cannot make that choice for you. I think all we can do here is improve the docs, which, in the case of this repository, seem to be the README.md :sweat_smile:

The Symfony bundle has more docs: https://github.com/doctrine/DoctrineFixturesBundle/blob/3.4.x/Resources/doc/index.rst#specifying-purging-behavior

But documenting everything there would be kind of unfriendly to people who use other frameworks / no framework / don't use the bundle.

[rtek]

Understood.

I ended up just moving the purge step outside of the transaction boundary.

$purger = new ORMPurger($em);
$purger->setPurgeMode(ORMPurger::PURGE_MODE_TRUNCATE);
$purger->purge();
$executer = new ORMExecutor($em);
$executer->execute($fixtures, true);

[greg0ire]

That makes the transaction a bit less useful, doesn't it? You went from "the fixture loading either succeeds or fails completely" to "the fixture loading may fail and leave you in a state where you have no data in the database"

[rtek]

"the fixture loading either succeeds or fails completely"

I believe it was the opposite. Using the truncate purger within the transaction, you get this:

• execute
• begin
• purge
• truncate [implicit commit]
• load [statements in autocommit mode]
• load failure
• rollback [ignored since there is no transaction]
• partial fixture data in the database

The difference now is that in PHP8 the \PDO is complaining about there being no transaction after the load because the truncate implicitly ended it, so it complains for both load success and failure.

By moving the purger outside of the load, the EM will be able to rollback to the purged database... which I think is the best you can do since the truncate will never be a transactional statement.

[greg0ire]

Oh right, indeed 👍

the best you can do since the truncate will never be a transactional statement.

Correct, why not switch to DELETE then? You need your ids to be reset, maybe?

[rtek]

Correct, why not switch to DELETE then? You need your ids to be reset, maybe?

You know... honestly I don't know what a valid use case would be . Only things I can think of are:

1) Reset IDs (but then again we're using this package to avoid dealing with that!) 2) Performance (but that doesn't matter since the number of rows is trivial)

Any idea why the purge mode was made available in the first place?

Was it a work around to deal with the purging not ordering the deletes correctly?

https://gist.github.com/Ocramius/96206e3b39e96bd64bc5

[greg0ire]

Any idea why the purge mode was made available in the first place?

The git log shows a commit linking to #17 and #25