error migrating liberty to mitaka recovering a sql dump from the scratch

[AlvaroVega]

Migrating from keystone liberty (spasword 1.4.X) to mitaka (1.5.X) keystone is getting this error

2021-11-03 11:11:50.028 147 ERROR keystone DBError: (_mysql_exceptions.IntegrityError) (1215, 'Cannot add foreign key constraint') [SQL: u'\nCREATE TABLE local_user (\n\tid INTEGER NOT NULL AUTO_INCREMENT, \n\tuser_id VARCHAR(64) NOT NULL, \n\tdomain_id VARCHAR(64) NOT NULL, \n\tname VARCHAR(255) NOT NULL, \n\tPRIMARY KEY (id)
, \n\tUNIQUE (domain_id, name), \n\tUNIQUE (user_id), \n\tFOREIGN KEY(user_id) REFERENCES user (id)
ON DELETE CASCADE\n)\n\n']

with some kind of data

Migrate from liberty to mitaka is needed as requested by #193

[AlvaroVega]

maybe related https://github.com/telefonicasc/iotp-onpremise/pull/91

Bugs Fixed

InnoDB: A delete operation on a parent table that initiated a cascading update on a child table with an indexed virtual column and indexed foreign key constraint column caused a virtual column corruption. (Bug #32124113)

[AlvaroVega]

mysql> CREATE TABLE local_user (id INTEGER NOT NULL AUTO_INCREMENT, user_id VARCHAR(64) NOT NULL, domain_id VARCHAR(64) NOT NULL, name VARCHAR(255) NOT NULL, PRIMARY KEY (id), UNIQUE (domain_id, name), UNIQUE (user_id), FOREIGN KEY (user_id) REFERENCES user (id) ON DELETE CASCADE);

ERROR 1215 (HY000): Cannot add foreign key constraint
mysql>

mysql> SHOW ENGINE INNODB STATUS;

LATEST FOREIGN KEY ERROR

2021-11-04 14:13:14 0x7f2aa05a6700 Error in foreign key constraint of table keystone/local_user:
FOREIGN KEY (user_id) REFERENCES user (id) ON DELETE CASCADE):
Cannot find an index in the referenced table where the
referenced columns appear as the first columns, or column types
in the table and the referenced table do not match for constraint.
Note that the internal storage type of ENUM and SET changed in
tables created with >= InnoDB-4.1.12, and such columns in old tables
cannot be referenced by such columns in new tables.
Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-foreign-key-constraints.html for correct foreign key definition.

[AlvaroVega]

https://dev.mysql.com/doc/refman/8.0/en/create-table-foreign-keys.html

Temporary disable with:
SET FOREIGN_KEY_CHECKS=0;

[AlvaroVega]

Unfortunately a keystone SQL dump is not stored all stuff needed to recover properly a database and then migrate it to a later version.
I.e. an SQL dump is not stored circular dependencies.

To achieve that is needed that keystone creates a its database and then load (without removing database before) data from a dump, in order to keep all stuff needed.