Temporary tables vs MysQL GTID consistency

[arnaud-lb]

Bug Report

Q	A
BC Break	yes
Version	^2.6

Summary

OneToManyPersister uses CREATE TEMPORARY TABLE when removing orphaned collections, which is not allowed in MySQL when using global transaction IDs.

Global transaction IDs is a feature that makes operating MySQL much easier when using replication. It is enabled server-wide. I suspect that it is very common that this feature is enabled on production servers.

Current behavior

When a OneToMany full collection is replaced, the following exception occurs:

PDOException: SQLSTATE[HY000]: General error: 1787 When @@GLOBAL.ENFORCE_GTID_CONSISTENCY = 1, the statements CREATE TEMPORARY TABLE and DROP TEMPORARY TABLE can be executed in a non-transactional context only, and require that AUTOCOMMIT = 1. These statements are also not allowed in a function or trigger because functions and triggers are also considered to be multi-statement transactions. in .composer/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOConnection.php:57

Full trace here: https://gist.github.com/arnaud-lb/31b20963b01ff2ccc8b1bcd5f75fc461

[Ocramius]

This is not fixable: as per https://github.com/doctrine/orm/commit/1587aac4ff6b0753ddd5f8b8d4558b6b40096057, this is required for Joined Table Inheritance and OneToMany associations in orphanRemoval.

Since the GTID consistency is a niche, I'm gonna mark this as "can't fix". If an alternate approach (to 1587aac4ff6b0753ddd5f8b8d4558b6b40096057) is needed, then we'd need that first.

[KVestergaard]

This is not fixable: as per 1587aac, this is required for Joined Table Inheritance and OneToMany associations in orphanRemoval.

Since the GTID consistency is a niche, I'm gonna mark this as "can't fix". If an alternate approach (to 1587aac) is needed, then we'd need that first.

Not a niche any more. Azure (and others) require enforced gtid compliance for replication (e.g. geo-replication for HA).

[Ocramius]

@KVestergaard open a new issue please: this is 4 years old, and the context may have changed.