2.23.5-3.2.2 DB migration not completing

1

CentOS 7, mysql.

For some reason, it looks like the policyconditions table can’t be created, and the db migration exits silently. However, all the files are pulled correctly, but accessing the webgui shows a json output.

Error code:

{"message": "(_mysql_exceptions.ProgrammingError) (1146, \"Table 'privacyidea.policycondition' doesn't exist\")\n[SQL: SELECT policy.id AS policy_id, policy.active AS policy_active, policy.check_all_resolvers AS policy_check_all_resolvers, policy.name AS policy_name, policy.scope AS policy_scope, policy.action AS policy_action, policy.realm AS policy_realm, policy.adminrealm AS policy_adminrealm, policy.resolver AS policy_resolver, policy.user AS policy_user, policy.client AS policy_client, policy.time AS policy_time, policy.priority AS policy_priority, policycondition_1.id AS policycondition_1_id, policycondition_1.policy_id AS policycondition_1_policy_id, policycondition_1.section AS policycondition_1_section, policycondition_1.`Key` AS `policycondition_1_Key`, policycondition_1.comparator AS policycondition_1_comparator, policycondition_1.`Value` AS `policycondition_1_Value`, policycondition_1.active AS policycondition_1_active \nFROM policy LEFT OUTER JOIN policycondition AS policycondition_1 ON policy.id = policycondition_1.policy_id ORDER BY policycondition_1.id]\n(Background on this error at: http://sqlalche.me/e/f405)", "code": -500}

mysql db errors:

------------------------
LATEST FOREIGN KEY ERROR
------------------------
200218 15:31:08 Error in foreign key constraint of table `privacyidea`.`policycondition`:
Create  table `privacyidea`.`policycondition` with foreign key constraint failed. Referenced table `privacyidea`.`policy` not found in the data dictionary near 'FOREIGN KEY(policy_id) REFERENCES policy (id),
        CHECK (active IN (0, 1))
)ROW_FORMAT=DYNAMIC'.
--------

“pi-manage db show -d …”

Rev: 0c7123345224 (head)
Parent: d756b34061ff
Path: /opt/privacyIDEA/lib/privacyidea/migrations/versions/0c7123345224_.py

Store privacyIDEA node in clientapplication table

Revision ID: 0c7123345224
Revises: d756b34061ff
Create Date: 2019-09-06 13:27:12.020779

Any thoughts on why this might fail?

2

Have you read https://github.com/privacyidea/privacyidea/blob/master/READ_BEFORE_UPDATE.md ?

There are heavy changes in 3.0 in regards to the token database and tokenowner.
I have no idea, why your update fails. You might want to check your database structure for tokenowners and check what version your DB reached.

3

Interesting. I didn’t see any of that in “official” docs, but now I know where to look.

The error that I’m getting seems to be coming from a partition on the ‘policy’ table – which is incompatible with foreign keys. I can’t find the CREATE script for that table, but so far I don’t see anything regarding partitioning?

CentOS7, mysql, btw.

For some reason, the mysql driver doesn’t change to “mysql+pymysql://” in pi.cfg at the end of this, though.

4

Well, I converted my db from MyISAM to InnoDB, reverted/stamped the db to a lower revision, and ran the migrations again. It seemed to have worked better this time, and seems functional.

Thanks.

5

I was not aware that there is something “official” besides an enterprise SLA containing “offical” RPM repositories for CentOS :wink:

Anyway, thanks for pointing out you missed this file. I think the place you would have looked and you missed the reference is here?
https://privacyidea.readthedocs.io/en/latest/installation/upgrade.html#basic-pip-upgrade-process

Thanks for you either confirming or pointing to another location.

6

Yes, that’s where I’ve been referring to for most of my documentation. Unfortunately, for a pip upgrade, it doesn’t list the READ BEFORE UPDATE (thought I think I have seen that previously).

Either way, for whatever reason, the policyconditions table was never created (and would fail each time). Switching the engine to InnoDB (and maybe removing partitions on the policy table, don’t recall if this helped) allowed the migration to proceed.

This is probably an edge case, I think the default mysql engine is InnoDB now.

But it would also be helpful noting the methods for re-running migrations in docs.

1 Like