Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-18409

Indexes were not replicated when updating foreign key

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Duplicate
    • Affects Version/s: 10.2.19
    • Fix Version/s: 10.2.23, 10.3.14, 10.4.4
    • Component/s: Galera
    • Labels:
      None
    • Environment:
      FreeBSD 11.2

      Description

      We are running galera in with 3 nodes(two master + 1 garb).
      We had an outage today when upgrading the schema.

      Here's the SQL that was run from our application

      ALTER TABLE WebContentVisibility DROP FOREIGN KEY WebContentVisibility_WebContent;
       
      ALTER TABLE WebContentVisibility ADD CONSTRAINT WebContentVisibility_WebContent FOREIGN KEY (WebContentId) REFERENCES WebContent (id) ON DELETE CASCADE ON UPDATE NO ACTION; 
      

      The result was as expected on one node, but all indexes on `WebContentVisibility` were lost on the other node.

      Here's the error logs of mysql while the upgrade was running:

      2019-01-29  9:45:41 50733614080 [Note] WSREP: EX-EX trx conflict for key (2,FLAT8)8632c156 4e7d3bb4: source: 2f0d48fe-1bd7-11e9-ad35-ab33104dd323 version: 4 local: 0 state: CERTIFYING flags: 1 conn_id: 496843 trx_id: 107690985050 seqnos (l: 6370504, g: 1410968285, s: 1410968251, d: 1410951808, ts: 14921712164934026) <---> source: 915c701c-13cd-11e9-9ddd-eef50d21aff0 version: 4 local: 1 state: COMMITTING flags: 1 conn_id: 476631 trx_id: 107691010520 seqnos (l: 6370497, g: 1410968278, s: 1410968251, d: 1410968251, ts: 1715428520444607)
      2019-01-29  9:45:54 50561541376 [Note] WSREP: EX-EX trx conflict for key (2,FLAT8)06e9fdd3 a18c4b56: source: 2f0d48fe-1bd7-11e9-ad35-ab33104dd323 version: 4 local: 0 state: CERTIFYING flags: 1 conn_id: 495235 trx_id: 107690985137 seqnos (l: 6370535, g: 1410968315, s: 1410968291, d: 1410951808, ts: 14921724947333703) <---> source: 915c701c-13cd-11e9-9ddd-eef50d21aff0 version: 4 local: 1 state: COMMITTED flags: 1 conn_id: 476634 trx_id: 107691010602 seqnos (l: 6370514, g: 1410968295, s: 1410968293, d: 1410968294, ts: 1715444686761896)
      2019-01-29  9:47:10 50733617920 [Note] WSREP: EX-EX trx conflict for key (2,FLAT8)06e9fdd3 a18c4b56: source: 2f0d48fe-1bd7-11e9-ad35-ab33104dd323 version: 4 local: 0 state: CERTIFYING flags: 1 conn_id: 486571 trx_id: 107690985319 seqnos (l: 6370616, g: 1410968396, s: 1410968357, d: 1410951936, ts: 14921800745555933) <---> source: 915c701c-13cd-11e9-9ddd-eef50d21aff0 version: 4 local: 1 state: COMMITTED flags: 1 conn_id: 476637 trx_id: 107691010828 seqnos (l: 6370609, g: 1410968389, s: 1410968381, d: 1410968381, ts: 1715523522616142)
      2019-01-29  9:47:10 50561545216 [Note] WSREP: EX-EX trx conflict for key (2,FLAT8)86942f98 c6d60e86: source: 2f0d48fe-1bd7-11e9-ad35-ab33104dd323 version: 4 local: 0 state: CERTIFYING flags: 1 conn_id: 496839 trx_id: 107690985325 seqnos (l: 6370619, g: 1410968399, s: 1410968357, d: 1410951936, ts: 14921800808462213) <---> source: 915c701c-13cd-11e9-9ddd-eef50d21aff0 version: 4 local: 1 state: COMMITTED flags: 1 conn_id: 476668 trx_id: 107691010759 seqnos (l: 6370582, g: 1410968362, s: 1410968354, d: 1410968349, ts: 1715498900811637)
      

        Attachments

          Activity

            People

            • Assignee:
              jplindst Jan Lindström
              Reporter:
              laocius TAO ZHOU
            • Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: