Migration issues in Cake5

[dereuromark]

This is a (multiple allowed):

• bug

• enhancement

• feature-discussion (RFC)

• CakePHP Version: 5.x

What you did

        $this->table('cities')
            ->addColumn('state_id', 'integer', [
                'default' => null,
                'limit' => 10,
                'null' => true,
            ])
            ->addColumn('timezone', 'string', [
                'default' => null,
                'limit' => 50,
                'null' => true,
            ])
			->update();

        $this->table('cities')
            ->addForeignKey('state_id', 'states', ['id'], ['delete' => 'SET_NULL'])
            ->update();

This used to work, now it throws

PDOException: SQLSTATE[HY000]: General error: 1005 Can't create table tmp.cities (errno: 150 "Foreign key constraint is incorrectly formed")

It seems this could be related to the unsigned issue/changes?

The primary key gets auto created as int(11) unsigned, the state_id as (int)10 signed.
I think the state_id should also be unsigned by default then unless specified otherwise.

Basically, defaulting integer columns to

'signed' => false,

[LordSimal]

Why do you specify a limit on an integer column?

But yes, the different column types are surely the problem here. I just don't have that issue because when we had that migrations change I already unified all my PK's and FK's to be unsigned int with no length/limit, therefore I can't reproduce this.

[dereuromark]

The integer length shouldnt be relevant afaik.
Legacy.
But yeah, we should probably default as outlined above. Maybe this is more a bake issue then?

[umer936]

This was the behavior in CakePHP 4.x as well. Seemed like id columns needed to be unsigned and same length.
We've run into this issue like a dozen times and make a migration to make them unsigned int (11).

Actually you can see the result of one such change here: #647 (comment)

In this case we had "fake users" with user_id's negative but changing to unsigned broke that so the Migration above was to "fix that" in the tables.

[dereuromark]

Closing in favor of more concrete ticket #667