MySQL 5.7 JSON ordering affects change computation #5948

New Issue

admin · 2026-01-22T15:22:55+01:00

admin commented

2026-01-22 15:22:55 +01:00

Originally created by @MisatoTremor on GitHub (Apr 10, 2018).

Originally assigned to: @Ocramius on GitHub.

This is not really an issue with ORM itself, but can happen in conjunction with DBAL and MySQL 5.7s JSON type

If you store an associative array as a object property which, due to processing, is always ordered before persisting (e.g. ["baz" => "bar", "foo" => "bar"]) it will be correctly processed by JsonType::convertToDatabaseValue into
{"baz": "bar", "foo": "bar"}
But due to MySQLs JSON normalization might end up in the database as
{"foo": "bar", "baz": "bar"}.
So when hydrating the object the UOW will store this as value in the original entity data and when computing changes later will detect it as change.

Originally created by @MisatoTremor on GitHub (Apr 10, 2018). Originally assigned to: @Ocramius on GitHub. This is not really an issue with ORM itself, but can happen in conjunction with DBAL and MySQL 5.7s JSON type If you store an associative array as a object property which, due to processing, is always ordered before persisting (e.g. ``["baz" => "bar", "foo" => "bar"]``) it will be correctly processed by ``JsonType::convertToDatabaseValue`` into ``{"baz": "bar", "foo": "bar"}`` But due to MySQLs [JSON normalization](https://dev.mysql.com/doc/refman/5.7/en/json.html#json-normalization) might end up in the database as ``{"foo": "bar", "baz": "bar"}``. So when hydrating the object the UOW will store this as value in the original entity data and when computing changes later will detect it as change.

admin added the Bug Can't Fix labels 2026-01-22 15:22:55 +01:00

admin closed this issue

2026-01-22 15:22:56 +01:00

admin commented

2026-01-22 15:22:56 +01:00

@Ocramius commented on GitHub (Apr 10, 2018):

This could probably be fixed by enforcing order in the DBAL type, but again, the JSON spec does not enforce any order.

The in-memory objects are PHP arrays coming from a simple json_decode()

@Ocramius commented on GitHub (Apr 10, 2018): This could probably be fixed by enforcing order in the DBAL type, but again, the JSON spec does not enforce any order. The in-memory objects are PHP arrays coming from a simple `json_decode()`

admin commented

2026-01-22 15:22:57 +01:00

@MisatoTremor commented on GitHub (Apr 10, 2018):

Sorry @Ocramius, just completed the description as I inadvertently posted the issue before finishing the text.

@MisatoTremor commented on GitHub (Apr 10, 2018): Sorry @Ocramius, just completed the description as I inadvertently posted the issue before finishing the text.

admin commented

2026-01-22 15:22:58 +01:00

@Ocramius commented on GitHub (Apr 10, 2018):

@MisatoTremor regardless about what MySQL does, we can't change anything on our side, because we'd destroy behavior that users might be relying upon.

The JSON type can sort keys before serializing, but that would be troublesome too.

If the only side-effect here is a repeated UPDATE query, then I'd rather leave this as a known issue (compared to introducing more awkwardness in JSON handling)

@Ocramius commented on GitHub (Apr 10, 2018): @MisatoTremor regardless about what MySQL does, we can't change anything on our side, because we'd destroy behavior that users might be relying upon. The JSON type can sort keys before serializing, but that would be troublesome too. If the only side-effect here is a repeated `UPDATE` query, then I'd rather leave this as a known issue (compared to introducing more awkwardness in JSON handling)

admin commented

2026-01-22 15:22:58 +01:00

@MisatoTremor commented on GitHub (Apr 10, 2018):

Yes, I thought so. My line of thinking was also that this could be noted in the docs where appropriate.

@MisatoTremor commented on GitHub (Apr 10, 2018): Yes, I thought so. My line of thinking was also that this could be noted in the docs where appropriate.

admin commented

2026-01-22 15:22:58 +01:00

@Ocramius commented on GitHub (Apr 10, 2018):

@MisatoTremor that would make a lot of sense. That affects also affects the object and simple_array types. The file to edit is probably 03c0797f03/docs/en/reference/types.rst

@Ocramius commented on GitHub (Apr 10, 2018): @MisatoTremor that would make a lot of sense. That affects also affects the `object` and `simple_array` types. The file to edit is probably https://github.com/doctrine/dbal/blob/03c0797f034be10759dff6e818a4d928872d97dc/docs/en/reference/types.rst

admin commented

2026-01-22 15:22:58 +01:00

@MisatoTremor commented on GitHub (Apr 10, 2018):

Thanks for the hads up @Ocramius. Shouldn't that just affect json and the (deprecated) json_array types as the other two use the CLOB declaration or am I overlooking something there?

@MisatoTremor commented on GitHub (Apr 10, 2018): Thanks for the hads up @Ocramius. Shouldn't that just affect ``json`` and the (deprecated) ``json_array`` types as the other two use the CLOB declaration or am I overlooking something there?

admin commented

2026-01-22 15:22:59 +01:00

@Ocramius commented on GitHub (Apr 10, 2018):

@MisatoTremor anything that the DB may manipulate/optimise after persistence.

@Ocramius commented on GitHub (Apr 10, 2018): @MisatoTremor anything that the DB may manipulate/optimise after persistence.

admin referenced this issue

2026-01-22 16:05:26 +01:00

[PR #5948] Update association-mapping.rst to ease understanding of what associations mean #9799

Sign in to join this conversation.