Announcement

Collapse
No announcement yet.

Small Bug in mysql-database.sql

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Small Bug in mysql-database.sql

    When creating a new database on a MySQL/MariaDB cluster you might run into the following error:

    Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityCons traintViolationException: Cannot add or update a child row: a foreign key constraint fails (`mirth`.`PERSON_PASSWORD`, CONSTRAINT `PERSON_ID_PP_FK` FOREIGN KEY (`PERSON_ID`) REFERENCES `PERSON` (`ID`) ON DELETE CASCADE)

    This is because of:

    Code:
    INSERT IGNORE INTO PERSON (USERNAME, LOGGED_IN) VALUES('admin', FALSE);
    INSERT IGNORE INTO PERSON_PASSWORD (PERSON_ID, PASSWORD) VALUES(1, 'YzKZIAnbQ5m+3llggrZvNtf5fg69yX7pAplfYg0Dngn/fESH93OktQ==');
    Running MySQL/MariaDB in a cluster does not guarantee the autoinc ID of the first row in table PERSON to be 1 as expected by the second insert. E.g. in our 3 node cluster this ID is set to 3.
    To fix this just replace the second INSERT with:

    Code:
    INSERT IGNORE INTO PERSON_PASSWORD (PERSON_ID, PASSWORD)
      SELECT
        ID,
        'YzKZIAnbQ5m+3llggrZvNtf5fg69yX7pAplfYg0Dngn/fESH93OktQ==' AS PASSWORD
      FROM PERSON 
      WHERE username = 'admin';

  • #2
    Thank you this is exactly what I need to fix my new cluster! Can you please tell me where to make the necessary change?

    Thanks
    Steve

    Comment

    Working...
    X