Upgrading privacyidea on a galera cluster does not work

Question
1

Hi all,

we are currently running PI 3.11.2 on a galera cluster (mariadb 10.11.18). I tried to upgrade (to PI 3.13.2) using

privacyidea-pip-update

but database migration stopped with the error:

sqlalchemy.exc.NotSupportedError: (pymysql.err.NotSupportedError) (1235, “This version of MariaDB doesn’t yet support ‘CACHE without INCREMENT BY 0 in Galera cluster’”)
[SQL: ALTER SEQUENCE tokencredentialidhash_seq RESTART WITH 10]

it seems to me this is related to:

any hints on what I could do to fix this? since we are affected by “OTP-bypass in the WebUI” we would like to upgrade as soon as possible.

If you need the whole log of the upgrade script, let me know.

Best,

Hp

2

Hello Hp,

thank you for your detailed description of the problem. This error occurs when running the 3.13.2 migration script with MariaDB Version 10.11.x with Galera Cluster. This simply means that there was an error, when altering the sequence for the tokencredentialidhash_seq table, which used for passkeys.

If you would not be using passkeys then this should not be relevant. But I can see that the value for the sequence id is 10, so you are probably using passkeys.

You can fix this manually as follows:

You need to issue the following sql commands in your MariaDB Database:

SELECT COALESCE(MAX(id), 0) + 1 FROM tokencredentialidhash;

This will result in a value, as seen in the screenshot below. You need this

ALTER SEQUENCE tokencredentialidhash_seq RESTART WITH <number_from_previous_command> INCREMENT BY 0;

It may look like this:

image
image857×226 25 KB

Hopefully this helps. Let me know.

Best Regards

Lucas

3

ok, thank you very much. just to be sure: I do run these commands before running the migration script, right?

4

If the migration script has already run and failed you can run it afterwards.

You can then check your alembic_version (version of database schema) with:

pi-manage db current

If you are still on version: 06b105a4f941

I suggest that you stamp your database, as described below.

If the migration script has not run yet, then you can run this command and then stamp the database with:

pi-manage db stamp b1a2c3d4e5f6

So that the migration script won’t run again.

Please let me know if it worked out, or if you have any further questions.

Best Regards

Lucas

5

hm, just that I understand correctly: in what you describe, I get the impression that the migration script will not run at all in the end (besides the commands that I execute manually). so besides adjusting the sequence, there are no database schema upgrades?

6

I get:

pi-manage db current
INFO [alembic.env] Running migrations online

7

Typically when running pi-manage db current it should look something like this:

image

You can also check your alembic version by running:

select * from pi.alembic_version;

in your database.

image
image614×170 10.4 KB

The latest migration script only contains the modification to the tokencredentialidhash_seq sequence.

Therefore if you have reached the alembic version: 06b105a4f941

Then just running the command manually and then stamping the database if fully fine.

8

You are using privacyidea-pip-update.
See privacyidea/tools/privacyidea-pip-update at master · privacyidea/privacyidea · GitHub
This can run the db schema upgrade or it can skip the upgrade. Totally depends on how you call it and if you hit y or n.

9

ok, thanks guys, this worked. one node us upgraded now. the 2nd node should upgrade without problems now :slight_smile:

10

Yes, the second node will only need to update the code, since the replicated database is already up to date.

11

hey guys,

upgraded our production cluster to 3.13.3 just now. expected to have to execute the above sql commands to complete the db migration. it seems to me, you incorporated that already in the migration script. if yes ⇒ great work, thank you! if no ⇒ please let me know if I need to do the above steps, even after the migration script went through without error.

best,

hp

12

Hi hp,

minor patch levels like 3.13.1 → 3.13.3 usually do not contain DB changes.

All update mechanisms (like ubuntu packages, post inst scsript) but also the privacyidea-pip-update contain the mechanism to run a schema update/database update.

If you have a support contract you can find more about this in the Knowledge Base in the article “Planning your privacyIDEA server update”.

Regards
Cornelius

13

ok, I try to ask differently, maybe that clears things up.

upgrading 3.13.1 ⇒ 3.13.2: I needed to manually execute the SQL statements you guys provided (see above)

upgrading 3.13.1 ⇒ 3.13.3: no need to run these SQL statements manually, so I was wondering whether these SQL changes were done by the upgrade/migration script.

and yes, we have a support contract, but to be honest, I do not believe that we ever got the information how to “invoke” it. but I will check my emails.

best,

14

Interesting. Normally there should be no DB update from 3.13.1. to 3.13.2.
But in this case a DB (schema) update was broken or did nor work correctly for Galera.

In 3.13.3 a DB migration script was fixed, that was broken for galera. I.e.

when updating to 3.13.2 the automatic update will break with Galera. You need to fix it manually.

When updating to 3.13.3 the automic update for Galera was fixed. So it ran automatically in the background.

1 Like