privacyidea-pip-update DB schema upgrade errors?

[Kris Lou]

I just upgraded my PI environment from 2.17 to 2.18.1 with the upgrade script, but the following errors were logged.  They seem innocent enough, and I've run the script twice with the 2nd time showing all dependencies installed but with obviously the same mysql errors.

Is this the end of the script?  Is there a "success" notification, or at least a "you're done?"  If not, it'd be nice!

<snip>
Installing collected packages: colorama
  Found existing installation: colorama 0.3.7
    Uninstalling colorama-0.3.7:
      Successfully uninstalled colorama-0.3.7
Successfully installed colorama-0.3.9
Requirement already up-to-date: click in ./lib/python2.7/site-packages
Requirement already up-to-date: cffi in ./lib/python2.7/site-packages
Requirement already up-to-date: pycparser in ./lib/python2.7/site-packages (from cffi)
Requirement already up-to-date: beautifulsoup4 in ./lib/python2.7/site-packages
Requirement already up-to-date: bcrypt in ./lib/python2.7/site-packages
Requirement already up-to-date: six>=1.4.1 in ./lib/python2.7/site-packages (from bcrypt)
Requirement already up-to-date: cffi>=1.1 in ./lib/python2.7/site-packages (from bcrypt)
Requirement already up-to-date: pycparser in ./lib/python2.7/site-packages (from cffi>=1.1->bcrypt)
Requirement already up-to-date: appdirs in ./lib/python2.7/site-packages
Requirement already up-to-date: alembic in ./lib/python2.7/site-packages
Requirement already up-to-date: SQLAlchemy>=0.7.6 in ./lib/python2.7/site-packages (from alembic)
Requirement already up-to-date: Mako in ./lib/python2.7/site-packages (from alembic)
Requirement already up-to-date: python-editor>=0.3 in ./lib/python2.7/site-packages (from alembic)
Requirement already up-to-date: MarkupSafe>=0.9.2 in ./lib/python2.7/site-packages (from Mako->alembic)
No handlers could be found for logger "privacyidea.lib.stats"
/opt/privacyIDEA/lib/python2.7/site-packages/privacyidea/app.py:58: ExtDeprecationWarning: Importing flask.ext.migrate is deprecated, use flask_migrate instead.
  from flask.ext.migrate import Migrate
/opt/privacyIDEA/lib/python2.7/site-packages/flask_sqlalchemy/__init__.py:839: FSADeprecationWarning: SQLALCHEMY_TRACK_MODIFICATIONS adds significant overhead and will be disabled by default in the future.  Set it to True or False to suppress this warning.
  'SQLALCHEMY_TRACK_MODIFICATIONS adds significant overhead and '
/opt/privacyIDEA/lib/python2.7/site-packages/flask_sqlalchemy/__init__.py:839: FSADeprecationWarning: SQLALCHEMY_TRACK_MODIFICATIONS adds significant overhead and will be disabled by default in the future.  Set it to True or False to suppress this warning.
  'SQLALCHEMY_TRACK_MODIFICATIONS adds significant overhead and '
             _                    _______  _______
   ___  ____(_)  _____ _______ __/  _/ _ \/ __/ _ |
  / _ \/ __/ / |/ / _ `/ __/ // // // // / _// __ |
 / .__/_/ /_/|___/\_,_/\__/\_, /___/____/___/_/ |_|
/_/                       /___/
Running online
No handlers could be found for logger "privacyidea.lib.stats"
/opt/privacyIDEA/lib/python2.7/site-packages/privacyidea/app.py:58: ExtDeprecationWarning: Importing flask.ext.migrate is deprecated, use flask_migrate instead.
  from flask.ext.migrate import Migrate
/opt/privacyIDEA/lib/python2.7/site-packages/flask_sqlalchemy/__init__.py:839: FSADeprecationWarning: SQLALCHEMY_TRACK_MODIFICATIONS adds significant overhead and will be disabled by default in the future.  Set it to True or False to suppress this warning.
  'SQLALCHEMY_TRACK_MODIFICATIONS adds significant overhead and '
/opt/privacyIDEA/lib/python2.7/site-packages/flask_sqlalchemy/__init__.py:839: FSADeprecationWarning: SQLALCHEMY_TRACK_MODIFICATIONS adds significant overhead and will be disabled by default in the future.  Set it to True or False to suppress this warning.
  'SQLALCHEMY_TRACK_MODIFICATIONS adds significant overhead and '
             _                    _______  _______
   ___  ____(_)  _____ _______ __/  _/ _ \/ __/ _ |
  / _ \/ __/ / |/ / _ `/ __/ // // // // / _// __ |
 / .__/_/ /_/|___/\_,_/\__/\_, /___/____/___/_/ |_|
/_/                       /___/
Running online
Column already exists
(_mysql_exceptions.OperationalError) (1060, "Duplicate column name 'Type'") [SQL: u'ALTER TABLE tokeninfo ADD COLUMN `Type` VARCHAR(100)']
(_mysql_exceptions.OperationalError) (1060, "Duplicate column name 'condition'") [SQL: u'ALTER TABLE policy ADD COLUMN `condition` INTEGER NOT NULL']
(_mysql_exceptions.OperationalError) (1060, "Duplicate column name 'priority'") [SQL: u'ALTER TABLE resolverrealm ADD COLUMN priority INTEGER']
(_mysql_exceptions.OperationalError) (1060, "Duplicate column name 'adminrealm'") [SQL: u'ALTER TABLE policy ADD COLUMN adminrealm VARCHAR(256)']
(_mysql_exceptions.OperationalError) (1060, "Duplicate column name 'revoked'") [SQL: u'ALTER TABLE token ADD COLUMN revoked BOOL']
(_mysql_exceptions.OperationalError) (1060, "Duplicate column name 'locked'") [SQL: u'ALTER TABLE token ADD COLUMN locked BOOL']
(_mysql_exceptions.OperationalError) (1050, "Table 'smtpserver' already exists") [SQL: u'\nCREATE TABLE smtpserver (\n\tid INTEGER NOT NULL AUTO_INCREMENT, \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 BOOL, \n\tdescription VARCHAR(2000), \n\tPRIMARY KEY (id), \n\tCHECK (tls IN (0, 1))\n)\n\n']
(_mysql_exceptions.OperationalError) (1050, "Table 'passwordreset' already exists") [SQL: u'\nCREATE TABLE passwordreset (\n\tid INTEGER NOT NULL AUTO_INCREMENT, \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 DATETIME, \n\texpiration DATETIME, \n\tPRIMARY KEY (id)\n)\n\n']
(_mysql_exceptions.OperationalError) (1050, "Table 'radiusserver' already exists") [SQL: u'\nCREATE TABLE radiusserver (\n\tid INTEGER NOT NULL AUTO_INCREMENT, \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']
(_mysql_exceptions.OperationalError) (1060, "Duplicate column name 'dictionary'") [SQL: u'ALTER TABLE radiusserver ADD COLUMN dictionary VARCHAR(255)']
(_mysql_exceptions.OperationalError) (1050, "Table 'eventhandler' already exists") [SQL: u'\nCREATE TABLE eventhandler (\n\tid INTEGER NOT NULL AUTO_INCREMENT, \n\tordering INTEGER NOT NULL, \n\tevent VARCHAR(255) NOT NULL, \n\thandlermodule VARCHAR(255) NOT NULL, \n\t`condition` VARCHAR(1024), \n\taction VARCHAR(1024), \n\tPRIMARY KEY (id)\n)\n\n']
Table already exists
(_mysql_exceptions.OperationalError) (1050, "Table 'smsgateway' already exists") [SQL: u'\nCREATE TABLE smsgateway (\n\tid INTEGER NOT NULL AUTO_INCREMENT, \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']
Table already exists
(_mysql_exceptions.OperationalError) (1050, "Table 'eventhandlercondition' already exists") [SQL: u'\nCREATE TABLE eventhandlercondition (\n\tid INTEGER NOT NULL AUTO_INCREMENT, \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']
Table already exists
(_mysql_exceptions.OperationalError) (1050, "Table 'clientapplication' already exists") [SQL: u'\nCREATE TABLE clientapplication (\n\tid INTEGER NOT NULL AUTO_INCREMENT, \n\tip VARCHAR(255) NOT NULL, \n\thostname VARCHAR(255), \n\tclienttype VARCHAR(255) NOT NULL, \n\tlastseen DATETIME, \n\tPRIMARY KEY (id), \n\tCONSTRAINT caix UNIQUE (ip, clienttype)\n)\n\n']
Table subscription exists
(_mysql_exceptions.OperationalError) (1050, "Table 'subscription' already exists") [SQL: u'\nCREATE TABLE subscription (\n\tid INTEGER NOT NULL AUTO_INCREMENT, \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 DATETIME, \n\tdate_till DATETIME, \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']
pidea_audit table obviously already exists.
(_mysql_exceptions.OperationalError) (1050, "Table 'pidea_audit' already exists") [SQL: u'\nCREATE TABLE pidea_audit (\n\tid INTEGER NOT NULL AUTO_INCREMENT, \n\tdate DATETIME, \n\tsignature VARCHAR(620), \n\taction VARCHAR(50), \n\tsuccess INTEGER, \n\tserial VARCHAR(20), \n\ttoken_type VARCHAR(12), \n\tuser 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']
column resolver in pidea_audit obviously already exists.
(_mysql_exceptions.OperationalError) (1060, "Duplicate column name 'resolver'") [SQL: u'ALTER TABLE pidea_audit ADD COLUMN resolver VARCHAR(50)']
Columns name and active already exist.
(_mysql_exceptions.OperationalError) (1060, "Duplicate column name 'name'") [SQL: u'ALTER TABLE eventhandler ADD COLUMN name VARCHAR(64)']
Could not add column 'check_all_resolvers'
(_mysql_exceptions.OperationalError) (1060, "Duplicate column name 'check_all_resolvers'") [SQL: u'ALTER TABLE policy ADD COLUMN check_all_resolvers BOOL']

Thanks!

Kris Lou
kl...@themusiclink.net

[Cornelius Kölbel]

The pip update script tries to apply all database migrations as failsafe.
You may ignore these errors.

Alternatively you could run

Pi-mange db update