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
11

I had a situation over the last two days that sounds very similar to this and updating to the latest image (or thereabouts: 2024.2.0.0-b145) did not resolve it.

However, splitting the migration in two did. The original migration looked like this:

CREATE TYPE EnumLevel as ENUM ('one', 'two', 'three', 'four');

ALTER TABLE Data ADD COLUMN enum_column EnumLevel;
UPDATE Bytes_Data SET classification = 'classification_not_set';
ALTER TABLE Data ALTER COLUMN enum_column SET NOT NULL;

If it was all one migration it would fail for various reasons during the migration sometimes because the column existed (which it didn’t) or because the column had NULL values (which shouldn’t matter during the migration).

Splitting the CREATE TYPE out into a separate migration worked. Also, this worked without the split.

CREATE TYPE EnumLevel as ENUM ('one', 'two', 'three', 'four');
ALTER TABLE Data ADD COLUMN enum_column EnumLevel DEFAULT 'one';
ALTER TABLE Data ALTER COLUMN enum_column SET NOT NULL;
ALTER TABLE Data ALTER COLUMN enum_column DROP DEFAULT;

Is this expected behavior? To double check that it worked in general I can execute the first set of commands all together outside of the migration just fine (I suspect that’s the transaction interferring?).

Anyway, some input would be appreciate. If I’m misunderstanding something or did something wrong I’m also happy to hear about that.

Thanks!