search

tigase / tigase-server

(M) Highly optimized, extremely modular and very flexible XMPP/Jabber server
https://tigase.net
GNU Affero General Public License v3.0
326 stars 106 forks source link

upgrade tigase from 7.1.5 to 8.2.2 #207

Closed davidemarrone closed 3 months ago

davidemarrone commented 1 year ago

Hi, I'm trying to upgrade tigase from 7.1.5-b4512 to 8.2.2-b11697

this is the output of ./scripts/tigase.sh upgrade-schema etc/tigase.conf

[2023-06-06 13:33:02:097] [CONFIG  ] [                  main ] DBSchemaLoader.init()            : Parameters: [adminPassword: ****, admins: null, dbHostname: mysql-hostname:3306, dbName: tigasedb, dbPass: ************, dbRootPass: ****, dbRootUser: null, dbRootAsk: false, dbType: mysql, dbUser: username, file: null, ingoreMissingFiles: false, logLevel: CONFIG, query: null, serverTimezone: null, useLegacyDatetimeCode: false, useSSL: false, forceReloadSchema: false, schemaDirectory: database/, otherParameters: {allowPublicKeyRetrieval=true, characterEncoding=UTF-8}]
[2023-06-06 13:33:02:103] [INFO    ] [                  main ] DBSchemaLoader.validateDBConnection(): Validating DBConnection, URI: jdbc:mysql://mysql-hostname:3306/?user=username&password=************&useSSL=false&useLegacyDatetimeCode=false&allowPublicKeyRetrieval=true&characterEncoding=UTF-8&allowPublicKeyRetrieval=true
[2023-06-06 13:33:02:498] [INFO    ] [                  main ] DBSchemaLoader.validateDBConnection(): Connection OK
[2023-06-06 13:33:02:509] [INFO    ] [                  main ] ConnectionLock.<init>()          : Prepared connection for locking
[2023-06-06 13:33:02:509] [INFO    ] [                  main ] ConnectionLock.lock()            : Trying to get lock for database, current state: false
[2023-06-06 13:33:02:543] [INFO    ] [                  main ] ConnectionLock.lock()            : Obtained lock for connection: jdbc:mysql://mysql-hostname:3306/?user=username&password=************&useSSL=false&useLegacyDatetimeCode=false&allowPublicKeyRetrieval=true&characterEncoding=UTF-8&allowPublicKeyRetrieval=true
[2023-06-06 13:33:02:544] [INFO    ] [                  main ] DBSchemaLoader.validateDBExists(): Validating whether DB Exists, URI: jdbc:mysql://mysql-hostname:3306/tigasedb?user=username&password=************&useSSL=false&useLegacyDatetimeCode=false&allowPublicKeyRetrieval=true&characterEncoding=UTF-8&allowPublicKeyRetrieval=true
[2023-06-06 13:33:02:559] [INFO    ] [                  main ] DBSchemaLoader.lambda$validateDBExists$9(): Exists OK
[2023-06-06 13:33:02:595] [WARNING ] [                  main ] DBSchemaLoader.lambda$getComponentVersionFromDb$14(): Getting version failed: { call TigGetComponentVersion( ? ) }, PROCEDURE tigasedb.TigGetComponentVersion does not exist
[2023-06-06 13:33:02:655] [WARNING ] [                  main ] DBSchemaLoader.lambda$getComponentVersionFromDb$14(): Getting version failed: { call TigGetComponentVersion( ? ) }, PROCEDURE tigasedb.TigGetComponentVersion does not exist
[2023-06-06 13:33:02:691] [WARNING ] [                  main ] DBSchemaLoader.lambda$getComponentVersionFromDb$14(): Getting version failed: { call TigGetComponentVersion( ? ) }, PROCEDURE tigasedb.TigGetComponentVersion does not exist
[2023-06-06 13:33:02:720] [WARNING ] [                  main ] DBSchemaLoader.lambda$getComponentVersionFromDb$14(): Getting version failed: { call TigGetComponentVersion( ? ) }, PROCEDURE tigasedb.TigGetComponentVersion does not exist
[2023-06-06 13:33:02:749] [WARNING ] [                  main ] DBSchemaLoader.lambda$getComponentVersionFromDb$14(): Getting version failed: { call TigGetComponentVersion( ? ) }, PROCEDURE tigasedb.TigGetComponentVersion does not exist
[2023-06-06 13:33:02:758] [INFO    ] [                  main ] ConnectionLock.unlock()          : Unlocking database
[2023-06-06 13:33:02:760] [INFO    ] [                  main ] ConnectionLock.cleanup()         : Closing lock connection
[2023-06-06 13:33:02:772] [CONFIG  ] [                  main ] DBSchemaLoader.init()            : Parameters: [adminPassword: ****, admins: null, dbHostname: mysql-hostname:3306, dbName: tigasedb, dbPass: ************, dbRootPass: ****, dbRootUser: null, dbRootAsk: false, dbType: mysql, dbUser: username, file: null, ingoreMissingFiles: false, logLevel: CONFIG, query: null, serverTimezone: null, useLegacyDatetimeCode: false, useSSL: false, forceReloadSchema: false, schemaDirectory: database/, otherParameters: {allowPublicKeyRetrieval=true, characterEncoding=UTF-8}]
[2023-06-06 13:33:02:773] [INFO    ] [                  main ] DBSchemaLoader.validateDBConnection(): Validating DBConnection, URI: jdbc:mysql://mysql-hostname:3306/?user=username&password=************&useSSL=false&useLegacyDatetimeCode=false&allowPublicKeyRetrieval=true&characterEncoding=UTF-8&allowPublicKeyRetrieval=true
[2023-06-06 13:33:02:782] [INFO    ] [                  main ] DBSchemaLoader.validateDBConnection(): Connection OK
[2023-06-06 13:33:02:793] [INFO    ] [                  main ] ConnectionLock.<init>()          : Prepared connection for locking
[2023-06-06 13:33:02:793] [INFO    ] [                  main ] ConnectionLock.lock()            : Trying to get lock for database, current state: false
[2023-06-06 13:33:02:794] [INFO    ] [                  main ] ConnectionLock.lock()            : Obtained lock for connection: jdbc:mysql://mysql-hostname:3306/?user=username&password=************&useSSL=false&useLegacyDatetimeCode=false&allowPublicKeyRetrieval=true&characterEncoding=UTF-8&allowPublicKeyRetrieval=true
[2023-06-06 13:33:02:795] [INFO    ] [                  main ] DBSchemaLoader.validateDBExists(): Validating whether DB Exists, URI: jdbc:mysql://mysql-hostname:3306/tigasedb?user=username&password=************&useSSL=false&useLegacyDatetimeCode=false&allowPublicKeyRetrieval=true&characterEncoding=UTF-8&allowPublicKeyRetrieval=true
[2023-06-06 13:33:02:802] [INFO    ] [                  main ] DBSchemaLoader.lambda$validateDBExists$9(): Exists OK
[2023-06-06 13:33:02:806] [CONFIG  ] [                  main ] DBSchemaLoader.loadSchema()      : SchemaInfo:: id: common, repositories: 0; version: 0.0.3
[2023-06-06 13:33:02:816] [WARNING ] [                  main ] DBSchemaLoader.lambda$getComponentVersionFromDb$14(): Getting version failed: { call TigGetComponentVersion( ? ) }, PROCEDURE tigasedb.TigGetComponentVersion does not exist
[2023-06-06 13:33:02:816] [CONFIG  ] [                  main ] DBSchemaLoader.loadSchemaFromSQLFiles(): Loading schema common, version: 0.0.3 from files, current: 0.0.0
[2023-06-06 13:33:02:822] [INFO    ] [                  main ] DBSchemaLoader.loadSchemaFromSQLFiles(): Schema files to load: {0.0.1=mysql-common-0.0.1.sql, 0.0.2=mysql-common-0.0.2.sql, 0.0.3=mysql-common-0.0.3.sql}
[2023-06-06 13:33:02:830] [INFO    ] [                  main ] DBSchemaLoader.loadSchemaFile()  : Loading schema from file(s): mysql-common-0.0.1.sql, URI: jdbc:mysql://mysql-hostname:3306/tigasedb?user=username&password=************&useSSL=false&useLegacyDatetimeCode=false&allowPublicKeyRetrieval=true&characterEncoding=UTF-8&allowPublicKeyRetrieval=true
[2023-06-06 13:33:02:877] [INFO    ] [                  main ] DBSchemaLoader.lambda$loadSchemaFile$21():  completed OK
[2023-06-06 13:33:02:878] [INFO    ] [                  main ] DBSchemaLoader.loadSchemaFile()  : Loading schema from file(s): mysql-common-0.0.2.sql, URI: jdbc:mysql://mysql-hostname:3306/tigasedb?user=username&password=************&useSSL=false&useLegacyDatetimeCode=false&allowPublicKeyRetrieval=true&characterEncoding=UTF-8&allowPublicKeyRetrieval=true
[2023-06-06 13:33:02:887] [INFO    ] [                  main ] DBSchemaLoader.lambda$loadSchemaFile$21():  completed OK
[2023-06-06 13:33:02:888] [INFO    ] [                  main ] DBSchemaLoader.loadSchemaFile()  : Loading schema from file(s): mysql-common-0.0.3.sql, URI: jdbc:mysql://mysql-hostname:3306/tigasedb?user=username&password=************&useSSL=false&useLegacyDatetimeCode=false&allowPublicKeyRetrieval=true&characterEncoding=UTF-8&allowPublicKeyRetrieval=true
[2023-06-06 13:33:02:909] [INFO    ] [                  main ] DBSchemaLoader.lambda$loadSchemaFile$21():  completed OK
[2023-06-06 13:33:02:910] [INFO    ] [                  main ] DBSchemaLoader.setComponentVersion(): Setting version of the component: common to: 0.0.3 for connection: jdbc:mysql://mysql-hostname:3306/tigasedb?user=username&password=************&useSSL=false&useLegacyDatetimeCode=false&allowPublicKeyRetrieval=true&characterEncoding=UTF-8&allowPublicKeyRetrieval=true
[2023-06-06 13:33:02:949] [WARNING ] [                  main ] DBSchemaLoader.lambda$getComponentVersionFromDb$14(): Getting version failed: { call TigGetComponentVersion( ? ) }, Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '='
[2023-06-06 13:33:02:963] [CONFIG  ] [                  main ] DBSchemaLoader.loadSchema()      : SchemaInfo:: id: message-archiving, repositories: 1; version: 3.0.0-b308/9c934c86
[2023-06-06 13:33:02:980] [WARNING ] [                  main ] DBSchemaLoader.lambda$getComponentVersionFromDb$14(): Getting version failed: { call TigGetComponentVersion( ? ) }, Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '='
[2023-06-06 13:33:02:980] [CONFIG  ] [                  main ] DBSchemaLoader.loadSchemaFromSQLFiles(): Loading schema message-archiving, version: 3.0.0-b308/9c934c86 from files, current: 0.0.0
[2023-06-06 13:33:02:984] [INFO    ] [                  main ] DBSchemaLoader.loadSchemaFromSQLFiles(): Schema files to load: {2.0.0=mysql-message-archiving-2.0.0.sql, 2.1.0=mysql-message-archiving-2.1.0.sql, 3.0.0=mysql-message-archiving-3.0.0.sql}
[2023-06-06 13:33:02:988] [INFO    ] [                  main ] DBSchemaLoader.loadSchemaFile()  : Loading schema from file(s): mysql-message-archiving-2.0.0.sql, URI: jdbc:mysql://mysql-hostname:3306/tigasedb?user=username&password=************&useSSL=false&useLegacyDatetimeCode=false&allowPublicKeyRetrieval=true&characterEncoding=UTF-8&allowPublicKeyRetrieval=true
[2023-06-06 13:33:03:133] [SEVERE  ] [                  main ] DBSchemaLoader.lambda$loadSchemaFile$21(): Failed to execute query: call TigAddColumnIfNotExists('tig_ma_msgs',  'buddy_res', 'varchar(1024)')
[2023-06-06 13:33:03:134] [SEVERE  ] [                  main ] DBSchemaLoader.withConnectionGeneric(): 

=====
Failure: Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation '='
=====

[2023-06-06 13:33:03:134] [SEVERE  ] [                  main ] DBSchemaLoader.withConnectionGeneric(): Failure: Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation '='
java.sql.SQLException: Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation '='
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129)
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
    at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
    at com.mysql.cj.jdbc.StatementImpl.executeInternal(StatementImpl.java:782)
    at com.mysql.cj.jdbc.StatementImpl.execute(StatementImpl.java:666)
    at tigase.db.util.DBSchemaLoader.lambda$loadSchemaFile$21(DBSchemaLoader.java:817)
    at tigase.db.util.DBSchemaLoader.lambda$withStatement$25(DBSchemaLoader.java:1167)
    at tigase.db.util.DBSchemaLoader.withConnectionGeneric(DBSchemaLoader.java:1120)
    at tigase.db.util.DBSchemaLoader.withConnection(DBSchemaLoader.java:1160)
    at tigase.db.util.DBSchemaLoader.withConnection(DBSchemaLoader.java:1112)
    at tigase.db.util.DBSchemaLoader.withStatement(DBSchemaLoader.java:1165)
    at tigase.db.util.DBSchemaLoader.loadSchemaFile(DBSchemaLoader.java:811)
    at tigase.db.util.DBSchemaLoader.lambda$loadSchemaFromSQLFiles$18(DBSchemaLoader.java:711)
    at java.base/java.util.stream.ReferencePipeline$3$1.accept(ReferencePipeline.java:195)
    at java.base/java.util.TreeMap$EntrySpliterator.forEachRemaining(TreeMap.java:2962)
    at java.base/java.util.stream.AbstractPipeline.copyInto(AbstractPipeline.java:484)
    at java.base/java.util.stream.AbstractPipeline.wrapAndCopyInto(AbstractPipeline.java:474)
    at java.base/java.util.stream.ReduceOps$ReduceOp.evaluateSequential(ReduceOps.java:913)
    at java.base/java.util.stream.AbstractPipeline.evaluate(AbstractPipeline.java:234)
    at java.base/java.util.stream.ReferencePipeline.collect(ReferencePipeline.java:578)
    at tigase.db.util.DBSchemaLoader.loadSchemaFromSQLFiles(DBSchemaLoader.java:712)
    at tigase.db.util.DBSchemaLoader.loadSchema(DBSchemaLoader.java:623)
    at tigase.db.util.SchemaManager.lambda$loadSchemas$34(SchemaManager.java:580)
    at tigase.db.util.SchemaManager.executeWithSchemaLoader(SchemaManager.java:704)
    at tigase.db.util.SchemaManager.loadSchemas(SchemaManager.java:553)
    at tigase.db.util.SchemaManager.lambda$loadSchemas$26(SchemaManager.java:493)
    at java.base/java.util.stream.ReferencePipeline$3$1.accept(ReferencePipeline.java:195)
    at java.base/java.util.HashMap$EntrySpliterator.forEachRemaining(HashMap.java:1764)
    at java.base/java.util.stream.AbstractPipeline.copyInto(AbstractPipeline.java:484)
    at java.base/java.util.stream.AbstractPipeline.wrapAndCopyInto(AbstractPipeline.java:474)
    at java.base/java.util.stream.ReduceOps$ReduceOp.evaluateSequential(ReduceOps.java:913)
    at java.base/java.util.stream.AbstractPipeline.evaluate(AbstractPipeline.java:234)
    at java.base/java.util.stream.ReferencePipeline.collect(ReferencePipeline.java:578)
    at tigase.db.util.SchemaManager.loadSchemas(SchemaManager.java:508)
    at tigase.db.util.SchemaManager.loadSchemas(SchemaManager.java:645)
    at tigase.db.util.SchemaManager.upgradeSchema(SchemaManager.java:377)
    at tigase.util.ui.console.Task.execute(Task.java:59)
    at tigase.db.util.SchemaManager.execute(SchemaManager.java:237)
    at tigase.db.util.SchemaManager.main(SchemaManager.java:191)

[2023-06-06 13:33:03:138] [INFO    ] [                  main ] DBSchemaLoader.loadSchemaFile()  : Loading schema from file(s): mysql-message-archiving-2.1.0.sql, URI: jdbc:mysql://mysql-hostname:3306/tigasedb?user=username&password=************&useSSL=false&useLegacyDatetimeCode=false&allowPublicKeyRetrieval=true&characterEncoding=UTF-8&allowPublicKeyRetrieval=true
[2023-06-06 13:33:03:153] [INFO    ] [                  main ] DBSchemaLoader.lambda$loadSchemaFile$21():  completed OK
[2023-06-06 13:33:03:154] [INFO    ] [                  main ] DBSchemaLoader.loadSchemaFile()  : Loading schema from file(s): mysql-message-archiving-3.0.0.sql, URI: jdbc:mysql://mysql-hostname:3306/tigasedb?user=username&password=************&useSSL=false&useLegacyDatetimeCode=false&allowPublicKeyRetrieval=true&characterEncoding=UTF-8&allowPublicKeyRetrieval=true
[2023-06-06 13:33:03:401] [SEVERE  ] [                  main ] DBSchemaLoader.lambda$loadSchemaFile$21(): Failed to execute query: call Tig_MA_Upgrade()
[2023-06-06 13:33:03:401] [SEVERE  ] [                  main ] DBSchemaLoader.withConnectionGeneric(): 

=====
Failure: Can't DROP 'buddy_res'; check that column/key exists
=====

[2023-06-06 13:33:03:402] [SEVERE  ] [                  main ] DBSchemaLoader.withConnectionGeneric(): Failure: Can't DROP 'buddy_res'; check that column/key exists
java.sql.SQLSyntaxErrorException: Can't DROP 'buddy_res'; check that column/key exists
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120)
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
    at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
    at com.mysql.cj.jdbc.StatementImpl.executeInternal(StatementImpl.java:782)
    at com.mysql.cj.jdbc.StatementImpl.execute(StatementImpl.java:666)
    at tigase.db.util.DBSchemaLoader.lambda$loadSchemaFile$21(DBSchemaLoader.java:817)
    at tigase.db.util.DBSchemaLoader.lambda$withStatement$25(DBSchemaLoader.java:1167)
    at tigase.db.util.DBSchemaLoader.withConnectionGeneric(DBSchemaLoader.java:1120)
    at tigase.db.util.DBSchemaLoader.withConnection(DBSchemaLoader.java:1160)
    at tigase.db.util.DBSchemaLoader.withConnection(DBSchemaLoader.java:1112)
    at tigase.db.util.DBSchemaLoader.withStatement(DBSchemaLoader.java:1165)
    at tigase.db.util.DBSchemaLoader.loadSchemaFile(DBSchemaLoader.java:811)
    at tigase.db.util.DBSchemaLoader.lambda$loadSchemaFromSQLFiles$18(DBSchemaLoader.java:711)
    at java.base/java.util.stream.ReferencePipeline$3$1.accept(ReferencePipeline.java:195)
    at java.base/java.util.TreeMap$EntrySpliterator.forEachRemaining(TreeMap.java:2962)
    at java.base/java.util.stream.AbstractPipeline.copyInto(AbstractPipeline.java:484)
    at java.base/java.util.stream.AbstractPipeline.wrapAndCopyInto(AbstractPipeline.java:474)
    at java.base/java.util.stream.ReduceOps$ReduceOp.evaluateSequential(ReduceOps.java:913)
    at java.base/java.util.stream.AbstractPipeline.evaluate(AbstractPipeline.java:234)
    at java.base/java.util.stream.ReferencePipeline.collect(ReferencePipeline.java:578)
    at tigase.db.util.DBSchemaLoader.loadSchemaFromSQLFiles(DBSchemaLoader.java:712)
    at tigase.db.util.DBSchemaLoader.loadSchema(DBSchemaLoader.java:623)
    at tigase.db.util.SchemaManager.lambda$loadSchemas$34(SchemaManager.java:580)
    at tigase.db.util.SchemaManager.executeWithSchemaLoader(SchemaManager.java:704)
    at tigase.db.util.SchemaManager.loadSchemas(SchemaManager.java:553)
    at tigase.db.util.SchemaManager.lambda$loadSchemas$26(SchemaManager.java:493)
    at java.base/java.util.stream.ReferencePipeline$3$1.accept(ReferencePipeline.java:195)
    at java.base/java.util.HashMap$EntrySpliterator.forEachRemaining(HashMap.java:1764)
    at java.base/java.util.stream.AbstractPipeline.copyInto(AbstractPipeline.java:484)
    at java.base/java.util.stream.AbstractPipeline.wrapAndCopyInto(AbstractPipeline.java:474)
    at java.base/java.util.stream.ReduceOps$ReduceOp.evaluateSequential(ReduceOps.java:913)
    at java.base/java.util.stream.AbstractPipeline.evaluate(AbstractPipeline.java:234)
    at java.base/java.util.stream.ReferencePipeline.collect(ReferencePipeline.java:578)
    at tigase.db.util.SchemaManager.loadSchemas(SchemaManager.java:508)
    at tigase.db.util.SchemaManager.loadSchemas(SchemaManager.java:645)
    at tigase.db.util.SchemaManager.upgradeSchema(SchemaManager.java:377)
    at tigase.util.ui.console.Task.execute(Task.java:59)
    at tigase.db.util.SchemaManager.execute(SchemaManager.java:237)
    at tigase.db.util.SchemaManager.main(SchemaManager.java:191)

[2023-06-06 13:33:03:417] [WARNING ] [                  main ] DBSchemaLoader.lambda$getComponentVersionFromDb$14(): Getting version failed: { call TigGetComponentVersion( ? ) }, Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '='
[2023-06-06 13:33:03:418] [CONFIG  ] [                  main ] DBSchemaLoader.loadSchema()      : SchemaInfo:: id: server, repositories: 5; version: 8.2.0-b5938/6b5a3651
[2023-06-06 13:33:03:430] [WARNING ] [                  main ] DBSchemaLoader.lambda$getComponentVersionFromDb$14(): Getting version failed: { call TigGetComponentVersion( ? ) }, Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '='
[2023-06-06 13:33:03:431] [CONFIG  ] [                  main ] DBSchemaLoader.loadSchemaFromSQLFiles(): Loading schema server, version: 8.2.0-b5938/6b5a3651 from files, current: 0.0.0
[2023-06-06 13:33:03:434] [INFO    ] [                  main ] DBSchemaLoader.loadSchemaFromSQLFiles(): Schema files to load: {8.0.0=mysql-server-8.0.0.sql, 8.1.0=mysql-server-8.1.0.sql, 8.2.0=mysql-server-8.2.0.sql}
[2023-06-06 13:33:03:435] [INFO    ] [                  main ] DBSchemaLoader.loadSchemaFile()  : Loading schema from file(s): mysql-server-8.0.0.sql, URI: jdbc:mysql://mysql-hostname:3306/tigasedb?user=username&password=************&useSSL=false&useLegacyDatetimeCode=false&allowPublicKeyRetrieval=true&characterEncoding=UTF-8&allowPublicKeyRetrieval=true
[2023-06-06 13:33:03:853] [INFO    ] [                  main ] DBSchemaLoader.lambda$loadSchemaFile$21():  completed OK
[2023-06-06 13:33:03:853] [INFO    ] [                  main ] DBSchemaLoader.loadSchemaFile()  : Loading schema from file(s): mysql-server-8.1.0.sql, URI: jdbc:mysql://mysql-hostname:3306/tigasedb?user=username&password=************&useSSL=false&useLegacyDatetimeCode=false&allowPublicKeyRetrieval=true&characterEncoding=UTF-8&allowPublicKeyRetrieval=true
[2023-06-06 13:33:03:866] [INFO    ] [                  main ] DBSchemaLoader.lambda$loadSchemaFile$21():  completed OK
[2023-06-06 13:33:03:867] [INFO    ] [                  main ] DBSchemaLoader.loadSchemaFile()  : Loading schema from file(s): mysql-server-8.2.0.sql, URI: jdbc:mysql://mysql-hostname:3306/tigasedb?user=username&password=************&useSSL=false&useLegacyDatetimeCode=false&allowPublicKeyRetrieval=true&characterEncoding=UTF-8&allowPublicKeyRetrieval=true
[2023-06-06 13:47:35:769] [INFO    ] [                  main ] DBSchemaLoader.lambda$loadSchemaFile$21():  completed OK
[2023-06-06 13:47:35:769] [INFO    ] [                  main ] DBSchemaLoader.setComponentVersion(): Setting version of the component: server to: 8.2.0-b5938 for connection: jdbc:mysql://mysql-hostname:3306/tigasedb?user=username&password=************&useSSL=false&useLegacyDatetimeCode=false&allowPublicKeyRetrieval=true&characterEncoding=UTF-8&allowPublicKeyRetrieval=true
[2023-06-06 13:47:35:818] [WARNING ] [                  main ] DBSchemaLoader.lambda$loadSchema$16(): You have 'password-encoding' property set to MD5-USERNAME-PASSWORD.
This setting will no longer work out of the box with this version of Tigase XMPP Server.
Please check Tigase XMPP Server Administration Guide, section "Changes to Schema in v8.0.0" at http://docs.tigase.org/ for more details.
[2023-06-06 13:47:35:850] [WARNING ] [                  main ] DBSchemaLoader.lambda$getComponentVersionFromDb$14(): Getting version failed: { call TigGetComponentVersion( ? ) }, Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '='
[2023-06-06 13:47:35:851] [CONFIG  ] [                  main ] DBSchemaLoader.loadSchema()      : SchemaInfo:: id: muc, repositories: 2; version: 3.2.0-b669/c05b2edc
[2023-06-06 13:47:35:873] [WARNING ] [                  main ] DBSchemaLoader.lambda$getComponentVersionFromDb$14(): Getting version failed: { call TigGetComponentVersion( ? ) }, Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '='
[2023-06-06 13:47:35:875] [CONFIG  ] [                  main ] DBSchemaLoader.loadSchemaFromSQLFiles(): Loading schema muc, version: 3.2.0-b669/c05b2edc from files, current: 0.0.0
[2023-06-06 13:47:35:880] [INFO    ] [                  main ] DBSchemaLoader.loadSchemaFromSQLFiles(): Schema files to load: {3.0.0=mysql-muc-3.0.0.sql, 3.1.0=mysql-muc-3.1.0.sql, 3.2.0=mysql-muc-3.2.0.sql}
[2023-06-06 13:47:35:882] [INFO    ] [                  main ] DBSchemaLoader.loadSchemaFile()  : Loading schema from file(s): mysql-muc-3.0.0.sql, URI: jdbc:mysql://mysql-hostname:3306/tigasedb?user=username&password=************&useSSL=false&useLegacyDatetimeCode=false&allowPublicKeyRetrieval=true&characterEncoding=UTF-8&allowPublicKeyRetrieval=true
[2023-06-06 13:47:36:094] [INFO    ] [                  main ] DBSchemaLoader.lambda$loadSchemaFile$21():  completed OK
[2023-06-06 13:47:36:097] [INFO    ] [                  main ] DBSchemaLoader.loadSchemaFile()  : Loading schema from file(s): mysql-muc-3.1.0.sql, URI: jdbc:mysql://mysql-hostname:3306/tigasedb?user=username&password=************&useSSL=false&useLegacyDatetimeCode=false&allowPublicKeyRetrieval=true&characterEncoding=UTF-8&allowPublicKeyRetrieval=true
[2023-06-06 13:47:36:292] [INFO    ] [                  main ] DBSchemaLoader.lambda$loadSchemaFile$21():  completed OK
[2023-06-06 13:47:36:292] [INFO    ] [                  main ] DBSchemaLoader.loadSchemaFile()  : Loading schema from file(s): mysql-muc-3.2.0.sql, URI: jdbc:mysql://mysql-hostname:3306/tigasedb?user=username&password=************&useSSL=false&useLegacyDatetimeCode=false&allowPublicKeyRetrieval=true&characterEncoding=UTF-8&allowPublicKeyRetrieval=true
[2023-06-06 13:47:36:304] [INFO    ] [                  main ] DBSchemaLoader.lambda$loadSchemaFile$21():  completed OK
[2023-06-06 13:47:36:306] [INFO    ] [                  main ] DBSchemaLoader.setComponentVersion(): Setting version of the component: muc to: 3.2.0-b669 for connection: jdbc:mysql://mysql-hostname:3306/tigasedb?user=username&password=************&useSSL=false&useLegacyDatetimeCode=false&allowPublicKeyRetrieval=true&characterEncoding=UTF-8&allowPublicKeyRetrieval=true
[2023-06-06 13:47:36:348] [WARNING ] [                  main ] DBSchemaLoader.lambda$getComponentVersionFromDb$14(): Getting version failed: { call TigGetComponentVersion( ? ) }, Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '='
[2023-06-06 13:47:36:349] [CONFIG  ] [                  main ] DBSchemaLoader.loadSchema()      : SchemaInfo:: id: http-api, repositories: 1; version: 2.2.0-b381/9a1646af
[2023-06-06 13:47:36:375] [WARNING ] [                  main ] DBSchemaLoader.lambda$getComponentVersionFromDb$14(): Getting version failed: { call TigGetComponentVersion( ? ) }, Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '='
[2023-06-06 13:47:36:375] [CONFIG  ] [                  main ] DBSchemaLoader.loadSchemaFromSQLFiles(): Loading schema http-api, version: 2.2.0-b381/9a1646af from files, current: 0.0.0
[2023-06-06 13:47:36:382] [INFO    ] [                  main ] DBSchemaLoader.loadSchemaFromSQLFiles(): Schema files to load: {2.0.0=mysql-http-api-2.0.0.sql, 2.1.0=mysql-http-api-2.1.0.sql, 2.2.0=mysql-http-api-2.2.0.sql}
[2023-06-06 13:47:36:383] [INFO    ] [                  main ] DBSchemaLoader.loadSchemaFile()  : Loading schema from file(s): mysql-http-api-2.0.0.sql, URI: jdbc:mysql://mysql-hostname:3306/tigasedb?user=username&password=************&useSSL=false&useLegacyDatetimeCode=false&allowPublicKeyRetrieval=true&characterEncoding=UTF-8&allowPublicKeyRetrieval=true
[2023-06-06 13:47:36:441] [SEVERE  ] [                  main ] DBSchemaLoader.lambda$loadSchemaFile$21(): Failed to execute query: call TigAddIndexIfNotExists('tig_hfu_slots', 'tig_hfu_slots_slot_id_index', 0, '(slot_id)')
[2023-06-06 13:47:36:441] [SEVERE  ] [                  main ] DBSchemaLoader.withConnectionGeneric(): 

=====
Failure: Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation '='
=====

[2023-06-06 13:47:36:441] [SEVERE  ] [                  main ] DBSchemaLoader.withConnectionGeneric(): Failure: Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation '='
java.sql.SQLException: Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation '='
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129)
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
    at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
    at com.mysql.cj.jdbc.StatementImpl.executeInternal(StatementImpl.java:782)
    at com.mysql.cj.jdbc.StatementImpl.execute(StatementImpl.java:666)
    at tigase.db.util.DBSchemaLoader.lambda$loadSchemaFile$21(DBSchemaLoader.java:817)
    at tigase.db.util.DBSchemaLoader.lambda$withStatement$25(DBSchemaLoader.java:1167)
    at tigase.db.util.DBSchemaLoader.withConnectionGeneric(DBSchemaLoader.java:1120)
    at tigase.db.util.DBSchemaLoader.withConnection(DBSchemaLoader.java:1160)
    at tigase.db.util.DBSchemaLoader.withConnection(DBSchemaLoader.java:1112)
    at tigase.db.util.DBSchemaLoader.withStatement(DBSchemaLoader.java:1165)
    at tigase.db.util.DBSchemaLoader.loadSchemaFile(DBSchemaLoader.java:811)
    at tigase.db.util.DBSchemaLoader.lambda$loadSchemaFromSQLFiles$18(DBSchemaLoader.java:711)
    at java.base/java.util.stream.ReferencePipeline$3$1.accept(ReferencePipeline.java:195)
    at java.base/java.util.TreeMap$EntrySpliterator.forEachRemaining(TreeMap.java:2962)
    at java.base/java.util.stream.AbstractPipeline.copyInto(AbstractPipeline.java:484)
    at java.base/java.util.stream.AbstractPipeline.wrapAndCopyInto(AbstractPipeline.java:474)
    at java.base/java.util.stream.ReduceOps$ReduceOp.evaluateSequential(ReduceOps.java:913)
    at java.base/java.util.stream.AbstractPipeline.evaluate(AbstractPipeline.java:234)
    at java.base/java.util.stream.ReferencePipeline.collect(ReferencePipeline.java:578)
    at tigase.db.util.DBSchemaLoader.loadSchemaFromSQLFiles(DBSchemaLoader.java:712)
    at tigase.db.util.DBSchemaLoader.loadSchema(DBSchemaLoader.java:623)
    at tigase.db.util.SchemaManager.lambda$loadSchemas$34(SchemaManager.java:580)
    at tigase.db.util.SchemaManager.executeWithSchemaLoader(SchemaManager.java:704)
    at tigase.db.util.SchemaManager.loadSchemas(SchemaManager.java:553)
    at tigase.db.util.SchemaManager.lambda$loadSchemas$26(SchemaManager.java:493)
    at java.base/java.util.stream.ReferencePipeline$3$1.accept(ReferencePipeline.java:195)
    at java.base/java.util.HashMap$EntrySpliterator.forEachRemaining(HashMap.java:1764)
    at java.base/java.util.stream.AbstractPipeline.copyInto(AbstractPipeline.java:484)
    at java.base/java.util.stream.AbstractPipeline.wrapAndCopyInto(AbstractPipeline.java:474)
    at java.base/java.util.stream.ReduceOps$ReduceOp.evaluateSequential(ReduceOps.java:913)
    at java.base/java.util.stream.AbstractPipeline.evaluate(AbstractPipeline.java:234)
    at java.base/java.util.stream.ReferencePipeline.collect(ReferencePipeline.java:578)
    at tigase.db.util.SchemaManager.loadSchemas(SchemaManager.java:508)
    at tigase.db.util.SchemaManager.loadSchemas(SchemaManager.java:645)
    at tigase.db.util.SchemaManager.upgradeSchema(SchemaManager.java:377)
    at tigase.util.ui.console.Task.execute(Task.java:59)
    at tigase.db.util.SchemaManager.execute(SchemaManager.java:237)
    at tigase.db.util.SchemaManager.main(SchemaManager.java:191)

[2023-06-06 13:47:36:442] [INFO    ] [                  main ] DBSchemaLoader.loadSchemaFile()  : Loading schema from file(s): mysql-http-api-2.1.0.sql, URI: jdbc:mysql://mysql-hostname:3306/tigasedb?user=username&password=************&useSSL=false&useLegacyDatetimeCode=false&allowPublicKeyRetrieval=true&characterEncoding=UTF-8&allowPublicKeyRetrieval=true
[2023-06-06 13:47:36:449] [INFO    ] [                  main ] DBSchemaLoader.lambda$loadSchemaFile$21():  completed OK
[2023-06-06 13:47:36:449] [INFO    ] [                  main ] DBSchemaLoader.loadSchemaFile()  : Loading schema from file(s): mysql-http-api-2.2.0.sql, URI: jdbc:mysql://mysql-hostname:3306/tigasedb?user=username&password=************&useSSL=false&useLegacyDatetimeCode=false&allowPublicKeyRetrieval=true&characterEncoding=UTF-8&allowPublicKeyRetrieval=true
[2023-06-06 13:47:36:490] [INFO    ] [                  main ] DBSchemaLoader.lambda$loadSchemaFile$21():  completed OK
[2023-06-06 13:47:36:501] [WARNING ] [                  main ] DBSchemaLoader.lambda$getComponentVersionFromDb$14(): Getting version failed: { call TigGetComponentVersion( ? ) }, Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '='
[2023-06-06 13:47:36:501] [CONFIG  ] [                  main ] DBSchemaLoader.loadSchema()      : SchemaInfo:: id: pubsub, repositories: 1; version: 5.0.1-b802/e24359b9
[2023-06-06 13:47:36:516] [WARNING ] [                  main ] DBSchemaLoader.lambda$getComponentVersionFromDb$14(): Getting version failed: { call TigGetComponentVersion( ? ) }, Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '='
[2023-06-06 13:47:36:517] [CONFIG  ] [                  main ] DBSchemaLoader.loadSchemaFromSQLFiles(): Loading schema pubsub, version: 5.0.1-b802/e24359b9 from files, current: 0.0.0
[2023-06-06 13:47:36:524] [INFO    ] [                  main ] DBSchemaLoader.loadSchemaFromSQLFiles(): Schema files to load: {4.0.0=mysql-pubsub-4.0.0.sql, 4.1.0=mysql-pubsub-4.1.0.sql, 5.0.0=mysql-pubsub-5.0.0.sql, 5.0.1=mysql-pubsub-5.0.1.sql}
[2023-06-06 13:47:36:529] [INFO    ] [                  main ] DBSchemaLoader.loadSchemaFile()  : Loading schema from file(s): mysql-pubsub-4.0.0.sql, URI: jdbc:mysql://mysql-hostname:3306/tigasedb?user=username&password=************&useSSL=false&useLegacyDatetimeCode=false&allowPublicKeyRetrieval=true&characterEncoding=UTF-8&allowPublicKeyRetrieval=true
[2023-06-06 13:47:36:793] [INFO    ] [                  main ] DBSchemaLoader.lambda$loadSchemaFile$21():  completed OK
[2023-06-06 13:47:36:793] [INFO    ] [                  main ] DBSchemaLoader.loadSchemaFile()  : Loading schema from file(s): mysql-pubsub-4.1.0.sql, URI: jdbc:mysql://mysql-hostname:3306/tigasedb?user=username&password=************&useSSL=false&useLegacyDatetimeCode=false&allowPublicKeyRetrieval=true&characterEncoding=UTF-8&allowPublicKeyRetrieval=true
[2023-06-06 13:47:36:821] [INFO    ] [                  main ] DBSchemaLoader.lambda$loadSchemaFile$21():  completed OK
[2023-06-06 13:47:36:822] [INFO    ] [                  main ] DBSchemaLoader.loadSchemaFile()  : Loading schema from file(s): mysql-pubsub-5.0.0.sql, URI: jdbc:mysql://mysql-hostname:3306/tigasedb?user=username&password=************&useSSL=false&useLegacyDatetimeCode=false&allowPublicKeyRetrieval=true&characterEncoding=UTF-8&allowPublicKeyRetrieval=true
[2023-06-06 13:47:37:207] [INFO    ] [                  main ] DBSchemaLoader.lambda$loadSchemaFile$21():  completed OK
[2023-06-06 13:47:37:208] [INFO    ] [                  main ] DBSchemaLoader.loadSchemaFile()  : Loading schema from file(s): mysql-pubsub-5.0.1.sql, URI: jdbc:mysql://mysql-hostname:3306/tigasedb?user=username&password=************&useSSL=false&useLegacyDatetimeCode=false&allowPublicKeyRetrieval=true&characterEncoding=UTF-8&allowPublicKeyRetrieval=true
[2023-06-06 13:47:37:259] [INFO    ] [                  main ] DBSchemaLoader.lambda$loadSchemaFile$21():  completed OK
[2023-06-06 13:47:37:260] [INFO    ] [                  main ] DBSchemaLoader.setComponentVersion(): Setting version of the component: pubsub to: 5.0.1-b802 for connection: jdbc:mysql://mysql-hostname:3306/tigasedb?user=username&password=************&useSSL=false&useLegacyDatetimeCode=false&allowPublicKeyRetrieval=true&characterEncoding=UTF-8&allowPublicKeyRetrieval=true
[2023-06-06 13:47:37:274] [WARNING ] [                  main ] DBSchemaLoader.addXmppAdminAccount(): Error: No admin users entered
[2023-06-06 13:47:37:274] [WARNING ] [                  main ] DBSchemaLoader.postInstallation(): No database root user credentials, skipping post database creation scripts.
[2023-06-06 13:47:37:275] [INFO    ] [                  main ] ConnectionLock.unlock()          : Unlocking database
[2023-06-06 13:47:37:276] [INFO    ] [                  main ] ConnectionLock.cleanup()         : Closing lock connection
[2023-06-06 13:47:37:276] [INFO    ] [                  main ] SchemaManager.loadSchemas()      : schema loading finished!

  =============================================================================
    Schema upgrade finished

  Data source: default with uri jdbc:mysql://mysql-hostname:3306/tigasedb?user=username&password=************&useSSL=false&useLegacyDatetimeCode=false&allowPublicKeyRetrieval=true&characterEncoding=UTF-8
    Checking connection to database ok
    Checking if database exists ok
    Loading Common Schema Files ok
    Loading schema: Tigase Message Archiving Component, version: 3.0.0-b308/9c934c86 (database version: none)   error
        Message: Getting version failed: { call TigGetComponentVersion( ? ) }, Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '='
                 Getting version failed: { call TigGetComponentVersion( ? ) }, Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '='
                 Schema files to load: {2.0.0=mysql-message-archiving-2.0.0.sql, 2.1.0=mysql-message-archiving-2.1.0.sql, 3.0.0=mysql-message-archiving-3.0.0.sql}
                 Loading schema from file(s): mysql-message-archiving-2.0.0.sql, URI: jdbc:mysql://mysql-hostname:3306/tigasedb?user=username&password=************&useSSL=false&useLegacyDatetimeCode=false&allowPublicKeyRetrieval=true&characterEncoding=UTF-8&allowPublicKeyRetrieval=true
                 Failed to execute query: call TigAddColumnIfNotExists('tig_ma_msgs',  'buddy_res', 'varchar(1024)')

                 =====
                 Failure: Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation '='
                 =====

                 Failure: Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation '='
                 Loading schema from file(s): mysql-message-archiving-2.1.0.sql, URI: jdbc:mysql://mysql-hostname:3306/tigasedb?user=username&password=************&useSSL=false&useLegacyDatetimeCode=false&allowPublicKeyRetrieval=true&characterEncoding=UTF-8&allowPublicKeyRetrieval=true
                  completed OK
                 Loading schema from file(s): mysql-message-archiving-3.0.0.sql, URI: jdbc:mysql://mysql-hostname:3306/tigasedb?user=username&password=************&useSSL=false&useLegacyDatetimeCode=false&allowPublicKeyRetrieval=true&characterEncoding=UTF-8&allowPublicKeyRetrieval=true
                 Failed to execute query: call Tig_MA_Upgrade()

                 =====
                 Failure: Can't DROP 'buddy_res'; check that column/key exists
                 =====

                 Failure: Can't DROP 'buddy_res'; check that column/key exists
    Loading schema: Tigase XMPP Server (Core), version: 8.2.0-b5938/6b5a3651 (database version: none)   warning
        Message: Getting version failed: { call TigGetComponentVersion( ? ) }, Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '='
                 Getting version failed: { call TigGetComponentVersion( ? ) }, Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '='
                 Schema files to load: {8.0.0=mysql-server-8.0.0.sql, 8.1.0=mysql-server-8.1.0.sql, 8.2.0=mysql-server-8.2.0.sql}
                 Loading schema from file(s): mysql-server-8.0.0.sql, URI: jdbc:mysql://mysql-hostname:3306/tigasedb?user=username&password=************&useSSL=false&useLegacyDatetimeCode=false&allowPublicKeyRetrieval=true&characterEncoding=UTF-8&allowPublicKeyRetrieval=true
                  completed OK
                 Loading schema from file(s): mysql-server-8.1.0.sql, URI: jdbc:mysql://mysql-hostname:3306/tigasedb?user=username&password=************&useSSL=false&useLegacyDatetimeCode=false&allowPublicKeyRetrieval=true&characterEncoding=UTF-8&allowPublicKeyRetrieval=true
                  completed OK
                 Loading schema from file(s): mysql-server-8.2.0.sql, URI: jdbc:mysql://mysql-hostname:3306/tigasedb?user=username&password=************&useSSL=false&useLegacyDatetimeCode=false&allowPublicKeyRetrieval=true&characterEncoding=UTF-8&allowPublicKeyRetrieval=true
                  completed OK
                 Setting version of the component: server to: 8.2.0-b5938 for connection: jdbc:mysql://mysql-hostname:3306/tigasedb?user=username&password=************&useSSL=false&useLegacyDatetimeCode=false&allowPublicKeyRetrieval=true&characterEncoding=UTF-8&allowPublicKeyRetrieval=true
                 You have 'password-encoding' property set to MD5-USERNAME-PASSWORD.
                 This setting will no longer work out of the box with this version of Tigase XMPP Server.
                 Please check Tigase XMPP Server Administration Guide, section "Changes to Schema in v8.0.0" at http://docs.tigase.org/ for more details.
    Loading schema: Tigase MUC Component, version: 3.2.0-b669/c05b2edc (database version: none) ok
    Loading schema: Tigase HTTP API - File Upload, version: 2.2.0-b381/9a1646af (database version: none)    error
        Message: Getting version failed: { call TigGetComponentVersion( ? ) }, Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '='
                 Getting version failed: { call TigGetComponentVersion( ? ) }, Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '='
                 Schema files to load: {2.0.0=mysql-http-api-2.0.0.sql, 2.1.0=mysql-http-api-2.1.0.sql, 2.2.0=mysql-http-api-2.2.0.sql}
                 Loading schema from file(s): mysql-http-api-2.0.0.sql, URI: jdbc:mysql://mysql-hostname:3306/tigasedb?user=username&password=************&useSSL=false&useLegacyDatetimeCode=false&allowPublicKeyRetrieval=true&characterEncoding=UTF-8&allowPublicKeyRetrieval=true
                 Failed to execute query: call TigAddIndexIfNotExists('tig_hfu_slots', 'tig_hfu_slots_slot_id_index', 0, '(slot_id)')

                 =====
                 Failure: Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation '='
                 =====

                 Failure: Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation '='
                 Loading schema from file(s): mysql-http-api-2.1.0.sql, URI: jdbc:mysql://mysql-hostname:3306/tigasedb?user=username&password=************&useSSL=false&useLegacyDatetimeCode=false&allowPublicKeyRetrieval=true&characterEncoding=UTF-8&allowPublicKeyRetrieval=true
                  completed OK
                 Loading schema from file(s): mysql-http-api-2.2.0.sql, URI: jdbc:mysql://mysql-hostname:3306/tigasedb?user=username&password=************&useSSL=false&useLegacyDatetimeCode=false&allowPublicKeyRetrieval=true&characterEncoding=UTF-8&allowPublicKeyRetrieval=true
                  completed OK
    Loading schema: Tigase PubSub Component, version: 5.0.1-b802/e24359b9 (database version: none)  ok
    Adding XMPP admin accounts  warning
        Message: Error: No admin users entered
    Post installation action    skipped
        Message: No database root user credentials, skipping post database creation scripts.
  =============================================================================

how can I fix it? regards

woj-tek commented 1 year ago

Which MySQL version do you use?

First of all I would suggest doing staged upgrade - first upgrade do version 8.0.x and then to the 8.2.x.

Collation problem most likely stems from differences in Tigase connection and database default collation. Please make sure that those match. The problematic StoredProcedure operates on information_schema database so keep that in mind as well.

create procedure TigAddColumnIfNotExists(tab varchar(255), col varchar(255), def varchar(255))
begin
call TigExecuteIfNot((select count(1) from information_schema.COLUMNS where TABLE_SCHEMA = DATABASE() AND TABLE_NAME = tab AND COLUMN_NAME = col), 
    CONCAT('alter table ', tab, ' add `', col, '` ', def)
);
davidemarrone commented 1 year ago

Thank for the feedback, we are using Mysql 5.7.41

Do you think is possible to understand exactly which query is failing?

I have seen that the drop column buddy_res is present both in mysql-message-archiving-3.0.0.sql:

            alter table tig_ma_msgs
                drop column direction,
                drop column type,
                drop column buddy_res,
                drop column msg_id;

code here

and in mysql-unified-archive-3.0.0.sql

        alter table tig_ma_msgs
            drop column buddy_res;

I didn't find in the public repos with SQL file, what is mysql-unified-archive?

I will try as suggested to fix the default collation and I will also try to make upgrade before to 8.0.0 and to 8.2.x (without anyway running 8.0.0) as suggested and check if there is any error.

Regarding this:

[2023-06-06 13:47:35:818] [WARNING ] [                  main ] DBSchemaLoader.lambda$loadSchema$16(): You have 'password-encoding' property set to MD5-USERNAME-PASSWORD.
This setting will no longer work out of the box with this version of Tigase XMPP Server.
Please check Tigase XMPP Server Administration Guide, section "Changes to Schema in v8.0.0" at http://docs.tigase.org/ for more details.

for what I have understood from the doc I have to add inside authRepository { default () {

this block:

    'user-login-query' = '{ call TigUserLoginPlainPw(?, ?) }'
    credentialEncoders () {
      'MD5-USERNAME-PASSWORD' () {}
    }
    credentialDecoders () {
      'MD5-USERNAME-PASSWORD' () {}
    }

as described here.

For sure I need the credentialDecoders otherwise old user cannot connect, it is not really clear if I need to use also credentialEncoders or not, could you explain better how it works the "migration" of the hashed password? For the old users will always remain MD5 hash also if they change their password? For new users created will be used MD5 or the new hashing of tigase 8.x?

I have also seen that TigUserLoginPlainPw was removed here so I guess that after upgrading to 8.2.2 I have to re-create it manually, correct?

woj-tek commented 1 year ago

The offending call is this one: TigAddColumnIfNotExists('tig_ma_msgs', 'buddy_res', 'varchar(1024)') - it tries to add the column. So it's about MA/message-archive table. The second one ("drop column buddy_res") is the result of the first failure.

If you have not enabled unified-archive, there shouldn't be any calls to it.

Regarding password-encoding - yes, you should add this block. You don't have to add user-login-query though (it's only needed if you were to use old login mechanism and in that instance you wouldn't need credentials decoder bean).

The encoders/decoders - if you add decoders, your users will be able to login using data covered by the existing decoder (i.e. MD5 passwrods). Encoders are responsible for storing passwords, so if you create a new user or existing user updates password it will be encoded in the database using all enabled encoders. So the transition/migration can be gradual. You can also force migration to new mechanism by forcing everyone on your system to update passwords.

davidemarrone commented 1 year ago

ok, it seems all clear, so there is no need to add credentialEncoders, regarding TigUserLoginPlainPw at the time we had modify the stored procedure adding a lower function, this was the change made in the 7.x:

stored procedure

how can we replicate the same behaviour with the 8.2.2 ?

woj-tek commented 1 year ago

You would have to create your own decoder based on MD5UsernamePasswordCredentialsEntry.java and modify verifyPlainPassword() method by converting result of getUsername(user) to lowercase. Afterwards you can disable 'MD5-USERNAME-PASSWORD' () {} and use your implementation.

davidemarrone commented 1 year ago

ok, so we need to re-build the code? Cannot we just use: 'user-login-query' = '{ call TigUserLoginPlainPw(?, ?) }' with a modified version of the "new version" of TigUserLoginPlainPw ?

woj-tek commented 1 year ago

ok, so we need to re-build the code?

Not exactly. You can create a dedicated maven project, add tigase-server as dependency, create the class with the implementation suggested as above, build the project and include the resulting jar file in the tigase-server/jars/ directory.

If you want to use your solution (user-login-query with restored stored procedure) you would have to - make sure that the SP is present (it could be re-deleted during upgrades), enable MD5 hashing encoder (so that SP could still authenticate) and modify the SP to actually use correct tables and columns. It doesn't really seem like viable solution.

davidemarrone commented 1 year ago

If you want to use your solution (user-login-query with restored stored procedure) you would have to - make sure that the SP is present (it could be re-deleted during upgrades), enable MD5 hashing encoder (so that SP could still authenticate) and modify the SP to actually use correct tables and columns. It doesn't really seem like viable solution.

well for the name we could just create a new one with a custom name so it cannot be re-deleted during upgrades

Not exactly. You can create a dedicated maven project, add tigase-server as dependency, create the class with the implementation suggested as above, build the project and include the resulting jar file in the tigase-server/jars/ directory.

ok, we can create the maven project, but I did not understood how it will be linked the implementation, I guess we have to create a new string for our mechanism implementation like "CUSTOM" and add in the configuration:

    credentialDecoders () {
      'CUSTOM' () {}
    }

correct?

woj-tek commented 1 year ago

Your implementation will basically look like MD5UsernamePasswordCredentialsEntry.java.

You will have to change Encoder and Decoder bean names, value returned by .getName() methods as well as value returned by .getMechanism() that matches the names to your custom one. And then in config.tdsl file you reference your custom named bean.

Please be aware, that we do plan on deprecating MD5 based implementations, and we would suggest switching to `SCRAM-encoding (passwords in the database would be encoded as such and your clients will be able to use eitherPLAINorSCRAM-*` SASL mechanism for authentication).

davidemarrone commented 1 year ago

I more than happy to remove the MD5 implementations but there is no other solution for the users already created. We have developer our implementation and will enable only the decoder so user can connect but new users/change of password will create only SCRAM-. I need some time to test it and I will let you know if all works correctly, thank you

woj-tek commented 1 year ago

You can have decoders for both YourDecoder and SCRAM, so new users would use SCRAM and old users would use YourDecoder. But you can prompt users to update their passwords, that would trigger encoding them to SCRAM (i.e. migrating to new decoder). Alternatively you can run both of them and over the time, when the users update the passwords, all userbase would be migrated to SCRAM eventually.

davidemarrone commented 1 year ago

Hi, I have been able to upgrade the vesion changing the collation to utf8_general_ci


[2023-10-05 15:30:54:159] [CONFIG  ] [                  main ] DBSchemaLoader.init()            : Parameters: [adminPassword: ****, admins: null, dbHostname: hostname:3306, dbName: tigasedb, dbPass: ****************, dbRootPass: ****, dbRootUser: null, dbRootAsk: false, dbType: mysql, dbUser: username, file: null, ingoreMissingFiles: false, logLevel: CONFIG, query: null, serverTimezone: null, useLegacyDatetimeCode: false, useSSL: false, forceReloadSchema: false, schemaDirectory: database/, otherParameters: {allowPublicKeyRetrieval=true, characterEncoding=UTF-8}]
[2023-10-05 15:30:54:162] [INFO    ] [                  main ] DBSchemaLoader.validateDBConnection(): Validating DBConnection, URI: jdbc:mysql://hostname:3306/?user=username&password=****************&useSSL=false&useLegacyDatetimeCode=false&allowPublicKeyRetrieval=true&characterEncoding=UTF-8&allowPublicKeyRetrieval=true
[2023-10-05 15:30:54:517] [INFO    ] [                  main ] DBSchemaLoader.validateDBConnection(): Connection OK
[2023-10-05 15:30:54:522] [INFO    ] [                  main ] ConnectionLock.<init>()          : Prepared connection for locking
[2023-10-05 15:30:54:523] [INFO    ] [                  main ] ConnectionLock.lock()            : Trying to get lock for database, current state: false
[2023-10-05 15:30:54:551] [INFO    ] [                  main ] ConnectionLock.lock()            : Obtained lock for connection: jdbc:mysql://hostname:3306/?user=username&password=****************&useSSL=false&useLegacyDatetimeCode=false&allowPublicKeyRetrieval=true&characterEncoding=UTF-8&allowPublicKeyRetrieval=true
[2023-10-05 15:30:54:552] [INFO    ] [                  main ] DBSchemaLoader.validateDBExists(): Validating whether DB Exists, URI: jdbc:mysql://hostname:3306/tigasedb?user=username&password=****************&useSSL=false&useLegacyDatetimeCode=false&allowPublicKeyRetrieval=true&characterEncoding=UTF-8&allowPublicKeyRetrieval=true
[2023-10-05 15:30:54:557] [INFO    ] [                  main ] DBSchemaLoader.lambda$validateDBExists$9(): Exists OK
[2023-10-05 15:30:54:586] [WARNING ] [                  main ] DBSchemaLoader.lambda$getComponentVersionFromDb$14(): Getting version failed: { call TigGetComponentVersion( ? ) }, PROCEDURE tigasedb.TigGetComponentVersion does not exist
[2023-10-05 15:30:54:618] [WARNING ] [                  main ] DBSchemaLoader.lambda$getComponentVersionFromDb$14(): Getting version failed: { call TigGetComponentVersion( ? ) }, PROCEDURE tigasedb.TigGetComponentVersion does not exist
[2023-10-05 15:30:54:633] [WARNING ] [                  main ] DBSchemaLoader.lambda$getComponentVersionFromDb$14(): Getting version failed: { call TigGetComponentVersion( ? ) }, PROCEDURE tigasedb.TigGetComponentVersion does not exist
[2023-10-05 15:30:54:645] [WARNING ] [                  main ] DBSchemaLoader.lambda$getComponentVersionFromDb$14(): Getting version failed: { call TigGetComponentVersion( ? ) }, PROCEDURE tigasedb.TigGetComponentVersion does not exist
[2023-10-05 15:30:54:655] [WARNING ] [                  main ] DBSchemaLoader.lambda$getComponentVersionFromDb$14(): Getting version failed: { call TigGetComponentVersion( ? ) }, PROCEDURE tigasedb.TigGetComponentVersion does not exist
[2023-10-05 15:30:54:660] [INFO    ] [                  main ] ConnectionLock.unlock()          : Unlocking database
[2023-10-05 15:30:54:661] [INFO    ] [                  main ] ConnectionLock.cleanup()         : Closing lock connection
[2023-10-05 15:30:54:670] [CONFIG  ] [                  main ] DBSchemaLoader.init()            : Parameters: [adminPassword: ****, admins: null, dbHostname: hostname:3306, dbName: tigasedb, dbPass: ****************, dbRootPass: ****, dbRootUser: null, dbRootAsk: false, dbType: mysql, dbUser: username, file: null, ingoreMissingFiles: false, logLevel: CONFIG, query: null, serverTimezone: null, useLegacyDatetimeCode: false, useSSL: false, forceReloadSchema: false, schemaDirectory: database/, otherParameters: {allowPublicKeyRetrieval=true, characterEncoding=UTF-8}]
[2023-10-05 15:30:54:671] [INFO    ] [                  main ] DBSchemaLoader.validateDBConnection(): Validating DBConnection, URI: jdbc:mysql://hostname:3306/?user=username&password=****************&useSSL=false&useLegacyDatetimeCode=false&allowPublicKeyRetrieval=true&characterEncoding=UTF-8&allowPublicKeyRetrieval=true
[2023-10-05 15:30:54:674] [INFO    ] [                  main ] DBSchemaLoader.validateDBConnection(): Connection OK
[2023-10-05 15:30:54:677] [INFO    ] [                  main ] ConnectionLock.<init>()          : Prepared connection for locking
[2023-10-05 15:30:54:677] [INFO    ] [                  main ] ConnectionLock.lock()            : Trying to get lock for database, current state: false
[2023-10-05 15:30:54:678] [INFO    ] [                  main ] ConnectionLock.lock()            : Obtained lock for connection: jdbc:mysql://hostname:3306/?user=username&password=****************&useSSL=false&useLegacyDatetimeCode=false&allowPublicKeyRetrieval=true&characterEncoding=UTF-8&allowPublicKeyRetrieval=true
[2023-10-05 15:30:54:679] [INFO    ] [                  main ] DBSchemaLoader.validateDBExists(): Validating whether DB Exists, URI: jdbc:mysql://hostname:3306/tigasedb?user=username&password=****************&useSSL=false&useLegacyDatetimeCode=false&allowPublicKeyRetrieval=true&characterEncoding=UTF-8&allowPublicKeyRetrieval=true
[2023-10-05 15:30:54:682] [INFO    ] [                  main ] DBSchemaLoader.lambda$validateDBExists$9(): Exists OK
[2023-10-05 15:30:54:683] [CONFIG  ] [                  main ] DBSchemaLoader.loadSchema()      : SchemaInfo:: id: common, repositories: 0; version: 0.0.3
[2023-10-05 15:30:54:687] [WARNING ] [                  main ] DBSchemaLoader.lambda$getComponentVersionFromDb$14(): Getting version failed: { call TigGetComponentVersion( ? ) }, PROCEDURE tigasedb.TigGetComponentVersion does not exist
[2023-10-05 15:30:54:688] [CONFIG  ] [                  main ] DBSchemaLoader.loadSchemaFromSQLFiles(): Loading schema common, version: 0.0.3 from files, current: 0.0.0
[2023-10-05 15:30:54:695] [INFO    ] [                  main ] DBSchemaLoader.loadSchemaFromSQLFiles(): Schema files to load: {0.0.1=mysql-common-0.0.1.sql, 0.0.2=mysql-common-0.0.2.sql, 0.0.3=mysql-common-0.0.3.sql}
[2023-10-05 15:30:54:696] [INFO    ] [                  main ] DBSchemaLoader.loadSchemaFile()  : Loading schema from file(s): mysql-common-0.0.1.sql, URI: jdbc:mysql://hostname:3306/tigasedb?user=username&password=****************&useSSL=false&useLegacyDatetimeCode=false&allowPublicKeyRetrieval=true&characterEncoding=UTF-8&allowPublicKeyRetrieval=true
[2023-10-05 15:30:54:727] [INFO    ] [                  main ] DBSchemaLoader.lambda$loadSchemaFile$21():  completed OK
[2023-10-05 15:30:54:728] [INFO    ] [                  main ] DBSchemaLoader.loadSchemaFile()  : Loading schema from file(s): mysql-common-0.0.2.sql, URI: jdbc:mysql://hostname:3306/tigasedb?user=username&password=****************&useSSL=false&useLegacyDatetimeCode=false&allowPublicKeyRetrieval=true&characterEncoding=UTF-8&allowPublicKeyRetrieval=true
[2023-10-05 15:30:54:732] [INFO    ] [                  main ] DBSchemaLoader.lambda$loadSchemaFile$21():  completed OK
[2023-10-05 15:30:54:733] [INFO    ] [                  main ] DBSchemaLoader.loadSchemaFile()  : Loading schema from file(s): mysql-common-0.0.3.sql, URI: jdbc:mysql://hostname:3306/tigasedb?user=username&password=****************&useSSL=false&useLegacyDatetimeCode=false&allowPublicKeyRetrieval=true&characterEncoding=UTF-8&allowPublicKeyRetrieval=true
[2023-10-05 15:30:54:756] [INFO    ] [                  main ] DBSchemaLoader.lambda$loadSchemaFile$21():  completed OK
[2023-10-05 15:30:54:757] [INFO    ] [                  main ] DBSchemaLoader.setComponentVersion(): Setting version of the component: common to: 0.0.3 for connection: jdbc:mysql://hostname:3306/tigasedb?user=username&password=****************&useSSL=false&useLegacyDatetimeCode=false&allowPublicKeyRetrieval=true&characterEncoding=UTF-8&allowPublicKeyRetrieval=true
[2023-10-05 15:30:54:791] [CONFIG  ] [                  main ] DBSchemaLoader.loadSchema()      : SchemaInfo:: id: message-archiving, repositories: 1; version: 3.0.0-b308/9c934c86
[2023-10-05 15:30:54:797] [CONFIG  ] [                  main ] DBSchemaLoader.loadSchemaFromSQLFiles(): Loading schema message-archiving, version: 3.0.0-b308/9c934c86 from files, current: 0.0.0
[2023-10-05 15:30:54:801] [INFO    ] [                  main ] DBSchemaLoader.loadSchemaFromSQLFiles(): Schema files to load: {2.0.0=mysql-message-archiving-2.0.0.sql, 2.1.0=mysql-message-archiving-2.1.0.sql, 3.0.0=mysql-message-archiving-3.0.0.sql}
[2023-10-05 15:30:54:801] [INFO    ] [                  main ] DBSchemaLoader.loadSchemaFile()  : Loading schema from file(s): mysql-message-archiving-2.0.0.sql, URI: jdbc:mysql://hostname:3306/tigasedb?user=username&password=****************&useSSL=false&useLegacyDatetimeCode=false&allowPublicKeyRetrieval=true&characterEncoding=UTF-8&allowPublicKeyRetrieval=true
[2023-10-05 15:30:55:181] [INFO    ] [                  main ] DBSchemaLoader.lambda$loadSchemaFile$21():  completed OK
[2023-10-05 15:30:55:181] [INFO    ] [                  main ] DBSchemaLoader.loadSchemaFile()  : Loading schema from file(s): mysql-message-archiving-2.1.0.sql, URI: jdbc:mysql://hostname:3306/tigasedb?user=username&password=****************&useSSL=false&useLegacyDatetimeCode=false&allowPublicKeyRetrieval=true&characterEncoding=UTF-8&allowPublicKeyRetrieval=true
[2023-10-05 15:30:55:187] [INFO    ] [                  main ] DBSchemaLoader.lambda$loadSchemaFile$21():  completed OK
[2023-10-05 15:30:55:188] [INFO    ] [                  main ] DBSchemaLoader.loadSchemaFile()  : Loading schema from file(s): mysql-message-archiving-3.0.0.sql, URI: jdbc:mysql://hostname:3306/tigasedb?user=username&password=****************&useSSL=false&useLegacyDatetimeCode=false&allowPublicKeyRetrieval=true&characterEncoding=UTF-8&allowPublicKeyRetrieval=true
[2023-10-05 15:30:55:675] [INFO    ] [                  main ] DBSchemaLoader.lambda$loadSchemaFile$21():  completed OK
[2023-10-05 15:30:55:675] [INFO    ] [                  main ] DBSchemaLoader.setComponentVersion(): Setting version of the component: message-archiving to: 3.0.0-b308 for connection: jdbc:mysql://hostname:3306/tigasedb?user=username&password=****************&useSSL=false&useLegacyDatetimeCode=false&allowPublicKeyRetrieval=true&characterEncoding=UTF-8&allowPublicKeyRetrieval=true
[2023-10-05 15:30:55:728] [CONFIG  ] [                  main ] DBSchemaLoader.loadSchema()      : SchemaInfo:: id: server, repositories: 5; version: 8.2.0-b5938/6b5a3651
[2023-10-05 15:30:55:734] [CONFIG  ] [                  main ] DBSchemaLoader.loadSchemaFromSQLFiles(): Loading schema server, version: 8.2.0-b5938/6b5a3651 from files, current: 0.0.0
[2023-10-05 15:30:55:737] [INFO    ] [                  main ] DBSchemaLoader.loadSchemaFromSQLFiles(): Schema files to load: {8.0.0=mysql-server-8.0.0.sql, 8.1.0=mysql-server-8.1.0.sql, 8.2.0=mysql-server-8.2.0.sql}
[2023-10-05 15:30:55:738] [INFO    ] [                  main ] DBSchemaLoader.loadSchemaFile()  : Loading schema from file(s): mysql-server-8.0.0.sql, URI: jdbc:mysql://hostname:3306/tigasedb?user=username&password=****************&useSSL=false&useLegacyDatetimeCode=false&allowPublicKeyRetrieval=true&characterEncoding=UTF-8&allowPublicKeyRetrieval=true
[2023-10-05 15:30:56:082] [INFO    ] [                  main ] DBSchemaLoader.lambda$loadSchemaFile$21():  completed OK
[2023-10-05 15:30:56:083] [INFO    ] [                  main ] DBSchemaLoader.loadSchemaFile()  : Loading schema from file(s): mysql-server-8.1.0.sql, URI: jdbc:mysql://hostname:3306/tigasedb?user=username&password=****************&useSSL=false&useLegacyDatetimeCode=false&allowPublicKeyRetrieval=true&characterEncoding=UTF-8&allowPublicKeyRetrieval=true
[2023-10-05 15:30:56:090] [INFO    ] [                  main ] DBSchemaLoader.lambda$loadSchemaFile$21():  completed OK
[2023-10-05 15:30:56:090] [INFO    ] [                  main ] DBSchemaLoader.loadSchemaFile()  : Loading schema from file(s): mysql-server-8.2.0.sql, URI: jdbc:mysql://hostname:3306/tigasedb?user=username&password=****************&useSSL=false&useLegacyDatetimeCode=false&allowPublicKeyRetrieval=true&characterEncoding=UTF-8&allowPublicKeyRetrieval=true
[2023-10-05 15:46:13:667] [INFO    ] [                  main ] DBSchemaLoader.lambda$loadSchemaFile$21():  completed OK
[2023-10-05 15:46:13:668] [INFO    ] [                  main ] DBSchemaLoader.setComponentVersion(): Setting version of the component: server to: 8.2.0-b5938 for connection: jdbc:mysql://hostname:3306/tigasedb?user=username&password=****************&useSSL=false&useLegacyDatetimeCode=false&allowPublicKeyRetrieval=true&characterEncoding=UTF-8&allowPublicKeyRetrieval=true
[2023-10-05 15:46:13:689] [WARNING ] [                  main ] DBSchemaLoader.lambda$loadSchema$16(): You have 'password-encoding' property set to MD5-USERNAME-PASSWORD.
This setting will no longer work out of the box with this version of Tigase XMPP Server.
Please check Tigase XMPP Server Administration Guide, section "Changes to Schema in v8.0.0" at http://docs.tigase.org/ for more details.
[2023-10-05 15:46:13:695] [CONFIG  ] [                  main ] DBSchemaLoader.loadSchema()      : SchemaInfo:: id: muc, repositories: 2; version: 3.2.0-b669/c05b2edc
[2023-10-05 15:46:13:700] [CONFIG  ] [                  main ] DBSchemaLoader.loadSchemaFromSQLFiles(): Loading schema muc, version: 3.2.0-b669/c05b2edc from files, current: 0.0.0
[2023-10-05 15:46:13:705] [INFO    ] [                  main ] DBSchemaLoader.loadSchemaFromSQLFiles(): Schema files to load: {3.0.0=mysql-muc-3.0.0.sql, 3.1.0=mysql-muc-3.1.0.sql, 3.2.0=mysql-muc-3.2.0.sql}
[2023-10-05 15:46:13:705] [INFO    ] [                  main ] DBSchemaLoader.loadSchemaFile()  : Loading schema from file(s): mysql-muc-3.0.0.sql, URI: jdbc:mysql://hostname:3306/tigasedb?user=username&password=****************&useSSL=false&useLegacyDatetimeCode=false&allowPublicKeyRetrieval=true&characterEncoding=UTF-8&allowPublicKeyRetrieval=true
[2023-10-05 15:46:13:856] [INFO    ] [                  main ] DBSchemaLoader.lambda$loadSchemaFile$21():  completed OK
[2023-10-05 15:46:13:856] [INFO    ] [                  main ] DBSchemaLoader.loadSchemaFile()  : Loading schema from file(s): mysql-muc-3.1.0.sql, URI: jdbc:mysql://hostname:3306/tigasedb?user=username&password=****************&useSSL=false&useLegacyDatetimeCode=false&allowPublicKeyRetrieval=true&characterEncoding=UTF-8&allowPublicKeyRetrieval=true
[2023-10-05 15:46:13:997] [INFO    ] [                  main ] DBSchemaLoader.lambda$loadSchemaFile$21():  completed OK
[2023-10-05 15:46:13:997] [INFO    ] [                  main ] DBSchemaLoader.loadSchemaFile()  : Loading schema from file(s): mysql-muc-3.2.0.sql, URI: jdbc:mysql://hostname:3306/tigasedb?user=username&password=****************&useSSL=false&useLegacyDatetimeCode=false&allowPublicKeyRetrieval=true&characterEncoding=UTF-8&allowPublicKeyRetrieval=true
[2023-10-05 15:46:13:999] [INFO    ] [                  main ] DBSchemaLoader.lambda$loadSchemaFile$21():  completed OK
[2023-10-05 15:46:14:000] [INFO    ] [                  main ] DBSchemaLoader.setComponentVersion(): Setting version of the component: muc to: 3.2.0-b669 for connection: jdbc:mysql://hostname:3306/tigasedb?user=username&password=****************&useSSL=false&useLegacyDatetimeCode=false&allowPublicKeyRetrieval=true&characterEncoding=UTF-8&allowPublicKeyRetrieval=true
[2023-10-05 15:46:14:016] [CONFIG  ] [                  main ] DBSchemaLoader.loadSchema()      : SchemaInfo:: id: http-api, repositories: 1; version: 2.2.0-b381/9a1646af
[2023-10-05 15:46:14:020] [CONFIG  ] [                  main ] DBSchemaLoader.loadSchemaFromSQLFiles(): Loading schema http-api, version: 2.2.0-b381/9a1646af from files, current: 0.0.0
[2023-10-05 15:46:14:022] [INFO    ] [                  main ] DBSchemaLoader.loadSchemaFromSQLFiles(): Schema files to load: {2.0.0=mysql-http-api-2.0.0.sql, 2.1.0=mysql-http-api-2.1.0.sql, 2.2.0=mysql-http-api-2.2.0.sql}
[2023-10-05 15:46:14:023] [INFO    ] [                  main ] DBSchemaLoader.loadSchemaFile()  : Loading schema from file(s): mysql-http-api-2.0.0.sql, URI: jdbc:mysql://hostname:3306/tigasedb?user=username&password=****************&useSSL=false&useLegacyDatetimeCode=false&allowPublicKeyRetrieval=true&characterEncoding=UTF-8&allowPublicKeyRetrieval=true
[2023-10-05 15:46:14:119] [INFO    ] [                  main ] DBSchemaLoader.lambda$loadSchemaFile$21():  completed OK
[2023-10-05 15:46:14:119] [INFO    ] [                  main ] DBSchemaLoader.loadSchemaFile()  : Loading schema from file(s): mysql-http-api-2.1.0.sql, URI: jdbc:mysql://hostname:3306/tigasedb?user=username&password=****************&useSSL=false&useLegacyDatetimeCode=false&allowPublicKeyRetrieval=true&characterEncoding=UTF-8&allowPublicKeyRetrieval=true
[2023-10-05 15:46:14:123] [INFO    ] [                  main ] DBSchemaLoader.lambda$loadSchemaFile$21():  completed OK
[2023-10-05 15:46:14:124] [INFO    ] [                  main ] DBSchemaLoader.loadSchemaFile()  : Loading schema from file(s): mysql-http-api-2.2.0.sql, URI: jdbc:mysql://hostname:3306/tigasedb?user=username&password=****************&useSSL=false&useLegacyDatetimeCode=false&allowPublicKeyRetrieval=true&characterEncoding=UTF-8&allowPublicKeyRetrieval=true
[2023-10-05 15:46:14:152] [INFO    ] [                  main ] DBSchemaLoader.lambda$loadSchemaFile$21():  completed OK
[2023-10-05 15:46:14:153] [INFO    ] [                  main ] DBSchemaLoader.setComponentVersion(): Setting version of the component: http-api to: 2.2.0-b381 for connection: jdbc:mysql://hostname:3306/tigasedb?user=username&password=****************&useSSL=false&useLegacyDatetimeCode=false&allowPublicKeyRetrieval=true&characterEncoding=UTF-8&allowPublicKeyRetrieval=true
[2023-10-05 15:46:14:168] [CONFIG  ] [                  main ] DBSchemaLoader.loadSchema()      : SchemaInfo:: id: pubsub, repositories: 1; version: 5.0.1-b802/e24359b9
[2023-10-05 15:46:14:173] [CONFIG  ] [                  main ] DBSchemaLoader.loadSchemaFromSQLFiles(): Loading schema pubsub, version: 5.0.1-b802/e24359b9 from files, current: 0.0.0
[2023-10-05 15:46:14:175] [INFO    ] [                  main ] DBSchemaLoader.loadSchemaFromSQLFiles(): Schema files to load: {4.0.0=mysql-pubsub-4.0.0.sql, 4.1.0=mysql-pubsub-4.1.0.sql, 5.0.0=mysql-pubsub-5.0.0.sql, 5.0.1=mysql-pubsub-5.0.1.sql}
[2023-10-05 15:46:14:176] [INFO    ] [                  main ] DBSchemaLoader.loadSchemaFile()  : Loading schema from file(s): mysql-pubsub-4.0.0.sql, URI: jdbc:mysql://hostname:3306/tigasedb?user=username&password=****************&useSSL=false&useLegacyDatetimeCode=false&allowPublicKeyRetrieval=true&characterEncoding=UTF-8&allowPublicKeyRetrieval=true
[2023-10-05 15:46:14:388] [INFO    ] [                  main ] DBSchemaLoader.lambda$loadSchemaFile$21():  completed OK
[2023-10-05 15:46:14:388] [INFO    ] [                  main ] DBSchemaLoader.loadSchemaFile()  : Loading schema from file(s): mysql-pubsub-4.1.0.sql, URI: jdbc:mysql://hostname:3306/tigasedb?user=username&password=****************&useSSL=false&useLegacyDatetimeCode=false&allowPublicKeyRetrieval=true&characterEncoding=UTF-8&allowPublicKeyRetrieval=true
[2023-10-05 15:46:14:407] [INFO    ] [                  main ] DBSchemaLoader.lambda$loadSchemaFile$21():  completed OK
[2023-10-05 15:46:14:408] [INFO    ] [                  main ] DBSchemaLoader.loadSchemaFile()  : Loading schema from file(s): mysql-pubsub-5.0.0.sql, URI: jdbc:mysql://hostname:3306/tigasedb?user=username&password=****************&useSSL=false&useLegacyDatetimeCode=false&allowPublicKeyRetrieval=true&characterEncoding=UTF-8&allowPublicKeyRetrieval=true
[2023-10-05 15:46:14:739] [INFO    ] [                  main ] DBSchemaLoader.lambda$loadSchemaFile$21():  completed OK
[2023-10-05 15:46:14:740] [INFO    ] [                  main ] DBSchemaLoader.loadSchemaFile()  : Loading schema from file(s): mysql-pubsub-5.0.1.sql, URI: jdbc:mysql://hostname:3306/tigasedb?user=username&password=****************&useSSL=false&useLegacyDatetimeCode=false&allowPublicKeyRetrieval=true&characterEncoding=UTF-8&allowPublicKeyRetrieval=true
[2023-10-05 15:46:14:777] [INFO    ] [                  main ] DBSchemaLoader.lambda$loadSchemaFile$21():  completed OK
[2023-10-05 15:46:14:777] [INFO    ] [                  main ] DBSchemaLoader.setComponentVersion(): Setting version of the component: pubsub to: 5.0.1-b802 for connection: jdbc:mysql://hostname:3306/tigasedb?user=username&password=****************&useSSL=false&useLegacyDatetimeCode=false&allowPublicKeyRetrieval=true&characterEncoding=UTF-8&allowPublicKeyRetrieval=true
[2023-10-05 15:46:14:785] [WARNING ] [                  main ] DBSchemaLoader.addXmppAdminAccount(): Error: No admin users entered
[2023-10-05 15:46:14:786] [WARNING ] [                  main ] DBSchemaLoader.postInstallation(): No database root user credentials, skipping post database creation scripts.
[2023-10-05 15:46:14:786] [INFO    ] [                  main ] ConnectionLock.unlock()          : Unlocking database
[2023-10-05 15:46:14:786] [INFO    ] [                  main ] ConnectionLock.cleanup()         : Closing lock connection
[2023-10-05 15:46:14:786] [INFO    ] [                  main ] SchemaManager.loadSchemas()      : schema loading finished!

  =============================================================================
    Schema upgrade finished

  Data source: default with uri jdbc:mysql://hostname:3306/tigasedb?user=username&password=****************&useSSL=false&useLegacyDatetimeCode=false&allowPublicKeyRetrieval=true&characterEncoding=UTF-8
    Checking connection to database ok
    Checking if database exists ok
    Loading Common Schema Files ok
    Loading schema: Tigase Message Archiving Component, version: 3.0.0-b308/9c934c86 (database version: none)   ok
    Loading schema: Tigase XMPP Server (Core), version: 8.2.0-b5938/6b5a3651 (database version: none)   warning
        Message: Schema files to load: {8.0.0=mysql-server-8.0.0.sql, 8.1.0=mysql-server-8.1.0.sql, 8.2.0=mysql-server-8.2.0.sql}
                 Loading schema from file(s): mysql-server-8.0.0.sql, URI: jdbc:mysql://hostname:3306/tigasedb?user=username&password=****************&useSSL=false&useLegacyDatetimeCode=false&allowPublicKeyRetrieval=true&characterEncoding=UTF-8&allowPublicKeyRetrieval=true
                  completed OK
                 Loading schema from file(s): mysql-server-8.1.0.sql, URI: jdbc:mysql://hostname:3306/tigasedb?user=username&password=****************&useSSL=false&useLegacyDatetimeCode=false&allowPublicKeyRetrieval=true&characterEncoding=UTF-8&allowPublicKeyRetrieval=true
                  completed OK
                 Loading schema from file(s): mysql-server-8.2.0.sql, URI: jdbc:mysql://hostname:3306/tigasedb?user=username&password=****************&useSSL=false&useLegacyDatetimeCode=false&allowPublicKeyRetrieval=true&characterEncoding=UTF-8&allowPublicKeyRetrieval=true
                  completed OK
                 Setting version of the component: server to: 8.2.0-b5938 for connection: jdbc:mysql://hostname:3306/tigasedb?user=username&password=****************&useSSL=false&useLegacyDatetimeCode=false&allowPublicKeyRetrieval=true&characterEncoding=UTF-8&allowPublicKeyRetrieval=true
                 You have 'password-encoding' property set to MD5-USERNAME-PASSWORD.
                 This setting will no longer work out of the box with this version of Tigase XMPP Server.
                 Please check Tigase XMPP Server Administration Guide, section "Changes to Schema in v8.0.0" at http://docs.tigase.org/ for more details.
    Loading schema: Tigase MUC Component, version: 3.2.0-b669/c05b2edc (database version: none) ok
    Loading schema: Tigase HTTP API - File Upload, version: 2.2.0-b381/9a1646af (database version: none)    ok
    Loading schema: Tigase PubSub Component, version: 5.0.1-b802/e24359b9 (database version: none)  ok
    Adding XMPP admin accounts  warning
        Message: Error: No admin users entered
    Post installation action    skipped
        Message: No database root user credentials, skipping post database creation scripts.
  =============================================================================

it seems all correct, right?

I have notice that some table withour 'tig_' prefix are still there:

broadcast_msgs
broadcast_msgs_recipients
cluster_nodes
msg_history
short_news
user_jid
xmpp_stanza

is it correct that they are not deleted during the migration? Can I delete them?

I have also noticed that the table tig_offline_messages is empty also if I have a lot of messages in msg_history there is no migration for that messages?

woj-tek commented 1 year ago

Hmm... it's because we support upgrading only from 3 "big" versions back, thus you could upgrade to 8.2.* from 7.2.x (7.2 -> 8.0 -> 8.1). If you want to upgrade from 7.1.x you have to upgrade to 8.1.x first and then you can upgrade to either 8.2.x or even 8.3.x.

davidemarrone commented 1 year ago

Could you try running upgrade-schema with -L ALL parameter?

I have to call with: ./scripts/tigase.sh upgrade-schema -L ALL etc/tigase.conf ?

Could you also check if the distribution package that you have contain same source of TigUpgradeMsgHistory() procedure as the one linked above.

from: https://github.com/tigase/tigase-server/releases/download/tigase-server-8.2.2/tigase-server-8.2.2-b11697-dist-enterprise.zip

davide:/tmp/test/tigase-server-8.2.2-b11697/database$ grep TigUpgradeMsgHistory  *
davide:/tmp/test/tigase-server-8.2.2-b11697/database$ grep msg_history *

there is no reference to that code

woj-tek commented 1 year ago

@davidemarrone yes, my bad. Please see my previous commend regarding version upgrade compatibility.

davidemarrone commented 1 year ago

ok thanks, tomorrow I will try to update to 8.1.x and I will let you know

davidemarrone commented 1 year ago

It worked:

[2023-10-06 07:08:58:051] [CONFIG  ] [                  main ] DBSchemaLoader.init()            : Parameters: [adminPassword: null, admins: null, dbHostname: dbhostname:3306, dbName: tigasedb, dbPass: dbPassword, dbRootPass: dbPassword, dbRootUser: dbuser, dbType: mysql, dbUser: dbUser, file: null, ingoreMissingFiles: false, logLevel: CONFIG, query: null, serverTimezone: null, useLegacyDatetimeCode: false, useSSL: false, forceReloadSchema: false, schemaDirectory: database/, otherParameters: {allowPublicKeyRetrieval=true, characterEncoding=UTF-8}]
[2023-10-06 07:08:58:052] [INFO    ] [                  main ] DBSchemaLoader.validateDBConnection(): Validating DBConnection, URI: jdbc:mysql://dbhostname:3306/?user=dbuser&password=dbPassword&useSSL=false&useLegacyDatetimeCode=false&allowPublicKeyRetrieval=true&characterEncoding=UTF-8&allowPublicKeyRetrieval=true
[2023-10-06 07:08:58:413] [INFO    ] [                  main ] DBSchemaLoader.validateDBConnection(): Connection OK
[2023-10-06 07:08:58:414] [INFO    ] [                  main ] DBSchemaLoader.validateDBExists(): Validating whether DB Exists, URI: jdbc:mysql://dbhostname:3306/tigasedb?user=dbUser&password=dbPassword&useSSL=false&useLegacyDatetimeCode=false&allowPublicKeyRetrieval=true&characterEncoding=UTF-8&allowPublicKeyRetrieval=true
[2023-10-06 07:08:58:420] [INFO    ] [                  main ] DBSchemaLoader.lambda$validateDBExists$9(): Exists OK
[2023-10-06 07:08:58:444] [WARNING ] [                  main ] DBSchemaLoader.lambda$getComponentVersionFromDb$14(): Getting version failed: { call TigGetComponentVersion( ? ) }, PROCEDURE tigasedb.TigGetComponentVersion does not exist
[2023-10-06 07:08:58:474] [WARNING ] [                  main ] DBSchemaLoader.lambda$getComponentVersionFromDb$14(): Getting version failed: { call TigGetComponentVersion( ? ) }, PROCEDURE tigasedb.TigGetComponentVersion does not exist
[2023-10-06 07:08:58:494] [WARNING ] [                  main ] DBSchemaLoader.lambda$getComponentVersionFromDb$14(): Getting version failed: { call TigGetComponentVersion( ? ) }, PROCEDURE tigasedb.TigGetComponentVersion does not exist
[2023-10-06 07:08:58:509] [CONFIG  ] [                  main ] DBSchemaLoader.init()            : Parameters: [adminPassword: null, admins: null, dbHostname: dbhostname:3306, dbName: tigasedb, dbPass: dbPassword, dbRootPass: dbPassword, dbRootUser: dbuser, dbType: mysql, dbUser: dbUser, file: null, ingoreMissingFiles: false, logLevel: CONFIG, query: null, serverTimezone: null, useLegacyDatetimeCode: false, useSSL: false, forceReloadSchema: false, schemaDirectory: database/, otherParameters: {allowPublicKeyRetrieval=true, characterEncoding=UTF-8}]
[2023-10-06 07:08:58:509] [INFO    ] [                  main ] DBSchemaLoader.validateDBConnection(): Validating DBConnection, URI: jdbc:mysql://dbhostname:3306/?user=dbuser&password=dbPassword&useSSL=false&useLegacyDatetimeCode=false&allowPublicKeyRetrieval=true&characterEncoding=UTF-8&allowPublicKeyRetrieval=true
[2023-10-06 07:08:58:514] [INFO    ] [                  main ] DBSchemaLoader.validateDBConnection(): Connection OK
[2023-10-06 07:08:58:515] [INFO    ] [                  main ] DBSchemaLoader.validateDBExists(): Validating whether DB Exists, URI: jdbc:mysql://dbhostname:3306/tigasedb?user=dbUser&password=dbPassword&useSSL=false&useLegacyDatetimeCode=false&allowPublicKeyRetrieval=true&characterEncoding=UTF-8&allowPublicKeyRetrieval=true
[2023-10-06 07:08:58:519] [INFO    ] [                  main ] DBSchemaLoader.lambda$validateDBExists$9(): Exists OK
[2023-10-06 07:08:58:519] [CONFIG  ] [                  main ] DBSchemaLoader.loadSchema()      : SchemaInfo:: id: common, repositories: 0; version: 0.0.3
[2023-10-06 07:08:58:525] [WARNING ] [                  main ] DBSchemaLoader.lambda$getComponentVersionFromDb$14(): Getting version failed: { call TigGetComponentVersion( ? ) }, PROCEDURE tigasedb.TigGetComponentVersion does not exist
[2023-10-06 07:08:58:526] [CONFIG  ] [                  main ] DBSchemaLoader.loadSchemaFromSQLFiles(): Loading schema common, version: 0.0.3 from files, current: 0.0.0
[2023-10-06 07:08:58:537] [INFO    ] [                  main ] DBSchemaLoader.loadSchemaFromSQLFiles(): Schema files to load: {0.0.1=mysql-common-0.0.1.sql, 0.0.2=mysql-common-0.0.2.sql, 0.0.3=mysql-common-0.0.3.sql}
[2023-10-06 07:08:58:538] [INFO    ] [                  main ] DBSchemaLoader.loadSchemaFile()  : Loading schema from file(s): mysql-common-0.0.1.sql, URI: jdbc:mysql://dbhostname:3306/tigasedb?user=dbUser&password=dbPassword&useSSL=false&useLegacyDatetimeCode=false&allowPublicKeyRetrieval=true&characterEncoding=UTF-8&allowPublicKeyRetrieval=true
[2023-10-06 07:08:58:571] [INFO    ] [                  main ] DBSchemaLoader.lambda$loadSchemaFile$21():  completed OK
[2023-10-06 07:08:58:572] [INFO    ] [                  main ] DBSchemaLoader.loadSchemaFile()  : Loading schema from file(s): mysql-common-0.0.2.sql, URI: jdbc:mysql://dbhostname:3306/tigasedb?user=dbUser&password=dbPassword&useSSL=false&useLegacyDatetimeCode=false&allowPublicKeyRetrieval=true&characterEncoding=UTF-8&allowPublicKeyRetrieval=true
[2023-10-06 07:08:58:578] [INFO    ] [                  main ] DBSchemaLoader.lambda$loadSchemaFile$21():  completed OK
[2023-10-06 07:08:58:579] [INFO    ] [                  main ] DBSchemaLoader.loadSchemaFile()  : Loading schema from file(s): mysql-common-0.0.3.sql, URI: jdbc:mysql://dbhostname:3306/tigasedb?user=dbUser&password=dbPassword&useSSL=false&useLegacyDatetimeCode=false&allowPublicKeyRetrieval=true&characterEncoding=UTF-8&allowPublicKeyRetrieval=true
[2023-10-06 07:08:58:594] [INFO    ] [                  main ] DBSchemaLoader.lambda$loadSchemaFile$21():  completed OK
[2023-10-06 07:08:58:594] [INFO    ] [                  main ] DBSchemaLoader.setComponentVersion(): Setting version of the component: common to: 0.0.3 for connection: jdbc:mysql://dbhostname:3306/tigasedb?user=dbUser&password=dbPassword&useSSL=false&useLegacyDatetimeCode=false&allowPublicKeyRetrieval=true&characterEncoding=UTF-8&allowPublicKeyRetrieval=true
[2023-10-06 07:08:58:615] [CONFIG  ] [                  main ] DBSchemaLoader.loadSchema()      : SchemaInfo:: id: message-archiving, repositories: 1; version: 2.1.0-b270/b48b562a
[2023-10-06 07:08:58:621] [CONFIG  ] [                  main ] DBSchemaLoader.loadSchemaFromSQLFiles(): Loading schema message-archiving, version: 2.1.0-b270/b48b562a from files, current: 0.0.0
[2023-10-06 07:08:58:627] [INFO    ] [                  main ] DBSchemaLoader.loadSchemaFromSQLFiles(): Schema files to load: {2.0.0=mysql-message-archiving-2.0.0.sql, 2.1.0=mysql-message-archiving-2.1.0.sql}
[2023-10-06 07:08:58:628] [INFO    ] [                  main ] DBSchemaLoader.loadSchemaFile()  : Loading schema from file(s): mysql-message-archiving-2.0.0.sql, URI: jdbc:mysql://dbhostname:3306/tigasedb?user=dbUser&password=dbPassword&useSSL=false&useLegacyDatetimeCode=false&allowPublicKeyRetrieval=true&characterEncoding=UTF-8&allowPublicKeyRetrieval=true
[2023-10-06 07:08:58:960] [INFO    ] [                  main ] DBSchemaLoader.lambda$loadSchemaFile$21():  completed OK
[2023-10-06 07:08:58:961] [INFO    ] [                  main ] DBSchemaLoader.loadSchemaFile()  : Loading schema from file(s): mysql-message-archiving-2.1.0.sql, URI: jdbc:mysql://dbhostname:3306/tigasedb?user=dbUser&password=dbPassword&useSSL=false&useLegacyDatetimeCode=false&allowPublicKeyRetrieval=true&characterEncoding=UTF-8&allowPublicKeyRetrieval=true
[2023-10-06 07:08:58:969] [INFO    ] [                  main ] DBSchemaLoader.lambda$loadSchemaFile$21():  completed OK
[2023-10-06 07:08:58:969] [INFO    ] [                  main ] DBSchemaLoader.setComponentVersion(): Setting version of the component: message-archiving to: 2.1.0-b270 for connection: jdbc:mysql://dbhostname:3306/tigasedb?user=dbUser&password=dbPassword&useSSL=false&useLegacyDatetimeCode=false&allowPublicKeyRetrieval=true&characterEncoding=UTF-8&allowPublicKeyRetrieval=true
[2023-10-06 07:08:58:985] [CONFIG  ] [                  main ] DBSchemaLoader.loadSchema()      : SchemaInfo:: id: server, repositories: 5; version: 8.1.1-b5650/86bb4486
[2023-10-06 07:08:58:990] [CONFIG  ] [                  main ] DBSchemaLoader.loadSchemaFromSQLFiles(): Loading schema server, version: 8.1.1-b5650/86bb4486 from files, current: 0.0.0
[2023-10-06 07:08:58:995] [INFO    ] [                  main ] DBSchemaLoader.loadSchemaFromSQLFiles(): Schema files to load: {7.1.0=mysql-server-7.1.0.sql, 8.0.0=mysql-server-8.0.0.sql, 8.1.0=mysql-server-8.1.0.sql, 8.1.1=mysql-server-8.1.1.sql}
[2023-10-06 07:08:58:996] [INFO    ] [                  main ] DBSchemaLoader.loadSchemaFile()  : Loading schema from file(s): mysql-server-7.1.0.sql, URI: jdbc:mysql://dbhostname:3306/tigasedb?user=dbUser&password=dbPassword&useSSL=false&useLegacyDatetimeCode=false&allowPublicKeyRetrieval=true&characterEncoding=UTF-8&allowPublicKeyRetrieval=true
[2023-10-06 07:09:22:018] [INFO    ] [                  main ] DBSchemaLoader.lambda$loadSchemaFile$21():  completed OK
[2023-10-06 07:09:22:019] [INFO    ] [                  main ] DBSchemaLoader.loadSchemaFile()  : Loading schema from file(s): mysql-server-8.0.0.sql, URI: jdbc:mysql://dbhostname:3306/tigasedb?user=dbUser&password=dbPassword&useSSL=false&useLegacyDatetimeCode=false&allowPublicKeyRetrieval=true&characterEncoding=UTF-8&allowPublicKeyRetrieval=true
Oct 06, 2023 7:19:17 AM tigase.licence.InstallationIdRetriever <init>
WARNING: There was a problem adding user licence-library to the repository! Please make sure your database is configured correctly
Oct 06, 2023 7:19:17 AM tigase.licence.InstallationIdRetriever c
WARNING: There was a problem accessing repository to retrieve ID
Oct 06, 2023 7:19:17 AM tigase.licence.InstallationIdRetriever c
WARNING: There was a problem accessing repository to retrieve ID
java.lang.NullPointerException
        at tigase.server.XMPPServer.getComponent(XMPPServer.java:90)
        at tigase.licence.InstallationIdRetriever.c(SourceFile:203)
        at tigase.licence.InstallationIdRetriever.b(SourceFile:116)
        at tigase.licence.LicenceCheckDailyTask$1.run(SourceFile:105)
        at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:515)
        at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264)
        at java.base/java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:304)
        at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
        at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
        at java.base/java.lang.Thread.run(Thread.java:829)

[2023-10-06 08:40:36:313] [INFO    ] [                  main ] DBSchemaLoader.lambda$loadSchemaFile$21():  completed OK
[2023-10-06 08:40:36:313] [INFO    ] [                  main ] DBSchemaLoader.loadSchemaFile()  : Loading schema from file(s): mysql-server-8.1.0.sql, URI: jdbc:mysql://dbhostname:3306/tigasedb?user=dbUser&password=dbPassword&useSSL=false&useLegacyDatetimeCode=false&allowPublicKeyRetrieval=true&characterEncoding=UTF-8&allowPublicKeyRetrieval=true
[2023-10-06 08:40:55:671] [INFO    ] [                  main ] DBSchemaLoader.lambda$loadSchemaFile$21():  completed OK
[2023-10-06 08:40:55:671] [INFO    ] [                  main ] DBSchemaLoader.loadSchemaFile()  : Loading schema from file(s): mysql-server-8.1.1.sql, URI: jdbc:mysql://dbhostname:3306/tigasedb?user=dbUser&password=dbPassword&useSSL=false&useLegacyDatetimeCode=false&allowPublicKeyRetrieval=true&characterEncoding=UTF-8&allowPublicKeyRetrieval=true
[2023-10-06 08:40:55:676] [INFO    ] [                  main ] DBSchemaLoader.lambda$loadSchemaFile$21():  completed OK
[2023-10-06 08:40:55:676] [INFO    ] [                  main ] DBSchemaLoader.setComponentVersion(): Setting version of the component: server to: 8.1.1-b5650 for connection: jdbc:mysql://dbhostname:3306/tigasedb?user=dbUser&password=dbPassword&useSSL=false&useLegacyDatetimeCode=false&allowPublicKeyRetrieval=true&characterEncoding=UTF-8&allowPublicKeyRetrieval=true
[2023-10-06 08:40:55:764] [WARNING ] [                  main ] DBSchemaLoader.lambda$loadSchema$16(): You have 'password-encoding' property set to MD5-USERNAME-PASSWORD.
This setting will no longer work out of the box with this version of Tigase XMPP Server.
Please check Tigase XMPP Server Administration Guide, section "Changes to Schema in v8.0.0" at http://docs.tigase.org/ for more details.
[2023-10-06 08:40:55:769] [CONFIG  ] [                  main ] DBSchemaLoader.loadSchema()      : SchemaInfo:: id: muc, repositories: 2; version: 3.1.2-b649/5e056afa
[2023-10-06 08:40:55:774] [CONFIG  ] [                  main ] DBSchemaLoader.loadSchemaFromSQLFiles(): Loading schema muc, version: 3.1.2-b649/5e056afa from files, current: 0.0.0
[2023-10-06 08:40:55:776] [INFO    ] [                  main ] DBSchemaLoader.loadSchemaFromSQLFiles(): Schema files to load: {3.0.0=mysql-muc-3.0.0.sql, 3.1.0=mysql-muc-3.1.0.sql, 3.1.2=mysql-muc-3.1.2.sql}
[2023-10-06 08:40:55:777] [INFO    ] [                  main ] DBSchemaLoader.loadSchemaFile()  : Loading schema from file(s): mysql-muc-3.0.0.sql, URI: jdbc:mysql://dbhostname:3306/tigasedb?user=dbUser&password=dbPassword&useSSL=false&useLegacyDatetimeCode=false&allowPublicKeyRetrieval=true&characterEncoding=UTF-8&allowPublicKeyRetrieval=true
[2023-10-06 08:40:57:367] [INFO    ] [                  main ] DBSchemaLoader.lambda$loadSchemaFile$21():  completed OK
[2023-10-06 08:40:57:368] [INFO    ] [                  main ] DBSchemaLoader.loadSchemaFile()  : Loading schema from file(s): mysql-muc-3.1.0.sql, URI: jdbc:mysql://dbhostname:3306/tigasedb?user=dbUser&password=dbPassword&useSSL=false&useLegacyDatetimeCode=false&allowPublicKeyRetrieval=true&characterEncoding=UTF-8&allowPublicKeyRetrieval=true
[2023-10-06 08:40:58:523] [INFO    ] [                  main ] DBSchemaLoader.lambda$loadSchemaFile$21():  completed OK
[2023-10-06 08:40:58:523] [INFO    ] [                  main ] DBSchemaLoader.loadSchemaFile()  : Loading schema from file(s): mysql-muc-3.1.2.sql, URI: jdbc:mysql://dbhostname:3306/tigasedb?user=dbUser&password=dbPassword&useSSL=false&useLegacyDatetimeCode=false&allowPublicKeyRetrieval=true&characterEncoding=UTF-8&allowPublicKeyRetrieval=true
[2023-10-06 08:40:58:526] [INFO    ] [                  main ] DBSchemaLoader.lambda$loadSchemaFile$21():  completed OK
[2023-10-06 08:40:58:527] [INFO    ] [                  main ] DBSchemaLoader.setComponentVersion(): Setting version of the component: muc to: 3.1.2-b649 for connection: jdbc:mysql://dbhostname:3306/tigasedb?user=dbUser&password=dbPassword&useSSL=false&useLegacyDatetimeCode=false&allowPublicKeyRetrieval=true&characterEncoding=UTF-8&allowPublicKeyRetrieval=true
[2023-10-06 08:40:58:577] [WARNING ] [                  main ] DBSchemaLoader.addXmppAdminAccount(): Error: No admin users entered
[2023-10-06 08:40:58:577] [INFO    ] [                  main ] DBSchemaLoader.postInstallation(): Post Installation, URI: jdbc:mysql://dbhostname:3306/tigasedb?user=dbuser&password=dbPassword&useSSL=false&useLegacyDatetimeCode=false&allowPublicKeyRetrieval=true&characterEncoding=UTF-8&allowPublicKeyRetrieval=true
[2023-10-06 08:40:58:580] [INFO    ] [                  main ] DBSchemaLoader.lambda$postInstallation$12(): Finalizing...
[2023-10-06 08:40:58:581] [INFO    ] [                  main ] DBSchemaLoader.lambda$postInstallation$12():  completed OK
Oct 06, 2023 8:40:58 AM tigase.db.util.SchemaManager loadSchemas
INFO: schema loading finished!

  =============================================================================
        Schema upgrade finished

        Data source: default with uri jdbc:mysql://dbhostname:3306/tigasedb?user=dbUser&password=dbPassword&useSSL=false&useLegacyDatetimeCode=false&allowPublicKeyRetrieval=true&characterEncoding=UTF-8
        Checking connection to database ok
        Checking if database exists     ok
        Loading Common Schema Files     ok
        Loading schema: Tigase Message Archiving Component, version: 2.1.0-b270/b48b562a (database version: none)       ok
        Loading schema: Tigase XMPP Server (Core), version: 8.1.1-b5650/86bb4486 (database version: none)       warning
                Message: Schema files to load: {7.1.0=mysql-server-7.1.0.sql, 8.0.0=mysql-server-8.0.0.sql, 8.1.0=mysql-server-8.1.0.sql, 8.1.1=mysql-server-8.1.1.sql}
                         Loading schema from file(s): mysql-server-7.1.0.sql, URI: jdbc:mysql://dbhostname:3306/tigasedb?user=dbUser&password=dbPassword&useSSL=false&useLegacyDatetimeCode=false&allowPublicKeyRetrieval=true&characterEncoding=UTF-8&allowPublicKeyRetrieval=true
                          completed OK
                         Loading schema from file(s): mysql-server-8.0.0.sql, URI: jdbc:mysql://dbhostname:3306/tigasedb?user=dbUser&password=dbPassword&useSSL=false&useLegacyDatetimeCode=false&allowPublicKeyRetrieval=true&characterEncoding=UTF-8&allowPublicKeyRetrieval=true
                          completed OK
                         Loading schema from file(s): mysql-server-8.1.0.sql, URI: jdbc:mysql://dbhostname:3306/tigasedb?user=dbUser&password=dbPassword&useSSL=false&useLegacyDatetimeCode=false&allowPublicKeyRetrieval=true&characterEncoding=UTF-8&allowPublicKeyRetrieval=true
                          completed OK
                         Loading schema from file(s): mysql-server-8.1.1.sql, URI: jdbc:mysql://dbhostname:3306/tigasedb?user=dbUser&password=dbPassword&useSSL=false&useLegacyDatetimeCode=false&allowPublicKeyRetrieval=true&characterEncoding=UTF-8&allowPublicKeyRetrieval=true
                          completed OK
                         Setting version of the component: server to: 8.1.1-b5650 for connection: jdbc:mysql://dbhostname:3306/tigasedb?user=dbUser&password=dbPassword&useSSL=false&useLegacyDatetimeCode=false&allowPublicKeyRetrieval=true&characterEncoding=UTF-8&allowPublicKeyRetrieval=true
                         You have 'password-encoding' property set to MD5-USERNAME-PASSWORD.
                         This setting will no longer work out of the box with this version of Tigase XMPP Server.
                         Please check Tigase XMPP Server Administration Guide, section "Changes to Schema in v8.0.0" at http://docs.tigase.org/ for more details.
        Loading schema: Tigase MUC Component, version: 3.1.2-b649/5e056afa (database version: none)     ok
        Adding XMPP admin accounts      warning
                Message: Error: No admin users entered
        Post installation action        ok
  =============================================================================

but still are remainig this tables (now empty)

broadcast_msgs_recipients
cluster_nodes
short_news
xmpp_stanza

but the migration seems good, regading:

WARNING: There was a problem adding user licence-library to the repository! Please make sure your database is configured correctly
Oct 06, 2023 7:19:17 AM tigase.licence.InstallationIdRetriever c
WARNING: There was a problem accessing repository to retrieve ID
Oct 06, 2023 7:19:17 AM tigase.licence.InstallationIdRetriever c
WARNING: There was a problem accessing repository to retrieve ID
java.lang.NullPointerException
        at tigase.server.XMPPServer.getComponent(XMPPServer.java:90)
        at tigase.licence.InstallationIdRetriever.c(SourceFile:203)
        at tigase.licence.InstallationIdRetriever.b(SourceFile:116)
        at tigase.licence.LicenceCheckDailyTask$1.run(SourceFile:105)
        at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:515)
        at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264)
        at java.base/java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:304)
        at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
        at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
        at java.base/java.lang.Thread.run(Thread.java:829)

this should not happen with production env because there is valid licence there, for this copy I have deleted the licence before running the upgrade. Do you think anyway that can cause problem?

davidemarrone commented 1 year ago

actually there is a big problem, the migration seems that worked but there is a "bug" somewhere. I cannot authenticate to the new instance and I see that in tig_user_credentials all the rows has the same value for"

username: default
username_sha1: 7505d64a54e061b7acd54ccd58b49dc43500b635
mechanism: MD5-USERNAME-PASSWORD

I will try to upgrade first to 8.0.0 and then to 8.2.2

hantu85 commented 1 year ago

As for the authentication issue, it is explained in the logs from the upgrade stage. Inside them there are following lines:

[2023-10-06 08:40:55:764] [WARNING ] [                  main ] DBSchemaLoader.lambda$loadSchema$16(): You have 'password-encoding' property set to MD5-USERNAME-PASSWORD.
This setting will no longer work out of the box with this version of Tigase XMPP Server.
Please check Tigase XMPP Server Administration Guide, section "Changes to Schema in v8.0.0" at http://docs.tigase.org/ for more details.

According to the documentation for version 8.0.0, you need to change your configuration, see https://docs.tigase.net/en/latest/Tigase_Administration/Database_Management/Management.html#schema-updates (especially Usage of MD5 decoder and Usage of MD5 encoder).

As for duplicated values in tig_user_credentials, mentioned fields are expected to have the same values.

davidemarrone commented 1 year ago

ok thanks, I saw the same values and I (wrongly) assumed that there was the bug there.

I have added in the config

authRepository {
    default () {
      'user-login-query' = '{ call TigUserLoginPlainPw(?, ?) }'
      credentialEncoders () {
        'MD5-USERNAME-PASSWORD' () {}
      }
      credentialDecoders () {
        'MD5-USERNAME-PASSWORD' () {}
      }

    }
}

I will make another test with 8.0.0 if something is different

davidemarrone commented 1 year ago

Ok, it works also the migration directly to 8.1.2. Thank you, the only strange thing noted on the startup is:

[2023-10-06 14:40:42:381] [WARNING ] [                  main ] AbstractBeanConfigurator.registerBeans(): unknown class null for bean credentialEncoders, skipping registration of a bean

but anyway the login is working correctly

davidemarrone commented 1 year ago

You would have to create your own decoder based on MD5UsernamePasswordCredentialsEntry.java and modify verifyPlainPassword() method by converting result of getUsername(user) to lowercase. Afterwards you can disable 'MD5-USERNAME-PASSWORD' () {} and use your implementation.

We have created our code:

package tigase.auth.credentials.entries;

import tigase.auth.CredentialsDecoderBean;
import tigase.auth.CredentialsEncoderBean;
import tigase.auth.credentials.Credentials;
import tigase.kernel.beans.Bean;
import tigase.util.Algorithms;
import tigase.xmpp.jid.BareJID;

import java.security.MessageDigest;
import java.util.logging.Level;
import java.util.logging.Logger;

// https://github.com/tigase/tigase-server/blob/1e1a35073ae9315e680f5a305d24ffedee2df010/src/main/java/tigase/auth/credentials/entries/MD5UsernamePasswordCredentialsEntry.java
public class MD5UsernamePasswordCredentialsEntry implements Credentials.Entry {

    private static final Logger log = Logger.getLogger(MD5UsernamePasswordCredentialsEntry.class.getCanonicalName());
    private final String passwordHash;
    private final BareJID user;

    private static final String CUSTOM_AUTH_NAME = "CUSTOM_AUTH";

    protected static String getUsername(BareJID user) {
        if (user.getLocalpart() == null) {
            return user.getDomain();
        } else {
            return user.getLocalpart();
        }
    }

    public MD5UsernamePasswordCredentialsEntry(BareJID user, String passwordHash) {
        this.user = user;
        this.passwordHash = passwordHash;
    }

    @Override
    public String getMechanism() {
        return CUSTOM_AUTH_NAME;
    }

    @Override
    public boolean verifyPlainPassword(String plain) {
        try {
            byte[] hash = MessageDigest.getInstance("MD5").digest((getUsername(user).toLowerCase() + plain).getBytes("UTF-8"));
            return passwordHash.equalsIgnoreCase(Algorithms.bytesToHex(hash));
        } catch (Exception ex) {
            log.log(Level.WARNING, "failed to verify password digest", ex);
        }
        return false;
    }

    @Bean(name = CUSTOM_AUTH_NAME, parent = CredentialsDecoderBean.class, active = false)
    public static class Decoder implements Credentials.Decoder {

        @Override
        public String getName() {
            return CUSTOM_AUTH_NAME;
        }

        @Override
        public Credentials.Entry decode(BareJID user, String value) {
            return new MD5UsernamePasswordCredentialsEntry(user, value);
        }
    }

    @Bean(name = CUSTOM_AUTH_NAME, parent = CredentialsEncoderBean.class, active = false)
    public static class Encoder implements Credentials.Encoder {

        @Override
        public String getName() {
            return CUSTOM_AUTH_NAME;
        }

        @Override
        public String encode(BareJID user, String password) {
            try {
                byte[] hash = MessageDigest.getInstance("MD5").digest((getUsername(user).toLowerCase() + password).getBytes("UTF-8"));
                return Algorithms.bytesToHex(hash);
            } catch (Exception ex) {
                throw new RuntimeException("failed to generate password hash", ex);
            }
        }
    }
}

what should be the correct configuration only:

authRepository {
    default () {
      credentialEncoders () {
        'CUSTOM_AUTH' () {}
      }
      credentialDecoders () {
        'CUSTOM_AUTH' () {}
      }
    }
}

or we have to use?

authRepository {
    default () {
      credentialEncoders () {
        'MD5-USERNAME-PASSWORD' () {}
      }
      credentialDecoders () {
        'MD5-USERNAME-PASSWORD' () {}
      }
    }
}

Considering that all the entries in the db of tig_user_credentials contains MD5-USERNAME-PASSWORD as mechanism?

woj-tek commented 1 year ago

Considering that you relayed on lowercase matching and also that existing MD* endoders/decoders are deprecated and will be removed in the future, I would recommend using your custom decoder: CUSTOM_AUTH

davidemarrone commented 1 year ago

ok, we will use our AUTH in the future making more test, at the moment I have to migrate from 7.1 to 8.1.2 that is compatible for the DB migration.

I have put in the config

authRepository {
    default () {
      'user-login-query' = '{ call TigUserLoginPlainPw(?, ?) }'
      credentialDecoders () {
        'MD5-USERNAME-PASSWORD' () {}
      }
      credentialEncoders () {
        'MD5-USERNAME-PASSWORD' () {}
      }
    }
}

ad described in the doc and I can successfully login old user created with 7.1

But if I create a new user or if I modify the password of an existing user I cannot login anymore with that user, in table the DB tig_user_credentials after the creation of modification I only found for that user 3 rows with SCRAM-SHA-1 SCRAM-SHA-256 SCRAM-SHA-512

and there is no row with MD5-USERNAME-PASSWORD. What I'm doing wrong? Is the row with MD5-USERNAME-PASSWORD needed since I'm using 'user-login-query' ?

davidemarrone commented 1 year ago

I have noticed in the logs:

[2023-10-09 11:33:58:124] [WARNING ] [                  main ] AbstractBeanConfigurator.registerBeans(): unknown class null for bean credentialEncoders, skipping registration of a bean

isn't the configuration correct?

davidemarrone commented 1 year ago

https://github.com/tigase/tigase-server/blob/tigase-server-8.1.2/src/main/java/tigase/auth/CredentialsEncoderBean.java

in the 8.1.2 it was with ths S! credentialsEncoders instead of credentialEncoders as described in the doc

davidemarrone commented 1 year ago

If I understood correctly, since I'm using user-login-query I MUST enable the credentialEncoders for MD5-USERNAME-PASSWORD I have changed the config like this:

authRepository {
    default () {
      'user-login-query' = '{ call TigUserLoginPlainPw(?, ?) }'
      credentialDecoders () {
        'SCRAM-SHA-1' (active: false) {}
        'SCRAM-SHA-256' (active: false) {}
        'SCRAM-SHA-512' (active: false) {}
        'MD5-USERNAME-PASSWORD' () {}
      }
      credentialsEncoders () {
        'SCRAM-SHA-1' (active: false) {}
        'SCRAM-SHA-256' (active: false) {}
        'SCRAM-SHA-512' (active: false) {}
        'MD5-USERNAME-PASSWORD' () {}
      }
    }
}

and finally it worked ! I will see in the future how to handle the migration to scram but at least I will be already on the 8.1.2 with all the password only in the form of MD5-USERNAME-PASSWORD

davidemarrone commented 1 year ago

Hmm... it's because we support upgrading only from 3 "big" versions back, thus you could upgrade to 8.2.* from 7.2.x (7.2 -> 8.0 -> 8.1). If you want to upgrade from 7.1.x you have to upgrade to 8.1.x first and then you can upgrade to either 8.2.x or even 8.3.x.

this should be documentated, it would be nice that command ./scripts/tigase.sh upgrade-schema etc/tigase.conf checks if the schema is too old and in that case stop the process

woj-tek commented 1 year ago

this should be documentated, it would be nice that command ./scripts/tigase.sh upgrade-schema etc/tigase.conf checks if the schema is too old and in that case stop the process

The check is already there. The problem is that in versions older than 8 we handled upgrade differently, and we didn't store current schema version in the database thus it wasn't possible to reliably detect if you upgrade from 7.1 or from 4.0.

davidemarrone commented 3 months ago

it seems that finally we made it