Foreign key constraint is incorrectly formed

Use this forum for questions regarding installation, upgrading and configuration of OpenEMM

Moderator: moderator

JuergenB
Posts: 65
Joined: Thu Dec 05, 2019 9:03 pm

Foreign key constraint is incorrectly formed

Post by JuergenB » Mon Feb 22, 2021 3:59 pm

Hello,

if i want to backup/dump from 20.04 and restore to 20.10 i get this erro message during restore.

Foreign key constraint is incorrectly formed

Any idea how to fix 20.04 database?

ERROR 1005 (HY000) at line 35: Can't create table `openemm`.`actop_content_view_tbl` (errno: 150 "Foreign key constraint is incorrectly formed")
[root@localhost tmp]# mysql -u root -p openemm < openemm.sql -force
mysql: option '-e' requires an argument
[root@localhost tmp]# mysql -u root -p openemm < openemm.sql -e force
Enter password:
ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'force' at line 1
[root@localhost tmp]# mysql -u root -p openemm < openemm.sql --force
Enter password:
ERROR 1050 (42S01) at line 3: Table 'access_data_tbl' already exists
ERROR 1050 (42S01) at line 19: Table 'active_subscriber_tbl' already exists
ERROR 1050 (42S01) at line 27: Table 'actop_activate_doi_tbl' already exists
ERROR 1005 (HY000) at line 35: Can't create table `openemm`.`actop_content_view_tbl` (errno: 150 "Foreign key constraint is incorrectly formed")
ERROR 1005 (HY000) at line 45: Can't create table `openemm`.`actop_execute_script_tbl` (errno: 150 "Foreign key constraint is incorrectly formed")
ERROR 1005 (HY000) at line 55: Can't create table `openemm`.`actop_get_archive_list_tbl` (errno: 150 "Foreign key constraint is incorrectly formed")
ERROR 1005 (HY000) at line 65: Can't create table `openemm`.`actop_get_archive_mailing_tbl` (errno: 150 "Foreign key constraint is incorrectly formed")
ERROR 1005 (HY000) at line 77: Can't create table `openemm`.`actop_get_customer_tbl` (errno: 150 "Foreign key constraint is incorrectly formed")
ERROR 1005 (HY000) at line 87: Can't create table `openemm`.`actop_identify_customer_tbl` (errno: 150 "Foreign key constraint is incorrectly formed")
ERROR 1005 (HY000) at line 98: Can't create table `openemm`.`actop_send_mailing_tbl` (errno: 150 "Foreign key constraint is incorrectly formed")
ERROR 1005 (HY000) at line 111: Can't create table `openemm`.`actop_service_mail_tbl` (errno: 150 "Foreign key constraint is incorrectly formed")
ERROR 1005 (HY000) at line 127: Can't create table `openemm`.`actop_subscribe_customer_tbl` (errno: 150 "Foreign key constraint is incorrectly formed")
ERROR 1005 (HY000) at line 139: Can't create table `openemm`.`actop_tbl` (errno: 150 "Foreign key constraint is incorrectly formed")
ERROR 1005 (HY000) at line 151: Can't create table `openemm`.`actop_update_customer_tbl` (errno: 150 "Foreign key constraint is incorrectly formed")
ERROR 1005 (HY000) at line 171: Can't create table `openemm`.`admin_group_permission_tbl` (errno: 150 "Foreign key constraint is incorrectly formed")
ERROR 1146 (42S02) at line 182: Table 'openemm.admin_group_permission_tbl' doesn't exist
ERROR 1005 (HY000) at line 207: Can't create table `openemm`.`admin_permission_tbl` (errno: 150 "Foreign key constraint is incorrectly formed")
ERROR 1146 (42S02) at line 217: Table 'openemm.admin_permission_tbl' doesn't exist
ERROR 1005 (HY000) at line 230: Can't create table `openemm`.`admin_tbl` (errno: 150 "Foreign key constraint is incorrectly formed")
ERROR 1146 (42S02) at line 263: Table 'openemm.admin_tbl' doesn't exist
ERROR 1005 (HY000) at line 298: Can't create table `openemm`.`auto_export_result_tbl` (errno: 150 "Foreign key constraint is incorrectly formed")
ERROR 1005 (HY000) at line 355: Can't create table `openemm`.`auto_import_mlist_bind_tbl` (errno: 150 "Foreign key constraint is incorrectly formed")
ERROR 1005 (HY000) at line 369: Can't create table `openemm`.`auto_import_result_tbl` (errno: 150 "Foreign key constraint is incorrectly formed")
ERROR 1005 (HY000) at line 386: Can't create table `openemm`.`auto_import_tbl` (errno: 150 "Foreign key constraint is incorrectly formed")
ERROR 1005 (HY000) at line 432: Can't create table `openemm`.`auto_import_used_files_tbl` (errno: 150 "Foreign key constraint is incorrectly formed")
ERROR 1005 (HY000) at line 545: Can't create table `openemm`.`birtreport_parameter_tbl` (errno: 150 "Foreign key constraint is incorrectly formed")
ERROR 1146 (42S02) at line 556: Table 'openemm.birtreport_parameter_tbl' doesn't exist
ERROR 1005 (HY000) at line 559: Can't create table `openemm`.`birtreport_recipient_tbl` (errno: 150 "Foreign key constraint is incorrectly formed")
ERROR 1146 (42S02) at line 566: Table 'openemm.birtreport_recipient_tbl' doesn't exist
ERROR 1005 (HY000) at line 731: Can't create table `openemm`.`company_permission_tbl` (errno: 150 "Foreign key constraint is incorrectly formed")
ERROR 1146 (42S02) at line 739: Table 'openemm.company_permission_tbl' doesn't exist
ERROR 1005 (HY000) at line 940: Can't create table `openemm`.`customer_1_binding_tbl` (errno: 150 "Foreign key constraint is incorrectly formed")
ERROR 1146 (42S02) at line 957: Table 'openemm.customer_1_binding_tbl' doesn't exist
ERROR 1146 (42S02) at line 958: Table 'openemm.customer_1_binding_tbl' doesn't exist
ERROR 1146 (42S02) at line 968: Table 'openemm.customer_1_binding_tbl' doesn't exist
ERROR 1146 (42S02) at line 1033: Table 'openemm.customer_1_binding_tbl' doesn't exist

JuergenB
Posts: 65
Joined: Thu Dec 05, 2019 9:03 pm

Re: Foreign key constraint is incorrectly formed

Post by JuergenB » Mon Feb 22, 2021 4:30 pm

I tried again with backup and restore from 20.04 into 20.10

i droped the live db 20.10 and created a empty one to /root/...

Next i tried to restore, this failed with an error, so i used the shell and this command.

[root@localhost bin]# mysql -u openemm -h localhost openemm -p'********' < /root/openemm.sql
ERROR 1050 (42S01) at line 3: Table 'access_data_tbl' already exists

Any idea how a backup / restore will work?

Has this been tested before?

maschoff
Site Admin
Posts: 2472
Joined: Thu Aug 03, 2006 10:20 am
Location: Munich, Germany
Contact:

Re: Foreign key constraint is incorrectly formed

Post by maschoff » Wed Feb 24, 2021 6:33 pm

The database schema of OpenEMM 20.10 is different from 20.04. To migrate a schema from 20.04 to 20.10 you should execute a code update with OMT (which applies changes to the schema) and start OpenEMM 20.10 once to initiate necessary data migration jobs.
OpenEMM Maintainer

JuergenB
Posts: 65
Joined: Thu Dec 05, 2019 9:03 pm

Re: Foreign key constraint is incorrectly formed

Post by JuergenB » Sat Feb 27, 2021 8:21 pm

Thanks,

i did a DB dump at 20.10 installation and modified the dump process in 20.04.
So i was able to dump a database from 20.04 release.

Next i dumped the 20.04 database and imported the 20.04 dump within mariadb @ the 20.10 system.
I started the rutime 20.10 and did a full reinstall of the backend modules, here all required sql scripts where executed on my imported db.

All is fine now.

Post Reply