Adjust scripting/installers for pubsub schemat change (#577)

Closed

Activities

wojciech.kapcia@tigase.net opened 9 years ago

Due Date
2015-12-30

Recently PubSub schema was changed which yields the need to user newer schema file while preparing database, adjust:

database schema creation scripts
izpack installer
web installer

Screenshot from 2015-10-14 09_19_10.png Derby error.png

wojciech.kapcia@tigase.net commented 9 years ago

I've changed how the schema files are loaded (added possibility to reference other schema files with -- LOAD FILE: <path> even if the database engine doesn't support it), adjusted all schema files to use it and bumped version of pubsub component to 3.1.0, I've modified IzPack to load queries from both *-pubsub-schema-3.0.0.sql and *-pubsub-schema-3.1.0.sql (it uses old mechanism and given that we are phasing it out I didn't want to tinker with it to much), I've also updated pubsub version in web-installer (which uses @tigase.util.DBSchemaLoader@) as well and TTS tests to use new schema version for the tests.

%kobit I would be in favour of either phasing out shellscripts or replacing logic in them with tigase.util.DBSchemaLoader which would allow better flexibility, independence on the operating system and ability to reference other files without being limited do only selected databases -- what do you think?

Daniel, could you test next builds, with the focus on schema loading correctness?

Daniel Wisnewski commented 9 years ago

Error using JAR installer in MySQL, I don't think this is working properly

See attached screenshot

Installing new version (b4055) on fresh mysql db

Daniel Wisnewski commented 9 years ago

derby shows same errors !Derby error.png!

Text shows as follows for errors
Checking connection to the database	Connection OK
Checking if the database exists	Exists OK
Checking the database schema	Can't load schema: 'ALTER TABLE' cannot be performed on 'TIG_PAIRS' because it does not exist.
Checking whether the database needs conversion	Something wrong, the schema still is not loaded...
Adding XMPP admin accounts	Database schema is invalid
Loading socks5 component schema	Loading socks5 schema... completed OK
Loading PubSub component schema	Can't load PubSub schema: Cannot find named Resource: '/res/derby-pubsub-schema-3.0.0' AND '/res/derby-pubsub-schema-3.0.0_eng'
Post installation actions	Database schema is invalid

Daniel Wisnewski commented 9 years ago

Web installer results for MySQL

Validating DBConnection, URI: jdbc:derby:tigasedbss;create=true
DriverManager (available drivers): [[org.apache.derby.jdbc.AutoloadedDriver@64412d34, jTDS 1.3.1, com.mysql.jdbc.Driver@72ee5d84, org.postgresql.Driver@134c370e]]
Connection OK
Validating whether DB Exists, URI: jdbc:derby:tigasedbss;create=true
Exists OK
Validating DBSchema, URI: jdbc:derby:tigasedbss;create=true
Exception, posibly schema hasn't been loaded yet.
DB schema doesn't exists, creating one..., URI: jdbc:derby:tigasedbss;create=true
New schema loaded OK
Adding XMPP Admin Account, URI: jdbc:derby:tigasedbss;create=true
RepositoryFactory.getAuthRepository(null, jdbc:derby:tigasedbss;create=true,[{data-repo-pool-size=1}])
All users added
Loading schema from file: database/derby-socks5-schema.sql, URI: jdbc:derby:tigasedbss;create=true
completed OK
Loading schema from file: database/derby-pubsub-schema-3.1.0.sql, URI: jdbc:derby:tigasedbss;create=true
Can't finalize: Syntax error: Encountered "run" at line 1, column 1.

Derby DB

Checking connection to database	OK
Checking if database exists	OK
Checking database schema	OK
Adding XMPP admin accounts	OK
Loading socks5 component schema	SKIPPED
Validating DBConnection, URI: jdbc:derby:tigasedb;create=true
DriverManager (available drivers): [[org.apache.derby.jdbc.AutoloadedDriver@64412d34, jTDS 1.3.1, com.mysql.jdbc.Driver@72ee5d84, org.postgresql.Driver@134c370e]]
Connection OK
Validating whether DB Exists, URI: jdbc:derby:tigasedb;create=true
Exists OK
Validating DBSchema, URI: jdbc:derby:tigasedb;create=true
Exception, posibly schema hasn't been loaded yet.
DB schema doesn't exists, creating one..., URI: jdbc:derby:tigasedb;create=true
New schema loaded OK
Adding XMPP Admin Account, URI: jdbc:derby:tigasedb;create=true
RepositoryFactory.getAuthRepository(null, jdbc:derby:tigasedb;create=true,[{data-repo-pool-size=1}])
All users added
Loading PubSub component schema	ERROR
Loading schema from file: database/derby-pubsub-schema-3.1.0.sql, URI: jdbc:derby:tigasedb;create=true
Can't finalize: Syntax error: Encountered "run" at line 1, column 1.
Post installation actions	OK

Daniel Wisnewski commented 9 years ago

postgreSQL DB setup yields this from installer

Checking connection to the database	Connection OK
Checking if the database exists	Doesn't exist, creating... OK
Checking the database schema	Can't load schema: ERROR: function tigadduserplainpw(unknown, unknown) does not exist
  Hint: No function matches the given name and argument types. You might need to add explicit type casts.
  Position: 8
Checking whether the database needs conversion	Something wrong, the schema still is not loaded...
Adding XMPP admin accounts	Database schema is invalid
Loading socks5 component schema	Loading socks5 schema... completed OK
Loading PubSub component schema	Loading PubSub schema... completed OK
Post installation actions	Database schema is invalid

Daniel Wisnewski commented 9 years ago

Any progress Wojciech? I have not seen any changes in the new builds.

wojciech.kapcia@tigase.net commented 9 years ago

Not yet

Daniel Wisnewski commented 9 years ago

Pubsub and socks schema files operate correctly, issues arise when basic schemas are loaded.

Error "'ALTER TABLE' cannot be performed on 'TIG_PAIRS' because it does not exist" indicates that while the schema 5.1 file is loaded, instructions to load previous schema files (v4) to be edited are not loading correctly or at all.

Web installer led to following errors:

tigase-console.log

tigase.util.DBSchemaLoader       <init>                  CONFIG     Properties:[{dbHostname=localhost, logLevel=CONFIG, adminJID=admin@lenovo-z585.home, dbType=derby, rootUser=root, dbPass=tigase12, adminJIDpass=tigase, schemaVersion=5-1,dbName=tigasedb, rootPass=root, dbUser=tigase}]
tigase.util.DBSchemaLoader       validateDBConnection    INFO       Validating DBConnection, URI: jdbc:derby:tigasedb;create=true
tigase.util.DBSchemaLoader       validateDBConnection    CONFIG     DriverManager (available drivers): [[org.apache.derby.jdbc.AutoloadedDriver@7fd69dd, jTDS 1.3.1, com.mysql.jdbc.Driver@455824ad, org.postgresql.Driver@6601cc93]]
tigase.util.DBSchemaLoader       validateDBConnection    INFO       Connection OK
tigase.util.DBSchemaLoader       validateDBExists        INFO       Validating whether DB Exists, URI: jdbc:derby:tigasedb;create=true
tigase.util.DBSchemaLoader       validateDBExists        INFO       Exists OK
tigase.util.DBSchemaLoader       validateDBSchema        INFO       Validating DBSchema, URI: jdbc:derby:tigasedb;create=true
tigase.util.DBSchemaLoader       validateDBSchema        INFO       Schema exists, users: 2
tigase.util.DBSchemaLoader       validateDBSchema        WARNING    Exception, posibly schema hasn't been loaded yet.
tigase.util.DBSchemaLoader       validateDBSchema        INFO       Old schema,accounts number: 2
tigase.util.DBSchemaLoader       addXmppAdminAccount     WARNING    Database schema is invalid
tigase.util.DBSchemaLoader       loadSchemaFile          INFO       Loading schema from file: database/derby-pubsub-schema 3.1.0.sql, URI: jdbc:derby:tigasedb;create=true
tigase.util.DBSchemaLoader       loadSchemaFile          WARNING    Can't finalize: Table/View 'TIG_PUBSUB_SERVICE_JIDS' already exists in Schema 'APP'.
tigase.util.DBSchemaLoader       postInstallation        WARNING    Database schema is invalid
tigase.util.DBSchemaLoader       shutdownDerby           INFO       Validating DBConnection, URI: jdbc:derby:tigasedb;create=true
tigase.util.DBSchemaLoader       shutdownDerby           WARNING    Database 'tigasedb' shutdown.

manually loading the schema files using the following command:

java -cp "jars/*" tigase.util.DBSchemaLoader -dbType derby -dbName tigasedb -dbUser tigase -dbPass tigase -dbHostname jdbc:derby:/tigase-derbydb/ -file database/derby-schema-5.1.sql

generates the following error:

tigase.util.DBSchemaLoader     	 validateDBConnection 	 CONFIG     DriverManager (available drivers):[[org.apache.derby.jdbc.AutoloadedDriver@10f87f48, org.postgresql.Driver@7cd84586, com.mysql.jdbc.Driver@6f539caf, jTDS 1.3.1]]
tigase.util.DBSchemaLoader     	 validateDBConnection 	 INFO       Connection OK
tigase.util.DBSchemaLoader     	 validateDBExists 	 INFO       Validating whether DB Exists, URI: jdbc:derby:tigasedb;create=true
tigase.util.DBSchemaLoader     	 validateDBExists 	 INFO       Exists OK
tigase.util.DBSchemaLoader     	 loadSchemaFile  	 INFO       Loading schema from file: database/derby-schema-5.1.sql, URI: jdbc:derby:tigasedb;create=true
tigase.util.DBSchemaLoader     	 getResource     	 SEVERE     null
tigase.util.DBSchemaLoader     	 loadSchemaFile  	 WARNING    Can't finalize: null
tigase.util.DBSchemaLoader     	 shutdownDerby   	 INFO       Validating DBConnection, URI: jdbc:derby:tigasedb;create=true
tigase.util.DBSchemaLoader     	 shutdownDerby   	 WARNING    Database 'tigasedb' shutdown.
tigase.util.DBSchemaLoader     	 printInfo       	 WARNING    Database schema is invalid

Daniel Wisnewski commented 9 years ago

Added new line between --LOAD FILE statements in -4 and -5 schema files, this change fixes the getResource null error encountered when using command line.

Fresh Derby database now initializes properly with command, output is as follows:

C:\Tigase>java -cp jars/* tigase.util.DBSchemaLoader -dbType derby -dbName tigasedb -dbHostname jdbc:derby:c:\Tigase\tigasedb -dbUser tigase -dbPass tigase12 -file database\derby-schema-5-1.sql
LogLevel: CONFIG
tigase.util.DBSchemaLoader       <init>                  CONFIG     Properties:[{dbHostname=jdbc:derby:c:\Tigase\tigasedb, logLevel=CONFIG, dbType=derby, file=database\derby-schema-5-1.sql, rootUser=root, dbPass=tigase12, dbName=tigasedb,schemaVersion=5-1, rootPass=root, dbUser=tigase}]
tigase.util.DBSchemaLoader       validateDBConnection    INFO       Validating DBConnection, URI: jdbc:derby:tigasedb;create=true
tigase.util.DBSchemaLoader       validateDBConnection    CONFIG     DriverManager (available drivers): [[org.apache.derby.jdbc.AutoloadedDriver@53d8d10a, jTDS 1.3.1, com.mysql.jdbc.Driver@7699a589, org.postgresql.Driver@7291c18f]]
tigase.util.DBSchemaLoader       validateDBConnection    INFO       Connection OK
tigase.util.DBSchemaLoader       validateDBExists        INFO       Validating whether DB Exists, URI: jdbc:derby:tigasedb;create=true
tigase.util.DBSchemaLoader       validateDBExists        INFO       Exists OK
tigase.util.DBSchemaLoader       loadSchemaFile          INFO       Loading schema from file: database\derby-schema-5-1.sql, URI: jdbc:derby:tigasedb;create=true
tigase.util.DBSchemaLoader       loadSchemaFile          INFO        completed OK
tigase.util.DBSchemaLoader       shutdownDerby           INFO       Validating DBConnection, URI: jdbc:derby:tigasedb;create=true
tigase.util.DBSchemaLoader       shutdownDerby           WARNING    Database 'tigasedb' shutdown.
tigase.util.DBSchemaLoader       printInfo               INFO

Database init.properties configuration:

--user-db=derby
--user-db-uri=jdbc:derby:tigasedb;create=true

Daniel Wisnewski commented 9 years ago

Issue sems to be confined to DBSchema Loader as external statements yield OK

DBSchemaLoader appears to terminate on any "table exists" errors

wojciech.kapcia@tigase.net commented 9 years ago

Daniel Wisnewski wrote:

Issue sems to be confined to DBSchema Loader as external statements yield OK

DBSchemaLoader appears to terminate on any "table exists" errors

%daniel

If memory serves me right if you are trying to simply load schema for particular version then failing is expected as there is no check for the current version of the schema and there is no appropriate update mechanism. In case of Installer there is additional logic before the execution of the schema loader and in case there is already schema loaded then only direct sql file is executed to perform the update.

wojciech.kapcia@tigase.net commented 9 years ago

I've corrected IzPack;
I wasn't able to replicate second issue but I've pushed changes with additional lines to repository;

I've also tested upgrade of the pubsub schema using SchemaLoader that you've mentioned in the e-mail and I wasn't able to replicate it.

Daniel Wisnewski commented 9 years ago

Getting unable to find schema error for MySQL, MSSQL, derby in both windows and linux environments.

Checking connection to the database Connection OK

Checking if the database exists Doesn't exist, creating... OK

Checking the database schema Can't load schema: could not find: database/mysql-schema-4-schema.sql

Checking whether the database needs conversion Something wrong, the schema still is not loaded...

Adding XMPP admin accounts Database schema is invalid

Loading socks5 component schema Loading socks5 schema... completed OK

Loading PubSub component schema Can't load PubSub schema: could not find: database/mysql-pubsub-schema-3.0.0.sql

Post installation actions Database schema is invalid

Checking connection to the database Connection OK

Checking if the database exists Doesn't exist, creating... OK

Checking the database schema Can't load schema: could not find: database/derby-schema-4-schema.sql

Checking whether the database needs conversion Something wrong, the schema still is not loaded...

Adding XMPP admin accounts Database schema is invalid

Loading socks5 component schema Loading socks5 schema... completed OK

Loading PubSub component schema Can't load PubSub schema: could not find: database/derby-pubsub-schema-3.0.0.sql

Post installation actions Database schema is invalid

Tried moving schema files to database/database but no change, all files are present from installer. I think it may a directory/location issue.

wojciech.kapcia@tigase.net commented 9 years ago

Daniel Wisnewski wrote:

Tried moving schema files to database/database but no change, all files are present from installer. I think it may a directory/location issue.

This won't work because IzPack uses internal resources to identify files. I've changed the logic and tested it again (in different path than tigase home directory - because in such paths existed) and it worked now.

Daniel Wisnewski commented 9 years ago

Scripts now work OK for Derby, MSSQL and MySQL. Closing issue.

Type	Task
Priority	Normal
Assignee	Daniel Wisnewski
RedmineID	3574
Version	tigase-server-7.1.0
Spent time	112h 30m

#581 Closed

b4068 Exceptions

Issue Votes (0)

Watchers (0)

Reference

tigase/_server/server-core#577