problem upgrading mysql database from 8.1.0-b10857 to 8.1.1-b10861 (#1412)

Activities

Unknown opened 4 years ago

Describe the bug I'm trying to upgrade tigase from 8.1.0-b10857 to 8.1.1-b10861, if I start the new version I got the usual upgrade error that doesn't start tigase:

  =============================================================================
  ERROR! Component muc (JDBCMucDAO) schema version is not loaded in the database or it is old!
  Version in database: 3.1.0-b639. Required version: 3.1.1-b643
  Please upgrade the installation by running:
  	$ ./scripts/tigase.sh upgrade-schema etc/tigase.conf
  =============================================================================

I run the command but the command fails, it's not my first upgrade from one version to another, in the past I have used that command without problems at least 10 times

To Reproduce Install 8.1.0-b10857 with a plain mysql DB, upgrade to 8.1.1-b10861

Impact Can't upgrade tigase version

Expected behavior Upgrade script working correctly

Details (please complete the following information): Here the output from $ ./scripts/tigase.sh upgrade-schema etc/tigase.conf

./scripts/tigase.sh upgrade-schema etc/tigase.conf
Sep 24, 2020 7:33:34 AM tigase.db.util.SchemaManager loadSchemas
INFO: beginning loading schema files...
Sep 24, 2020 7:33:34 AM tigase.util.dns.DNSResolverDefault <init>
WARNING: Resolving default host name: XXXXXXXXX took: 8

Database root account username used to create tigase user and database at 172.31.24.160:3306 : root
Database root account password used to create tigase user and database at 172.31.24.160:3306 : 
[2020-09-24 07:34:20:835] [CONFIG  ] [                  main ] DBSchemaLoader.init()            : Parameters: [adminPassword: null, admins: null, dbHostname: 172.31.24.160:3306, dbName: tigasedb, dbPass: XXXXXXXXXX, dbRootPass: XXXXXXXXXX, dbRootUser: root, dbType: mysql, dbUser: tigaseUser, file: null, ingoreMissingFiles: false, logLevel: CONFIG, query: null, serverTimezone: null, useLegacyDatetimeCode: true, useSSL: false, forceReloadSchema: false, schemaDirectory: database/]
[2020-09-24 07:34:20:836] [INFO    ] [                  main ] DBSchemaLoader.validateDBConnection(): Validating DBConnection, URI: jdbc:mysql://172.31.24.160:3306/?user=root&password=XXXXXXXXXX&useSSL=false&allowPublicKeyRetrieval=true
[2020-09-24 07:34:21:173] [INFO    ] [                  main ] DBSchemaLoader.validateDBConnection(): Connection OK
[2020-09-24 07:34:21:174] [INFO    ] [                  main ] DBSchemaLoader.validateDBExists(): Validating whether DB Exists, URI: jdbc:mysql://172.31.24.160:3306/tigasedb?user=tigaseUser&password=XXXXXXXXXX&useSSL=false&allowPublicKeyRetrieval=true
[2020-09-24 07:34:21:179] [INFO    ] [                  main ] DBSchemaLoader.lambda$validateDBExists$9(): Exists OK
[2020-09-24 07:34:21:280] [CONFIG  ] [                  main ] DBSchemaLoader.init()            : Parameters: [adminPassword: null, admins: null, dbHostname: 172.31.24.160:3306, dbName: tigasedb, dbPass: XXXXXXXXXX, dbRootPass: XXXXXXXXXX, dbRootUser: root, dbType: mysql, dbUser: tigaseUser, file: null, ingoreMissingFiles: false, logLevel: CONFIG, query: null, serverTimezone: null, useLegacyDatetimeCode: true, useSSL: false, forceReloadSchema: false, schemaDirectory: database/]
[2020-09-24 07:34:21:280] [INFO    ] [                  main ] DBSchemaLoader.validateDBConnection(): Validating DBConnection, URI: jdbc:mysql://172.31.24.160:3306/?user=root&password=XXXXXXXXXX&useSSL=false&allowPublicKeyRetrieval=true
[2020-09-24 07:34:21:284] [INFO    ] [                  main ] DBSchemaLoader.validateDBConnection(): Connection OK
[2020-09-24 07:34:21:284] [INFO    ] [                  main ] DBSchemaLoader.validateDBExists(): Validating whether DB Exists, URI: jdbc:mysql://172.31.24.160:3306/tigasedb?user=tigaseUser&password=XXXXXXXXXX&useSSL=false&allowPublicKeyRetrieval=true
[2020-09-24 07:34:21:289] [INFO    ] [                  main ] DBSchemaLoader.lambda$validateDBExists$9(): Exists OK
[2020-09-24 07:34:21:289] [CONFIG  ] [                  main ] DBSchemaLoader.loadSchema()      : SchemaInfo:: id: common, repositories: 0; version: 0.0.3
[2020-09-24 07:34:21:296] [CONFIG  ] [                  main ] DBSchemaLoader.loadSchemaFromSQLFiles(): Loading schema common, version: 0.0.3 from files, current: 0.0.3
[2020-09-24 07:34:21:304] [INFO    ] [                  main ] DBSchemaLoader.loadSchema()      : Required schema is already loaded in correct version
Sep 24, 2020 7:34:21 AM tigase.db.util.SchemaManager lambda$loadSchemas$33
INFO: Required schema is already loaded in correct version
Sep 24, 2020 7:34:21 AM tigase.db.util.SchemaManager lambda$loadSchemas$33
INFO: Required schema is already loaded in correct version
[2020-09-24 07:34:21:328] [CONFIG  ] [                  main ] DBSchemaLoader.loadSchema()      : SchemaInfo:: id: muc, repositories: 2; version: 3.1.1-b643/96806982
[2020-09-24 07:34:21:333] [CONFIG  ] [                  main ] DBSchemaLoader.loadSchemaFromSQLFiles(): Loading schema muc, version: 3.1.1-b643/96806982 from files, current: 3.1.0-b639
[2020-09-24 07:34:21:337] [INFO    ] [                  main ] DBSchemaLoader.loadSchema()      : Required schema is already loaded in correct version
Sep 24, 2020 7:34:21 AM tigase.db.util.SchemaManager lambda$loadSchemas$33
INFO: Required schema is already loaded in correct version
[2020-09-24 07:34:21:345] [WARNING ] [                  main ] DBSchemaLoader.addXmppAdminAccount(): Database schema is invalid
[2020-09-24 07:34:21:345] [WARNING ] [                  main ] DBSchemaLoader.postInstallation(): Database schema is invalid
Sep 24, 2020 7:34:21 AM tigase.db.util.SchemaManager loadSchemas
INFO: schema loading finished!




  =============================================================================
  	Schema upgrade finished
  
  Data source: default with uri jdbc:mysql://172.31.24.160:3306/tigasedb?user=tigaseUser&password=XXXXXXXXXX&useUnicode=true&characterEncoding=UTF-8&autoCreateUser=true&useSSL=false
  	Checking connection to database	ok
  	Checking if database exists	ok
  	Loading Common Schema Files	skipped
  		Message: Required schema is already loaded in correct version
  	Skipping schema: Tigase Message Archiving Component, version: 2.1.0-b269/1a9d06e9 (database version: 2.1.0-b269)	skipped
  	Skipping schema: Tigase XMPP Server (Core), version: 8.1.0-b5641/99fb6fd4 (database version: 8.1.0-b5641)	skipped
  	Loading schema: Tigase MUC Component, version: 3.1.1-b643/96806982 (database version: 3.1.0-b639)	skipped
  		Message: Required schema is already loaded in correct version
  	Skipping schema: Tigase PubSub Component, version: 4.1.0-b715/821100fb (database version: 4.1.0-b715)	skipped
  	Adding XMPP admin accounts	error
  		Message: Database schema is invalid
  	Post installation action	error
  		Message: Database schema is invalid
  =============================================================================

Here the current versions in DB SELECT * FROM tig_schema_versions;

+-------------------+-------------+---------------------+
| component         | version     | last_update         |
+-------------------+-------------+---------------------+
| common            | 0.0.3       | 2020-07-30 16:19:07 |
| message-archiving | 2.1.0-b269  | 2020-07-30 16:19:07 |
| muc               | 3.1.0-b639  | 2020-07-30 16:19:08 |
| pubsub            | 4.1.0-b715  | 2020-07-30 16:19:08 |
| server            | 8.1.0-b5641 | 2020-07-30 16:19:08 |
+-------------------+-------------+---------------------+

Unknown commented 4 years ago

Thank you for reporting it. Release page has been updated with relevant information. The suggested workaround involves:

creating empty sql file (that's missing from the release):

$ touch database/mysql-muc-3.1.1.sql
$ touch database/derby-muc-3.1.1.sql
$ touch database/postgresql-muc-3.1.1.sql
$ touch database/sqlserver-muc-3.1.1.sql

re-running upgrade-schema

Type

Bug

Issue Votes (0)

Watchers (0)

Reference

tigase/_server/server-core#1412