DB Cleanup on privacyidea-pip-upgrade script?


#1

On upgrade to 2.20. The following warnings appear to either fail to create tables/indexes (because they already exist) or remove unneeded tables (because they might already be dropped?).

Either way, just thought I’d note them here.

No handlers could be found for logger "privacyidea.lib.stats"
/opt/privacyIDEA/lib/python2.7/site-packages/flask_sqlalchemy/__init__.py:778: 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:778: 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']
Could not add index in table pidea_audit.
(_mysql_exceptions.OperationalError) (1061, "Duplicate key name 'ix_pidea_audit_user'") [SQL: u'CREATE INDEX ix_pidea_audit_user ON pidea_audit (user)']
Could not add index to 'challenge.serial'
(_mysql_exceptions.OperationalError) (1061, "Duplicate key name 'ix_challenge_serial'") [SQL: u'CREATE INDEX ix_challenge_serial ON challenge (serial)']
Could not remove uniqueness from 'challenge.transaction_id'
(_mysql_exceptions.OperationalError) (1091, "Can't DROP 'ix_challenge_transaction_id'; check that column/key exists") [SQL: u'\nDROP INDEX ix_challenge_transaction_id ON challenge']
Could not create table 'usercache'.
(_mysql_exceptions.OperationalError) (1050, "Table 'usercache' already exists") [SQL: u'\nCREATE TABLE usercache (\n\tid INTEGER NOT NULL AUTO_INCREMENT, \n\tusername VARCHAR(64), \n\tresolver VARCHAR(120), \n\tuser_id VARCHAR(320), \n\ttimestamp DATETIME, \n\tPRIMARY KEY (id)\n)\n\n']
## Schema seems already to be oracle compatible.
(_mysql_exceptions.OperationalError) (1091, "Can't DROP 'ix_clientapplication_id'; check that column/key exists") [SQL: u'\nDROP INDEX ix_clientapplication_id ON clientapplication']
## Unnecessary columns in table usercache obviously do not exist anymore.
(_mysql_exceptions.OperationalError) (1091, "Can't DROP 'realm'; check that column/key exists") [SQL: u'ALTER TABLE usercache DROP COLUMN realm']
Could not add table 'authcache' - probably already exists!
(_mysql_exceptions.OperationalError) (1050, "Table 'authcache' already exists") [SQL: u'\nCREATE TABLE authcache (\n\tid INTEGER NOT NULL AUTO_INCREMENT, \n\tfirst_auth DATETIME, \n\tlast_auth DATETIME, \n\tusername VARCHAR(64), \n\tresolver VARCHAR(120), \n\trealm VARCHAR(120), \n\tclient_ip VARCHAR(40), \n\tuser_agent VARCHAR(120), \n\tauthentication VARCHAR(64), \n\tPRIMARY KEY (id)\n)\n\n']
Can not create table 'privacyideaserver'. It probably already exists
(_mysql_exceptions.OperationalError) (1050, "Table 'privacyideaserver' already exists") [SQL: u'\nCREATE TABLE privacyideaserver (\n\tid INTEGER NOT NULL AUTO_INCREMENT, \n\tidentifier VARCHAR(255) NOT NULL, \n\turl VARCHAR(255) NOT NULL, \n\ttls BOOL, \n\tdescription VARCHAR(2000), \n\tPRIMARY KEY (id), \n\tUNIQUE (identifier), \n\tCHECK (tls IN (0, 1))\n)\n\n']

#2

This is OK.
The update script tries to apply all changes since 2.0.