Phalcon 4 issue with TINYINT(1)

[daniel18387]

I have noticed an issue when saving records to tables in MySQL. Of the tables which have field columns with the data type TINYINT(1), I can see the values in the rows default to 1 when a row gets saved, regardless of the value given.

This only occurs when the mask is set to 1.

[ruudboon]

Can you give us a dump of your database structure and some basic code to reproduce this?

[daniel18387]

DB table mp_kyc_documents:

+--------------+---------------------+------+-----+---------+-------+
| Field        | Type                | Null | Key | Default | Extra |
+--------------+---------------------+------+-----+---------+-------+
| id           | bigint(20) unsigned | NO   | PRI | NULL    |       |
| user_id      | bigint(20) unsigned | NO   | MUL | NULL    |       |
| type         | tinyint(1) unsigned | YES  |     | 1       |       |
| status       | tinyint(1) unsigned | YES  |     | 1       |       |
| file_id      | bigint(20) unsigned | YES  |     | NULL    |       |
| created_on   | datetime            | YES  |     | NULL    |       |
| processed_on | datetime            | YES  |     | NULL    |       |
+--------------+---------------------+------+-----+---------+-------+

Code used:

$detail = [
    'id' => 123456,
    'userId' => 1234567,
    'type' => 2,
    'status' => 3,
];

$kycDocument = (new Models\MP\KycDocuments)
    ->setId($detail['id'])
    ->setUserId($detail['userId'])
    ->setType($detail['type'])
    ->setStatus($detail['status']);

$kycDocument->save();

After the save, both the type and status fields will set to 1. This has occurred since our systems upgraded to version 4 yesterday.

[ruudboon]

Thnx for the info.
We will investigate this. Looks like tinyint(1) is handled as boolean.
If you have time could try to save with value 0 for type of status?

https://github.com/phalcon/cphalcon/blob/master/phalcon/Db/Adapter/Pdo/Mysql.zep#L141

Looks like we made the assumption tinyint(1) is a boolean. Something I assumed as wel since MySql will save a boolean column as tinyint(1). Just looked up the specs but tinyint is handled as a normal tinyint (-128 through 127) but will only display 1 digit.

[daniel18387]

You are correct. It's being treated as a boolean value but it shouldn't be. As you've pointed out, it can go all the way up to 127 when signed and 255 when unsigned. It's worth noting that the (1) is only a display width. It doesn't control the width of the integer. https://dev.mysql.com/doc/refman/8.0/en/numeric-type-attributes.html

Key point from the the MySQL docs: The display width does not constrain the range of values that can be stored in the column. Nor does it prevent values wider than the column display width from being displayed correctly. For example, a column specified as SMALLINT(3) has the usual SMALLINT range of -32768 to 32767, and values outside the range permitted by three digits are displayed in full using more than three digits.

Therefore, this is a bug.

This has been brought up before on Phalcon too. See #2012

[daniel18387]

If anyone else is experiencing this issue, remove the mask from the integer data type to solve it your end before a fix is in place. i.e. Remove (1) from TINYINT(1) to leave a data type of TINYINT.

[niden]

Resolved in #14709

Thank you @daniel18387

[Ruzgfpegk]

On the other hand, those who used TINYINT(1) as a BOOLEAN (because declaring a column as BOOL in MySQL Workbench outputs a TINYINT(1)) may have welcomed the v4.0.0 behavior.

Wouldn't a setting to specify the wished behavior be a better option in the long run?