Loading schema for DerbyDB fails (#924)

Andrzej Wójcik (Tigase) opened 7 years ago

Currently initialization of schema for DerbyDB fails with following error:

  =============================================================================
  	Schema upgrade finished
  
  Data source: default with uri jdbc:derby:tigase_iot;create=true
  	Checking connection to database	ok
  	Checking if database exists	ok
  	Loading Common Schema Files	ok
  	Loading schema: Tigase XMPP Server (Core), version: 8.0.0-SNAPSHOT-b5134/819ce08d (database version: none)	error
  		Message: Schema files to load: [derby-issue #7.0.0.sql, derby-issue #7.1.0.sql, derby-issue #8.0.0.sql]
  		         Loading schema from file(s): derby-issue #7.0.0.sql, URI: jdbc:derby:tigase_iot;create=true
  		          completed OK
  		         Loading schema from file(s): derby-issue #7.1.0.sql, URI: jdbc:derby:tigase_iot;create=true
  		         Failed to execute query: call TigAddUserPlainPw('db-properties', NULL)
  		         
  		         
  		         
  		         =====
  		         Failure: The exception 'org.apache.derby.shared.common.error.DerbySQLIntegrityConstraintViolationException: The statement was aborted because it would have caused a duplicate key value in a unique or primary key constraint or unique index identified by 'USER_ID' defined on 'TIG_USERS'.' was thrown while evaluating an expression.
  		         =====
  		         
  		         
  		         Loading schema from file(s): derby-issue #8.0.0.sql, URI: jdbc:derby:tigase_iot;create=true
  		         Failed to execute query: call TigAddUserPlainPw('db-properties', NULL)
  		         
  		         
  		         
  		         =====
  		         Failure: The exception 'org.apache.derby.shared.common.error.DerbySQLIntegrityConstraintViolationException: The statement was aborted because it would have caused a duplicate key value in a unique or primary key constraint or unique index identified by 'USER_ID' defined on 'TIG_USERS'.' was thrown while evaluating an expression.
  		         =====
  	Loading schema: Tigase PubSub Component, version: 4.0.0-SNAPSHOT-b640/9dc9e0a4 (database version: none)	ok
  	Adding XMPP admin accounts	warning
  		Message: Error: No admin users entered
  	Post installation action	ok
  =============================================================================

Andrzej Wójcik (Tigase) commented 7 years ago

As discussed with %wojtek this issue is more complex than it looks like. In this task we need to:

make sure that released versions will make sure that they are using schema version which is also released (schema version in tig_schema_version cannot be snapshot)
update all existing entries initially to mark them as SNAPSHOT (right now this table should not contain entries for stable version)
modify TigPutDBProperty to make sure that user db-properties exists
remove calls to TigAddUser to add db-properties from all schema files
ensure that new version of schema is loaded
if version in database is smaller (older)
if version in database is equal but it is marked as SNAPSHOT

Note: If users will not use upgrade-schema and will want to manually load schema, then they will be resposible for that all the time. To use upgrade-schema again it will need to be executed with parameter forcing it to once again load schemas.

Andrzej Wójcik (Tigase) commented 7 years ago

I've modified schema loader as we discussed and verified that changes work fine. Please check if I've not skipped anything which we discussed and review changes to SchemaLoader and DBSchemaLoader - as you supposed there were minor changes.

Andrzej Wójcik (Tigase) commented 7 years ago

%Daniel Issue with the installation of Tigase IoT Hub should be solved. Next snapshot build should be fine.

Daniel Wisnewski commented 7 years ago

Current version displays following message:

2018-03-01 17:03:09.488 [pool-1-thread-8]  SchemaVersionCheckerLogger.printErrorMessage()  WARNING: 

	It's possible that following data sources are out of date:
		* pubsub (PubSubDAOJDBC) ~ version in database: 4.0.0-SNAPSHOT-b640, required version: 4.0.0-SNAPSHOT-b640)
		* server (JDBCRepository) ~ version in database: 8.0.0-SNAPSHOT-b5135, required version: 8.0.0-SNAPSHOT-b5135)
	Please upgrade the installation by running:
		$ ./scripts/tigase.sh upgrade-schema etc/tigase.conf

	(this warning is printed each time SNAPSHOT version is started, you can ignore thismessage if you've just run above command)

hub functions OK.

wojciech.kapcia@tigase.net commented 7 years ago

Andrzej Wójcik wrote:

I've modified schema loader as we discussed and verified that changes work fine. Please check if I've not skipped anything which we discussed and review changes to SchemaLoader and DBSchemaLoader - as you supposed there were minor changes.

I reviewed the changes and everything looks OK. Thank you.

	Andrzej Wójcik (Tigase) opened 7 years ago Currently initialization of schema for DerbyDB fails with following error: ============================================================================= Schema upgrade finished Data source: default with uri jdbc:derby:tigase_iot;create=true Checking connection to database ok Checking if database exists ok Loading Common Schema Files ok Loading schema: Tigase XMPP Server (Core), version: 8.0.0-SNAPSHOT-b5134/819ce08d (database version: none) error Message: Schema files to load: [derby-issue #7.0.0.sql, derby-issue #7.1.0.sql, derby-issue #8.0.0.sql] Loading schema from file(s): derby-issue #7.0.0.sql, URI: jdbc:derby:tigase_iot;create=true completed OK Loading schema from file(s): derby-issue #7.1.0.sql, URI: jdbc:derby:tigase_iot;create=true Failed to execute query: call TigAddUserPlainPw('db-properties', NULL) ===== Failure: The exception 'org.apache.derby.shared.common.error.DerbySQLIntegrityConstraintViolationException: The statement was aborted because it would have caused a duplicate key value in a unique or primary key constraint or unique index identified by 'USER_ID' defined on 'TIG_USERS'.' was thrown while evaluating an expression. ===== Loading schema from file(s): derby-issue #8.0.0.sql, URI: jdbc:derby:tigase_iot;create=true Failed to execute query: call TigAddUserPlainPw('db-properties', NULL) ===== Failure: The exception 'org.apache.derby.shared.common.error.DerbySQLIntegrityConstraintViolationException: The statement was aborted because it would have caused a duplicate key value in a unique or primary key constraint or unique index identified by 'USER_ID' defined on 'TIG_USERS'.' was thrown while evaluating an expression. ===== Loading schema: Tigase PubSub Component, version: 4.0.0-SNAPSHOT-b640/9dc9e0a4 (database version: none) ok Adding XMPP admin accounts warning Message: Error: No admin users entered Post installation action ok =============================================================================
	Andrzej Wójcik (Tigase) commented 7 years ago As discussed with %wojtek this issue is more complex than it looks like. In this task we need to: make sure that released versions will make sure that they are using schema version which is also released (schema version in `tig_schema_version` cannot be snapshot) update all existing entries initially to mark them as `SNAPSHOT` (right now this table should not contain entries for stable version) modify `TigPutDBProperty` to make sure that user `db-properties` exists remove calls to `TigAddUser` to add `db-properties` from all schema files ensure that new version of schema is loaded if version in database is smaller (older) if version in database is equal but it is marked as `SNAPSHOT` Note: If users will not use `upgrade-schema` and will want to manually load schema, then they will be resposible for that all the time. To use `upgrade-schema` again it will need to be executed with parameter forcing it to once again load schemas.
	Andrzej Wójcik (Tigase) commented 7 years ago I've modified schema loader as we discussed and verified that changes work fine. Please check if I've not skipped anything which we discussed and review changes to `SchemaLoader` and `DBSchemaLoader` - as you supposed there were minor changes.
	Andrzej Wójcik (Tigase) commented 7 years ago %Daniel Issue with the installation of Tigase IoT Hub should be solved. Next snapshot build should be fine.
	Daniel Wisnewski commented 7 years ago Current version displays following message: 2018-03-01 17:03:09.488 [pool-1-thread-8] SchemaVersionCheckerLogger.printErrorMessage() WARNING: It's possible that following data sources are out of date: * pubsub (PubSubDAOJDBC) ~ version in database: 4.0.0-SNAPSHOT-b640, required version: 4.0.0-SNAPSHOT-b640) * server (JDBCRepository) ~ version in database: 8.0.0-SNAPSHOT-b5135, required version: 8.0.0-SNAPSHOT-b5135) Please upgrade the installation by running: $ ./scripts/tigase.sh upgrade-schema etc/tigase.conf (this warning is printed each time SNAPSHOT version is started, you can ignore thismessage if you've just run above command) hub functions OK.
	wojciech.kapcia@tigase.net commented 7 years ago Andrzej Wójcik wrote: I've modified schema loader as we discussed and verified that changes work fine. Please check if I've not skipped anything which we discussed and review changes to `SchemaLoader` and `DBSchemaLoader` - as you supposed there were minor changes. I reviewed the changes and everything looks OK. Thank you.

Type	Bug
Priority	Normal
Assignee	wojciech.kapcia@tigase.net
RedmineID	6749
Version	tigase-server-8.0.0
Spent time	45h