OH DB Container Startup Failure with "Foreign key constraint is incorrectly formed" Error
Description
Environment
Attachments
blocks
Activity
Alessandro Domanico June 15, 2023 at 9:12 AM
Let’s say that the important missing part is the first block, converting interested (all) tables to InnoDB. The target table OH_USER was already InnoDB.
But having both tables MyISAM would not work because MyISAM does not support foreign keys and workarounds are needed in that case (see https://dev.mysql.com/doc/workbench/en/wb-tutorial-plugins-myisam-fk.html )
The disable/enable part is not mandatory but useful, I think it was needed when I tested over demo data because the create_all_demo.sql was creating tables in a different order than a native dump would do it (now fixed it also). So I guess it is important to keep it because the script could be executed on a non-empty DB some times, over a "dirty" installation (for instance when we migrate from a previous version of Open Hospital)
Niccolò Pasquetto June 15, 2023 at 8:05 AM
I did a few checks before proceeding with the tests.
I've tested the script and it works fine, but I've verified that it's not necessary to disable/re-enable foreing_key_checks (even removing the first 4 and last 4 lines, everything works fine).
It seems to me that the problem was related to the inconsistency of engines used between the two tables on which you wanted to set the foreign key.
Alessandro Domanico June 14, 2023 at 9:57 PM
Added new PR where I fix also demo data https://github.com/informatici/openhospital-core/pull/1018
Alessandro Domanico June 14, 2023 at 8:45 AM
please @Niccolò Pasquetto we need to close this issue for the release, could you have a test of the attached step_95? Thanks.
Alessandro Domanico June 5, 2023 at 8:47 PMEdited
found out that in the MySQL documentation, such error is caused when “Performing an ALTER TABLE operation on a table that has a foreign key relationship” if foreign_key_checks variable is enabled.
Therefore in the script step_95_missing_user_related_fk.sql we need to disable Foreign Key Checks first:
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRAsqlDITIONAL';and re-enable after the alters:
SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
Moreover I found many tables still ENGINE = MyISAM, that will just ignore new FKs:
mysql> SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'oh' AND engine = 'MyISAM';
+--------------------------+
| TABLE_NAME |
+--------------------------+
| oh_agetype |
| oh_groupmenu |
| oh_help |
| oh_log |
| oh_menuitem |
| oh_patienthistory |
| oh_patient_profile_photo |
| oh_pricesothers |
| oh_version |
+--------------------------+
9 rows in set (0.23 sec)So we need to add this at the beginning of the script
ALTER TABLE OH_AGETYPE ENGINE = INNODB, CONVERT TO CHARACTER SET utf8;
ALTER TABLE OH_GROUPMENU ENGINE = INNODB, CONVERT TO CHARACTER SET utf8;
ALTER TABLE OH_HELP ENGINE = INNODB, CONVERT TO CHARACTER SET utf8;
ALTER TABLE OH_LOG ENGINE = INNODB, CONVERT TO CHARACTER SET utf8;
ALTER TABLE OH_MENUITEM ENGINE = INNODB, CONVERT TO CHARACTER SET utf8;
ALTER TABLE OH_PATIENTHISTORY ENGINE = INNODB, CONVERT TO CHARACTER SET utf8;
ALTER TABLE OH_PATIENT_PROFILE_PHOTO ENGINE = INNODB, CONVERT TO CHARACTER SET utf8;
ALTER TABLE OH_PRICESOTHERS ENGINE = INNODB, CONVERT TO CHARACTER SET utf8;
ALTER TABLE OH_SUPPLIER ENGINE = INNODB, CONVERT TO CHARACTER SET utf8;
ALTER TABLE OH_VERSION ENGINE = INNODB, CONVERT TO CHARACTER SET utf8;
Lastly, I found that the script step_95_missing_user_related_fk.sql was adding FKs only for CREATED_BY fields not also for LAST_MODIFIED_BY ones… so the full script should be this one attached.
Please try it and update the Pull Request, thanks!
I encountered a problem while starting the container using Docker Compose. The container fails to start at step 95 with the following error: "Foreign key constraint is incorrectly formed."
Steps to Reproduce:
Set up the environment with Docker and Docker Compose.
Build and run the container using Docker Compose.
Observe the startup process and error.
Expected Behavior: The container should start successfully without any errors related to foreign key constraints.
Actual Behavior: The container fails to start at step 95 with the error message "Foreign key constraint is incorrectly formed."