Uploaded image for project: 'Hippo Repository'
  1. Hippo Repository
  2. REPO-1939

Created database table for LockManager most likely misses unique index in case of MySQL 5.6

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Blocker
    • Resolution: Fixed
    • Affects Version/s: 3.2.9, 5.1.0, 5.0.3, 4.2.4, 4.2.5, 5.0.4, 5.1.1, 3.2.10, 5.0.5, 4.2.6
    • Fix Version/s: 5.2.0
    • Labels:
      None
    • Similar issues:
    • Processed by team:
      Turing

      Description

      The LockManager implementation creates on startup a new table called

       ${prefix}HIPPO_LOCK
      

      On this table, its set a UNIQUE index for the lockKey. It does this in a separate statement, after the table has been created.

      Unfortunately, in MySQL 5.6, by default, the UNIQUE index creation fails because the lockKey is defined as VARCHAR(255) which in 5.6 is too long. In MySQL 5.7 it might be too long, depending on a specific MySQL setting and the char encoding, however, by default, it succeeds in 5.7.

      There is a runtime script that fixes the database table. This script is as follows:

      -------------------------------- MySQL Script -------------------------------
      set @prefix = 'REPOSITORY_';
      set @x := ( select 1
                 from information_schema.statistics
                 where table_name = CONCAT(@prefix, 'HIPPO_LOCK')
                 and index_name = CONCAT(@prefix, 'HIPPO_LOCK_idx_1')
                 and table_schema = database()
               );
      set @sql := if( @x = 1, 'select ''Index exists.''' , CONCAT('ALTER TABLE ', @prefix, 'HIPPO_LOCK CHANGE lockKey lockKey varchar(190)'));
      PREPARE stmt FROM @sql;
      EXECUTE stmt;
      set @sql := if( @x = 1, 'select ''Index exists.''', CONCAT('TRUNCATE ', @prefix, 'HIPPO_LOCK'));
      PREPARE stmt FROM @sql;
      EXECUTE stmt;
      set @sql := if( @x = 1, 'select ''Index exists.''' , CONCAT('ALTER TABLE ', @prefix, 'HIPPO_LOCK ADD UNIQUE INDEX ', @prefix,'HIPPO_LOCK_idx_1 (lockKey)'));
      PREPARE stmt FROM @sql;
      EXECUTE stmt;
      -------------------------------- MySQL Script -------------------------------

      After the above script, the running environment is fixed (the cluster nodes might log some errors wrt locking just after the script but that is fine).

      In the code, we should reduce the VARCHAR(255) length to VARCHAR(190) to avoid these problems.

      Also, the code will get a change that if on startup the ${prefix}HIPPO_LOCK exists, we check whether the table is correct. Whether the table has lockKey as primary key or as unique index is irrelevant : The code should ONLY fix the table IF there is no index for 'lockKey' (regardless whether it is a primary index or unique index...namely infra can have manually fixed the tables by setting primary keys instead of unique which is fine as well)

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                Unassigned
                Reporter:
                aschrijvers Ard Schrijvers
              • Votes:
                0 Vote for this issue
                Watchers:
                3 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: