Uploaded image for project: '[Read Only] - Hippo Repository'
  1. [Read Only] - Hippo Repository
  2. REPO-1942

[Backport 4.2.7] Created database table for LockManager most likely misses unique index in case of MySQL 5.6



    • 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: 4.2.7
    • Component/s: None
    • Labels:


      The LockManager implementation creates on startup a new table called


      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)


          Issue Links



              Unassigned Unassigned
              aschrijvers Ard Schrijvers
              0 Vote for this issue
              1 Start watching this issue