initial snapshot does not define collation info

[rrd108]

Description

When using bin/cake bake migration_snapshot Initial, the generated migration file lacks collation information. Consequently, applying this migration in a production environment results in default collation settings for all tables, deviating from the collation settings on the development machine.

This issue remained unnoticed for 2 years and 62 migrations until a table creation failed due to a mismatch between the collation settings of a UUID foreign key field and the base table field.

This issue likely arose because I initially created the schema on my development machine, added data, performed a dump, and imported it to the production environment. The initial schema creation was not executed on the production machine, potentially causing this discrepancy.

Suggestion

I propose that the initial snapshot should either incorporate collation information or provide a notification in the generated migration file to alert users about potential collation discrepancies.

Question

How can I rectify this situation now? Should I create a new migration to modify the collations of all tables? I intend to switch to utf8mb4_hungarian_ci from the default collation, ensuring it doesn't result in data errors.

[dereuromark]

Refs https://github.com/cakephp/migrations/issues/332

[rrd108]

I would try to implement this and send a PR.

As I have limited time it would be nice to get a few hints on what files should I concentrate.

As I see the tests uses sqlite, but this problem is more of a MySQL problem. So should I add tests using MySQL?