[Docker] Upgrade from 2.4.1 to 2.5.0 fail

[RikoDEV]

Describe the Bug

I updated the docker image to the latest version and got a prisma migrate deploy command error after container boot.

Database

MySQL

Relevant log output

umami-umami-1  | yarn run v1.22.19
umami-umami-1  | $ npm-run-all check-db update-tracker start-server
umami-umami-1  | $ node scripts/check-db.js
umami-umami-1  | ✓ DATABASE_URL is defined.
umami-umami-1  | ✓ Database connection successful.
umami-umami-1  | ✓ Database version check successful.
umami-umami-1  | Error: P3009
umami-umami-1  | 
umami-umami-1  | 
umami-umami-1  | migrate found failed migrations in the target database, new migrations will not be applied. Read more about how to resolve migration issues in a production database: https://pris.ly/d/migrate-resolve
umami-umami-1  | The `03_metric_performance_index` migration started at 2023-08-18 06:55:19.444 UTC failed
umami-umami-1  | 
umami-umami-1  | 
umami-umami-1  | 
umami-umami-1  | 
umami-umami-1  | 
umami-umami-1  | 
umami-umami-1  | error Command failed with exit code 1.
umami-umami-1  | info Visit https://yarnpkg.com/en/docs/cli/run for documentation about this command.
umami-umami-1  | ERROR: "check-db" exited with 1.
umami-umami-1  | error Command failed with exit code 1.

Which browser are you using? (if relevant)

No response

How are you deploying your application? (if relevant)

Docker

Temp fix:

UPDATE _prisma_migrations SET `finished_at` = CURRENT_TIMESTAMP WHERE `migration_name` = '03_metric_performance_index';

[HermesDE]

Same here with Postgres

[magicgg91]

Same issue for me. Rolling back to V2.4.1 doesn't fix issue, DB is blocked on this state. Needed to restore my last backup.

[iYoungblood]

FWIW, this did work:

docker compose pull docker compose down docker compose up -d

[briancao]

@RikoDEV Can you pull the error message from the _prisma_migration table and post it?

[RikoDEV]

Sure. I just point out that my error can be caused by deleting the migration (trying to fix container). Unfortunately, I did not copy the earlier error.

Migration name: 03_metric_performance_index
Database error code: 1061
Database error:
Duplicate key name 'event_data_website_id_created_at_idx'
Please check the query number 1 from the migration file.
   0: sql_schema_connector::apply_migration::apply_script
           with migration_name="03_metric_performance_index"
             at schema-engine/connectors/sql-schema-connector/src/apply_migration.rs:106
   1: schema_core::commands::apply_migrations::Applying migration
           with migration_name="03_metric_performance_index"
             at schema-engine/core/src/commands/apply_migrations.rs:91
   2: schema_core::state::ApplyMigrations
             at schema-engine/core/src/state.rs:201

[diarpu]

Same here with MySQL on Vercel

[13:18:21.775] $ node scripts/check-db.js
[13:18:21.882] ✓ DATABASE_URL is defined.
[13:18:22.361] ✓ Database connection successful.
[13:18:22.736] ✓ Database version check successful.
[13:18:25.046] Error: P3009
[13:18:25.047] 
[13:18:25.047] migrate found failed migrations in the target database, new migrations will not be applied. Read more about how to resolve migration issues in a production database: https://pris.ly/d/migrate-resolve
[13:18:25.047] The `03_metric_performance_index` migration started at 2023-08-19 01:03:29.294 UTC failed
[13:18:25.047] 
[13:18:25.047] 
[13:18:25.047] ✗ Command failed: prisma migrate deploy
[13:18:25.047] Error: P3009
[13:18:25.047] 
[13:18:25.047] migrate found failed migrations in the target database, new migrations will not be applied. Read more about how to resolve migration issues in a production database: https://pris.ly/d/migrate-resolve
[13:18:25.047] The `03_metric_performance_index` migration started at 2023-08-19 01:03:29.294 UTC failed
[13:18:25.047] 
[13:18:25.047] 
[13:18:25.047] 
[13:18:25.062] error Command failed with exit code 1.
[13:18:25.063] info Visit https://yarnpkg.com/en/docs/cli/run for documentation about this command.
[13:18:25.078] ERROR: "check-db" exited with 1.
[13:18:25.093] error Command failed with exit code 1.
[13:18:25.093] info Visit https://yarnpkg.com/en/docs/cli/run for documentation about this command.
[13:18:25.117] Error: Command "yarn run build" exited with 1

[briancao]

@RikoDEV Did you (or any of the reporters in this thread) upgrade your Umami from v1 -> v2? If so, I assume the old schema/tables need to be dropped as the key event_data_website_id_created_at_idx currently does not exists in any v2 migrations, but the one trying to be applied.

[RikoDEV]

@RikoDEV Did you (or any of the reporters in this thread) upgrade your Umami from v1 -> v2? If so, I assume the old schema/tables need to be dropped as the key event_data_website_id_created_at_idx currently does not exists in any v2 migrations, but the one trying to be applied.

Yes, I updated the umami from v1 to v2.

[4ortytwo]

@RikoDEV Did you (or any of the reporters in this thread) upgrade your Umami from v1 -> v2? If so, I assume the old schema/tables need to be dropped as the key event_data_website_id_created_at_idx currently does not exists in any v2 migrations, but the one trying to be applied.

I have, but it was a long time ago, I've been on probably every updated version after that. Getting this error as well:

[franciscao633]

Can you try manually dropping the index event_data_website_id_created_at_idx and rerunning the migrations? I can't track down that index in both the v1/v2 branch, so I'm not exactly sure how it got there. Maybe it was present before we started prisma migrations?

1. DROP INDEX `event_data_website_id_created_at_idx` ON `event_data`; (this is mysql syntax, postgresql will be different)
2. npx prisma migrate resolve --rolled-back "03_metric_performance_index"
3. yarn build

Let me know if you still have issues.

[cirrusflyer]

I'm having the same issue and running on fly.io. What's the best way to handle this here?

[4ortytwo]

Any solution to this, @franciscao633? Can't use umami since the 2.5.0 update and can't seem to find a way to execute this query using dokku.

[franciscao633]

@4ortytwo are you able to pull the logs from the failed 03_ migration? Should be in the logs column from the _prisma_migrations table. From there I can probably help you out more.

[FuzzyMistborn]

Can you try manually dropping the index event_data_website_id_created_at_idx and rerunning the migrations? I can't track down that index in both the v1/v2 branch, so I'm not exactly sure how it got there. Maybe it was present before we started prisma migrations?

1. `` DROP INDEX `event_data_website_id_created_at_idx` ON `event_data`; ``  (this is mysql syntax, postgresql will be different)

2. `npx prisma migrate resolve --rolled-back "03_metric_performance_index"`

3. `yarn build`

Let me know if you still have issues.

I had to roll back to 2.4.1 and load in a backup. When I tried to run these commands I got the following:

Running DROP INDEX `event_data_website_id_created_at_idx` ON `event_data`; got me: ERROR 1091 (42000): Can't DROP INDEX `event_data_website_id_created_at_idx`; check that it exists

Then when I tried to run npx prisma migrate resolve --rolled-back "03_metric_performance_index" I got:

Prisma schema loaded from prisma/schema.prisma
Datasource "db": MySQL database "umami" at "umami-db:3306"
Error: P3011

Migration `03_metric_performance_index` cannot be rolled back because it was never applied to the database. Hint: did you pass in the whole migration name? (example: "20201207184859_initial_migration")

[dennishendricks]

@franciscao633 Unfortunately, we get the same error. During the update to 2.5, the Prisma migration process fails with the following error:

Error: P3009

migrate found failed migrations in the target database, new migrations will not be applied. Read more about how to resolve migration issues in a production database: https://pris.ly/d/migrate-resolve
The `03_metric_performance_index` migration started at 2023-08-29 07:23:29.907336 UTC failed

I tried a rollback, unfortunately without success. Strangely, the logs column is completely empty:

By the way, an index on event_data_website_id_created_at_idx is not created in our case, here is the structure of the event_data table:

I will try an update from 2.4.1 to 2.6.1 today or tomorrow and report back.

[franciscao633]

@FuzzyMistborn Since you rolled back from a backup those steps aren't needed anymore. What happens when you try to upgrade now? If it errors out can you provide the logs?

[FuzzyMistborn]

Nope, same issue.

yarn run v1.22.19
$ npm-run-all check-db update-tracker start-server
$ node scripts/check-db.js
✓ DATABASE_URL is defined.
✓ Database connection successful.
✓ Database version check successful.
Error: P3009
migrate found failed migrations in the target database, new migrations will not be applied. Read more about how to resolve migration issues in a production database: https://pris.ly/d/migrate-resolve
The `03_metric_performance_index` migration started at 2023-08-31 18:07:35.757 UTC failed
✗ Command failed: prisma migrate deploy
Error: P3009
migrate found failed migrations in the target database, new migrations will not be applied. Read more about how to resolve migration issues in a production database: https://pris.ly/d/migrate-resolve
The `03_metric_performance_index` migration started at 2023-08-31 18:07:35.757 UTC failed
error Command failed with exit code 1.
info Visit https://yarnpkg.com/en/docs/cli/run for documentation about this command.
ERROR: "check-db" exited with 1.
error Command failed with exit code 1.
info Visit https://yarnpkg.com/en/docs/cli/run for documentation about this command.

[franciscao633]

Feels like the migration is running from an earlier process and that is where the error is to help troubleshoot. What you are seeing the the 2nd attempted try. Your scenario seems very similar to this.

[FuzzyMistborn]

Just a thought, I'm running Mariadb 11.x. I know they made some changes, could that be the source of the problem?

[github-actions[bot]]

This issue is stale because it has been open for 60 days with no activity.

[FuzzyMistborn]

Still an issue.

[HermesDE]

Still an issue.

It is. I deleted my database and set up a fresh install. It works now.

[github-actions[bot]]

This issue is stale because it has been open for 60 days with no activity.

[FuzzyMistborn]

Still an issue.

[FuzzyMistborn]

I've given up. I just nuked my existing install/data and recreated a new Umami instance. Not happy, but doesn't seem like there will be a fix and didn't want to keep using old software in case there are security issues.

[MatthK]

I have the same issue...

I was still running V1.33.1 as I had an issue previously to update for some reason. I tried again and after changing the script name from a to m, it somehow worked with a bit of fiddling. I made it to V1.40.0 and then upgraded to V2.0.0. That also worked and I then slowly upgraded my container from V2.0.0 to now V.2.4.1.

However upgrading further keeps erroring out, claiming issues with the indexes. Manually deleting various indexes didn't help unfortunately.

Luckily in my case, I could revert back to V2.4.1 and it is now still up and running. However, after making so much progress, it would be nice to update to the latest version to become finally current again. Any suggestion on how I could pass that hurdle?

[MatthK]

Ok, so I did get past it and now I'm back at the latest version (V2.12.1).

I did it with a little trick.

1. I took a backup of my database by dumping the SQL
2. I then removed the structure data
3. I fired up another container with V2.4.1. into a new database
4. After deleting the admin user, I imported the data only from my real container
5. I removed the container, fired up V2.5.0 and let it do the migration on the new database
6. After the upgrade, I took another SQL dump of the new database
7. I deleted my real database, created a blank new one with the same name and imported the dump which had V2.5.0
8. I fired up the container with V2.5.0 and I passed the migration
9. I then updated the container from 2.5.0 to 2.12.0 in one go and it succeeded again.
10. Last step was to update to V.2.12.1

Where is the version number displayed now in the site? It disappeared from the footer?