A transaction is created and never terminated if flush throws an error.

[PetrAda]

Describe the bug
A transaction is created and never terminated if flush throws an error.

To Reproduce

We have the following code snippet:

/**
 * @property int $id {primary}
 * @property DateTimeImmutable|null $createdAt {default now}
 * @property string|null $presenter
 * @property string|null $method
 * @property string|null $action
 * @property string|null $url
 * @property string|null $ip
 * @property string|null $request
 * @property User|null $user {m:1 User, oneSided=true}
 */
class Log extends AbstractEntity {}


function logRequest(): void
    {
        try {
            $log = new Log();
           $orm->getRepositoryForEntity($log)->attach($log);
            $log->user = $this->getUser()->isLoggedIn() ? (int)$this->getUser()->getId() : null;
            $log->createdAt = new DateTimeImmutable();
            $log->presenter = $this->getName();
            $log->action = $this->getAction();
            $log->url = $this->getHttpRequest()->getUrl()->getRelativeUrl();
            $log->method = $this->getHttpRequest()->getMethod();
            $log->ip = $this->getHttpRequest()->getRemoteAddress();
            $log->request = $request ?: null;
            $this->orm->persistAndFlush($log);
        } catch (\Exception) {
        }
    }

And a database which does NOT have the request column.
This results in an exception being thrown.

The SQL ONLY RUNS
BEGIN TRANSACTION

Steps to reproduce the behavior. It could include:

1. Code which you run
2. Definition of entities
3. SQL table definitions
4. Which SQL queries are executed

Expected behavior
A clear and concise description of what you expected to happen.

Versions::

• Database: [PERCONA DB]
• nextras/orm:v5.0.1
• nextras/dbal:v5.0.7

[hrach]

Expected behavior
A clear and concise description of what you expected to happen.

This is important -> what do you expect to happen?

---

My expectation is the following: it is the dev's responsibility to decide how to proceed, if rollback, do something else, etc. You can work directly with the connection. Also, there are differences among particular RDBMSes, so the actual decision on how to proceed may vary widely.

Yes, we may miss some kind of proxy "api" on Model, so if you have any suggestions...

See https://nextras.org/orm/docs/main/unique-constraint - that is kinda the way to go - at least for now.

[PetrAda]

Expected behavior

The database state has been rolled back (if resonable, rollbacks may not exist after all) to the state before the flush began and so has the connection state - charset, ignore foreing keys, the current transaction nesting level ( if it was 3 before it should be 3 after), etc if any of that is internally used.

---

I suppose we can follow the idea of the https://nextras.org/orm/docs/main/unique-constraint
The issue with that is that the database and connections to it are left in an undefined case.

But assuming that I can handle a generic error from the flush call feels misguided. Basically all I could reasonably do to recover from an error like that is to show an error, discard all changes and move on.

There is no way to get a "commited/rejected" changelist from the ORM. There is no way to know which of the potentially multiple connections I use (see #760 for an example of someone else using multiple connections) have been touched during the flush.

If anything the mapper should be the place to handle this error and potentially propagate it for handling if en error can be handled.

I have personally not experienced a case where I could recover a database error and fix the cause in runtime.

[JanTvrdik]

AFAIK the only general way to correctly handle transaction commit failure is to consistently use retryable transaction pattern, i.e. clean identity map and reprocess everything again.

https://gist.github.com/JanTvrdik/3e98d0a60e67c81ded82024d7b3e1592#file-transactionmanager-php-L58-L102