Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

CandidateID migration patch errors #9561

Open
maximemulder opened this issue Feb 13, 2025 · 4 comments
Open

CandidateID migration patch errors #9561

maximemulder opened this issue Feb 13, 2025 · 4 comments
Assignees
@kongtiaowang
Labels
Category: Bug PR or issue that aims to report or fix a bug Language: SQL PR or issue that update SQL code Priority: High PR or issue should be prioritised over others for review and testing
Milestone
27.0.0

Comments

@maximemulder
Copy link
Contributor

maximemulder commented Feb 13, 2025 •
edited
Loading

While applying the SQL patch of #9556 to my recent Raisinbread installation, I got the following errors:

MariaDB [LORIS]> SOURCE 2025_02_05_change_candid_fk_to_id.sql;
Query OK, 0 rows affected (0.029 sec)
Records: 0  Duplicates: 0  Warnings: 0

Query OK, 4 rows affected (0.003 sec)
Rows matched: 4  Changed: 4  Warnings: 0

Query OK, 4 rows affected (0.030 sec)                      
Records: 4  Duplicates: 0  Warnings: 0

Query OK, 4 rows affected (0.029 sec)                      
Records: 4  Duplicates: 0  Warnings: 0

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Query OK, 14 rows affected (0.051 sec)             
Records: 14  Duplicates: 0  Warnings: 0

Query OK, 14 rows affected (0.045 sec)             
Records: 14  Duplicates: 0  Warnings: 0

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

Query OK, 1136 rows affected (0.015 sec)
Rows matched: 1136  Changed: 1136  Warnings: 0

Query OK, 1136 rows affected (0.061 sec)                   
Records: 1136  Duplicates: 0  Warnings: 0

Query OK, 1136 rows affected (0.064 sec)                   
Records: 1136  Duplicates: 0  Warnings: 0

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

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

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

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

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

Query OK, 14 rows affected (0.003 sec)
Rows matched: 14  Changed: 14  Warnings: 0

Query OK, 14 rows affected (0.022 sec)                     
Records: 14  Duplicates: 0  Warnings: 0

Query OK, 14 rows affected (0.026 sec)                     
Records: 14  Duplicates: 0  Warnings: 0

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

Query OK, 4 rows affected (0.003 sec)
Rows matched: 4  Changed: 4  Warnings: 0

Query OK, 4 rows affected (0.021 sec)                      
Records: 4  Duplicates: 0  Warnings: 0

Query OK, 4 rows affected (0.019 sec)                      
Records: 4  Duplicates: 0  Warnings: 0

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

Query OK, 20295 rows affected (0.130 sec)
Rows matched: 20295  Changed: 20295  Warnings: 0

Query OK, 20295 rows affected (0.128 sec)                  
Records: 20295  Duplicates: 0  Warnings: 0

Query OK, 20295 rows affected (0.194 sec)                  
Records: 20295  Duplicates: 0  Warnings: 0

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

Query OK, 4 rows affected (0.002 sec)
Rows matched: 4  Changed: 4  Warnings: 0

Query OK, 4 rows affected (0.018 sec)                      
Records: 4  Duplicates: 0  Warnings: 0

Query OK, 4 rows affected (0.017 sec)                      
Records: 4  Duplicates: 0  Warnings: 0

--------------
ALTER TABLE candidate_diagnosis_evolution_rel DROP CONSTRAINT `PK_candidate_diagnosis_evolution_rel`
--------------

ERROR 1091 (42000) at line 57 in file: '2025_02_05_change_candid_fk_to_id.sql': Can't DROP CONSTRAINT `PK_candidate_diagnosis_evolution_rel`; check that it exists
Query OK, 0 rows affected (0.006 sec)
Records: 0  Duplicates: 0  Warnings: 0

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

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

--------------
ALTER TABLE candidate_diagnosis_evolution_rel ADD CONSTRAINT PK_candidate_diagnosis_evolution_rel PRIMARY KEY (CandidateID, DxEvolutionID)
--------------

ERROR 1068 (42000) at line 61 in file: '2025_02_05_change_candid_fk_to_id.sql': Multiple primary key defined
Query OK, 0 rows affected (0.013 sec)              
Records: 0  Duplicates: 0  Warnings: 0

Query OK, 50 rows affected (0.003 sec)
Rows matched: 50  Changed: 50  Warnings: 0

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

MariaDB version:

(loris-mri-python) lorisadmin@loris:~$ mariadb --version
mariadb from 11.4.4-MariaDB, client 15.2 for debian-linux-gnu (x86_64) using  EditLine wrapper
@maximemulder maximemulder added Category: Bug PR or issue that aims to report or fix a bug Language: SQL PR or issue that update SQL code Priority: High PR or issue should be prioritised over others for review and testing labels Feb 13, 2025
@maximemulder maximemulder added this to the 27.0.0 milestone Feb 13, 2025
@maximemulder maximemulder changed the title CandidateID migration bugs CandidateID migration patch errors Feb 13, 2025
@driusan
Copy link
Collaborator

driusan commented Feb 13, 2025

@kongtiaowang can you check if these are errors with the patch or if the DB that it was run on just wasn't up to date?

@maximemulder
Copy link
Contributor Author

maximemulder commented Feb 13, 2025 •
edited
Loading

@driusan I don't think my database was out of date as I created it from main in January and applied both patches marked as 2025 in order (the second patch being this one). I should probably have given this info MB.

@jeffersoncasimir
Copy link
Contributor

jeffersoncasimir commented Feb 14, 2025 •
edited
Loading

This is odd. Can you query for what column candidate_diagnosis_evolution_rel is referring to as PK?

@maximemulder
Copy link
Contributor Author

maximemulder commented Feb 14, 2025 •
edited
Loading

After running the migration:

MariaDB [LORIS]> SHOW KEYS FROM candidate_diagnosis_evolution_rel WHERE Key_name = 'PRIMARY';
+-----------------------------------+------------+----------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| Table                             | Non_unique | Key_name | Seq_in_index | Column_name   | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Ignored |
+-----------------------------------+------------+----------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| candidate_diagnosis_evolution_rel |          0 | PRIMARY  |            1 | CandidateID   | A         |           0 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| candidate_diagnosis_evolution_rel |          0 | PRIMARY  |            2 | DxEvolutionID | A         |           0 |     NULL | NULL   |      | BTREE      |         |               | NO      |
+-----------------------------------+------------+----------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
2 rows in set (0.001 sec)

If needed, someone else could check their keys, run the patch on their dev VM, and see if they get the same error.

I think the problem may be that you ADD a new primary key for this table in the patch, but you forgot to DROP the old one beforehand (which you do for the candidate table).

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@kongtiaowang kongtiaowang

Labels
Category: Bug PR or issue that aims to report or fix a bug Language: SQL PR or issue that update SQL code Priority: High PR or issue should be prioritised over others for review and testing
Projects
None yet
Milestone
27.0.0
Development

No branches or pull requests
4 participants
@driusan @kongtiaowang @jeffersoncasimir @maximemulder