Metabase Lost Track of Already used IDs

Bug reports
1

Hello,

It seems like my Metabase instance lost track of the already used identifier.

Leading to constant errors saying X (ID) is duplicate and violates foreign key.

It seems like Metabase uses a counter and of course after months of using the IDs are now very high numbers and the counter starting from 0 means data fails.

This leads to saving figures requiring multiple attempts (until ID is higher than last), logs not recording, among others.

Is there any way to restore the counter to the correct position?

Thanks in advance!

2

it seems that you either migrated Metabase to a new database or you did a reset of the sequences, is that correct?

3

Indeed, a migration was made.

4

Okay, I fixed it.

As Luiggi pointed out, the sequences were not correct.

In PostgreSQL, I made the following script:

DO $$
DECLARE
    rec record;
    seq_val bigint;
    max_val bigint;
    related_table_name text;
    related_column_name text;
    is_table_exist boolean;
BEGIN
    FOR rec IN SELECT sequencename AS sequence_name FROM pg_sequences WHERE schemaname = 'metabase'
    LOOP
        -- Derive table and column names based on the sequence name
        related_table_name := regexp_replace(rec.sequence_name, '_id_seq$', '');
        related_column_name := 'id';

        -- Check if the table exists
        SELECT EXISTS(SELECT 1 FROM information_schema.tables 
                      WHERE table_schema = 'metabase' 
                      AND table_name = related_table_name) INTO is_table_exist;

        IF is_table_exist THEN
            -- Get current value of the sequence
            EXECUTE 'SELECT last_value FROM metabase.' || quote_ident(rec.sequence_name) INTO seq_val;

            -- Get the max value from the related table and column
            EXECUTE format('SELECT COALESCE(MAX(%I), 0) FROM metabase.%I', related_column_name, related_table_name) INTO max_val;

            -- Compare and adjust if necessary
            IF seq_val < max_val THEN
                RAISE NOTICE 'Adjusting sequence %', rec.sequence_name;
                EXECUTE format('SELECT setval(''metabase.%I'', %L)', rec.sequence_name, max_val);
            ELSE
                RAISE NOTICE 'Sequence % is fine', rec.sequence_name;
            END IF;
        ELSE
            RAISE NOTICE 'Related table for sequence % does not exist', rec.sequence_name;
        END IF;
    END LOOP;
END $$;