SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint

[christophengelmayer]

The MySql database throws an error when trying to run the migrations.

An exception occurred while executing 'ALTER TABLE sandstorm_usermanagement_domain_model_user ADD CONSTRAINT FK_5DEB8A977D3656A4 FOREIGN KEY (account) REFERENCES neos_flow_security_account (persistence_object_identifier)':

SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint

The exception details have been logged to the Flow system log.
Exception in line 184 of /[NEOSROOT]
/Packages/Libraries/doctrine/dbal/lib/Doctrine/DBAL/DBALException.php: An exception occurred while executing 'ALTER TABLE sandstorm_usermanagement_domain_model_user ADD CONSTRAINT FK_5DEB8A977D3656A4 FOREIGN KEY (account) REFERENCES neos_flow_security_account (persistence_object_identifier)':

SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint - See also: 2018112314574529eefa.txt

2018112314574529eefa.txt

Steps to reproduce:

• Fresh installation of Neos composer create-project neos/neos-base-distribution Neos
• Add database credentials to 'Configuration/Settings.yaml'
• Run migrations ./flow doctrine:migrate
• Add UserManagement package composer require sandstorm/usermanagement
• Run migrations again (fails with error above) ./flow doctrine:migrate

Software

• PHP 7.2.11
• MySql 5.7.23
• Neos 4.1.8
• Flow 5.1.5

[christophengelmayer]

I found a solution.

The database (or only the sandstorm_usermanagement_domain_model_user.account field) has to be set to collation utf8mb4_unicode_ci.

Mine was utf8mb4_general_ci which caused the error.

[christophengelmayer]

Just found a more convenient way to fix this issue, simply run:

./flow database:setcharset

to set the right collation and encoding on your database.