Error in DB migration with PostgreSQL for Langflow v0.6

mkohei commented 3 months ago

Describe the bug

When using PostgreSQL as the database for Langflow version 0.6, an error occurs during the DB migration process. Despite the log suggesting to run langflow migration --fix, executing this command does not resolve the error.

Browser and Version

Google Chrome: Version 121.0.6167.160 (Official Build) (arm64)
Langflow: v0.6.6

To Reproduce

Steps to reproduce the behavior:

Construct the environment using the attached compose.yaml and Dockerfile.
Run the command docker compose up.
Observe the startup error log.
Attempt to fix with langflow migration --fix and note the error persists.

Additional context

Directory Structure:

.
├── .env
├── Dockerfile
├── compose.yaml
└── src
    └── db
        └── data

`compose.yaml`

services:
  langflow:
    build: .
    env_file:
      - .env
    ports:
      - "7860:7860"
    # command: sh -c "echo y | langflow migration --fix"
    depends_on:
      db:
        condition: service_healthy

  db:
    image: postgres:15
    ports:
      - "5432:5432"
    environment:
      POSTGRES_DB: langflow
      POSTGRES_USER: user
      POSTGRES_PASSWORD: password
    volumes:
      - ./src/db/data:/var/lib/postgresql/data
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U user -d postgres"]
      timeout: 20s
      interval: 10s
      retries: 5
      start_period: 30s

`Dockerfile`

FROM python:3.10-slim

RUN apt-get update && apt-get install gcc g++ git make -y && apt-get clean \
    && rm -rf /var/lib/apt/lists/*
RUN useradd -m -u 1000 user
USER user
ENV HOME=/home/user \
    PATH=/home/user/.local/bin:$PATH

WORKDIR $HOME/app

COPY --chown=user . $HOME/app

RUN pip install langflow>==0.5.0 -U --user
CMD ["python", "-m", "langflow", "run", "--host", "0.0.0.0", "--port", "7860"]

Error log at start-up

...
                             │                                    │             
                             │   29 │   │   logger.error(f"Error  │             
                             │   30 │   │   raise RuntimeError("E │             
                             │   31 │   try:                      │             
                             │ ❱ 32 │   │   database_service.run_ │             
                             │   33 │   except CommandError as ex │             
                             │   34 │   │   # if "overlaps with o │             
                             │      identified by"                │             
                             │   35 │   │   # are not in the exce │             
                             │                                    │             
                             │ /home/user/.local/lib/python3.10/s │             
                             │ ite-packages/langflow/services/dat │             
                             │ abase/service.py:159 in            │             
                             │ run_migrations                     │             
                             │                                    │             
                             │   156 │   │   except util.exc.Auto │             
                             │   157 │   │   │   logger.exception │             
                             │   158 │   │   │   if not fix:      │             
                             │ ❱ 159 │   │   │   │   raise Runtim │             
                             │   160 │   │   │   │   │   "Somethi │             
                             │       migration --fix`"            │             
                             │   161 │   │   │   │   ) from e     │             
                             │   162                              │             
                             ╰────────────────────────────────────╯             
                             RuntimeError: Something went wrong                 
                             running migrations. Please, run                    
                             `langflow migration --fix`                         
[2024-02-15 07:55:13 +0000] [23] [ERROR] Traceback (most recent call last):
  File "/home/user/.local/lib/python3.10/site-packages/langflow/services/database/service.py", line 155, in run_migrations
    command.check(alembic_cfg)
  File "/home/user/.local/lib/python3.10/site-packages/alembic/command.py", line 300, in check
    raise util.AutogenerateDiffsDetected(
alembic.util.exc.AutogenerateDiffsDetected: New upgrade operations detected: [('add_constraint', UniqueConstraint(Column('id', NullType(), table=<apikey>))), ('add_constraint', UniqueConstraint(Column('id', NullType(), table=<flow>))), ('add_constraint', UniqueConstraint(Column('id', NullType(), table=<user>)))]

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/home/user/.local/lib/python3.10/site-packages/starlette/routing.py", line 734, in lifespan
    async with self.lifespan_context(app) as maybe_state:
  File "/usr/local/lib/python3.10/contextlib.py", line 199, in __aenter__
    return await anext(self.gen)
  File "/home/user/.local/lib/python3.10/site-packages/langflow/main.py", line 20, in lifespan
    initialize_services()
  File "/home/user/.local/lib/python3.10/site-packages/langflow/services/utils.py", line 206, in initialize_services
    raise exc
  File "/home/user/.local/lib/python3.10/site-packages/langflow/services/utils.py", line 203, in initialize_services
    initialize_database(fix_migration=fix_migration)
  File "/home/user/.local/lib/python3.10/site-packages/langflow/services/database/utils.py", line 52, in initialize_database
    raise exc
  File "/home/user/.local/lib/python3.10/site-packages/langflow/services/database/utils.py", line 32, in initialize_database
    database_service.run_migrations(fix=fix_migration)
  File "/home/user/.local/lib/python3.10/site-packages/langflow/services/database/service.py", line 159, in run_migrations
    raise RuntimeError(
RuntimeError: Something went wrong running migrations. Please, run `langflow migration --fix`

[2024-02-15 07:55:13 +0000] [23] [ERROR] Application startup failed. Exiting.
[2024-02-15 07:55:13 +0000] [23] [INFO] Worker exiting (pid: 23)
[2024-02-15 07:55:14 +0000] [1] [ERROR] Worker (pid:23) exited with code 3
[2024-02-15 07:55:14 +0000] [1] [ERROR] Shutting down: Master
[2024-02-15 07:55:14 +0000] [1] [ERROR] Reason: Worker failed to boot.

Error log when running `langflow migration --fix`

...
│ │                       ismulti = False                                    │ │
│ │                      newraise = None                                     │ │
│ │                    parameters = {}                                       │ │
│ │                          self = <sqlalchemy.engine.base.Connection       │ │
│ │                                 object at 0xffff56fecdf0>                │ │
│ │                   should_wrap = True                                     │ │
│ │          sqlalchemy_exception = InternalError('(psycopg2.errors.InFaile… │ │
│ │                                 current transaction is aborted, commands │ │
│ │                                 ignored until end of transaction         │ │
│ │                                 block\n')                                │ │
│ │                     statement = "UPDATE alembic_version SET              │ │
│ │                                 version_num='0b8757876a7c' WHERE         │ │
│ │                                 alembic_version.vers"+24                 │ │
│ ╰──────────────────────────────────────────────────────────────────────────╯ │
│                                                                              │
│ /home/user/.local/lib/python3.10/site-packages/sqlalchemy/engine/base.py:196 │
│ 0 in _exec_single_context                                                    │
│                                                                              │
│   1957 │   │   │   │   │   │   │   evt_handled = True                        │
│   1958 │   │   │   │   │   │   │   break                                     │
│   1959 │   │   │   │   if not evt_handled:                                   │
│ ❱ 1960 │   │   │   │   │   self.dialect.do_execute(                          │
│   1961 │   │   │   │   │   │   cursor, str_statement, effective_parameters,  │
│   1962 │   │   │   │   │   )                                                 │
│   1963                                                                       │
│                                                                              │
│ ╭───────────────────────────────── locals ─────────────────────────────────╮ │
│ │              context = <sqlalchemy.dialects.postgresql.psycopg2.PGExecu… │ │
│ │                        object at 0xffff570c4490>                         │ │
│ │               cursor = <cursor object at 0xffff5722a980; closed: -1>     │ │
│ │              dialect = <sqlalchemy.dialects.postgresql.psycopg2.PGDiale… │ │
│ │                        object at 0xffff57077bb0>                         │ │
│ │ effective_parameters = {}                                                │ │
│ │          evt_handled = False                                             │ │
│ │           parameters = [{}]                                              │ │
│ │                 self = <sqlalchemy.engine.base.Connection object at      │ │
│ │                        0xffff56fecdf0>                                   │ │
│ │            statement = <sqlalchemy.dialects.postgresql.base.PGCompiler   │ │
│ │                        object at 0xffff570c41f0>                         │ │
│ │        str_statement = "UPDATE alembic_version SET                       │ │
│ │                        version_num='0b8757876a7c' WHERE                  │ │
│ │                        alembic_version.vers"+24                          │ │
│ ╰──────────────────────────────────────────────────────────────────────────╯ │
│                                                                              │
│ /home/user/.local/lib/python3.10/site-packages/sqlalchemy/engine/default.py: │
│ 924 in do_execute                                                            │
│                                                                              │
│    921 │   │   cursor.executemany(statement, parameters)                     │
│    922 │                                                                     │
│    923 │   def do_execute(self, cursor, statement, parameters, context=None) │
│ ❱  924 │   │   cursor.execute(statement, parameters)                         │
│    925 │                                                                     │
│    926 │   def do_execute_no_params(self, cursor, statement, context=None):  │
│    927 │   │   cursor.execute(statement)                                     │
│                                                                              │
│ ╭───────────────────────────────── locals ─────────────────────────────────╮ │
│ │    context = <sqlalchemy.dialects.postgresql.psycopg2.PGExecutionContex… │ │
│ │              object at 0xffff570c4490>                                   │ │
│ │     cursor = <cursor object at 0xffff5722a980; closed: -1>               │ │
│ │ parameters = {}                                                          │ │
│ │       self = <sqlalchemy.dialects.postgresql.psycopg2.PGDialect_psycopg2 │ │
│ │              object at 0xffff57077bb0>                                   │ │
│ │  statement = "UPDATE alembic_version SET version_num='0b8757876a7c'      │ │
│ │              WHERE alembic_version.vers"+24                              │ │
│ ╰──────────────────────────────────────────────────────────────────────────╯ │
╰──────────────────────────────────────────────────────────────────────────────╯
InternalError: (psycopg2.errors.InFailedSqlTransaction) current transaction is 
aborted, commands ignored until end of transaction block

[SQL: UPDATE alembic_version SET version_num='0b8757876a7c' WHERE 
alembic_version.version_num = 'b2fa308044b5']
(Background on this error at: https://sqlalche.me/e/20/2j85)

dosubot[bot] commented 3 months ago

🤖

Hey @mkohei! 🚀 Great to see you back. Hope you're ready for another round of troubleshooting adventure with Langflow!

Based on the information you've provided, it seems like you're encountering a similar issue to the ones reported in these two issues: Alembic DB migration error in Langflow v0.6.0 with PostgreSQL and The migration is failed when the database is postgresql.

The errors you're seeing, RuntimeError: Something went wrong running migrations and InternalError: (psycopg2.errors.InFailedSqlTransaction) current transaction is aborted, commands ignored until end of transaction block, are likely due to discrepancies between your current database schema and the schema defined in the migration scripts.

The langflow migration --fix command is designed to resolve such issues by downgrading and upgrading the database schema until it matches the schema defined in the migration scripts. However, if this command does not resolve the issue, it could be due to a more serious problem with the database schema or the migration scripts themselves.

Here are a few steps you could try:

Check the database schema to ensure it matches the schema defined in the migration scripts.
Check the migration scripts for any errors or inconsistencies.
If possible, try running the migrations on a fresh database to see if the issue persists.
Check the PostgreSQL logs for more detailed error messages that could help identify the issue.

In the Hotfix 0.6.2 commit that resolved the similar issue, four new columns were added to the 'flow' table, an index was created on the 'user_id' column, and a foreign key was created that references the 'id' column in the 'user' table. You can find more details about these changes in the commit.

If these steps do not resolve your issue, could you provide more specific information about your database schema, the migration scripts, and the exact SQL command that is failing? This information could help identify the root cause of the issue.

Sources

#### About Dosu

This response is meant to be useful and save you time. It is **not meant to be a precise solution**, but rather a starting point for your own research. Help me be more useful! Please leave a 👍 if this is helpful and 👎 if it is irrelevant. If you want to continue the conversation, start your reply with @dosu-bot.

mkohei commented 3 months ago

https://github.com/sqlalchemy/sqlalchemy/issues/10665

mkohei commented 3 months ago

ALTER TABLE apikey ADD CONSTRAINT uq_apikey_id UNIQUE(id);
ALTER TABLE flow ADD CONSTRAINT uq_flow_id UNIQUE(id);
ALTER TABLE user ADD CONSTRAINT uq_user_id UNIQUE(id);

ogabrielluiz commented 3 months ago

Hey @mkohei

I'm having trouble with that too lately.

I'm considering improving the migration fix script to back up the database first, then rebuilding it and reinserting the data.

artemus717 commented 3 months ago

have this issue when deploy a new langflow with empty postgresql database. langflow version 0.6.4

Alperemrehas commented 2 months ago

Hello everyone, is there any improvements regarding this problem? I am having same issue lately but didn't have a chance to overcome. Do you have any suggestions or work around?

ogabrielluiz commented 2 months ago

Hi everyone.

I have release a fix for this in the latest version and managed to get it working locally.

Unfortunately, it seems some cases are still not working so what I need from you all is the output of the following:

cd src/backend/langflow

LANGFLOW_DATABASE_URL=<your database url here> poetry run alembic check

Or, you could just get the output when you run langflow INCLUDING the part where it says:

 alembic.util.exc.AutogenerateDiffsDetected: New upgrade operations ...

Alperemrehas commented 2 months ago

Hi everyone.

I have release a fix for this in the latest version and managed to get it working locally.

Unfortunately, it seems some cases are still not working so what I need from you all is the output of the following:
cd src/backend/langflow

LANGFLOW_DATABASE_URL=<your database url here> poetry run alembic check
Or, you could just get the output when you run langflow INCLUDING the part where it says:
 alembic.util.exc.AutogenerateDiffsDetected: New upgrade operations ...

When I applied this

cd src/backend/langflow

LANGFLOW_DATABASE_URL=<your database url here> poetry run alembic check

The result is

024-03-10 15:31:46.072 | DEBUG | langflow.services.manager:_create_service:48 - Create service database_service 2024-03-10 15:31:46.072 | ERROR | env_py:run_migrations_online:92 - Error getting database engine: No factory registered for the service class 'DATABASE_SERVICE' INFO [alembic.runtime.migration] Context impl PostgresqlImpl. INFO [alembic.runtime.migration] Will assume transactional DDL. ERROR [alembic.util.messaging] Target database is not up to date. FAILED: Target database is not up to date.

What I did I created a .env and installed the node and poetry. I create .env file commented out the sqlite database URL and past my postgre db URL as LANGFLOW_DATABASE_URL = xxx . It didn't workout even I cleaned the target database.

ogabrielluiz commented 2 months ago

Ok. I think the command might not be correct. Since you know your way around the repo. Could you go to the file src/backend/langflow/alembic.ini and in the line where it writes sqlalchemy.url ... replace the SQLite url with your database url, then try running the command again, please. No need to pass the Langflow database url variable again.

Alperemrehas commented 2 months ago

Okay,I did update sqlalchemy.url ... in alembic.ini and then run the same command and here is the result: LANGFLOW_DATABASE_URL=postgresqlxxx poetry run alembic check 2024-03-10 18:19:11.217 | DEBUG | langflow.services.manager:_create_service:48 - Create service database_service 2024-03-10 18:19:11.217 | ERROR | env_py:run_migrations_online:70 - Error getting database engine: No factory registered for the service class 'DATABASE_SERVICE' INFO [alembic.runtime.migration] Context impl PostgresqlImpl. INFO [alembic.runtime.migration] Will assume transactional DDL. ERROR [alembic.util.messaging] Target database is not up to date. FAILED: Target database is not up to date. Note: I am using schema in the Postgres database(I think I should apply the schema into alembic/ .env dile), also should I update the .env database URL either?

ogabrielluiz commented 2 months ago

Good.

Could try running poetry run alembic history and poetry run alembic upgrade head , please?

ogabrielluiz commented 2 months ago

@Alperemrehas If you are ok with dumping the database, I can run the tests with it on my end to get to the bottom of this. You can send it to me on discord.

Alperemrehas commented 2 months ago

@Alperemrehas If you are ok with dumping the database, I can run the tests with it on my end to get to the bottom of this. You can send it to me on discord.

@ogabrielluiz Yes, I can try that and let you know.

Alperemrehas commented 1 month ago

@Alperemrehas If you are ok with dumping the database, I can run the tests with it on my end to get to the bottom of this. You can send it to me on discord.

@ogabrielluiz Yes, I can try that and let you know.

Hello everyone,

Unfortunantly, I was not able to solve this migration issue.

What I tried so far:

I pulled a postgre docker and run the docker to create the URL.

After that I have updated LANGFLOW_DATABASE_URL to postsql URL.

But when I run the poetry run alembic upgrade head command same error occured.

The interesting part actually whenever I tried to make backend and frontend after creating .env file it crashes eventhough keeping the database url same didn't make any difference.

ogabrielluiz commented 1 month ago

That is so weird. I'm going to get to the bottom of this and update it asap.

ogabrielluiz commented 1 month ago

I've updated it and pushed version 0.6.14

rycben commented 1 month ago

Had the same issue. Just installed and ran the pre-release version and it's now working.

python -m pip install langflow --pre --force-reinstall

fitzroy-c commented 1 month ago

Same issue, once i force-reinstalled the langflow from 0.6 to the pre-release version.

Alperemrehas commented 1 month ago

I've updated it and pushed version 0.6.14

Hello Gabriel,

Thank you for your ongoing support. I wanted to inform you that I've tested version 0.6.14 thoroughly, particularly with .env files containing updated LANGFLOW_DATABASE_URL, and it worked smoothly. However, I encountered an issue specifically when using PostgreSQL on Azure resources.

Azure requires the presence of an "@" symbol in PostgreSQL passwords. Unfortunately, during Alembic initialization, the use of a single "%40" to represent "@" failed. To address this, I've implemented a fix in version 0.6.14. I've extensively tested this fix with PostgreSQL passwords containing "@" symbols on Azure resources, and it's functioning impeccably.

I believe this fix will alleviate the problem for others encountering the same issue.

langflow-ai / langflow