A misleading error message in DB migrations
A few days ago we ran into the following error messages during deployment of a Flow 7.x based application where the deployment failed while executing the database migrations:
An exception occurred while executing 'ALTER TABLE vendor_package_domain_model_something
ADD CONSTRAINT FK_BD82590B2B219D70 FOREIGN KEY (entry) REFERENCES
vendor_package_domain_model_something_else (persistence_object_identifier)':
SQLSTATE[HY000]: General error: 1005 Can't create table `package`.
`vendor_package_domain_model_something` (errno: 150 "Foreign key constraint is
incorrectly formed")
It took a while to understand that the migration itself was not the problem and fully OK. After using two tons of Google foo and the search in Neos’ Slack, we discovered that the error message is completely misleading and the source of the issue is coming from a completely different source!
We already knew about the database:setcharset command one can execute to update the charset config of tables within a Flow application’s database. But we were not aware that this was needed to be performed in this case. But the help text of that command revealed it:
FLOW_CONTEXT=Production ./flow help database:setcharset
(...)
The main purpose of this is to fix setups that were created with Flow before version 5.0. In those cases,
the tables will have a collation that does not match the default collation of later Flow versions, potentially
leading to problems when creating foreign key constraints (among others, potentially).
(...)
And with that we came a lot closer to the solution: This application was indeed older and was upgraded in the past - then not touched or further developed for some time (= no database migrationes were ever executed). Now with the newly developed feature, some new migrations were added and executing them failed.
It turned out, that some of the tables had charset and collation set to some utf-8 variant, but not all tables were set to utf8mb4 something.
Running the above command cleaned this up and harmonized all existing tables to be using the now current utf8mb4 variant charset and collation config.
After that, the “half executed” migration had to be manually fixed/reverted in the database - and then the migration could be executed and the deployment finished.
Since I don’t know whether this is a bug in MySQL / MariaDB, the Doctrine ORM layer or the Flow framework, I did not yet submit a bug report or “ideas to improve the solution issues” and just noted it here, in the hope it helps someone else with this issue searching for a solution.