Unknown opened 2 years 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_schemadatabase 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) ); -
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_resis present both inmysql-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;and in
mysql-unified-archive-3.0.0.sqlalter 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
credentialDecodersotherwise old user cannot connect, it is not really clear if I need to use alsocredentialEncodersor 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
TigUserLoginPlainPwwas removed here so I guess that after upgrading to 8.2.2 I have to re-create it manually, correct? -
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 adduser-login-querythough (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.
-
You would have to create your own decoder based on
MD5UsernamePasswordCredentialsEntry.javaand modifyverifyPlainPassword()method by converting result ofgetUsername(user)to lowercase. Afterwards you can disable'MD5-USERNAME-PASSWORD' () {}and use your implementation. -
ok, so we need to re-build the code?
Not exactly. You can create a dedicated maven project, add
tigase-serveras dependency, create the class with the implementation suggested as above, build the project and include the resultingjarfile in thetigase-server/jars/directory.If you want to use your solution (
user-login-querywith 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. -
If you want to use your solution (
user-login-querywith 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-serveras dependency, create the class with the implementation suggested as above, build the project and include the resultingjarfile in thetigase-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?
-
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 inconfig.tdslfile 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). -
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
-
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.
-
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_stanzais it correct that they are not deleted during the migration? Can I delete them?
I have also noticed that the table
tig_offline_messagesis empty also if I have a lot of messages inmsg_historythere is no migration for that messages? -
Could you try running upgrade-schema with
-L ALLparameter?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.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
-
@davidemarrone yes, my bad. Please see my previous commend regarding version upgrade compatibility.
-
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_stanzabut 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?
-
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-PASSWORDI will try to upgrade first to 8.0.0 and then to 8.2.2
-
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 decoderandUsage of MD5 encoder).As for duplicated values in
tig_user_credentials, mentioned fields are expected to have the same values. -
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
-
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 beanbut anyway the login is working correctly
-
You would have to create your own decoder based on
MD5UsernamePasswordCredentialsEntry.javaand modifyverifyPlainPassword()method by converting result ofgetUsername(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_credentialscontainsMD5-USERNAME-PASSWORDasmechanism? -
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' ?
-
in the 8.1.2 it was with ths S!
credentialsEncodersinstead ofcredentialEncodersas described in the doc -
If I understood correctly, since I'm using
user-login-queryI MUST enable thecredentialEncodersforMD5-USERNAME-PASSWORDI 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 -
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.confchecks if the schema is too old and in that case stop the process -
this should be documentated, it would be nice that command
./scripts/tigase.sh upgrade-schema etc/tigase.confchecks if the schema is too old and in that case stop the processThe 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.
Hi, I'm trying to upgrade tigase from
7.1.5-b4512to8.2.2-b11697this is the output of
./scripts/tigase.sh upgrade-schema etc/tigase.confhow can I fix it? regards