Issue with diesel migration, schema version mismatch for table

General
1

Hi,

I’m trying to use diesel with yugabyte. Everything seems to works well so far but I got one issue (that I could fix but in a hacky way).

I made a github repo to reproduce my issue.

The issue happen when I try to do an ALTER TABLE followed by a UPDATE related to this table.

Here is and example of sql

-- init.sql
CREATE TABLE mytable (
    id SERIAL PRIMARY KEY,
    uuid character varying(44) UNIQUE
);

-- change_uuid.sql
ALTER TABLE mytable ALTER COLUMN uuid TYPE VARCHAR(44);

-- update_uuid.sql
UPDATE mytable
    SET uuid = CONCAT(
        SUBSTRING(uuid, 0, 8), '-',
        SUBSTRING(uuid, 8, 8), '-',
        SUBSTRING(uuid, 16, 8), '-',
        SUBSTRING(uuid, 24, 8), '-',
        SUBSTRING(uuid, 32, 8)
    )
    WHERE uuid IS NOT NULL;

If I execute them by hand everything seems fine. When I use diesel something goes wrong and I got the message

❯ diesel migration run --database-url 'postgres://user:password@yb-tserver.local:5433/db1'
Running migration 2024-09-10-190931_init
Running migration 2024-09-10-191017_change_uuid
Running migration 2024-09-11-120055_update_uuid
Failed to run 2024-09-11-120055_update_uuid with: schema version mismatch for table 0000460a00003000800000000000400a: expected 3, got 2

I need to retry another time to finish the migration.

I am wondering what causing this issue and if I need to configure something in my yugabyte cluster (running in k8s with the default helm configuration)?

2

Hi @czotti

Please see https://support.yugabyte.com/hc/en-us/articles/4406287763597-How-to-troubleshoot-Schema-or-Catalog-version-mismatch-database-errors

It’s probably because you’re trying to run a DDL + DML inside one transaction. Can you make them run in separate transactions?

3

Hi @czotti, I’ve opened [YSQL] schema version mismatch with non-autocommit DML, after some no-op DDL · Issue #23882 · yugabyte/yugabyte-db · GitHub, and colleagues are looking at it. It’s unrelated to your configuration, but probably a regression in some session cache invalidation.
Here the workaround is easy because VARCHAR(44) and character varying(44) are synonyms so removing change_uuid.sql should be ok :slightly_smiling_face:

4

Well I did found a work around but I’m not sure. After digging in diesel I found the use of migrations/migrations_name/metadata.toml and I put

# migrations/migrations_name/metadata.toml
run_in_transaction = false

for the migration update_uuid and it seems to work without issues.

@dorian_yugabyte it’s the opposite of what you said about about the transaction and I’m not sure why it works.

1 Like
5

@FranckPachot thanks for the report but the issue is not from the change_uuid.sql it appear in the update_uuid.sql.
It’s a typo on my end the first should be a VARCHAR(40) then modify it to `VARCHAR(44).

I did remove the UPDATE section of my migration to make it works as intended (which is not optimal). The solution on my previous post works too.

I updated the github with the correct sql files to reproduce my issue with diesel.

6

Thanks. It looks like a regression in cache invalidation. Yes, run_in_transaction = false seems like a good workaround.

7

Do you know which version I should take to avoid this regression?
This is to test our migrations correctly and wait until this regressions is fixed

8

Quick update: the fix will be in the next release 2024.1.3 (probably in one week)

1 Like
9

I have seen the issue update yesterday.
Again thanks for your prompt fixes and answers.

10

I tested yesterday the 2024.1.3 version and everything is fine now. :clap: yugabyte team for the awesome responsiveness.

1 Like