How to manually re-sync HI-LO identifiers in metabase deployed on SQLite

This article’s objective is to describe how to manually re-sync hi-lo identifiers of metabase objects stored in SQLite. It is useful in an unlikely event of losing synchronization of metadata object IDs stored in the metadata database and Querona server.

Warning

Direct modification of Querona’s metabase from Querona’s GUI is not supported and should be done only when instructed by support personnel.

Prerequisites

Stop Querona service. Spark service is not affected and stopping it is not required.

Manually re-sync the HI-LO identifiers in metabase on SQLite

1. Follow the instruction described in the article How to manually edit metabase deployed on SQLite

2. Save the following SQL query into a file sqlite_qmeta_hilo.sql reachable from your Querona server:

-- HI-LO diagnostics
--
select TabName
     , maxid
     , chunksize
     , round(ifNull(1.0*maxid/chunksize,0), 2) as NextHi_min_expected
     , hl.NextHi as NextHi_actual
     , case when round(ifNull(1.0*maxid/chunksize,0), 2) <= ifNull(hl.NextHi, 0) then 'OK' else '<-- WARN! Consider running sql: UPDATE HiLo SET NextHi=' || cast(round(ifNull(1.0*maxid/chunksize,0), 0) + 1 as INT) || ' WHERE TableName = ''' || TabName || '''' end as "isOK?"
from (
    select 'VirtualTable' as TabName,        (select max(tableid)      from VirtualTable)        as maxid,     50 as chunksize   union all
    select 'VirtualColumn',                  (select max(columnid)     from VirtualColumn),                   100                union all
    select 'VirtualForeignKey',              (select max(ForeignKeyId) from VirtualForeignKey),                50                union all
    select 'VirtualDatabaseJob' as TabName,  (select max(ObjectJobId)  from VirtualDatabaseJob),               25                union all
    select 'VirtualForeignKeyColumnMapping', (select max(ForeignKeyColumnMappingId) from VirtualForeignKeyColumnMapping), 50
) as exp
join HiLo as hl
  on exp.TabName = hl.TableName
order by "isOK?" asc, exp.TabName
;

3. In sqlite3 console run the following command (adjust the path if necessary):

sqlite> .read ./sqlite_qmeta_hilo.sql

The following example resultest appears:

TabName            maxid       chunksize   NextHi_min_expected  NextHi_actual  isOK?
-----------------  ----------  ----------  -------------------  -------------  ----------------------------------------------------------------------------------------------
VirtualForeignKey  102         50          2.04                 2              <-- WARN! Consider running sql: UPDATE HiLo SET NextHi=2 WHERE TableName = 'VirtualForeignKey'
VirtualColumn      2338        100         23.38                24             OK
VirtualDatabaseJo              25          0.0                  0              OK
VirtualForeignKey  103         50          2.06                 3              OK
VirtualTable       1568        50          31.36                36             OK

The isOK? column displays OK or a warning, if the NextHi_min_expected is lower than NextHi_actual.

4. If any value in the column isOK? contains WARN, for each of these metadata tables do:

5. Run SQL displayed in isOK? column, after the “Consider running sql:” statement, for example:

UPDATE HiLo SET NextHi=2 WHERE TableName = 'VirtualForeignKey';

6. In sqlite3 console run again the following command:

sqlite> .read sqlite_qmeta_hilo.sql

7. Verify that all values in colum isOK are equal OK

8. Start Querona service