oliwel•14mo ago

Enforce update of projections

Hi All, I had some issues with an upgrade and found out, that my "projections.users*" tables are not properly migrated. We had some back and forth with different binary versions and schema updates and now I am left with an existing set of "users13" projections that are missing all users that have been added recently in "users12". Rerunning the setup phase does not fix the projections even after dropping the user13 tables. Is there any trick to enforce a check/migration or trigger it manually? While it is obvious that the user tables are broken, is there any way to check all other versioned items to be uptodate? I am running self-hosted with Postres, current active binary is 2.54 and I am trying to upgrade to 2.59. best regards Oli

10 Replies

oliwelOP•14mo ago

I am confused now...it seems that after waiting some minutes the systems started to copy the data from users12 to users13, but three (out of almost 58k users) are not copied and those are unfortunately the admin accounts. So I am now left with a system holding 58k users but no admin can any longer access the UI 😦

FFO•14mo ago

Uhm that sounds weird. I guess 2.54 is still working. right? Can you share the logs from the 2.59 setup job?

oliwelOP•14mo ago

@FFO find attached the log of the setup command and the initial startup log. After some minutes the value of "iteration=XX" for the "user13" query reaches 0 which seems to be the point in time when all (but not the 3 missing accounts) are syned.

oliwelOP•14mo ago

zitadel=# select * from projections.users12; zitadel=# select count() from projections.users12; count ------- 52839 (1 row) zitadel=# select count() from projections.users13; count ------- 52836 (1 row)

setup.log

start.log

oliwelOP•14mo ago

...and yes the installation is still working when running the same database with the old binary

FFO•14mo ago

hm let me ask internally about this and thanks for the information

oliwelOP•14mo ago

thx - I can share the db if needed and privacy is guranteed

FFO•14mo ago

We are discussing this internally ATM, I keep you posted if we need additional infos. Thanks for raising this

oliwelOP•14mo ago

I meanwhile got the upgrade working (at least to what I was able to test in my local copy) by manually coping the missing items in the three users tables but I would really appreciate to get an "official" comment or fix for this before ruining my setup 🙂 So for anybody stumbling over this too, here is the SQL I used to fix the issue: Find the users that are missing:

SELECT id
FROM projections.users12 source
WHERE NOT EXISTS (
    SELECT 1 
    FROM projections.users13 destination 
    WHERE destination.username = source.username
);

SELECT id
FROM projections.users12 source
WHERE NOT EXISTS (
    SELECT 1 
    FROM projections.users13 destination 
    WHERE destination.username = source.username
);

You need to feed the user ids found into the brackets (...)

start transaction;
insert into projections.users13 select * from projections.users12 where id in (...);
insert into projections.users13_humans select *, null as password_changed from  projections.users12_humans where user_id in (...);
insert into projections.users13_notifications select user_id, instance_id, last_email, verified_email, last_phone, verified_phone, password_set  from projections.users12_notifications where user_id in (...);
commit;

start transaction;
insert into projections.users13 select * from projections.users12 where id in (...);
insert into projections.users13_humans select *, null as password_changed from  projections.users12_humans where user_id in (...);
insert into projections.users13_notifications select user_id, instance_id, last_email, verified_email, last_phone, verified_phone, password_set  from projections.users12_notifications where user_id in (...);
commit;

FFO•14mo ago

Thank you for sharing!

Enforce update of projections

Did you find this page helpful?