Migrations - foreign key Problem

Plugins
1

Hello CakePHP community!

My Problem

Since some weeks a problem with my migrations appeared.
About three months ago I created the initial migrations file.

Now when I try to set up a new developing environment and execute the initial migration I get the error:

errno: 150 “Foreign key constraint is incorrectly formed”

The “id” columns were created automatically and not manually defined in the migrations file.
In my initial migration file the column I want to create an foreign key on has the data type “int(11)”.
In older developing environments all “id” columns have the data type “int(11)”
In new developing environments all “id” columns have the data type “int(11) unsigned”.

So the data types in new developing environments do not macht.
This is the reason why the migration fails.

It seems like suddenly the “id” column in my database have different default values on newer systems.
Here on an older developing Environment:

MariaDB [user_service_test]> describe dummytable;
+-------------+---------+------+-----+---------+----------------+
| Field       | Type    | Null | Key | Default | Extra          |
+-------------+---------+------+-----+---------+----------------+
| id          | int(11) | NO   | PRI | NULL    | auto_increment |

And here on a new one:

MariaDB [newsletter_service_test]> describe dummytable;
+-------------+------------------+------+-----+---------+----------------+
| Field       | Type             | Null | Key | Default | Extra          |
+-------------+------------------+------+-----+---------+----------------+
| id          | int(11) unsigned | NO   | PRI | NULL    | auto_increment |

My question (s)

Who is responsible for the data type of the “id” column?
Is it somewhere in the plugins? Or the MySQL adapter? …the PDO driver?

Can somebody provide me with some information how I can solve this problem without changing the data type of the columns in the database?

Some information on my environments.
Via composer the following versions are installed:

cakephp/cakephp                                4.4.10
cakephp/migrations                             3.7.1
robmorgan/phinx                                0.13.3

I am using Docker Containers running 10.10.2-MariaDB and 8.1-PHP.
I also tried Vagrant VMs and there is the same behaviour. In an older VM everything works. In a fresh VM I have the same problem.

2

See Release 3.7.0 · cakephp/migrations · GitHub

1 Like
3

Wow!
Thank you very much @KevinPfeifer for you incredible fast, simple and helpful reply!

Simple solution:

Using
"cakephp/migrations": "3.6.1"
instead of using
"cakephp/migrations": "@stable"

Harder Solution

When I want to continue to use
"cakephp/migrations": "@stable"
Or versions >= 3.7.1

I need to…

In my old developing environments and production environments I have to change the Database manually. I need to change the data type of all my “id” columns and foreign key columns manually from “int(11)” to "int(11) unsigned".

Then I have to create a new snapshot and delete all old migratons.
Or I have to change the data type of all foreigen key columns in my migration files manually.