🐛 Bug: Deployment Failure when migrating from 0.111.1 to 0.202.5 (Railway) - "Migration from 0108002 to 0111002 failed", Relation aready exists

[candideu]

Please confirm if bug report does NOT exists already ?

• I confirm there is no existing issue for this

Steps to reproduce ?

When adapting to upgrade Nocodb on Railway from 0.111.1 to 0.202.5, my deployment crashed.

Here are some error messages I've extracted from the Deployment logs:

Desired Behavior

After redeploying, deployment works as it did before.

Project Details

Deployment: Railway
Version: 0.202.5
Database: Postgres

Attachments

No response

[pranavxc]

I've few queries which will be helpful to debug more

1. Which version of Postgres are you using?
2. Did you modified database outside Nocodb using CLI or any other tool ?
3. Do you have access to postgres, if yes can you run the following query and share the result.

SELECT
      f.attname AS cn,
      i.relname as key_name,
      ix.indnatts, ix.indkey, f.attnum as seq_in_index,
      pg_catalog.format_type(f.atttypid,f.atttypmod) AS type,
      f.attnotnull as rqd,
      p.contype as cst,
      p.conname as cstn,
      CASE
          WHEN i.oid<>0 THEN true
          ELSE false
      END AS is_index,
      CASE
          WHEN p.contype = 'p' THEN true
          ELSE false
      END AS primarykey,
      CASE
          WHEN p.contype = 'u' THEN 0
      WHEN p.contype = 'p' THEN 0
          ELSE 1
      END AS non_unique_original,
      CASE
          WHEN p.contype = 'p' THEN true
          ELSE false
      END AS primarykey,
      CASE
          WHEN p.contype = 'u' THEN 0
      WHEN p.contype = 'p' THEN 0
          ELSE 1
      END AS non_unique,
      CASE
          WHEN f.atthasdef = 't' THEN pg_get_expr(d.adbin, d.adrelid)
      END AS default  FROM pg_attribute f
      JOIN pg_class c ON c.oid = f.attrelid
      JOIN pg_type t ON t.oid = f.atttypid
      LEFT JOIN pg_attrdef d ON d.adrelid = c.oid AND d.adnum = f.attnum
      LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
      LEFT JOIN pg_constraint p ON p.conrelid = c.oid AND f.attnum = ANY (p.conkey)
      LEFT JOIN pg_class AS g ON p.confrelid = g.oid
      LEFT JOIN pg_index AS ix ON f.attnum = ANY(ix.indkey) and c.oid = f.attrelid and c.oid = ix.indrelid
      LEFT JOIN pg_class AS i ON ix.indexrelid = i.oid
      WHERE
        c.relkind = 'r'::char
      AND n.nspname = 'public'
      AND c.relname = 'nc_goya__nc_m2m_rv6ot3ewwh'
      and i.oid<>0
      AND f.attnum > 0
      ORDER BY i.relname, f.attnum;

[pranavxc]

I ran the query in Railway and it said it was successful. I restarted the database and tried to redeploy the app.

Can you share the result of the query, which will help us to figure out the root cause.

@candideu : sorry I'd accidentally deleted your comment.

[candideu]

@pranavxc There weren't any particular results, it just said "successful" and "No data to display, table is empty.". Would the results show up, anywhere in the tables?

[pranavxc]

@candideu : We are using that query for getting indexes for a particular table but it seems not returning anything and which causing the issue. One more query, are you using a different schema other than default public schema and does the user have all permissions(postgres user) ?

[candideu]

One more query, are you using a different schema other than default public schema and does the user have all permissions(postgres user) ?

I'm not sure... Schema-wise, I'm using this Dockerfile, so I don't think it's different.

Permission-wise: Let me ask the Railway team.

[candideu]

Also, I ran the query again in Railway and it said, "No data to display, table is empty." I can try again withthe Postgres desktop app, if you think that would produce different results.

[candideu]

Permission-wise: Let me ask the Railway team.

Based on what I've been told by a Railway community member, the permissions should be the same as what is included in the dockerfile: https://github.com/railwayapp-templates/postgres-ssl/blob/main/Dockerfile.latest

FROM postgres:latest

# Install OpenSSL and sudo
RUN apt-get update && apt-get install -y openssl sudo

# Allow the postgres user to execute certain commands as root without a password
RUN echo "postgres ALL=(root) NOPASSWD: /usr/bin/mkdir, /bin/chown" > /etc/sudoers.d/postgres

# Add init scripts
COPY init-ssl.sh /docker-entrypoint-initdb.d/
COPY wrapper.sh /usr/local/bin/wrapper.sh

# Set permissions
RUN chmod +x /docker-entrypoint-initdb.d/init-ssl.sh
RUN chmod +x /usr/local/bin/wrapper.sh

ENTRYPOINT ["wrapper.sh"]
CMD ["postgres", "--port=5432"]

[pranavxc]

Can you run following query and share the result

SELECT
    tablename,
    indexname,
    indexdef
FROM
    pg_indexes
WHERE
    schemaname = 'public'
ORDER BY
    tablename,
    indexname;

[candideu]

Can you run following query and share the result

It produced 14 pages of results in Railway

Click here to view screenshots

[candideu]

Unfortunately, I'm still running into issues with this deployment. See: #7242