Prefer native MySQL boolean type for SQL definition

[Firehed]

Feature Request

What

Update the MySQL family (inc. MariaDB, etc) to use BOOLEAN instead of TINYINT for columns defined as boolean.

Why

#7221 changed the definition of a MySQL boolean column from TINYINT(1) to TINYINT, which MySQL then spits back as TINYINT(4) in several paths (SHOW CREATE TABLE, mysqldump, etc). While today's storage engines make that a meaningless change in terms of the actual data, this comment leads me to believe that may not always be true.

Probably of more immediate relevance, I'm trying to update a large legacy app to use Doctrine tooling, and this results in a diff in the schema. At best it'll cause some people to be concerned; more likely, it could lead to inadvertent ALTER statements.

I realize it's possible to work around this with a couple of different approaches, but that seems like it shouldn't be necessary.

How

The naive approach would be to change the getBooleanTypeDeclarationSQL() in AbstractMySQLPlatform to BOOLEAN. I suspected this wouldn't work since it's an alias, but I tried it with a local patch and it actually seemed fine (both with DBAL's and ORM's diff utilities), at least against MariaDB 11.8.

[derrabus]

Prefer native MySQL boolean type for SQL definition

MySQL does not have a "native" boolean type.

While today's storage engines make that a meaningless change in terms of the actual data,

Yes. The display width is not only meaningless, it's deprecated in modern MySQL. That's why we've moved away from it.

this comment leads me to believe that may not always be true.

TINYINT(1) is excluded from a change that made MySQL not report the display width anymore, basically to help other tooling that performs schema introspection. DBAL does not need to rely on this workaround anymore.

Probably of more immediate relevance, I'm trying to update a large legacy app to use Doctrine tooling, and this results in a diff in the schema. At best it'll cause some people to be concerned; more likely, it could lead to inadvertent ALTER statements.

We might think about not generating an ALTER statement, if the display width is the only thing it would change.

The naive approach would be to change the getBooleanTypeDeclarationSQL() in AbstractMySQLPlatform to BOOLEAN.

This would basically be the same as reverting this PR because – as you've noted – BOOLEAN is just an alias.

[Firehed]

I agree with you on nearly all points. Integer display width in MySQL has always been silly, even before deprecation. The fact that it's (finally) being phased out is telling.

Having said that, not all versions and platforms have the same behavior, and the previously-mentioned change doesn't really account for that. Maybe this is more of a MariaDB-specific issue (since it still shows widths in the various show create table type commands), but IMO even MySQL after the deprecation/removal of widths are impacted too, due to how it continues to special-case these fields.

DBAL does not need to rely on this workaround anymore.

Indeed! Unfortunately, not all apps are going through DBAL tooling exclusively; anything in a more hybrid situation can be broken by the previously-mentioned change. This isn't just application code, but all sorts of data pipeline tooling.

We might think about not generating an ALTER statement, if the display width is the only thing it would change.

This is probably a good idea regardless, though it wouldn't actually matter for the nature of the migration I'm involved with.

This would basically be the same as reverting this PR because – as you've noted – BOOLEAN is just an alias.

A full revert would reintroduce the warnings on affected MySQL versions; changing to the BOOLEAN alias should be fully compatible across the board. This also has the added benefit that if MySQL were to ever introduce a true native boolean type, it should be fully forward compatible since the intent and semantics of the schema author are preserved.

[derrabus]

DBAL does not need to rely on this workaround anymore.

Indeed! Unfortunately, not all apps are going through DBAL tooling exclusively; anything in a more hybrid situation can be broken by the previously-mentioned change. This isn't just application code, but all sorts of data pipeline tooling.

We can't really account for other tools. But you can still override our platform classes, if that helps you being more compatible with other tools introspecting the schema.