Database Schema Mismatch - Missing parent_message_id Column

[kkailaasa]

Self Checks

• [X] This is only for bug report, if you would like to ask a question, please head to Discussions.
• [X] I have searched for existing issues search for existing issues, including closed ones.
• [X] I confirm that I am using English to submit this report (我已阅读并同意 Language Policy).
• [X] [FOR CHINESE USERS] 请务必使用英文提交 Issue，否则会被关闭。谢谢！:）
• [X] Please do not modify this template :) and fill in all the required fields.

Dify version

0.9.1

Cloud or Self Hosted

Self Hosted (Docker)

Steps to reproduce

Trying to Upgrade from 8.3 to 9.1 did standard git checkout main, docker compose pull & docker compose up

✔️ Expected Behavior

No errors, all services running smoothly

❌ Actual Behavior

The Dify application is encountering errors due to a mismatch between the expected database schema and the actual schema. Specifically, the application is trying to access a parent_message_id column in the messages table, which does not exist in the current database schema.

Logs:

2024-10-01 16:30:56.730 UTC [560] ERROR:  column "parent_message_id" of relation "messages" does not exist at character 239
2024-10-01 16:30:56.730 UTC [560] STATEMENT:  INSERT INTO messages (app_id, model_provider, model_id, override_model_configs, conversation_id, inputs, query, message, message_tokens, message_unit_price, message_price_unit, answer, answer_tokens, answer_unit_price, answer_price_unit, parent_message_id, provider_response_latency, total_price, currency, error, message_metadata, invoke_from, from_source, from_end_user_id, from_account_id, workflow_run_id) VALUES (...) RETURNING messages.id, messages.status, messages.created_at, messages.updated_at, messages.agent_based

Failed Migration Attempt:
2024-10-01 16:29:55.238 UTC [298] ERROR:  duplicate key value violates unique constraint "message_pkey"
2024-10-01 16:29:55.238 UTC [298] STATEMENT:  UPDATE messages SET parent_message_id = uuid_nil() WHERE parent_message_id IS NULL

[dosubot[bot]]

To resolve the database schema mismatch error due to the missing parent_message_id column when upgrading Dify from version 8.3 to 9.1, you need to apply the migration script that adds this column to the messages table.

Here's what you need to do:

1. Apply the Migration Script:

   Run the following migration script to add the parent_message_id column:

   def upgrade():
       # Add the parent_message_id column to the messages table
       with op.batch_alter_table('messages', schema=None) as batch_op:
           batch_op.add_column(sa.Column('parent_message_id', models.types.StringUUID(), nullable=True))
   
       # Set parent_message_id for existing messages to uuid_nil()
       op.execute('UPDATE messages SET parent_message_id = uuid_nil() WHERE parent_message_id IS NULL')
   

2. Perform the Migration:

   • Navigate to the api directory:

     cd api
     

   • Ensure your environment is set up with Poetry and the necessary dependencies:

     poetry env use 3.10
     poetry install
     

   • Activate the environment and run the migration:

     poetry shell
     flask db upgrade
     

These steps should resolve the schema mismatch and allow your application to run smoothly ^[1][2].

To continue talking to Dosu, mention @dosu.

[kkailaasa]

in the standard upgrade instructions of 8.3, 9.0 & 9.1 there is not mention of running any migration script as a part of the upgrade process. Is there something I missing in the instructions? Have been using dify for around 8 months now and never faced a db problem like this...

[dosubot[bot]]

The standard upgrade instructions for Dify do include a step for database migrations, but it seems this might not have been clear or emphasized enough. When upgrading from version 8.3 to 9.1, you need to ensure that database migrations are applied. This is typically done by running the flask db upgrade command in the api directory if you are deploying from source code ^[1].

If you are using Docker, ensure that the MIGRATION_ENABLED configuration is set to 'true' in your Docker setup, which will automatically handle migrations during the application startup ^[2].

If these steps were missed or not clearly outlined in your upgrade process, it could lead to the database schema mismatch you are experiencing. Make sure to follow these steps to apply the necessary migrations.

To continue talking to Dosu, mention @dosu.

[kkailaasa]

In my docker compose, and in my env file, migrations is set to "true"

[Lilliansiyin]

Hi @kkailaasa,

Since you're using self hosted dify, can I check from you when using dify with local deployment: would there be any restrictions on the number of message via dify APIs & Web Sessions per day? Also for the history log. => Cause for dify cloud, there will be limitations (https://dify.ai/pricing)

Thanks!!!

[crazywoola]

If you are using self-hosted version, the migration will be triggered automatically.

[kkailaasa]

Thank you for the fix in the latest release