You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@devlake.apache.org by "jcg-juan (via GitHub)" <gi...@apache.org> on 2025/09/04 13:12:14 UTC

[I] [Bug][Devlake DB Migration] DB Migration failure on PostgreSQL due to MODIFY syntax in migration 20250320_modify_file_meta.go [incubator-devlake]

jcg-juan opened a new issue, #8564:
URL: https://github.com/apache/incubator-devlake/issues/8564

   ### Search before asking
   
   - [x] I had searched in the [issues](https://github.com/apache/incubator-devlake/issues?q=is%3Aissue) and found no similar issues.
   
   
   ### What happened
   
   Starting from v1.0.2 (and still present in latest), DevLake fails to start when using PostgreSQL because the migration plugins/q_dev/models/migrationscripts/20250320_modify_file_meta.go runs a MySQL-specific ALTER TABLE ... MODIFY statement.
   
   This causes the DB migration process to panic immediately on startup, blocking usage of the app.
   
   ### What do you expect to happen
   
   Expected Behavior:
   DB migrations should succeed on PostgreSQL (as supported in prior versions like v1.0.2-beta8).
   
   ### How to reproduce
   
   Steps to Reproduce:
   1. Use PostgreSQL as the DevLake database.
   3. Run DevLake v1.0.2 or later (ensure image version is v1.0.2).
   5. Observe the startup migration step.
   
   Expected Behaviour:
   DB migrations should succeed on PostgreSQL (as supported in prior versions like v1.0.2-beta8).
   
   Actual Behaviour (Error Log):
   ```
   ERROR: syntax error at or near "MODIFY" (SQLSTATE 42601)
   failed to modify processed_time column
   panic recovered:
   github.com/apache/incubator-devlake/plugins/q_dev/models/migrationscripts.(*modifyFileMetaTable).Up
       /app/plugins/q_dev/models/migrationscripts/20250320_modify_file_meta.go:38
   ```
   
   
   ### Anything else
   
   Root Cause:
   The migration contains this SQL:
   `sql := "ALTER TABLE _tool_q_dev_s3_file_meta MODIFY processed_time DATETIME NULL"`
   - MODIFY is MySQL/MariaDB-specific and not valid PostgreSQL syntax.
   - DATETIME is also a MySQL type; PostgreSQL uses TIMESTAMP.
   This breaks PostgreSQL migrations and blocks DevLake startup.
   
   Environment:
   
   - DevLake: v1.0.2+
   - Database: PostgreSQL (fails)
   - Last known working: v1.0.2-beta8
   
   Impact:
   Blocks DB migration process on PostgreSQL → prevents DevLake from starting.
   
   ### Version
   
   v1.0.2+
   
   ### Are you willing to submit PR?
   
   - [ ] Yes I am willing to submit a PR!
   
   ### Code of Conduct
   
   - [x] I agree to follow this project's [Code of Conduct](https://www.apache.org/foundation/policies/conduct)
   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: dev-unsubscribe@devlake.apache.org.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

Re: [I] [Bug][Devlake DB Migration] DB Migration failure on PostgreSQL due to MODIFY syntax in migration 20250320_modify_file_meta.go [incubator-devlake]

Posted by "GoSimplicity (via GitHub)" <gi...@apache.org>.
GoSimplicity commented on issue #8564:
URL: https://github.com/apache/incubator-devlake/issues/8564#issuecomment-3256625051

   > ### Search before asking
   > * [x]  I had searched in the [issues](https://github.com/apache/incubator-devlake/issues?q=is%3Aissue) and found no similar issues.
   > 
   > ### What happened
   > Starting from v1.0.2 (and still present in latest), DevLake fails to start when using PostgreSQL because the migration plugins/q_dev/models/migrationscripts/20250320_modify_file_meta.go runs a MySQL-specific ALTER TABLE ... MODIFY statement.
   > 
   > This causes the DB migration process to panic immediately on startup, blocking usage of the app.
   > 
   > ### What do you expect to happen
   > Expected Behavior: DB migrations should succeed on PostgreSQL (as supported in prior versions like v1.0.2-beta8).
   > 
   > ### How to reproduce
   > Steps to Reproduce:
   > 
   > 1. Use PostgreSQL as the DevLake database.
   > 2. Run DevLake v1.0.2 or later (ensure image version is v1.0.2).
   > 3. Observe the startup migration step.
   > 
   > Expected Behaviour: DB migrations should succeed on PostgreSQL (as supported in prior versions like v1.0.2-beta8).
   > 
   > Actual Behaviour (Error Log):
   > 
   > ```
   > ERROR: syntax error at or near "MODIFY" (SQLSTATE 42601)
   > failed to modify processed_time column
   > panic recovered:
   > github.com/apache/incubator-devlake/plugins/q_dev/models/migrationscripts.(*modifyFileMetaTable).Up
   >     /app/plugins/q_dev/models/migrationscripts/20250320_modify_file_meta.go:38
   > ```
   > 
   > ### Anything else
   > Root Cause: The migration contains this SQL: `sql := "ALTER TABLE _tool_q_dev_s3_file_meta MODIFY processed_time DATETIME NULL"`
   > 
   > * MODIFY is MySQL/MariaDB-specific and not valid PostgreSQL syntax.
   > * DATETIME is also a MySQL type; PostgreSQL uses TIMESTAMP.
   >   This breaks PostgreSQL migrations and blocks DevLake startup.
   > 
   > Environment:
   > 
   > * DevLake: v1.0.2+
   > * Database: PostgreSQL (fails)
   > * Last known working: v1.0.2-beta8
   > 
   > Impact: Blocks DB migration process on PostgreSQL → prevents DevLake from starting.
   > 
   > ### Version
   > v1.0.2+
   > 
   > ### Are you willing to submit PR?
   > * [ ]  Yes I am willing to submit a PR!
   > 
   > ### Code of Conduct
   > * [x]  I agree to follow this project's [Code of Conduct](https://www.apache.org/foundation/policies/conduct)
   
   I resolved the compatibility issue of the PostgreSQL migration by replacing the MySQL-specific syntax with the SQL that supports the dialect and adding idempotency checks. The PR has been submitted.


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: dev-unsubscribe@devlake.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

Re: [I] [Bug][Devlake DB Migration] DB Migration failure on PostgreSQL due to MODIFY syntax in migration 20250320_modify_file_meta.go [incubator-devlake]

Posted by "github-actions[bot] (via GitHub)" <gi...@apache.org>.
github-actions[bot] commented on issue #8564:
URL: https://github.com/apache/incubator-devlake/issues/8564#issuecomment-3488546786

   This issue has been automatically marked as stale because it has been inactive for 60 days. It will be closed in next 7 days if no further activity occurs.


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: dev-unsubscribe@devlake.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

Re: [I] [Bug][Devlake DB Migration] DB Migration failure on PostgreSQL due to MODIFY syntax in migration 20250320_modify_file_meta.go [incubator-devlake]

Posted by "github-actions[bot] (via GitHub)" <gi...@apache.org>.
github-actions[bot] closed issue #8564: [Bug][Devlake DB Migration] DB Migration failure on PostgreSQL due to MODIFY syntax in migration 20250320_modify_file_meta.go
URL: https://github.com/apache/incubator-devlake/issues/8564


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: dev-unsubscribe@devlake.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

Re: [I] [Bug][Devlake DB Migration] DB Migration failure on PostgreSQL due to MODIFY syntax in migration 20250320_modify_file_meta.go [incubator-devlake]

Posted by "dosubot[bot] (via GitHub)" <gi...@apache.org>.
dosubot[bot] commented on issue #8564:
URL: https://github.com/apache/incubator-devlake/issues/8564#issuecomment-3253665979

   <!-- Greeting -->
   Hi @jcg-juan! I'm [Dosu](https://go.dosu.dev/dosubot) and I’m helping the incubator-devlake team.
   
   <!-- Answer -->
   The migration failure is caused by MySQL-specific SQL in `20250320_modify_file_meta.go`, which uses `ALTER TABLE ... MODIFY` and `DATETIME`—these are not valid in PostgreSQL. The script does not include any logic to handle different SQL dialects, so PostgreSQL migrations will fail on DevLake v1.0.2+ as you described. There are currently no fixes, workarounds, or open PRs addressing this issue in the repository, and the migration script has not been updated for PostgreSQL compatibility as of the latest commit [source](https://github.com/apache/incubator-devlake/blob/2c15ad880dce830c6512fa58039527cbf4745b8b/backend/plugins/q_dev/models/migrationscripts/20250320_modify_file_meta.go).
   
   To resolve this, migration scripts should detect the database dialect and run compatible SQL for each engine (for example, use `ALTER TABLE ... ALTER COLUMN ... TYPE TIMESTAMP` for PostgreSQL). The DevLake DAL interface supports dialect detection, but it's not used in this migration. Until the script is updated, PostgreSQL support for this migration remains broken.
   
   
   <!-- Dosu Comment Footer -->
   *To reply, just mention [@dosu](https://go.dosu.dev/dosubot).*
   
   ---
   <sup>How did I do? [Good](https://app.dosu.dev/response-feedback/85a63558-c075-4afd-9e0b-686fb0baa219?feedback_type=great_response) | [Irrelevant](https://app.dosu.dev/response-feedback/85a63558-c075-4afd-9e0b-686fb0baa219?feedback_type=irrelevant_answer) | [Incorrect](https://app.dosu.dev/response-feedback/85a63558-c075-4afd-9e0b-686fb0baa219?feedback_type=incorrect_sources) | [Verbose](https://app.dosu.dev/response-feedback/85a63558-c075-4afd-9e0b-686fb0baa219?feedback_type=too_verbose) | [Hallucination](https://app.dosu.dev/response-feedback/85a63558-c075-4afd-9e0b-686fb0baa219?feedback_type=hallucination) | [Report 🐛](https://app.dosu.dev/response-feedback/85a63558-c075-4afd-9e0b-686fb0baa219?feedback_type=bug_report) | [Other](https://app.dosu.dev/response-feedback/85a63558-c075-4afd-9e0b-686fb0baa219?feedback_type=other)</sup>&nbsp;&nbsp;[![Chat with Dosu](https://dosu.dev/dosu-chat-badge.svg)](https://app.dosu.dev/b4e8e847-d479-4541-83a8-d88d83fea5c9/ask?utm_source=githu
 b)&nbsp;[![Join Discord](https://img.shields.io/badge/join-5865F2?logo=discord&logoColor=white&label=)](https://go.dosu.dev/discord-bot)&nbsp;[![Share on X](https://img.shields.io/badge/X-share-black)](https://twitter.com/intent/tweet?text=%40dosu_ai%20helped%20me%20solve%20this%20issue!&url=https%3A//github.com/apache/incubator-devlake/issues/8564)


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: dev-unsubscribe@devlake.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

Re: [I] [Bug][Devlake DB Migration] DB Migration failure on PostgreSQL due to MODIFY syntax in migration 20250320_modify_file_meta.go [incubator-devlake]

Posted by "klesh (via GitHub)" <gi...@apache.org>.
klesh commented on issue #8564:
URL: https://github.com/apache/incubator-devlake/issues/8564#issuecomment-3257438557

   Loop @warren830


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: dev-unsubscribe@devlake.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

Re: [I] [Bug][Devlake DB Migration] DB Migration failure on PostgreSQL due to MODIFY syntax in migration 20250320_modify_file_meta.go [incubator-devlake]

Posted by "github-actions[bot] (via GitHub)" <gi...@apache.org>.
github-actions[bot] commented on issue #8564:
URL: https://github.com/apache/incubator-devlake/issues/8564#issuecomment-3524503393

   This issue has been closed because it has been inactive for a long time. You can reopen it if you encounter the similar problem in the future.


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: dev-unsubscribe@devlake.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org