Id Generators are not compatible with PrimaryReadConnection anymore in 2.10

[stof]

BC Break Report

Q	A
BC Break	yes
Version	2.10.2

Summary

The ID generators are not using executeQuery() instead of query. But executeQuery does not guarantee that the primary connection is used (on purpose) and the id is generated on persist, so before starting the flush transaction. See https://github.com/doctrine/dbal/blob/141daa53e4f717a9ca234182b81179f9d1a573d5/src/Connections/PrimaryReadReplicaConnection.php#L36-L56

Previous behavior

In 2.9 and older, the id generators (I'm using the SequenceGenerator in my case) were using query() and so were automatically switching to the primary

Current behavior

Reading the next val of the sequence is done on the read replica, which fails as that action cannot be performed with read-only priviledges.

How to reproduce

[stof]

Commit from https://github.com/doctrine/orm/pull/8794/files#r762331392

[derrabus]

Would you be able to work on a fix?

[derrabus]

Fixed by #9239