SQL patch for candID refactoring failing on RB

[cmadjar]

When sourcing the SQL patch 2025_02_05_change_candid_fk_to_id.sql, got a lot of errors. See below for the log

mysql> source /var/www/loris/SQL/New_patches/2025_02_05_change_candid_fk_to_id.sql;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'constraint CNV_ibfk_3' at line 1
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`cecile_raisin_bread`.`CNV`, CONSTRAINT `CNV_ibfk_3` FOREIGN KEY (`CandID`) REFERENCES `candidate` (`CandID`))
ERROR 1846 (0A000): ALGORITHM=COPY is not supported. Reason: Columns participating in a foreign key are renamed. Try ALGORITHM=INPLACE.
ERROR 1072 (42000): Key column 'CandidateID' doesn't exist in table
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CONSTRAINT `FK_candidate_consent_rel_CandidateID`' at line 1
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`cecile_raisin_bread`.`candidate_consent_rel`, CONSTRAINT `FK_candidate_consent_rel_CandidateID` FOREIGN KEY (`CandidateID`) REFERENCES `candidate` (`CandID`))
ERROR 1832 (HY000): Cannot change column 'CandidateID': used in a foreign key constraint 'FK_candidate_consent_rel_CandidateID'
ERROR 1215 (HY000): Cannot add foreign key constraint
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CONSTRAINT `fk_participant_status_3`' at line 1
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

ERROR 1846 (0A000): ALGORITHM=COPY is not supported. Reason: Columns participating in a foreign key are renamed. Try ALGORITHM=INPLACE.
ERROR 1072 (42000): Key column 'CandidateID' doesn't exist in table
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CONSTRAINT `dataquery_run_results_ibfk_1`' at line 1
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

ERROR 1846 (0A000): ALGORITHM=COPY is not supported. Reason: Columns participating in a foreign key are renamed. Try ALGORITHM=INPLACE.
ERROR 1072 (42000): Key column 'CandidateID' doesn't exist in table
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CONSTRAINT `fk_issues_3`' at line 1
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`cecile_raisin_bread`.`issues`, CONSTRAINT `fk_issues_3` FOREIGN KEY (`candID`) REFERENCES `candidate` (`CandID`))
ERROR 1846 (0A000): ALGORITHM=COPY is not supported. Reason: Columns participating in a foreign key are renamed. Try ALGORITHM=INPLACE.
ERROR 1072 (42000): Key column 'CandidateID' doesn't exist in table
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CONSTRAINT `fk_session_1`' at line 1
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`cecile_raisin_bread`.`session`, CONSTRAINT `FK_session_1` FOREIGN KEY (`CandID`) REFERENCES `candidate` (`CandID`))
ERROR 1846 (0A000): ALGORITHM=COPY is not supported. Reason: Columns participating in a foreign key are renamed. Try ALGORITHM=INPLACE.
ERROR 1072 (42000): Key column 'CandidateID' doesn't exist in table
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CONSTRAINT `genomic_candidate_files_rel_ibfk_1`' at line 1
Query OK, 0 rows affected (0.01 sec)
Rows matched: 0  Changed: 0  Warnings: 0

ERROR 1846 (0A000): ALGORITHM=COPY is not supported. Reason: Columns participating in a foreign key are renamed. Try ALGORITHM=INPLACE.
ERROR 1072 (42000): Key column 'CandidateID' doesn't exist in table
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CONSTRAINT `FK_mri_scanner_1`' at line 1
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`cecile_raisin_bread`.`mri_scanner`, CONSTRAINT `FK_mri_scanner_1` FOREIGN KEY (`CandID`) REFERENCES `candidate` (`CandID`))
ERROR 1846 (0A000): ALGORITHM=COPY is not supported. Reason: Columns participating in a foreign key are renamed. Try ALGORITHM=INPLACE.
ERROR 1072 (42000): Key column 'CandidateID' doesn't exist in table
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CONSTRAINT `genomic_sample_candidate_rel_ibfk_1`' at line 1
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`cecile_raisin_bread`.`genomic_sample_candidate_rel`, CONSTRAINT `genomic_sample_candidate_rel_ibfk_1` FOREIGN KEY (`CandID`) REFERENCES `candidate` (`CandID`))
ERROR 1846 (0A000): ALGORITHM=COPY is not supported. Reason: Columns participating in a foreign key are renamed. Try ALGORITHM=INPLACE.
ERROR 1072 (42000): Key column 'CandidateID' doesn't exist in table
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CONSTRAINT `fk_SNP_candidate_rel_2`' at line 1
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`cecile_raisin_bread`.`SNP_candidate_rel`, CONSTRAINT `fk_SNP_candidate_rel_2` FOREIGN KEY (`CandID`) REFERENCES `candidate` (`CandID`) ON DELETE NO ACTION ON UPDATE NO ACTION)
ERROR 1846 (0A000): ALGORITHM=COPY is not supported. Reason: Columns participating in a foreign key are renamed. Try ALGORITHM=INPLACE.
ERROR 1072 (42000): Key column 'CandidateID' doesn't exist in table
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CONSTRAINT `FK_parameter_candidate_2`' at line 1
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`cecile_raisin_bread`.`parameter_candidate`, CONSTRAINT `FK_parameter_candidate_2` FOREIGN KEY (`CandID`) REFERENCES `candidate` (`CandID`))
ERROR 1846 (0A000): ALGORITHM=COPY is not supported. Reason: Columns participating in a foreign key are renamed. Try ALGORITHM=INPLACE.
ERROR 1072 (42000): Key column 'CandidateID' doesn't exist in table
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CONSTRAINT `PK_candidate_diagnosis_evolution_rel`' at line 1
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CONSTRAINT `FK_candidate_diagnosis_evolution_rel_CandID`' at line 1
Query OK, 0 rows affected (0.01 sec)
Rows matched: 0  Changed: 0  Warnings: 0

ERROR 1846 (0A000): ALGORITHM=COPY is not supported. Reason: Columns participating in a foreign key are renamed. Try ALGORITHM=INPLACE.
ERROR 1072 (42000): Key column 'CandidateID' doesn't exist in table
ERROR 1072 (42000): Key column 'CandidateID' doesn't exist in table
Query OK, 50 rows affected (0.01 sec)
Rows matched: 50  Changed: 50  Warnings: 0

Query OK, 50 rows affected (0.04 sec)
Records: 50  Duplicates: 0  Warnings: 0

Query OK, 50 rows affected (0.01 sec)
Records: 50  Duplicates: 0  Warnings: 0

Query OK, 109 rows affected (0.00 sec)
Rows matched: 109  Changed: 109  Warnings: 0

Query OK, 109 rows affected (0.02 sec)
Records: 109  Duplicates: 0  Warnings: 0

Query OK, 109 rows affected (0.01 sec)
Records: 109  Duplicates: 0  Warnings: 0

Query OK, 198 rows affected (0.01 sec)
Rows matched: 198  Changed: 198  Warnings: 0

Query OK, 198 rows affected (0.01 sec)
Records: 198  Duplicates: 0  Warnings: 0

Query OK, 198 rows affected (0.02 sec)
Records: 198  Duplicates: 0  Warnings: 0

Query OK, 31 rows affected (0.00 sec)
Rows matched: 31  Changed: 31  Warnings: 0

Query OK, 31 rows affected (0.01 sec)
Records: 31  Duplicates: 0  Warnings: 0

Query OK, 31 rows affected (0.01 sec)
Records: 31  Duplicates: 0  Warnings: 0

Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0

Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0

Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

ERROR 1553 (HY000): Cannot drop index 'PRIMARY': needed in a foreign key constraint

[jeffersoncasimir]

Appears to only occur on MySQL and not MariaDB