yii2: Boolean MySQL migration vs. auto-model-attribute-typecast inconsistency

That is why I propose a way to define types of columns in config and keep current type detection as a fallback. For me this guessing PHP type from DB schema was always a bit too magic, I would prefer to keep these definitions as part of the code with explicitly defined defaults or types.

Yes, i agree, this commit should be reverted.

@samdark @bizley IMHO, the changes in #20045 should be reverted, there’s just too much what could break.

Ie. all models generated with gii which have a property for a tinyint(1) column have IntegerValidators, but the value would now be a boolean. And this is just one example.

https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-19.html

"Display width specification for integer data types was deprecated in MySQL 8.0.17,
and now statements that include data type definitions in their output no longer show the display width for
integer types, with these exceptions:

The type is TINYINT(1). MySQL Connectors make the assumption that TINYINT(1) columns originated as BOOLEAN
columns; this exception enables them to continue to make that assumption."

@rob006 How would that look like?

I will look into it and try to propose a solution.

Just for the record … I did some tests on the db-level.

column       length
-------------------------
tiny1        1
tinynolen    4
tiny5        5
tiny0        4

tinyint and tinyint(0) is tinyint(4)

If you are right in this case, we can fix it

How? Column schema does not check what values are stored in this column.

And even if you look at what is stored so far, how is schema supposed to know what i want to store tomorrow?

IMHO it is simply wrong to assume in the schema that EVERY tinyint col (no matter if with or without length) is a boolean. https://github.com/terabytesoftw/yii2/blob/b40de6a5380819472f5bde6c5d120e9c83146c87/framework/db/mysql/Schema.php#L282

this is valid (My)SQL and the state column is clearly NOT a boolean.

CREATE TABLE `dev1` (
       `id` int(11) NOT NULL,
       `state` tinyint(1) unsigned DEFAULT 0,
       PRIMARY KEY (`id`)
  ) ENGINE=InnoDB;

INSERT INTO `dev1` (`id`, `state`) VALUES (1, 255);

select * from `dev1`;
+----+-------+
| id | state |
+----+-------+
|  1 |   255 |
+----+-------+

And for MySQL the length param for tinyint has no effect. The possible range of values is only dependent on signed or unsigned.

Maybe we should have the same condition in MySQL Schema as for SQLite:

https://github.com/yiisoft/yii2/blob/5f3d36ea21dfc7b39d1c438e85971f03bf43f193/framework/db/sqlite/Schema.php#L333

btw in Yii3, we use bit(1), but that here would be BC.

After investigating all the yii2 issues, i also saw that yii2 supports legacy versions of Mysql in its versions 5, if it is correct that tinyint(1) would be synonymous with boolean, but not tinyint(1) unsigned which would be integer, then for me two solutions.

1.- Check the case when tinyint is unsigned, and it will work fine.

Example:

https://mysqlconnector.net/api/mysqlconnector/mysqlconnectionstringbuilder/treattinyasboolean/

2.- Let’s simply reverse the commit, and this problem should never have existed.

It’s my two cents.

@terabytesoftw sorry, but if it is boolean or not has nothing to do with signed or unsigned. the signed/unsigned flag “just” define the possible range for tinyint. https://dev.mysql.com/doc/refman/8.0/en/integer-types.html

Still valid SQL but still NO boolean col:

CREATE TABLE `dev1` (
       `id` int(11) NOT NULL,
       `state` tinyint(1) DEFAULT 0,
       PRIMARY KEY (`id`)
  ) ENGINE=InnoDB;

INSERT INTO `dev1` (`id`, `state`) VALUES (1, 127);

select * from dev1;
+----+-------+
| id | state |
+----+-------+
|  1 |   127 |
+----+-------+

To be clear: i understand the problem, that MySQL has no real boolean column type, and that the docs state that boolean “is” a tinyint(1), but the assumption the other way around, that every tinyint(1) is a boolean is simply wrong.

If you are right in this case, we can fix it

How? Column schema does not check what values are stored in this column.

https://github.com/yiisoft/yii2/blob/6804fbeae8aa5f8ad5066b50f1864eb0b9d77849/framework/db/mysql/Schema.php#L282

My questions stands also for existing projects - why would you specify length as 1 for tinyint? It does not have any practical effect, and for me it was always used to indicate that this is a boolean column.

Yeah, but this also works the other way 😃 Since it has no practical effect why bother; or maybe you work against an existing database where this is actually used to store values from 0, 255. Such an application would be completely broken.

For new projects this would be fine, but I think @VladimirRakovich comment was about existing projects

My questions stands also for existing projects - why would you specify length as 1 for tinyint? It does not have any practical effect, and for me it was always used to indicate that this is a boolean column.

Why would you use tinyint(1) if you want just tinyint? Defining length does not create any additional constrains and does not make data more compact.

For new projects this would be fine, but I think @VladimirRakovich comment was about existing projects, where model values are now boolean instead of integer - this should be a concern, especially with PHP 8.1 and above.

Why would you use tinyint(1) if you want just tinyint? Defining length does not create any additional constrains and does not make data more compact.

If you look at the mysql documentation, you can see that this data type can be -128…127 signed and 0…255 unsigned. tinyint(1) doesn’t equal the boolean type and this change will potentially break a lot of projects

https://dev.mysql.com/doc/refman/8.0/en/integer-types.html