umami
umami copied to clipboard
umami-software
prisma migrate deploy Error: P3009
Describe the Bug
Didn't have this issue on last UMAMI version. I deployed a fresh instance just 1 hour ago I'm facing an issue.
I have installed multiple instances and never faced an issue.
Database error: FUNCTION Webinoly_8DXefk0h.BIN_TO_UUID does not exist
Please check the query number 2 from the migration file.
✗ Command failed: prisma migrate deploy Error: P3018
A migration failed to apply. New migrations cannot be applied before the error is recovered from. Read more about how to resolve migration issues in a production database: https://pris.ly/d/migrate-resolve
Migration name: 05_add_visit_id
Database error code: 1305
Database error: FUNCTION Webinoly_8DXefk0h.BIN_TO_UUID does not exist
Please check the query number 2 from the migration file.
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.
Database
MySQL
Relevant log output
No response
Which Umami version are you using? (if relevant)
latest
Which browser are you using? (if relevant)
Edge
How are you deploying your application? (if relevant)
VPS
MySQL needs to generate random UUID's in the migration script. It looks like UUID support was introduced in MySQL 8.0. We bumped our minimum version to 8.0 not too far back for different reasons. Are you able to upgrade by any chance?
Alternatively you can replace the BIN_TO_UUID function in the migration script with something like below which also validates as UUIDv4.
SELECT LOWER(CONCAT(
HEX(RANDOM_BYTES(4)), '-',
HEX(RANDOM_BYTES(2)), '-4',
SUBSTR(HEX(RANDOM_BYTES(2)), 2, 3), '-',
CONCAT(HEX(FLOOR(ASCII(RANDOM_BYTES(1)) / 64)+8),SUBSTR(HEX(RANDOM_BYTES(2)), 2, 3)), '-',
HEX(RANDOM_BYTES(6))
))
I'm using mysql 8.0. I had different error
$ 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 `05_add_visit_id` migration started at 2024-04-04 23:54:45.543 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 `05_add_visit_id` migration started at 2024-04-04 23:54:45.543 UTC failed
I'm using mysql 8.0. I had different error
$ 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 `05_add_visit_id` migration started at 2024-04-04 23:54:45.543 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 `05_add_visit_id` migration started at 2024-04-04 23:54:45.543 UTC failed
btw, I'm running a docker version. Not sure exactly what to do to recover com this.
after amending as you mentioned its smooth sailing
-- AlterTable
ALTER TABLE website_event ADD COLUMN visit_id VARCHAR(36) NULL;
-- Update with UUIDv4
UPDATE website_event we
JOIN (
SELECT DISTINCT
s.session_id,
s.visit_time,
LOWER(CONCAT(
HEX(RANDOM_BYTES(4)), '-',
HEX(RANDOM_BYTES(2)), '-4',
SUBSTR(HEX(RANDOM_BYTES(2)), 2, 3), '-',
CONCAT(HEX(FLOOR(ASCII(RANDOM_BYTES(1)) / 64)+8),SUBSTR(HEX(RANDOM_BYTES(2)), 2, 3)), '-',
HEX(RANDOM_BYTES(6))
)) AS uuid
FROM (
SELECT DISTINCT session_id,
DATE_FORMAT(created_at, '%Y-%m-%d %H:00:00') visit_time
FROM website_event
) s
) a ON we.session_id = a.session_id AND DATE_FORMAT(we.created_at, '%Y-%m-%d %H:00:00') = a.visit_time
SET we.visit_id = a.uuid
WHERE we.visit_id IS NULL;
-- ModifyColumn
ALTER TABLE website_event MODIFY visit_id VARCHAR(36) NOT NULL;
-- CreateIndex
CREATE INDEX website_event_visit_id_idx ON website_event(visit_id);
-- CreateIndex
CREATE INDEX website_event_website_id_visit_id_created_at_idx ON website_event(website_id, visit_id, created_at);
@camponez thats the prisma error. You could find the migration error inside the _prisma_migrations table if you're able to query it.
For docker you can specify the version if you need to roll back instead of using latest, but it's best to just resolve the migration error.
@camponez thats the prisma error. You could find the migration error inside the
_prisma_migrationstable if you're able to query it.For docker you can specify the version if you need to roll back instead of using latest, but it's best to just resolve the migration error.
I could not find error in that table.
I have tried to use last version. It didn't work. I get the same error.
I have tried to run sql code pasted above, but I think now I'm in a state where I don't know if it's fully applied or not
Describe the Bug
Didn't have this issue on last UMAMI version. I deployed a fresh instance just 1 hour ago I'm facing an issue.
Database
MySQL
Which Umami version are you using? (if relevant)
latest
What's your origin umami versions?
You can check if the code applied by checking if you have a populated visit_id column in the website_event table.
If so you can run the code below inside the umami container to resolve the migration error.
npx prisma migrate resolve --applied "05_add_visit_id"
Im running 10.11.2-MariaDB-1:10.11.2+maria~ubu2204 and having same issue.
...
Migration name: 05_add_visit_id
Database error code: 1305
Database error:
FUNCTION db_name.BIN_TO_UUID does not exist
...
I am facing the same issue as well, and the word 'visits' remains untranslated in the Chinese version.
Same error here, when using the last docker image and a mariadb server on host (the BIN_TO_UUID is not defined in mariadb). I tryied to create the missing function with https://gist.github.com/jamesgmarks/56502e46e29a9576b0f5afea3a0f595c, but the error still occurs (I’m not a db export, thus not sure if I did things right).
Most importantly, since the error I cannot rollback as trying to restart last working docker image report the same error :/ Is there a way to properly rollback the broken migration to restart old version until a fix is found?
Here is the compatible version for MariaDB < 10.10.0 instances. Thanks for @HDBear
Run this commands with mariadb/mysql client, it can throw an error on line 2, simply comment it out:
-- AlterTable
ALTER TABLE website_event ADD COLUMN visit_id VARCHAR(36) NULL;
-- Update with UUIDv4
UPDATE website_event we
JOIN (
SELECT DISTINCT
s.session_id,
s.visit_time,
LOWER(CONCAT(
HEX(SUBSTR(MD5(RAND()), 1, 4)), '-',
HEX(SUBSTR(MD5(RAND()), 1, 2)), '-4',
SUBSTR(HEX(SUBSTR(MD5(RAND()), 1, 2)), 2, 3), '-',
CONCAT(HEX(FLOOR(ASCII(SUBSTR(MD5(RAND()), 1, 1)) / 64)+8),SUBSTR(HEX(SUBSTR(MD5(RAND()), 1, 2)), 2, 3)), '-',
HEX(SUBSTR(MD5(RAND()), 1, 6))
)) AS uuid
FROM (
SELECT DISTINCT session_id,
DATE_FORMAT(created_at, '%Y-%m-%d %H:00:00') visit_time
FROM website_event
) s
) a ON we.session_id = a.session_id AND DATE_FORMAT(we.created_at, '%Y-%m-%d %H:00:00') = a.visit_time
SET we.visit_id = a.uuid
WHERE we.visit_id IS NULL;
-- ModifyColumn
ALTER TABLE website_event MODIFY visit_id VARCHAR(36) NOT NULL;
-- CreateIndex
CREATE INDEX website_event_visit_id_idx ON website_event(visit_id);
-- CreateIndex
CREATE INDEX website_event_website_id_visit_id_created_at_idx ON website_event(website_id, visit_id, created_at);
Then resolve the migration in Umami folder:
npx prisma migrate resolve --applied "05_add_visit_id"
I confirm it works as expected! Thank you very much for your help @yibudak
If you are using docker like me / or don’t want to / cannot use prisma for whatever reason, resolving the migration is as easy as doing the following:
UPDATE _prisma_migrations
SET finished_at = NOW(), logs = NULL, applied_steps_count = 1
WHERE migration_name = '05_add_visit_id';
Yeah having the same error with MariaDB but technically Umami doesn't support mariadb, only mysql - as mentioned in the readme. Mariadb doesn't have that function
same error with 10.4.33-MariaDB-1:10.4.33+maria~deb10-log
Database error: FUNCTION Webinoly_8DXefk0h.BIN_TO_UUID does not exist
;-(
I'm running ghcr.io/umami-software/umami:postgresql-v2.11.0 and experiencing the same:
umami | yarn run v1.22.19
umami | $ npm-run-all check-db update-tracker start-server
umami | $ node scripts/check-db.js
umami | ✓ DATABASE_URL is defined.
umami | ✓ Database connection successful.
umami | ✓ Database version check successful.
umami_db | 2024-04-05 11:06:31.962 UTC [120] LOG: could not receive data from client: Connection reset by peer
umami | Error: P3009
umami |
umami | 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 | The `05_add_visit_id` migration started at 2024-04-05 11:02:55.305423 UTC failed
umami |
umami |
umami | ✗ Command failed: prisma migrate deploy
umami | Error: P3009
umami |
umami | 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 | The `05_add_visit_id` migration started at 2024-04-05 11:02:55.305423 UTC failed
In case this helps anyone else (or me in the future), on my Docker/postgres install the comment at https://github.com/umami-software/umami/issues/2645#issuecomment-2039235057 worked for me:
myserver$ docker exec -it umami_db_1 psql --user umami
umami=# UPDATE _prisma_migrations SET finished_at = NOW(), logs = NULL, applied_steps_count = 1 WHERE migration_name = '05_add_visit_id';
UPDATE 1
umami=# exit
myserver$ docker-compose restart
(note that the UPDATE 1 line is output from the psql command, not something I typed.)
Spoke too soon, it is started now, but it says: "Something went wrong".
In the browser console;
https://(mysite.com)/api/websites/e12341e9-405e-4e8e-123f-0e1f431e145c/stats?startAt=1712239200000&endAt=1712329199999
500 Internal Server Error
PrismaClientKnownRequestError:
Invalid prisma.websiteEvent.create() invocation:
The logs look like this - repeated lots:
The column `visit_id` does not exist in the current database.
at In.handleRequestError (/app/node_modules/@prisma/client/runtime/library.js:122:6854)
at In.handleAndLogRequestError (/app/node_modules/@prisma/client/runtime/library.js:122:6188)
at In.request (/app/node_modules/@prisma/client/runtime/library.js:122:5896)
at async l (/app/node_modules/@prisma/client/runtime/library.js:127:10871)
at async h (/app/.next/server/pages/api/send.js:1:3092)
at async K (/app/node_modules/next/dist/compiled/next-server/pages-api.runtime.prod.js:20:16545)
at async U.render (/app/node_modules/next/dist/compiled/next-server/pages-api.runtime.prod.js:20:16981)
at async NextNodeServer.runApi (/app/node_modules/next/dist/server/next-server.js:554:9)
at async NextNodeServer.handleCatchallRenderRequest (/app/node_modules/next/dist/server/next-server.js:266:37)
at async NextNodeServer.handleRequestImpl (/app/node_modules/next/dist/server/base-server.js:791:17) {
code: 'P2022',
clientVersion: '5.11.0',
meta: { modelName: 'WebsiteEvent', column: 'visit_id' }
}
Glad I took a db dump before I did this. I was able to restore to a working version (2.10.0).
@orhun not seeing much in that log on why it failed besides a connection error. If the error wasn't captured its hard to troubleshoot. The migration is fairly simple, its just adding a column, populating, and adding indexes. I would run the SQL code below on your db then resolve the prisma migration with either method, similar to @yibudak comment. If the code below produced an error please post.
Run this commands with psql client or IDE, it can throw an error on line 2, simply comment it out:
-- AlterTable
ALTER TABLE "website_event" ADD COLUMN "visit_id" UUID NULL;
UPDATE "website_event" we
SET visit_id = a.uuid
FROM (SELECT DISTINCT
s.session_id,
s.visit_time,
gen_random_uuid() uuid
FROM (SELECT DISTINCT session_id,
date_trunc('hour', created_at) visit_time
FROM "website_event") s) a
WHERE we.session_id = a.session_id
and date_trunc('hour', we.created_at) = a.visit_time;
ALTER TABLE "website_event" ALTER COLUMN "visit_id" SET NOT NULL;
-- CreateIndex
CREATE INDEX "website_event_visit_id_idx" ON "website_event"("visit_id");
-- CreateIndex
CREATE INDEX "website_event_website_id_visit_id_created_at_idx" ON "website_event"("website_id", "visit_id", "created_at");
Then resolve the migration in Umami folder:
npx prisma migrate resolve --applied "05_add_visit_id"
OR if you want to just run a SQL statement
UPDATE _prisma_migrations
SET finished_at = NOW(), logs = NULL, applied_steps_count = 1
WHERE migration_name = '05_add_visit_id';
I run the migration but now I'm getting the exact same error as @artfulrobot ("Something went wrong")
I gave up debugging this and downgraded, will wait for the solution to this / new release.
I wanted to add my experience on doing this upgrade to PostgreSQL. We have a small-medium workload but a considerable database size already (website_event is 1.5GB). With default tuning (wal_size_maximum = 1GB) we had troubles during this upgrade spending almost 30 minutes running this migration.
We ended up increasing the default values for write workload but unable to rerun the migration so I'm not aware if the gains are significant (some articles mention that tuning for wal_size_maximum of 8GB can increase 2x the write performance).
@lightningspirit Just curious, were you running the migration manually outside of prisma and it's still failing? I believe this is the first time we had to populate data on an existing table, which is a headache since some users can have million+ records. The migration is probably exceeding the statement_timeout config for their db and failing for some users.
It took several hours to update the website_event which is almost 1GB, so I wrote a python script to upgrade it.
Here is it:
- First, create a temp table to store the upgrade progress
CREATE TABLE public.update_progress (
last_processed_time timestamptz NULL,
rows_updated int4 NULL
);
This can help restore the progress if there is any issue.
- Run this script in your local
import psycopg2
batch_size = 100000
last_created_at = '1970-01-01T00:00:00Z'
total_updated = 0
while True:
conn = psycopg2.connect("postgres://DB_USER:DB_PASSWORD@DB_HOST/DB_NAME")
cur = conn.cursor()
cur.execute("set statement_timeout = '2000 s';")
cur.execute("SET work_mem = '16MB';")
cur.execute("SET search_path TO public, public;")
cur.execute("SELECT last_processed_time, rows_updated FROM update_progress;")
update_progress_row = cur.fetchone()
if update_progress_row:
last_created_at, total_updated = update_progress_row
cur.execute(f"""
WITH upd AS (
UPDATE public.website_event we
SET visit_id = a.uuid
FROM (
SELECT
s.session_id,
s.visit_time,
gen_random_uuid() AS uuid,
s.created_at
FROM (
SELECT
session_id,
date_trunc('hour', created_at) AS visit_time,
created_at
FROM public.website_event
WHERE created_at > '{last_created_at}'
ORDER BY created_at
LIMIT {batch_size}
) s
) a
WHERE we.session_id = a.session_id
AND date_trunc('hour', we.created_at) = a.visit_time
AND we.created_at = a.created_at
RETURNING we.created_at
)
SELECT COUNT(*), MAX(created_at) FROM upd;
""")
row_count, new_last_created_at = cur.fetchone()
total_updated += row_count
if row_count > 0:
cur.execute("TRUNCATE TABLE update_progress;")
cur.execute("INSERT INTO update_progress (last_processed_time, rows_updated) VALUES (%s, %s);", (new_last_created_at, total_updated))
conn.commit()
print(f"Updated {total_updated} rows, last processed at: {new_last_created_at}")
else:
break
cur.close()
conn.close()
- Last, update the rest parts.
ALTER TABLE "website_event" ALTER COLUMN "visit_id" SET NOT NULL;
-- CreateIndex
CREATE INDEX "website_event_visit_id_idx" ON "website_event"("visit_id");
-- CreateIndex
CREATE INDEX "website_event_website_id_visit_id_created_at_idx" ON "website_event"("website_id", "visit_id", "created_at");
UPDATE _prisma_migrations
SET finished_at = NOW(), logs = NULL, applied_steps_count = 1
WHERE migration_name = '05_add_visit_id';
i deploy it on vercel and get p3009 error as well, but can't use cli to try to solve the problem
@lightningspirit Just curious, were you running the migration manually outside of prisma and it's still failing? I believe this is the first time we had to populate data on an existing table, which is a headache since some users can have million+ records. The migration is probably exceeding the
statement_timeoutconfig for their db and failing for some users.
@franciscao633 Actually I had to run query statements manually (no changes to the script needed). Wasn't able to perform using Prisma migrate, so, yes, outside of Prisma migrate the provided statements work as expected.
Our website_event table is quite large and our DB server is not that powerful. When I attempted to run that migration (postgres), it locked up everything and failed every time.
So I did almost exactly what @madawei2699 did and created a script (but in Ruby) to do it in batches. It took some time to run but it worked and didn't lock everything up in the meanwhile.
We should probably be more careful with migrations that update or move data around, we don't know people's infrastructure and the amount of data they have.
@zavan I agree, but new features will have to touch the schema of existing tables eventually. I think the alternative would be to not force people to populate existing data as part of the migration, but provide the option. This would mean the visitor metrics would be empty on all historic data as a default. I'm not sure this is the solution, but just throwing out ideas.
Is there a plan to make this migration work? Is there a target milestone for this? I'm not confident using the python/ruby scripts.
At the mo I'm staying on 2.10.0 but I'd love to get the features in 2.11!