SQL Error during update privacyidea-apache2 Version 2.9.1

[RickP]

On Ubuntu just had installed 2.8.1 week before last and saw the 2.9.1 released, this is on our test/dev instances so figured we would update to support the LDAP machine resolver list function.

# dpkg -s python-privacyidea | grep Version

Version: 2.8-1trusty

# dpkg -s privacyidea-apache2 | grep Version

Version: 2.8.1-1trusty

# apt-get install python-privacyidea 

      - no errors 

# dpkg -s python-privacyidea | grep Version

Version: 2.9-1trusty

But when updating privacyidea-apache2 the installer hangs trying to do some DB update for version_num?

Traceback (most recent call last):

  File "/usr/bin/pi-manage", line 606, in <module>

    manager.run()

  File "/usr/lib/python2.7/dist-packages/flask_script/__init__.py", line 405, in run

    result = self.handle(sys.argv[0], sys.argv[1:])

  File "/usr/lib/python2.7/dist-packages/flask_script/__init__.py", line 384, in handle

    return handle(app, *positional_args, **kwargs)

  File "/usr/lib/python2.7/dist-packages/flask_script/commands.py", line 145, in handle

    return self.run(*args, **kwargs)

  File "/usr/lib/python2.7/dist-packages/flask_migrate/__init__.py", line 98, in upgrade

    command.upgrade(config, revision, sql = sql, tag = tag)

  File "/usr/lib/python2.7/dist-packages/alembic/command.py", line 124, in upgrade

    script.run_env()

  File "/usr/lib/python2.7/dist-packages/alembic/script.py", line 199, in run_env

    util.load_python_file(self.dir, 'env.py')

  File "/usr/lib/python2.7/dist-packages/alembic/util.py", line 199, in load_python_file

    module = load_module(module_id, path)

  File "/usr/lib/python2.7/dist-packages/alembic/compat.py", line 55, in load_module

    mod = imp.load_source(module_id, path, fp)

  File "/usr/lib/privacyidea/migrations/env.py", line 72, in <module>

    run_migrations_online()

  File "/usr/lib/privacyidea/migrations/env.py", line 65, in run_migrations_online

    context.run_migrations()

  File "<string>", line 7, in run_migrations

  File "/usr/lib/python2.7/dist-packages/alembic/environment.py", line 652, in run_migrations

    self.get_context().run_migrations(**kw)

  File "/usr/lib/python2.7/dist-packages/alembic/migration.py", line 232, in run_migrations

    self._update_current_rev(current_rev, rev)

  File "/usr/lib/python2.7/dist-packages/alembic/migration.py", line 182, in _update_current_rev

    values(version_num=literal_column("'%s'" % new))

  File "/usr/lib/python2.7/dist-packages/alembic/ddl/impl.py", line 76, in _exec

    conn.execute(construct, *multiparams, **params)

  File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 662, in execute

    params)

  File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 761, in _execute_clauseelement

    compiled_sql, distilled_params

  File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 874, in _execute_context

    context)

  File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1024, in _handle_dbapi_exception

    exc_info

  File "/usr/lib/python2.7/dist-packages/sqlalchemy/util/compat.py", line 196, in raise_from_cause

    reraise(type(exception), exception, tb=exc_tb)

  File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 867, in _execute_context

    context)

  File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/default.py", line 324, in do_execute

    cursor.execute(statement, parameters)

sqlalchemy.exc.InternalError: (InternalError) current transaction is aborted, commands ignored until end of transaction block

 "UPDATE alembic_version SET version_num='20969b4cbf06'" {}

[RickP]

One additional detail, we are using a postgres database on a separate host, the URI connection string is in the pi.cfg file and is functioning in the current 2.8.1 version

[Cornelius Kölbel]

Didn't you run apt-get upgrade or apt-get dist-upgrade?

What is the status of the package and how does your alembic_version
table look like.

Is it a single postgres database on the other server?

You could however remove privacyidea-apache2 (backup /etc/privacyidea)
and reinstall it.

> --
> You received this message because you are subscribed to the Google
> Groups "privacyidea" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to privacyidea...@googlegroups.com.
> To post to this group, send email to priva...@googlegroups.com.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/privacyidea/8ff69969-ae41-4bbe-a683-ff2dd869f8b5%40googlegroups.com.
> For more options, visit https://groups.google.com/d/optout.

--
Cornelius Kölbel
corneliu...@netknights.it
+49 151 2960 1417

NetKnights GmbH
http://www.netknights.it
Landgraf-Karl-Str. 19, 34131 Kassel, Germany
Tel: +49 561 3166797, Fax: +49 561 3166798

Amtsgericht Kassel, HRB 16405
Geschäftsführer: Cornelius Kölbel

[RickP]

yes first I ran apt-get update which is what then pointed it at the 2.9 versions.

I rolled it back via snapshots to the state prior to the upgrades using VMware snapshots.

One way I know I can get around it is starting the local mySQL instance up, moving the /etc/privacyidea folder to /etc/privacyidea.ORG and then doing the updates. 

But leaving the /etc/privacyidea/ folder in place hangs at that trying to do something in the DB step.

** Is there things that should be done before trying to update I see some reference to other version updates here but nothing with the versions we are moving between.

   http://privacyidea.readthedocs.org/en/latest/installation/upgrade.html

I will get the info on the alemic_version table from the DBA team.

[Cornelius Kölbel]

Hello Rick,

the database scheme 20969b4cbf06 was introduced in version 2.6!
When the columns "revoked" and "locked" were added to the token table.

I am wondering, what your database looks like at the moment.
What the alembic version is and which columns exist in your token table.

The update script between the versions just does the following:


# Set the version to the first PI 2.0 version
pi-manage db stamp 4f32a4e1bf33 -d /usr/lib/privacyidea/migrations > /dev/null
# Upgrade the database
pi-manage db upgrade -d /usr/lib/privacyidea/migrations > /dev/null

It resets the version to the first 2.0 version (as we do not know, at which version we are at the moment) and then upgrades it to the latest.

But: If you are running on postgres on another server, you might consider to not use the package
privacyidea-apache2!
I would recommend to only use python-privacyidea, do the apache config manually on have no issue with the postgres DB. When upgrading python-privacyidea you
need to run the above commands manually.

Kind regards
Cornelius

Am Montag, den 28.12.2015, 11:32 -0800 schrieb RickP:

[RickP]

 version_num

--------------

 4f32a4e1bf33

[Cornelius Kölbel]

This belongs to the first version for PI 2.0.

As mentioned in my earlier mail, I recommend dumping privacyidea-apache2
(since this requires mysql), only using python-privacyidea and upgrade
the database manually = means more control.

If you need any help on this, please contact me privately.

Kind regards
Cornelius

Am Dienstag, den 29.12.2015, 14:35 -0800 schrieb RickP:
> version_num
> --------------
> 4f32a4e1bf33

>
>
>
>
> --
> You received this message because you are subscribed to the Google
> Groups "privacyidea" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to privacyidea...@googlegroups.com.
> To post to this group, send email to priva...@googlegroups.com.
> To view this discussion on the web visit

> https://groups.google.com/d/msgid/privacyidea/7bbec8c7-d1a0-4b2d-aee4-512c5e85cdff%40googlegroups.com.

[RickP]

Here is the output of attempting the db upgrade manually twice.

# pi-manage db stamp 4f32a4e1bf33 -d /usr/lib/privacyidea/migrations > /dev/null

No handlers could be found for logger "privacyidea.lib.stats"

# pi-manage db upgrade -d /usr/lib/privacyidea/migrations > /dev/null

No handlers could be found for logger "privacyidea.lib.stats"

Second Attempt... apparently the first run did add some fields to tables.

#: /var/log/privacyidea# pi-manage db upgrade -d /usr/lib/privacyidea/migrations

No handlers could be found for logger "privacyidea.lib.stats"

The configuration name is: production

Additional configuration can be read from the file /etc/privacyidea/pi.cfg

Reading Logging settings from /etc/privacyidea/logging.cfg

             _                    _______  _______

   ___  ____(_)  _____ _______ __/  _/ _ \/ __/ _ |

  / _ \/ __/ / |/ / _ `/ __/ // // // // / _// __ |

 / .__/_/ /_/|___/\_,_/\__/\_, /___/____/___/_/ |_|

/_/                       /___/

Could not add the column 'Type' to table tokeninfo

(ProgrammingError) column "Type" of relation "tokeninfo" already exists

 'ALTER TABLE tokeninfo ADD COLUMN "Type" VARCHAR(100)' {}

Could not add column 'condition' to table 'policy'

(InternalError) current transaction is aborted, commands ignored until end of transaction block

 'ALTER TABLE policy ADD COLUMN condition INTEGER NOT NULL' {}

Could not add column 'priority' to table 'resolverrealm'

(InternalError) current transaction is aborted, commands ignored until end of transaction block

 'ALTER TABLE resolverrealm ADD COLUMN priority INTEGER' {}

Could not add the column 'adminrealm' to table policy

(InternalError) current transaction is aborted, commands ignored until end of transaction block

 'ALTER TABLE policy ADD COLUMN adminrealm VARCHAR(256)' {}

Could not add column 'revoked' to table 'token'

(InternalError) current transaction is aborted, commands ignored until end of transaction block

 'ALTER TABLE token ADD COLUMN revoked BOOLEAN NOT NULL' {}

Could not add column 'locked' to table 'token'

(InternalError) current transaction is aborted, commands ignored until end of transaction block

 'ALTER TABLE token ADD COLUMN locked BOOLEAN NOT NULL' {}

[Cornelius Kölbel]

Hello Rick,

I think it is due to the postgres driver handling exceptions in a
different way.

Can you please submit an issue at github with as many information like
at least:

* basic information (postgres server, pg version, pg authentication way)
* This traceback
* you connection string from pi.cfg (of course without credentials)
* upgrade from which version to which version of privacyIDEA
* commands used to upgrade

I will see how I have time to look into this after my vacation.

(As a workaround: You could also check the database in which state it is
(which tables and columns exist) and set the alembic version manually
and only run the required migration scripts.)

Thanks a lot and kind regards
Cornelius

> --
> You received this message because you are subscribed to the Google
> Groups "privacyidea" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to privacyidea...@googlegroups.com.
> To post to this group, send email to priva...@googlegroups.com.
> To view this discussion on the web visit

> https://groups.google.com/d/msgid/privacyidea/b5e7fe26-94ab-45dc-a90b-d9572ce3704e%40googlegroups.com.

[RickP]

I will post as much as I can remember and trace back on github.

Although I am able to pull keys via the command line privacyidea-authorized keys, I cant authenticate as Im now getting a uuencode error see below from sshd

# privacyidea-authorizedkeys username

ssh-rsa AAAA.....keyvalue data removed........BXfB1bHFIpfu+LnMlb4WHi+EkrIrCoII/tpRDKyIAXCXiPAeR username

auth.log errors:

Dec 30 13:55:12 NWSLPIDEA001 sshd[7254]: message repeated 3 times: [ error: key_read: uudecode AAAA.....keyvalue data removed....pBXfB1bHFIpfu+LnMlb4WHi+EkrIrCoII/tpRDKyIAXCXiPAeR e116284\n failed]

This seems familiar but I don't recall the previous issue/fix.