Problem upgrading from privacyidea 2.19 to 2.21

ali-axiadids · February 16, 2018, 11:47pm

Platform: Amazon Linux
Database: PostgreSQL 9.5
Current version: privacyidea 2.19
Target version: privacyidea 2.21

Following the steps documented in the link below using privacyidea-pip-update:

http://privacyidea.readthedocs.io/en/latest/installation/upgrade.html?highlight=upgrade

After upgrade, I have encountered error while trying to enroll new token (HOTP, TOTP, SPASS etc).

/opt/privacyidea/local/lib/python2.7/dist-packages/sqlalchemy/sql/sqltypes.py:226: SAWarning: Unicode type received non-unicode bind param value '__timestamp__'. (this warning may be suppressed after 10 occurrences)
      (util.ellipses_string(value),))
/opt/privacyidea/local/lib/python2.7/dist-packages/sqlalchemy/sql/sqltypes.py:226: SAWarning: Unicode type received non-unicode bind param value '__timestamp__'. (this warning may be suppressed after 10 occurrences)
  (util.ellipses_string(value),))
/opt/privacyidea/local/lib/python2.7/dist-packages/sqlalchemy/sql/sqltypes.py:226: SAWarning: Unicode type received non-unicode bind param value '__timestamp__'. (this warning may be suppressed after 10 occurrences)
  (util.ellipses_string(value),))
/opt/privacyidea/local/lib/python2.7/dist-packages/sqlalchemy/sql/sqltypes.py:226: SAWarning: Unicode type received non-unicode bind param value ''. (this warning may be suppressed after 10 occurrences)
  (util.ellipses_string(value),))
/opt/privacyidea/local/lib/python2.7/dist-packages/sqlalchemy/sql/sqltypes.py:226: SAWarning: Unicode type received non-unicode bind param value '__timestamp__'. (this warning may be suppressed after 10 occurrences)
  (util.ellipses_string(value),))
/opt/privacyidea/local/lib/python2.7/dist-packages/sqlalchemy/sql/sqltypes.py:226: SAWarning: Unicode type received non-unicode bind param value 'OATH0000AF29'. (this warning may be suppressed after 10 occurrences)
  (util.ellipses_string(value),))
[2018-02-16 15:07:17,224] ERROR in app: Exception on /token/init [POST]
Traceback (most recent call last):
  File "/opt/privacyidea/local/lib/python2.7/dist-packages/flask/app.py", line 1982, in wsgi_app
    response = self.full_dispatch_request()
  File "/opt/privacyidea/local/lib/python2.7/dist-packages/flask/app.py", line 1614, in full_dispatch_request
    rv = self.handle_user_exception(e)
  File "/opt/privacyidea/local/lib/python2.7/dist-packages/flask/app.py", line 1517, in handle_user_exception
    reraise(exc_type, exc_value, tb)
  File "/opt/privacyidea/local/lib/python2.7/dist-packages/flask/app.py", line 1612, in full_dispatch_request
    rv = self.dispatch_request()
  File "/opt/privacyidea/local/lib/python2.7/dist-packages/flask/app.py", line 1598, in dispatch_request
    return self.view_functions[rule.endpoint](**req.view_args)
  File "/opt/privacyidea/local/lib/python2.7/dist-packages/privacyidea/api/lib/prepolicy.py", line 117, in policy_wrapper
    return wrapped_function(*args, **kwds)
  File "/opt/privacyidea/local/lib/python2.7/dist-packages/privacyidea/api/lib/prepolicy.py", line 117, in policy_wrapper
    return wrapped_function(*args, **kwds)
  File "/opt/privacyidea/local/lib/python2.7/dist-packages/privacyidea/api/lib/prepolicy.py", line 117, in policy_wrapper
    return wrapped_function(*args, **kwds)
  File "/opt/privacyidea/local/lib/python2.7/dist-packages/privacyidea/api/lib/prepolicy.py", line 117, in policy_wrapper
    return wrapped_function(*args, **kwds)
  File "/opt/privacyidea/local/lib/python2.7/dist-packages/privacyidea/api/lib/prepolicy.py", line 117, in policy_wrapper
    return wrapped_function(*args, **kwds)
  File "/opt/privacyidea/local/lib/python2.7/dist-packages/privacyidea/api/lib/prepolicy.py", line 117, in policy_wrapper
    return wrapped_function(*args, **kwds)
  File "/opt/privacyidea/local/lib/python2.7/dist-packages/privacyidea/api/lib/prepolicy.py", line 117, in policy_wrapper
    return wrapped_function(*args, **kwds)
  File "/opt/privacyidea/local/lib/python2.7/dist-packages/privacyidea/api/lib/prepolicy.py", line 117, in policy_wrapper
    return wrapped_function(*args, **kwds)
  File "/opt/privacyidea/local/lib/python2.7/dist-packages/privacyidea/api/lib/prepolicy.py", line 117, in policy_wrapper
    return wrapped_function(*args, **kwds)
  File "/opt/privacyidea/local/lib/python2.7/dist-packages/privacyidea/api/lib/prepolicy.py", line 117, in policy_wrapper
    return wrapped_function(*args, **kwds)
  File "/opt/privacyidea/local/lib/python2.7/dist-packages/privacyidea/api/lib/prepolicy.py", line 117, in policy_wrapper
    return wrapped_function(*args, **kwds)
  File "/opt/privacyidea/local/lib/python2.7/dist-packages/privacyidea/api/lib/prepolicy.py", line 117, in policy_wrapper
    return wrapped_function(*args, **kwds)
  File "/opt/privacyidea/local/lib/python2.7/dist-packages/privacyidea/api/lib/prepolicy.py", line 117, in policy_wrapper
    return wrapped_function(*args, **kwds)
  File "/opt/privacyidea/local/lib/python2.7/dist-packages/privacyidea/api/lib/prepolicy.py", line 117, in policy_wrapper
    return wrapped_function(*args, **kwds)
  File "/opt/privacyidea/local/lib/python2.7/dist-packages/privacyidea/api/lib/postpolicy.py", line 102, in policy_wrapper
    response = wrapped_function(*args, **kwds)
  File "/opt/privacyidea/local/lib/python2.7/dist-packages/privacyidea/lib/subscriptions.py", line 331, in check_subscription_wrapper
    f_result = func(*args, **kwds)
  File "/opt/privacyidea/local/lib/python2.7/dist-packages/privacyidea/lib/event.py", line 60, in event_wrapper
    f_result = func(*args, **kwds)
  File "/opt/privacyidea/local/lib/python2.7/dist-packages/privacyidea/lib/log.py", line 154, in log_wrapper
    return func(*args, **kwds)
  File "/opt/privacyidea/local/lib/python2.7/dist-packages/privacyidea/api/token.py", line 249, in init
    tokenrealms=tokenrealms)
  File "/opt/privacyidea/local/lib/python2.7/dist-packages/privacyidea/lib/log.py", line 154, in log_wrapper
    return func(*args, **kwds)
  File "/opt/privacyidea/local/lib/python2.7/dist-packages/privacyidea/lib/token.py", line 911, in init_token
    db_token.save()
  File "/opt/privacyidea/local/lib/python2.7/dist-packages/privacyidea/models.py", line 72, in save
    db.session.commit()
  File "/opt/privacyidea/local/lib/python2.7/dist-packages/sqlalchemy/orm/scoping.py", line 153, in do
    return getattr(self.registry(), name)(*args, **kwargs)
  File "/opt/privacyidea/local/lib/python2.7/dist-packages/sqlalchemy/orm/session.py", line 943, in commit
    self.transaction.commit()
  File "/opt/privacyidea/local/lib/python2.7/dist-packages/sqlalchemy/orm/session.py", line 467, in commit
    self._prepare_impl()
  File "/opt/privacyidea/local/lib/python2.7/dist-packages/sqlalchemy/orm/session.py", line 447, in _prepare_impl
    self.session.flush()
  File "/opt/privacyidea/local/lib/python2.7/dist-packages/sqlalchemy/orm/session.py", line 2243, in flush
    self._flush(objects)
  File "/opt/privacyidea/local/lib/python2.7/dist-packages/sqlalchemy/orm/session.py", line 2369, in _flush
    transaction.rollback(_capture_exception=True)
  File "/opt/privacyidea/local/lib/python2.7/dist-packages/sqlalchemy/util/langhelpers.py", line 66, in __exit__
    compat.reraise(exc_type, exc_value, exc_tb)
  File "/opt/privacyidea/local/lib/python2.7/dist-packages/sqlalchemy/orm/session.py", line 2333, in _flush
    flush_context.execute()
  File "/opt/privacyidea/local/lib/python2.7/dist-packages/sqlalchemy/orm/unitofwork.py", line 391, in execute
    rec.execute(self)
  File "/opt/privacyidea/local/lib/python2.7/dist-packages/sqlalchemy/orm/unitofwork.py", line 556, in execute
    uow
  File "/opt/privacyidea/local/lib/python2.7/dist-packages/sqlalchemy/orm/persistence.py", line 181, in save_obj
    mapper, table, insert)
  File "/opt/privacyidea/local/lib/python2.7/dist-packages/sqlalchemy/orm/persistence.py", line 866, in _emit_insert_statements
    execute(statement, params)
  File "/opt/privacyidea/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 948, in execute
    return meth(self, multiparams, params)
  File "/opt/privacyidea/local/lib/python2.7/dist-packages/sqlalchemy/sql/elements.py", line 269, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "/opt/privacyidea/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1060, in _execute_clauseelement
    compiled_sql, distilled_params
  File "/opt/privacyidea/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1200, in _execute_context
    context)
  File "/opt/privacyidea/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1413, in _handle_dbapi_exception
    exc_info
  File "/opt/privacyidea/local/lib/python2.7/dist-packages/sqlalchemy/util/compat.py", line 203, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "/opt/privacyidea/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1193, in _execute_context
    context)
  File "/opt/privacyidea/local/lib/python2.7/dist-packages/sqlalchemy/engine/default.py", line 507, in do_execute
    cursor.execute(statement, parameters)
ProgrammingError: (psycopg2.ProgrammingError) relation "token_seq" does not exist
LINE 1: ...ndow, sync_window, rollout_state) VALUES (nextval('token_seq...
                                                             ^
 [SQL: "INSERT INTO token (id, description, serial, tokentype, user_pin, user_pin_iv, so_pin, so_pin_iv, resolver, resolver_type, user_id, pin_seed, otplen, pin_hash, key_enc, key_iv, maxfail, active, revoked, locked, failcount, count, count_window, sync_window, rollout_state) VALUES (nextval('token_seq'), %(description)s, %(serial)s, %(tokentype)s, %(user_pin)s, %(user_pin_iv)s, %(so_pin)s, %(so_pin_iv)s, %(resolver)s, %(resolver_type)s, %(user_id)s, %(pin_seed)s, %(otplen)s, %(pin_hash)s, %(key_enc)s, %(key_iv)s, %(maxfail)s, %(active)s, %(revoked)s, %(locked)s, %(failcount)s, %(count)s, %(count_window)s, %(sync_window)s, %(rollout_state)s) RETURNING token.id"] [parameters: {'key_enc': u'a829[root@: /opt/privacyidea/var/log]$

Here are the upgrade logs:

Running online
Column already exists
(psycopg2.ProgrammingError) column "Type" of relation "tokeninfo" already exists
 [SQL: 'ALTER TABLE tokeninfo ADD COLUMN "Type" VARCHAR(100)'] (Background on this error at: http://sqlalche.me/e/f405)
(psycopg2.ProgrammingError) column "condition" of relation "policy" already exists
 [SQL: 'ALTER TABLE policy ADD COLUMN condition INTEGER NOT NULL'] (Background on this error at: http://sqlalche.me/e/f405)
(psycopg2.ProgrammingError) column "priority" of relation "resolverrealm" already exists
 [SQL: 'ALTER TABLE resolverrealm ADD COLUMN priority INTEGER'] (Background on this error at: http://sqlalche.me/e/f405)
(psycopg2.ProgrammingError) column "adminrealm" of relation "policy" already exists
 [SQL: 'ALTER TABLE policy ADD COLUMN adminrealm VARCHAR(256)'] (Background on this error at: http://sqlalche.me/e/f405)
(psycopg2.ProgrammingError) column "revoked" of relation "token" already exists
 [SQL: 'ALTER TABLE token ADD COLUMN revoked BOOLEAN'] (Background on this error at: http://sqlalche.me/e/f405)
(psycopg2.ProgrammingError) column "locked" of relation "token" already exists
 [SQL: 'ALTER TABLE token ADD COLUMN locked BOOLEAN'] (Background on this error at: http://sqlalche.me/e/f405)
(psycopg2.ProgrammingError) relation "smtpserver" already exists
 [SQL: '\nCREATE TABLE smtpserver (\n\tid SERIAL NOT NULL, \n\tidentifier VARCHAR(255) NOT NULL, \n\tserver VARCHAR(255) NOT NULL, \n\tport INTEGER, \n\tusername VARCHAR(255), \n\tpassword VARCHAR(255), \n\tsender VARCHAR(255), \n\ttls BOOLEAN, \n\tdescription VARCHAR(2000), \n\tPRIMARY KEY (id)\n)\n\n'] (Background on this error at: http://sqlalche.me/e/f405)
(psycopg2.ProgrammingError) relation "passwordreset" already exists
 [SQL: '\nCREATE TABLE passwordreset (\n\tid SERIAL NOT NULL, \n\trecoverycode VARCHAR(255) NOT NULL, \n\tusername VARCHAR(64) NOT NULL, \n\trealm VARCHAR(64) NOT NULL, \n\tresolver VARCHAR(64), \n\temail VARCHAR(255), \n\ttimestamp TIMESTAMP WITHOUT TIME ZONE, \n\texpiration TIMESTAMP WITHOUT TIME ZONE, \n\tPRIMARY KEY (id)\n)\n\n'] (Background on this error at: http://sqlalche.me/e/f405)
(psycopg2.ProgrammingError) relation "radiusserver" already exists
 [SQL: '\nCREATE TABLE radiusserver (\n\tid SERIAL NOT NULL, \n\tidentifier VARCHAR(255) NOT NULL, \n\tserver VARCHAR(255) NOT NULL, \n\tport INTEGER, \n\tsecret VARCHAR(255), \n\tdescription VARCHAR(2000), \n\tPRIMARY KEY (id), \n\tUNIQUE (identifier)\n)\n\n'] (Background on this error at: http://sqlalche.me/e/f405)
(psycopg2.ProgrammingError) column "dictionary" of relation "radiusserver" already exists
 [SQL: 'ALTER TABLE radiusserver ADD COLUMN dictionary VARCHAR(255)'] (Background on this error at: http://sqlalche.me/e/f405)
(psycopg2.ProgrammingError) relation "eventhandler" already exists
 [SQL: '\nCREATE TABLE eventhandler (\n\tid SERIAL NOT NULL, \n\tordering INTEGER NOT NULL, \n\tevent VARCHAR(255) NOT NULL, \n\thandlermodule VARCHAR(255) NOT NULL, \n\tcondition VARCHAR(1024), \n\taction VARCHAR(1024), \n\tPRIMARY KEY (id)\n)\n\n'] (Background on this error at: http://sqlalche.me/e/f405)
Table already exists
(psycopg2.ProgrammingError) relation "smsgateway" already exists
 [SQL: '\nCREATE TABLE smsgateway (\n\tid SERIAL NOT NULL, \n\tidentifier VARCHAR(255) NOT NULL, \n\tdescription VARCHAR(1024), \n\tprovidermodule VARCHAR(1024) NOT NULL, \n\tPRIMARY KEY (id), \n\tUNIQUE (identifier)\n)\n\n'] (Background on this error at: http://sqlalche.me/e/f405)
Table already exists
(psycopg2.ProgrammingError) relation "eventhandlercondition" already exists
 [SQL: '\nCREATE TABLE eventhandlercondition (\n\tid SERIAL NOT NULL, \n\teventhandler_id INTEGER, \n\t"Key" VARCHAR(255) NOT NULL, \n\t"Value" VARCHAR(2000), \n\tcomparator VARCHAR(255), \n\tPRIMARY KEY (id), \n\tFOREIGN KEY(eventhandler_id) REFERENCES eventhandler (id), \n\tCONSTRAINT ehcix_1 UNIQUE (eventhandler_id, "Key")\n)\n\n'] (Background on this error at: http://sqlalche.me/e/f405)
Table already exists
(psycopg2.ProgrammingError) relation "clientapplication" already exists
 [SQL: '\nCREATE TABLE clientapplication (\n\tid SERIAL NOT NULL, \n\tip VARCHAR(255) NOT NULL, \n\thostname VARCHAR(255), \n\tclienttype VARCHAR(255) NOT NULL, \n\tlastseen TIMESTAMP WITHOUT TIME ZONE, \n\tPRIMARY KEY (id), \n\tCONSTRAINT caix UNIQUE (ip, clienttype)\n)\n\n'] (Background on this error at: http://sqlalche.me/e/f405)
Table subscription exists
(psycopg2.ProgrammingError) relation "subscription" already exists
 [SQL: '\nCREATE TABLE subscription (\n\tid SERIAL NOT NULL, \n\tapplication VARCHAR(30), \n\tfor_name VARCHAR(50) NOT NULL, \n\tfor_address VARCHAR(128), \n\tfor_email VARCHAR(128) NOT NULL, \n\tfor_phone VARCHAR(50) NOT NULL, \n\tfor_url VARCHAR(80), \n\tfor_comment VARCHAR(255), \n\tby_name VARCHAR(50) NOT NULL, \n\tby_email VARCHAR(128) NOT NULL, \n\tby_address VARCHAR(128), \n\tby_phone VARCHAR(50), \n\tby_url VARCHAR(80), \n\tdate_from TIMESTAMP WITHOUT TIME ZONE, \n\tdate_till TIMESTAMP WITHOUT TIME ZONE, \n\tnum_users INTEGER, \n\tnum_tokens INTEGER, \n\tnum_clients INTEGER, \n\tlevel VARCHAR(30), \n\tsignature VARCHAR(640), \n\tPRIMARY KEY (id)\n)\n\n'] (Background on this error at: http://sqlalche.me/e/f405)
pidea_audit table obviously already exists.
(psycopg2.ProgrammingError) relation "pidea_audit" already exists
 [SQL: '\nCREATE TABLE pidea_audit (\n\tid SERIAL NOT NULL, \n\tdate TIMESTAMP WITHOUT TIME ZONE, \n\tsignature VARCHAR(620), \n\taction VARCHAR(50), \n\tsuccess INTEGER, \n\tserial VARCHAR(20), \n\ttoken_type VARCHAR(12), \n\t"user" VARCHAR(20), \n\trealm VARCHAR(20), \n\tadministrator VARCHAR(20), \n\taction_detail VARCHAR(50), \n\tinfo VARCHAR(50), \n\tprivacyidea_server VARCHAR(255), \n\tclient VARCHAR(50), \n\tloglevel VARCHAR(12), \n\tclearance_level VARCHAR(12), \n\tPRIMARY KEY (id)\n)\n\n'] (Background on this error at: http://sqlalche.me/e/f405)
column resolver in pidea_audit obviously already exists.
(psycopg2.ProgrammingError) column "resolver" of relation "pidea_audit" already exists
 [SQL: 'ALTER TABLE pidea_audit ADD COLUMN resolver VARCHAR(50)'] (Background on this error at: http://sqlalche.me/e/f405)
Columns name and active already exist.
(psycopg2.ProgrammingError) column "name" of relation "eventhandler" already exists
 [SQL: 'ALTER TABLE eventhandler ADD COLUMN name VARCHAR(64)'] (Background on this error at: http://sqlalche.me/e/f405)
Could not add column 'check_all_resolvers'
(psycopg2.ProgrammingError) column "check_all_resolvers" of relation "policy" already exists
 [SQL: 'ALTER TABLE policy ADD COLUMN check_all_resolvers BOOLEAN'] (Background on this error at: http://sqlalche.me/e/f405)
Could not add index in table pidea_audit.
(psycopg2.ProgrammingError) relation "ix_pidea_audit_user" already exists
 [SQL: 'CREATE INDEX ix_pidea_audit_user ON pidea_audit ("user")'] (Background on this error at: http://sqlalche.me/e/f405)
Could not add index to 'challenge.serial'
(psycopg2.ProgrammingError) relation "ix_challenge_serial" already exists
 [SQL: 'CREATE INDEX ix_challenge_serial ON challenge (serial)'] (Background on this error at: http://sqlalche.me/e/f405)
Could not create table 'usercache'.
(psycopg2.ProgrammingError) relation "usercache" already exists
 [SQL: '\nCREATE TABLE usercache (\n\tid SERIAL NOT NULL, \n\tusername VARCHAR(64), \n\tresolver VARCHAR(120), \n\tuser_id VARCHAR(320), \n\ttimestamp TIMESTAMP WITHOUT TIME ZONE, \n\tPRIMARY KEY (id)\n)\n\n'] (Background on this error at: http://sqlalche.me/e/f405)
## Schema seems already to be oracle compatible.
(psycopg2.ProgrammingError) relation "ix_subscription_application" already exists
 [SQL: 'CREATE INDEX ix_subscription_application ON subscription (application)'] (Background on this error at: http://sqlalche.me/e/f405)
## Unnecessary columns in table usercache obviously do not exist anymore.
(psycopg2.ProgrammingError) column "realm" of relation "usercache" does not exist
 [SQL: 'ALTER TABLE usercache DROP COLUMN realm'] (Background on this error at: http://sqlalche.me/e/f405)

Note: We are able to bypass this issue by dropping the DB and re-creating a fresh one but this is not an acceptable option for our production upgrade. Also, we were able to successfully upgrade privacyidea (2.17, 2.18) in the past but not this time.

Would you please assist?

Thank you.

cornelinux · February 17, 2018, 9:42am

I think we added this with the oracle compatibility in version 2.20.

It looks like for some reason a sequence was not created.

You should be able to do this via:

CREATE SEQUENCE token_seq START <your next id>;

Please respond, if this will solve your problem. BUT: There are a lot of other sequences:

id = db.Column(db.Integer, Sequence("token_seq"),
id = db.Column(db.Integer, Sequence("tokeninfo_seq"), primary_key=True)
id = db.Column(db.Integer, Sequence("realm_seq"), primary_key=True,
id = db.Column(db.Integer, Sequence("caconnector_seq"), primary_key=True,
id = db.Column(db.Integer, Sequence("caconfig_seq"), primary_key=True)
id = db.Column(db.Integer, Sequence("resolver_seq"), primary_key=True,
id = db.Column(db.Integer, Sequence("resolverconf_seq"), primary_key=True)
id = db.Column(db.Integer, Sequence("resolverrealm_seq"), primary_key=True)
id = db.Column(db.Integer(), Sequence("tokenrealm_seq"), primary_key=True,
id = db.Column(db.Integer(), Sequence("pwreset_seq"), primary_key=True,
id = db.Column(db.Integer(), Sequence("challenge_seq"), primary_key=True,
id = db.Column(db.Integer, Sequence("policy_seq"), primary_key=True)
id = db.Column(db.Integer(), Sequence("machinetoken_seq"),
id = db.Column(db.Integer(), Sequence("machtokenopt_seq"),
id = db.Column(db.Integer, Sequence("eventhandler_seq"), primary_key=True,
id = db.Column(db.Integer, Sequence("eventhandlercond_seq"),
id = db.Column(db.Integer, Sequence("eventhandleropt_seq"),
id = db.Column(db.Integer, Sequence("machineresolver_seq"),
id = db.Column(db.Integer, Sequence("machineresolverconf_seq"),
id = db.Column(db.Integer, Sequence("smsgateway_seq"), primary_key=True)
id = db.Column(db.Integer, Sequence("smsgwoption_seq"), primary_key=True)
id = db.Column(db.Integer, Sequence("privacyideaserver_seq"),
id = db.Column(db.Integer, Sequence("radiusserver_seq"), primary_key=True)
id = db.Column(db.Integer, Sequence("smtpserver_seq"),primary_key=True)
id = db.Column(db.Integer, Sequence("clientapp_seq"), primary_key=True)
id = db.Column(db.Integer, Sequence("subscription_seq"), primary_key=True)
id = db.Column(db.Integer, Sequence("audit_seq"), primary_key=True)
id = db.Column(db.Integer, Sequence("usercache_seq"), primary_key=True)
id = db.Column(db.Integer, Sequence("usercache_seq"), primary_key=True)

roughly for each table. Either you add them manually or we figure out a better way…

lavesh-axiadids · February 20, 2018, 8:45pm

Below is the list of sequences present in db:-

But in models.py file, sequences names present are:-

audit_seq
caconfig_seq
caconnector_seq
challenge_seq
clientapp_seq
eventhandler_seq
eventhandlercond_seq
eventhandleropt_seq
machineresolver_seq
machineresolverconf_seq
machinetoken_seq
machtokenopt_seq
policy_seq
privacyideaserver_seq
pwreset_seq
radiusserver_seq
realm_seq
resolver_seq
resolverconf_seq
resolverrealm_seq
smsgateway_seq
smsgwoption_seq
smtpserver_seq
subscription_seq
token_seq
tokeninfo_seq
tokenrealm_seq
usercache_seq

This might be the issue

lavesh-axiadids · February 20, 2018, 8:56pm

After creating the below sequences I was able to enrol tokens. START VALUE should be greater than previous ids of respective tables.

privacyidea=> CREATE SEQUENCE token_seq START 5;
CREATE SEQUENCE
privacyidea=>
privacyidea=>
privacyidea=> CREATE SEQUENCE tokenrealm_seq START 8 INCREMENT BY 2;;
CREATE SEQUENCE
privacyidea=> CREATE SEQUENCE tokeninfo_seq START 5;
CREATE SEQUENCE

cornelinux · February 21, 2018, 9:25am

I think I understand the problem.

postgres created the sequences automatically and named them token_id_seq. The problem is, that Oracle does not create sequences automatically. This is why we added them to the schema in version 2.20.

This does work fine with MySQL, MariaDB, Oracle and SQLite.

Perfect, you recreated new sequences and you have to take care about, that the new sequence number is higher than the existing one - just as you mentioned.

I would prefer to be able to rename the sequences, this would be probably the easier solution, which does not include searching for old values of the old sequences. I will have a look at that.

According to https://www.postgresql.org/docs/9.1/static/sql-altersequence.html you should also be able to do

ALTER SEQUENCE audit_id_seq RENAME TO audit_seq;
ALTER SEQUENCE caconfig_id_seq RENAME TO caconfig_seq;
ALTER SEQUENCE caconnector_id_seq RENAME TO caconnector_seq;
ALTER SEQUENCE challenge_id_seq RENAME TO challenge_seq;
ALTER SEQUENCE clientapp_id_seq RENAME TO clientapp_seq;
ALTER SEQUENCE eventhandler_id_seq RENAME TO eventhandler_seq;
ALTER SEQUENCE machineresolver_id_seq RENAME TO machineresolver_seq;
ALTER SEQUENCE machineresolverconf_id_seq RENAME TO machineresolverconf_seq;
ALTER SEQUENCE machinetoken_id_seq RENAME TO machine_seq;
ALTER SEQUENCE machtokenopt_id_seq RENAME TO machtokenopt_seq;
ALTER SEQUENCE policy_id_seq RENAME TO policy_seq;
ALTER SEQUENCE privacyideaserver_id_seq RENAME TO privacyideaserver_seq;
ALTER SEQUENCE pwreset_id_seq RENAME TO pwreset_seq;
ALTER SEQUENCE radiusserver_id_seq RENAME TO radiusserver_seq;
ALTER SEQUENCE realm_id_seq RENAME TO realm_seq;
ALTER SEQUENCE resolver_id_seq RENAME TO resolver_seq;
ALTER SEQUENCE resolverconf_id_seq RENAME TO resolverconf_seq;
ALTER SEQUENCE resolverrealm_id_seq RENAME TO resolverrealm_seq;
ALTER SEQUENCE smsgateway_id_seq RENAME TO smsgateway_seq;
ALTER SEQUENCE smsgwoption_id_seq RENAME TO smsgwoption_seq;
ALTER SEQUENCE smtpserver_id_seq RENAME TO smtpserver_seq;
ALTER SEQUENCE subscription_id_seq RENAME TO subscription_seq;
ALTER SEQUENCE token_id_seq RENAME TO token_seq;
ALTER SEQUENCE tokeninfo_id_seq RENAME TO tokeninfo_seq;
ALTER SEQUENCE usercache_id_seq RENAME TO usercache_seq;
ALTER SEQUENCE authcache_id_seq RENAME TO authcache_seq;

I will check, if these are all right. Some tables were not available in 2.19.

cornelinux · February 21, 2018, 10:09am

Ok, I reproduced and tested the following.

Install privacyIDEA 2.19.1 in a pip environment and postgres server.

pip install privacyidea==2.19.1

Sequences with privacyIDEA 2.19.1

pi=> \ds
                       List of relations
 Schema |             Name             |   Type   |    Owner    
--------+------------------------------+----------+-------------
 public | caconnector_id_seq           | sequence | privacyidea
 public | caconnectorconfig_id_seq     | sequence | privacyidea
 public | challenge_id_seq             | sequence | privacyidea
 public | clientapplication_id_seq     | sequence | privacyidea
 public | eventhandler_id_seq          | sequence | privacyidea
 public | eventhandlercondition_id_seq | sequence | privacyidea
 public | eventhandleroption_id_seq    | sequence | privacyidea
 public | machineresolver_id_seq       | sequence | privacyidea
 public | machineresolverconfig_id_seq | sequence | privacyidea
 public | machinetoken_id_seq          | sequence | privacyidea
 public | machinetokenoptions_id_seq   | sequence | privacyidea
 public | passwordreset_id_seq         | sequence | privacyidea
 public | pidea_audit_id_seq           | sequence | privacyidea
 public | policy_id_seq                | sequence | privacyidea
 public | radiusserver_id_seq          | sequence | privacyidea
 public | realm_id_seq                 | sequence | privacyidea
 public | resolver_id_seq              | sequence | privacyidea
 public | resolverconfig_id_seq        | sequence | privacyidea
 public | resolverrealm_id_seq         | sequence | privacyidea
 public | smsgateway_id_seq            | sequence | privacyidea
 public | smsgatewayoption_id_seq      | sequence | privacyidea
 public | smtpserver_id_seq            | sequence | privacyidea
 public | subscription_id_seq          | sequence | privacyidea
 public | token_id_seq                 | sequence | privacyidea
 public | tokeninfo_id_seq             | sequence | privacyidea
 public | tokenrealm_id_seq            | sequence | privacyidea
 public | usercache_id_seq             | sequence | privacyidea
(27 rows)

Update to privacyIDEA 2.21.4:

pip install --uppgrade  privacyidea

Note: Now you should fix the versions of ldap3 and pyasn1. Running an update
will install to new versions.

pip install ldap3==2.1.1
pip install pyasn1==0.1.9

Now we need to update the database schema:

pi-manage db stamp 4f32a4e1bf33 -d lib/privacyidea/migrations
pi-manage db upgrade -d lib/privacyidea/migrations

Now we will run into the sequence problem, when creating a token.

Run the following commands in psql:

ALTER SEQUENCE audit_id_seq RENAME TO audit_seq;
ALTER SEQUENCE caconfig_id_seq RENAME TO caconfig_seq;
ALTER SEQUENCE caconnector_id_seq RENAME TO caconnector_seq;
ALTER SEQUENCE challenge_id_seq RENAME TO challenge_seq;
ALTER SEQUENCE clientapp_id_seq RENAME TO clientapp_seq;
ALTER SEQUENCE eventhandler_id_seq RENAME TO eventhandler_seq;
ALTER SEQUENCE machineresolver_id_seq RENAME TO machineresolver_seq;
ALTER SEQUENCE machineresolverconf_id_seq RENAME TO machineresolverconf_seq;
ALTER SEQUENCE machinetoken_id_seq RENAME TO machine_seq;
ALTER SEQUENCE machtokenopt_id_seq RENAME TO machtokenopt_seq;
ALTER SEQUENCE policy_id_seq RENAME TO policy_seq;
ALTER SEQUENCE privacyideaserver_id_seq RENAME TO privacyideaserver_seq;
ALTER SEQUENCE pwreset_id_seq RENAME TO pwreset_seq;
ALTER SEQUENCE radiusserver_id_seq RENAME TO radiusserver_seq;
ALTER SEQUENCE realm_id_seq RENAME TO realm_seq;
ALTER SEQUENCE resolver_id_seq RENAME TO resolver_seq;
ALTER SEQUENCE resolverconf_id_seq RENAME TO resolverconf_seq;
ALTER SEQUENCE resolverrealm_id_seq RENAME TO resolverrealm_seq;
ALTER SEQUENCE smsgateway_id_seq RENAME TO smsgateway_seq;
ALTER SEQUENCE smsgwoption_id_seq RENAME TO smsgwoption_seq;
ALTER SEQUENCE smtpserver_id_seq RENAME TO smtpserver_seq;
ALTER SEQUENCE subscription_id_seq RENAME TO subscription_seq;
ALTER SEQUENCE token_id_seq RENAME TO token_seq;
ALTER SEQUENCE tokeninfo_id_seq RENAME TO tokeninfo_seq;
ALTER SEQUENCE usercache_id_seq RENAME TO usercache_seq;
ALTER SEQUENCE authcache_id_seq RENAME TO authcache_seq;

Everything should be fine again.

lavesh-axiadids · February 22, 2018, 1:44pm

WRONG
ALTER SEQUENCE audit_id_seq RENAME TO audit_seq;
CORRECT
CREATE SEQUENCE audit_seq START WITH 1;

WRONG
ALTER SEQUENCE caconfig_id_seq RENAME TO caconfig_seq;
CORRECT
ALTER SEQUENCE caconnectorconfig_id_seq RENAME TO caconfig_seq;

ALTER SEQUENCE caconnector_id_seq RENAME TO caconnector_seq;
ALTER SEQUENCE challenge_id_seq RENAME TO challenge_seq;

WRONG
ALTER SEQUENCE clientapp_id_seq RENAME TO clientapp_seq;
CORRECT
ALTER SEQUENCE clientapplication_id_seq RENAME TO clientapp_seq;

ALTER SEQUENCE eventhandler_id_seq RENAME TO eventhandler_seq;
ALTER SEQUENCE machineresolver_id_seq RENAME TO machineresolver_seq;
ALTER SEQUENCE machineresolverconf_id_seq RENAME TO machineresolverconf_seq;
ALTER SEQUENCE machinetoken_id_seq RENAME TO machine_seq;

WRONG
ALTER SEQUENCE machtokenopt_id_seq RENAME TO machtokenopt_seq;
CORRECT
ALTER SEQUENCE machinetokenoptions_id_seq RENAME TO machtokenopt_seq;

ALTER SEQUENCE policy_id_seq RENAME TO policy_seq;
ALTER SEQUENCE privacyideaserver_id_seq RENAME TO privacyideaserver_seq;

WRONG
ALTER SEQUENCE pwreset_id_seq RENAME TO pwreset_seq;
CORRECT
ALTER SEQUENCE passwordreset_id_seq RENAME TO pwreset_seq;

ALTER SEQUENCE radiusserver_id_seq RENAME TO radiusserver_seq;
ALTER SEQUENCE realm_id_seq RENAME TO realm_seq;
ALTER SEQUENCE resolver_id_seq RENAME TO resolver_seq;

WRONG
ALTER SEQUENCE resolverconf_id_seq RENAME TO resolverconf_seq;
CORRECT
ALTER SEQUENCE resolverconfig_id_seq RENAME TO resolverconf_seq;

ALTER SEQUENCE resolverrealm_id_seq RENAME TO resolverrealm_seq;
ALTER SEQUENCE smsgateway_id_seq RENAME TO smsgateway_seq;

WRONG
ALTER SEQUENCE smsgwoption_id_seq RENAME TO smsgwoption_seq;
CORRECT
ALTER SEQUENCE smsgatewayoption_id_seq RENAME TO smsgwoption_seq;

ALTER SEQUENCE smtpserver_id_seq RENAME TO smtpserver_seq;
ALTER SEQUENCE subscription_id_seq RENAME TO subscription_seq;
ALTER SEQUENCE token_id_seq RENAME TO token_seq;
ALTER SEQUENCE tokeninfo_id_seq RENAME TO tokeninfo_seq;
ALTER SEQUENCE usercache_id_seq RENAME TO usercache_seq;
ALTER SEQUENCE authcache_id_seq RENAME TO authcache_seq;

lavesh-axiadids · February 22, 2018, 1:47pm

ADDING

ALTER SEQUENCE tokenrealm_id_seq RENAME TO tokenrealm_seq;

lavesh-axiadids · February 23, 2018, 11:28am

Correct ALTER statements

ALTER SEQUENCE authcache_id_seq RENAME TO authcache_seq;
ALTER SEQUENCE pidea_audit_id_seq RENAME TO audit_seq;
ALTER SEQUENCE caconnectorconfig_id_seq RENAME TO caconfig_seq;
ALTER SEQUENCE caconnector_id_seq RENAME TO caconnector_seq;
ALTER SEQUENCE challenge_id_seq RENAME TO challenge_seq;
ALTER SEQUENCE clientapplication_id_seq RENAME TO clientapp_seq;
ALTER SEQUENCE eventhandler_id_seq RENAME TO eventhandler_seq;
ALTER SEQUENCE eventhandlercondition_id_seq RENAME TO eventhandlercond_seq;
ALTER SEQUENCE eventhandleroption_id_seq RENAME TO eventhandleropt_seq;
ALTER SEQUENCE machineresolver_id_seq RENAME TO machineresolver_seq;
ALTER SEQUENCE machineresolverconf_id_seq RENAME TO machineresolverconf_seq;
ALTER SEQUENCE machinetoken_id_seq RENAME TO machinetoken_seq;
ALTER SEQUENCE machinetokenoptions_id_seq RENAME TO machtokenopt_seq;
ALTER SEQUENCE policy_id_seq RENAME TO policy_seq;
ALTER SEQUENCE privacyideaserver_id_seq RENAME TO privacyideaserver_seq;
ALTER SEQUENCE passwordreset_id_seq RENAME TO pwreset_seq;
ALTER SEQUENCE radiusserver_id_seq RENAME TO radiusserver_seq;
ALTER SEQUENCE realm_id_seq RENAME TO realm_seq;
ALTER SEQUENCE resolver_id_seq RENAME TO resolver_seq;
ALTER SEQUENCE resolverconfig_id_seq RENAME TO resolverconf_seq;
ALTER SEQUENCE resolverrealm_id_seq RENAME TO resolverrealm_seq;
ALTER SEQUENCE smsgateway_id_seq RENAME TO smsgateway_seq;
ALTER SEQUENCE smsgatewayoption_id_seq RENAME TO smsgwoption_seq;
ALTER SEQUENCE smtpserver_id_seq RENAME TO smtpserver_seq;
ALTER SEQUENCE subscription_id_seq RENAME TO subscription_seq;
ALTER SEQUENCE token_id_seq RENAME TO token_seq;
ALTER SEQUENCE tokeninfo_id_seq RENAME TO tokeninfo_seq;
ALTER SEQUENCE tokenrealm_id_seq RENAME TO tokenrealm_seq;
ALTER SEQUENCE usercache_id_seq RENAME TO usercache_seq;

cornelinux · February 23, 2018, 7:53pm

Thanks a lot for bringing this up and modifying accordingly!

berndnicklas · April 20, 2018, 1:03pm

Hi,

just stumbled upon this while upgrading our PostgreSQL database schema

I think this should be:

ALTER SEQUENCE machineresolverconfig_id_seq RENAME TO machineresolverconfig_seq;

So the complete list would be:

ALTER SEQUENCE authcache_id_seq RENAME TO authcache_seq;
ALTER SEQUENCE pidea_audit_id_seq RENAME TO audit_seq;
ALTER SEQUENCE caconnectorconfig_id_seq RENAME TO caconfig_seq;
ALTER SEQUENCE caconnector_id_seq RENAME TO caconnector_seq;
ALTER SEQUENCE challenge_id_seq RENAME TO challenge_seq;
ALTER SEQUENCE clientapplication_id_seq RENAME TO clientapp_seq;
ALTER SEQUENCE eventhandler_id_seq RENAME TO eventhandler_seq;
ALTER SEQUENCE eventhandlercondition_id_seq RENAME TO eventhandlercond_seq;
ALTER SEQUENCE eventhandleroption_id_seq RENAME TO eventhandleropt_seq;
ALTER SEQUENCE machineresolver_id_seq RENAME TO machineresolver_seq;
ALTER SEQUENCE machineresolverconfig_id_seq RENAME TO machineresolverconfig_seq;
ALTER SEQUENCE machinetoken_id_seq RENAME TO machinetoken_seq;
ALTER SEQUENCE machinetokenoptions_id_seq RENAME TO machtokenopt_seq;
ALTER SEQUENCE policy_id_seq RENAME TO policy_seq;
ALTER SEQUENCE privacyideaserver_id_seq RENAME TO privacyideaserver_seq;
ALTER SEQUENCE passwordreset_id_seq RENAME TO pwreset_seq;
ALTER SEQUENCE radiusserver_id_seq RENAME TO radiusserver_seq;
ALTER SEQUENCE realm_id_seq RENAME TO realm_seq;
ALTER SEQUENCE resolver_id_seq RENAME TO resolver_seq;
ALTER SEQUENCE resolverconfig_id_seq RENAME TO resolverconf_seq;
ALTER SEQUENCE resolverrealm_id_seq RENAME TO resolverrealm_seq;
ALTER SEQUENCE smsgateway_id_seq RENAME TO smsgateway_seq;
ALTER SEQUENCE smsgatewayoption_id_seq RENAME TO smsgwoption_seq;
ALTER SEQUENCE smtpserver_id_seq RENAME TO smtpserver_seq;
ALTER SEQUENCE subscription_id_seq RENAME TO subscription_seq;
ALTER SEQUENCE token_id_seq RENAME TO token_seq;
ALTER SEQUENCE tokeninfo_id_seq RENAME TO tokeninfo_seq;
ALTER SEQUENCE tokenrealm_id_seq RENAME TO tokenrealm_seq;
ALTER SEQUENCE usercache_id_seq RENAME TO usercache_seq;