Migrator workflow

gistfile1.md

Start with version 1.0.3 and a clean postgres database.

If you get an annoying error about grunt then cd into admin-ui and run bower install.

NOTE
When deploying to EAP you must use the as7 server and not the wildfly ag-push.war. This is also means that you should not be executing the wildfly scripts in the databases directory to create the datasources.

Deploy UPS 1.0.3:

• Start wildfly using:

./standalone.sh -c standalone-full.xml -b 0.0.0.0

• Create the datasource by executing:

~/work/wildfly/bundles/wildfly-8.2.0.Final/bin/jboss-cli.sh --file=postgresql-database-config-wildfly.cli

• Create the database schema:

cd migrator/target
unzip -d migrator unifiedpush-migrator-1.0.3-dist.zip
cd migrator
cp liquibase-postgresql-example.properties liquibase.properties and update the configuration file
./bin/ups-migrator --logLevel=debug update

• Deploy the auth-server:

cp servers/auth-server/target/auth-server.war ~/work/wildfly/bundles/wildfly-8.2.0.Final/standalone/deployments/

• Deploy UPS:

cp servers/ups-wildfly/target/ag-push.war ~/work/wildfly/bundles/wildfly-8.2.0.Final/standalone/deployments/

• Register an application using the admin console

• After registering an application and client device plus sending a notification. Take a dump of the database in this state: Postgres:

pg_dump unifiedpush > ups_1.0.3

MySql:

mysqldump -u root unifiedpush > ups_1.0.3_mysql

• Restore the 1.0.3 database Postgres:

postgres=# drop database unifiedpush
postgres=# create database unifiedpush
$ psql unifiedpush < ~/Downloads/ups_1_0_3

Mysql:

drop database unifiedpush
create database unifiedpush default character set = "UTF8" default collate = "utf8_general_ci";
mysql -u root unifiedpush < ups_1_0_3_mysql

• Create the database schema:

cd migrator/target
unzip -d migrator unifiedpush-migrator-1.0.3-dist.zip; cd migrator
cp liquibase-mysql-example.properties liquibase.properties and update the configuration file
./bin/ups-migrator --logLevel=debug update

Starting EAP 6.4 with an empty database

Has Hibernate version 4.2.18.Final-redhat-2 compared to Wildfly 8.2 which has version hibernate-core-4.3.7.Final

Deployment warnings

Deploying auth-server.war produces the following warnings:

07:13:16,354 INFO  [org.jboss.as.server.deployment] (MSC service thread 1-10) JBAS015876: Starting deployment of "auth-server.war" (runtime-name: "auth-server.war")
07:13:17,307 WARN  [org.jboss.as.server.deployment] (MSC service thread 1-10) JBAS015960: Class Path entry lib/snakeyaml-1.13.jar in /content/auth-server.war/WEB-INF/lib/liquibase-core-3.3.5.jar  does not point to a valid jar for a Class-Path reference.
07:13:17,363 INFO  [org.jboss.as.jpa] (MSC service thread 1-2) JBAS011401: Read persistence.xml for keycloak-default
07:13:17,554 WARN  [org.jboss.as.dependency.private] (MSC service thread 1-10) JBAS015867: Deployment "deployment.auth-server.war" is using a private module ("org.codehaus.jackson.jackson-core-asl:main") which may be changed or removed in future versions without notice.
07:13:17,554 WARN  [org.jboss.as.dependency.private] (MSC service thread 1-10) JBAS015867: Deployment "deployment.auth-server.war" is using a private module ("org.codehaus.jackson.jackson-core-asl:main") which may be changed or removed in future versions without notice.
07:13:17,555 WARN  [org.jboss.as.dependency.private] (MSC service thread 1-10) JBAS015867: Deployment "deployment.auth-server.war" is using a private module ("org.codehaus.jackson.jackson-mapper-asl:main") which may be changed or removed in future versions without notice.
07:13:17,555 WARN  [org.jboss.as.dependency.private] (MSC service thread 1-10) JBAS015867: Deployment "deployment.auth-server.war" is using a private module ("org.codehaus.jackson.jackson-mapper-asl:main") which may be changed or removed in future versions without notice.
07:13:17,555 WARN  [org.jboss.as.dependency.unsupported] (MSC service thread 1-10) JBAS015868: Deployment "deployment.auth-server.war" is using an unsupported module ("org.infinispan:main") which may be changed or removed in future versions without notice.
07:13:17,555 WARN  [org.jboss.as.dependency.unsupported] (MSC service thread 1-10) JBAS015868: Deployment "deployment.auth-server.war" is using an unsupported module ("org.infinispan:main") which may be changed or removed in future versions without notice.

Final testing

EAP 6.4

• Migrate from UPS 1.0.3 Postgres database
• Migrate from UPS 1.0.3 Mysql database

EAP 6.4.2

• Migrate from UPS 1.0.3 Postgres database
• Migrate from UPS 1.0.3 Mysql database

Wildfly 8.2

• Migrate from UPS 1.0.3 Postgres database
• Migrate from UPS 1.0.3 Mysql database

qu-reported.md

Start with a clean database:

mysql> drop database unifiedpush;
mysql> create database unifiedpush default character set = "UTF8" default collate = "utf8_general_ci";

git co -b migrator-qe-issue bbe1c0e

mvn clean install -DskipTests=true

Now deploy ag-push.war so that the database tables are created.

Dump the database:

mysqldump -u root unifiedpush > ups_eq_issue_mysql

Checkout master and build again. The use the migrator to try to update the database.

DEBUG 8/13/15 9:14 AM: liquibase: liquibase/master.xml: liquibase/1.0.0.Final/initial-mysql.xml::025::generated: Executing EXECUTE database command: ALTER TABLE unifiedpush.VariantMetricInformation ADD CONSTRAINT FK_ci9sa3eevtel9l172w9b0musj FOREIGN KEY (variantInformations_id) REFERENCES unifiedpush.PushMessageInformation (id) ON UPDATE NO ACTION ON DELETE NO ACTION
SEVERE 8/13/15 9:14 AM: liquibase: liquibase/master.xml: liquibase/1.0.0.Final/initial-mysql.xml::025::generated: Change Set liquibase/1.0.0.Final/initial-mysql.xml::025::generated failed.  Error: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Key column 'variantInformations_id' doesn't exist in table
liquibase.exception.DatabaseException: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Key column 'variantInformations_id' doesn't exist in table
	at liquibase.executor.jvm.JdbcExecutor$ExecuteStatementCallback.doInStatement(JdbcExecutor.java:316)
	at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:55)
	at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:122)
	at liquibase.database.AbstractJdbcDatabase.execute(AbstractJdbcDatabase.java:1227)
	at liquibase.database.AbstractJdbcDatabase.executeStatements(AbstractJdbcDatabase.java:1210)
	at liquibase.changelog.ChangeSet.execute(ChangeSet.java:550)
	at liquibase.changelog.visitor.UpdateVisitor.visit(UpdateVisitor.java:43)
	at liquibase.changelog.ChangeLogIterator.run(ChangeLogIterator.java:73)
	at liquibase.Liquibase.update(Liquibase.java:200)
	at liquibase.integration.commandline.Main.doMigration(Main.java:1044)
	at liquibase.integration.commandline.Main.run(Main.java:175)
	at liquibase.integration.commandline.Main.main(Main.java:94)
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Key column 'variantInformations_id' doesn't exist in table
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
	at java.lang.reflect.Constructor.newInstance(Constructor.java:408)
	at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
	at com.mysql.jdbc.Util.getInstance(Util.java:386)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1052)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3609)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3541)
	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2002)
	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2163)
	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2618)
	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2568)
	at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:842)
	at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:681)
	at liquibase.executor.jvm.JdbcExecutor$ExecuteStatementCallback.doInStatement(JdbcExecutor.java:314)
	... 11 more
DEBUG 8/13/15 9:14 AM: liquibase: liquibase/1.0.0.Final/initial-mysql.xml::025::generated: Release Database Lock
DEBUG 8/13/15 9:14 AM: liquibase: liquibase/1.0.0.Final/initial-mysql.xml::025::generated: Executing UPDATE database command: UPDATE unifiedpush.ups_db_changeloglock SET LOCKED = 0, LOCKEDBY = NULL, LOCKGRANTED = NULL WHERE ID = 1
INFO 8/13/15 9:14 AM: liquibase: liquibase/1.0.0.Final/initial-mysql.xml::025::generated: Successfully released change log lock
Unexpected error running Liquibase: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Key column 'variantInformations_id' doesn't exist in table

SEVERE 8/13/15 9:14 AM: liquibase: liquibase/1.0.0.Final/initial-mysql.xml::025::generated: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Key column 'variantInformations_id' doesn't exist in table
liquibase.exception.MigrationFailedException: Migration failed for change set liquibase/1.0.0.Final/initial-mysql.xml::025::generated:
     Reason: liquibase.exception.DatabaseException: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Key column 'variantInformations_id' doesn't exist in table
	at liquibase.changelog.ChangeSet.execute(ChangeSet.java:586)
	at liquibase.changelog.visitor.UpdateVisitor.visit(UpdateVisitor.java:43)
	at liquibase.changelog.ChangeLogIterator.run(ChangeLogIterator.java:73)
	at liquibase.Liquibase.update(Liquibase.java:200)
	at liquibase.integration.commandline.Main.doMigration(Main.java:1044)
	at liquibase.integration.commandline.Main.run(Main.java:175)
	at liquibase.integration.commandline.Main.main(Main.java:94)
Caused by: liquibase.exception.DatabaseException: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Key column 'variantInformations_id' doesn't exist in table
	at liquibase.executor.jvm.JdbcExecutor$ExecuteStatementCallback.doInStatement(JdbcExecutor.java:316)
	at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:55)
	at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:122)
	at liquibase.database.AbstractJdbcDatabase.execute(AbstractJdbcDatabase.java:1227)
	at liquibase.database.AbstractJdbcDatabase.executeStatements(AbstractJdbcDatabase.java:1210)
	at liquibase.changelog.ChangeSet.execute(ChangeSet.java:550)
	... 6 more
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Key column 'variantInformations_id' doesn't exist in table
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
	at java.lang.reflect.Constructor.newInstance(Constructor.java:408)
	at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
	at com.mysql.jdbc.Util.getInstance(Util.java:386)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1052)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3609)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3541)
	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2002)
	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2163)
	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2618)
	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2568)
	at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:842)
	at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:681)
	at liquibase.executor.jvm.JdbcExecutor$ExecuteStatementCallback.doInStatement(JdbcExecutor.java:314)
	... 11 more

Database changed
mysql> show tables;
+--------------------------+
| Tables_in_unifiedpush    |
+--------------------------+
| AdmVariant               |
| AndroidVariant           |
| Category                 |
| Installation             |
| Installation_Category    |
| PushApplication          |
| SimplePushVariant        |
| Variant                  |
| VariantMetricInformation |
| WindowsMPNSVariant       |
| WindowsWNSVariant        |
| hibernate_sequence       |
| iOSVariant               |
| pushmessageinformation   |
+--------------------------+
14 rows in set (0.00 sec)

mysql> desc VariantMetricInformation;
+---------------------------+--------------+------+-----+---------+-------+
| Field                     | Type         | Null | Key | Default | Extra |
+---------------------------+--------------+------+-----+---------+-------+
| id                        | varchar(255) | NO   | PRI | NULL    |       |
| variantID                 | varchar(255) | NO   | MUL | NULL    |       |
| receivers                 | bigint(20)   | YES  |     | NULL    |       |
| deliveryStatus            | bit(1)       | YES  |     | NULL    |       |
| reason                    | varchar(255) | YES  |     | NULL    |       |
| variantOpenCounter        | bigint(20)   | YES  |     | NULL    |       |
| servedBatches             | int(11)      | YES  |     | NULL    |       |
| totalBatches              | int(11)      | YES  |     | NULL    |       |
| pushMessageInformation_id | varchar(255) | YES  | MUL | NULL    |       |
+---------------------------+--------------+------+-----+---------+-------+
9 rows in set (0.00 sec)

In commit the definition of the VariantMetricInformation.hbm.xml where the column pushMessageInformation was changed to pushMessageInformation_id. This was for version 1.1.0.Final-SNAPSHOT:

git show bbe1c0e:pom.xml

With the addition of the migrator we now have the requirement to create the data base schema using the migrator and not have it automatically created by hibernate upon application startup.

./bin/ups-migrator --logLevel=debug update

This would populate the database succesfully.

No if we take checkout the commit above:

git co -b migrator-qe-issue bbe1c0e

Now, build and deploy against to the server (with a clean mysql database) will create the data base schema, but an incorrect one. Running the migrator against this database will error with the above reported error. I'm just wondering is this is a valid use of the migrator. Upgrading from 1.1.0.Final to 1.1.0.Final basically. At the time this the bbe1c0e commit there was no migrator yet so letting hibernate create the data base schema was the way to do it. Now that the migrator was introduced it should be used to generate the schema.