Latest WebAPI Setting Requirements

alfranke · February 24, 2015, 10:40pm

Here is a fairly comprehensive list, including the datasource.cdm.database property that Chris added. I’ll see if I can get to the CI server…

<profile>
	<id>webapi-postgresql</id>
	<properties>
		<datasource.driverClassName>org.postgresql.Driver</datasource.driverClassName>
		<datasource.url>jdbc:postgresql://54.209.111.128:5432/vocabularyv5</datasource.url>
		<datasource.username>USER</datasource.username>
		<datasource.password>PASS</datasource.password>
		<datasource.dialect>postgresql</datasource.dialect>
		<datasource.cdm.database />
		<datasource.cdm.schema>unrestricted</datasource.cdm.schema>
		<datasource.ohdsi.schema>ohdsi</datasource.ohdsi.schema>
		<datasource.cohort.schema>ohdsi</datasource.cohort.schema>
		<flyway.datasource.driverClassName>${datasource.driverClassName}</flyway.datasource.driverClassName>
		<flyway.datasource.url>${datasource.url}</flyway.datasource.url>
		<flyway.datasource.username>userWithWritesToOhdsiSchema</flyway.datasource.username>
		<flyway.datasource.password>PASS</flyway.datasource.password>
		<flyway.locations>classpath:db/migration/postgresql</flyway.locations>
		<!-- Note: Schema name is case-sensitive. -->
		<flyway.schemas>${datasource.ohdsi.schema}</flyway.schemas>
	</properties>
</profile>

alfranke · February 24, 2015, 10:46pm

Looks like a connection cannot be made. Possibly due to the change in property name convention (dropping ‘spring.’ from the main datasource properties.
I’d be happy to take a look if access can be set up…

2015-02-24 20:23:22.630 WARN main org.hibernate.engine.jdbc.internal.JdbcServicesImpl -  - HHH000342: Could not obtain connection to query metadata : Connection refused. Check that the hostname and port are correct and that the postmaster is accepting TCP/IP connections.

Chris_Knoll · February 24, 2015, 10:55pm

This is the settigns I use internally (in settings.xml):

<settings>
  <profiles>
    <profile>
      <id>webapi-mssql</id>
      <properties>
        <datasource.driverClassName>com.microsoft.sqlserver.jdbc.SQLServerDriver</datasource.driverClassName>
        <datasource.url>jdbc:sqlserver://rndusrdhit09;databaseName=CDM_TRUVEN_CCAE_6K_V5_OHDSI</datasource.url>
        <datasource.username>{use hix account}</datasource.username>
        <datasource.password>{use hix pwd}</datasource.password>
        <datasource.dialect>sql server</datasource.dialect>
        <datasource.cdm.database>CDM_TRUVEN_CCAE_6K_V5</datasource.cdm.database>
        <datasource.cdm.schema>dbo</datasource.cdm.schema>
        <datasource.ohdsi.schema>dbo</datasource.ohdsi.schema>
        <datasource.cohort.schema>dbo</datasource.cohort.schema>
        <flyway.datasource.driverClassName>${datasource.driverClassName}</flyway.datasource.driverClassName>
        <flyway.datasource.url>${datasource.url}</flyway.datasource.url>
        <flyway.datasource.username>{use hix builder account}</flyway.datasource.username>
        <flyway.datasource.password>{use hix builder pwd}</flyway.datasource.password>
        <flyway.locations>classpath:db/migration/sqlserver</flyway.locations>
      </properties>
    </profile>    
  </profiles>
  <activeProfiles>
    <activeProfile>webapi-mssql</activeProfile>
  </activeProfiles>
</settings>

Ensure you have clean build, there were application.properties that were updated that I’m not sure the webserver will pickup without a hard reset.

Chris_Knoll · February 24, 2015, 11:00pm

re: hibernate dialect: you won’t find any setting that specifies the hibernate dialect. you should see the following in your logs:

2015-02-24 15:41:26.726 INFO http-nio-8084-exec-15 org.springframework.jdbc.datasource.DriverManagerDataSource -  - Loaded JDBC driver: com.microsoft.sqlserver.jdbc.SQLServerDriver
2015-02-24 15:41:27.923 INFO http-nio-8084-exec-15 org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean -  - Building JPA container EntityManagerFactory for persistence unit 'default'
2015-02-24 15:41:27.948 INFO http-nio-8084-exec-15 org.hibernate.jpa.internal.util.LogHelper -  - HHH000204: Processing PersistenceUnitInfo [
	name: default
	...]
2015-02-24 15:41:28.178 INFO http-nio-8084-exec-15 org.hibernate.Version -  - HHH000412: Hibernate Core {4.3.7.Final}
2015-02-24 15:41:28.181 INFO http-nio-8084-exec-15 org.hibernate.cfg.Environment -  - HHH000206: hibernate.properties not found
2015-02-24 15:41:28.184 INFO http-nio-8084-exec-15 org.hibernate.cfg.Environment -  - HHH000021: Bytecode provider name : javassist
2015-02-24 15:41:28.626 INFO http-nio-8084-exec-15 org.hibernate.annotations.common.Version -  - HCANN000001: Hibernate Commons Annotations {4.0.5.Final}
2015-02-24 15:41:28.834 INFO http-nio-8084-exec-15 org.hibernate.dialect.Dialect -  - HHH000400: Using dialect: org.hibernate.dialect.SQLServer2012Dialect

It appears to derive the dialect from making a connection to the server. If your connection strings aren’t set up correctly then it won’t be able to connect tot he server to find out. Your issue is probably coming from an earlier problem.

lee_evans · February 25, 2015, 1:47am

The Jenkins CI server WebAPI build is working again now.

The above posts were very useful info when I was updating the settings.xml on the CI server. I also had to drop the original manually created postgresql batch_job tables because they are now automatically maintained by hibernate.

Frank · February 25, 2015, 3:50pm

Agreed @lee_evans, thanks for all the settings information as I’m now able to build.

Unfortunately I’m getting errors on deployment now. Looks like it has something to do with flyway. I’ve pasted my error log below. This issue is particularly challenging because when flyway fails the entire deployment fails and nothing is available. Its currently blocking me from development on hermes and treatment pathways.

Is this the way it has to be? In as much as using flyway is ensuring tables exist that are required for certain services and so the application won’t start at all if there is a flyway failure, or could we degrade more gracefully with the app still starting in the event of a flyway failure?

I’m also seeing that it is taking about 4 minutes from build to deploy (and then sadly, crash). Not sure if there is anything we can do to improve that time.

2015-02-25 10:31:47.575 ERROR http-nio-8084-exec-17 org.springframework.boot.SpringApplication -  - Application startup failed
org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'flyway' defined in class path resource [org/springframework/boot/autoconfigure/flyway/FlywayAutoConfiguration$FlywayConfiguration.class]: Invocation of init method failed; nested exception is org.flywaydb.core.api.FlywayException: Validate failed. Found differences between applied migrations and available migrations: Migration Checksum mismatch for migration V1.0.0.2.1__alter_jpa_identity.sql: DB=-1572324914, Classpath=50888723

cahilton · February 25, 2015, 3:59pm

@Frank I think this the same issue I had (with flyway). I had to change my line endings in git, then checkout webapi again. You can do it globally, or use a .gitattributes file for project specific settings.

cahilton · February 25, 2015, 4:02pm

I didn’t have any problems with speed though. Still takes <8s to deploy.

The other option, if you really need to get running, is to turn flyway off in application.properties.

flyway.enabled=false

That’s probably not something we want to do by default, but it will get you running in a pinch if you need to get going with this error.

lee_evans · February 25, 2015, 4:09pm

Hi @Frank. I’m wondering if flyway is complaining because the schema tables that it is trying to manage in your database already exist but flyway didn’t create them? I had to drop the batch_job tables (and sequences) I created manually in my postgresql db so flyway could create them and then it was happy. It’s also probably worth reviewing the sqlserver sql files in the src/main/resources/db/migration/sqlserver/ folder to see what tables flyway is managing.

Frank · February 25, 2015, 4:09pm

I tested pointing at a new empty database and that seems to have gotten it to start up and create all the tables. I’m not sure if that means that there is an issue with our existing OHDSI database that @Chris_Knoll had setup or if it has to do with the checksum issues.

On the speed issue, there are a lot of differences between our environments. I’ll have to try out STS and see if perhaps its just the way netbeans is launching. I have to imagine that STS is tuned for working with spring better than my current setup.

Frank · February 25, 2015, 4:10pm

My understanding is that the database environment I was trying to use was created by flyway. Its shared between @Chris_Knoll and I so I don’t want to just go and drop the tables just yet.

alfranke · February 25, 2015, 4:14pm

@Frank, you’re running into the “Flyway validation” error Charity ran in to yesterday, having to do with differences between operating systems and line ending characters. Charity was able to get past this error by turning ‘git config --global core.autocrlf true’ (she was on Mac and the default is false - some say to set to INPUT but that didn’t work). I think we should add a .gitattributes file to the repo to ensure this flyway validation passes (or to have better chances across systems - i guess eclipse git EGIT does not acknowledge .gitattributes,etc.). Flyway 4.x will have a fix for this.

github.com/flyway/flyway

Ignore line endings when calculating checksums

opened 10:11AM - 25 Jun 13 UTC

closed 09:50AM - 20 Jan 16 UTC

flyway

m: Core t: bug

_Original author: micha...@utest.com (September 28, 2011 15:49:50)_ We ran into… the following problem. Developer on Mac/Linux creates a sql migration file and checks it into Git. It runs on production (which is on Linux) just fine. Then a Windows developer takes a copy of the production database and loads it into their local machine. But when they pulled the sql migration file from Git, it automatically converted the line endings to CRLF (Windows style). Now Flyway validate and migrate fail saying there is a checksum mismatch. One solution I can think of would be to ignore line endings when doing the checksum. _Original issue: http://code.google.com/p/flyway/issues/detail?id=167_

Chris_Knoll · February 25, 2015, 4:33pm

Had this problem too. Solved it a different way (but I think I’ll need to enable that git config setting).

The resolution was to do a flyway:repair goal. The problem was that it’s difficult to get this to launch via maven for the following reasons:

Our flyway configs is being managed by spring boot constructs. So maven plugin doesn’t know about it.
If you want to pass the values in via config, you’ll have to put it into the section of the plugin, which means modifying pom.xml for checkin.

So, to get this fixed on my side (and I might have disrupted the checksums for Frank, not sure yet), I downloaded the command line interface tool for flyway from:
http://flywaydb.org/documentation/commandline/
Then I copied the sql migration files from the git WebAPI directory into the unzipped command line folder (there’s placeholder directories int he zip to place migration scripts, custom jars, etc). Seems like this command line tool is a boilerplate that if you have different configurations, you make a copy of this folder and set it for the specific application you are managing.

Anyways, I set the flyway jdbc url, credentials, locations in the comamd line configuation, and was able to run ‘repair’ at which point it reset the checksums based on the content in the migrations folder. After that, I could launch webAPI without complaints.

Just wanted to share that if you are still borked after setting the git CRLF option, there is a way to reset the checksums in the schema_version if something gets messed up due to text file change.

-Chris

alfranke · February 25, 2015, 4:34pm

Regarding speed…
As I’ve indicated elsewhere, there are a lot of differences between how Eclipse (and therefore STS) and Netbeans support maven projects. If you love netbeans and don’t want to make a change, I would recommend trying to use the ‘mvn spring-boot:run’ goal instead of deploying the tomcat. You should be able to use this for most development (not sure how debug is handled in Netbeans for maven projects). Netbeans did not seem to allow me to run the WebApi class as a “java application” as Eclipse does (eclipse adds the dependencies to the classpath). This is the quickest way to run it and is how I do it in Eclipse (STS NOT required). STS provides a “run as spring boot application” which essentially does the exact same thing as “run as java application” except that it discovers the WebApi class (@SpringBootApplication).
Frank, 4 minutes is a concern. Have you told netbeans “run” to skip tests?
The following is what I get when I click run in netbeans

NetBeans: Deploying on Apache Tomcat 8.0.9.0
    profile mode: false
    debug mode: false
    force redeploy: true
Starting Tomcat process...
Waiting for Tomcat...
Tomcat server started.
Undeploying ...
undeploy?path=/WebAPI
OK - Undeployed application at context path /WebAPI
In-place deployment at C:\IDE\netbeans\webapi-github\target\WebAPI
deploy?config=file%3A%2FC%3A%2FUsers%2Fafranken%2FAppData%2FLocal%2FTemp%2Fcontext3364885498616779524.xml&path=/WebAPI
OK - Deployed application at context path /WebAPI
Start is in progress...
start?path=/WebAPI
OK - Started application at context path /WebAPI

This seems to start and stop WebAPI, and then restarts WebAPI a second time, and takes about 25 seconds. We can likely fix these issues if you need to run it in this way.
Looks like Netbeans has a “Navigator” view that shows common/available maven goals, spring-boot:run is there. Try using this (right-click->execute goals). This took about 8 seconds for me in netbeans.
It should be noted that using an external tomcat (vs embedded) in an IDE will take longer, but still shouldn’t take 4 minutes. I would guess that maybe tests are being executed? Or that you may have other webapps in the tomcat instance?

We also want to look at Miredot, it seems to be bound way too early in the lifecycle if you ask me. We should look at configuring this to only run during packaging, etc. I can take a quick look at that.

Chris_Knoll · February 25, 2015, 4:42pm

So there’s another thing that just came up that will have an impact: I just branched out to add a new feature for cohort definition persistence. I introduced a new migration file as a place holder. It migrated (it didn’t actually do anything, it’s just a place holder), but the schema version did show up.

I wanted to see what happens when someone is using this database that doesn’t have this migration file. So, I deleted the file, restarted the app. This is the message:

2015-02-25 11:35:01.279 INFO http-nio-8084-exec-11 org.flywaydb.core.internal.command.DbValidate -  - Validated 4 migrations (execution time 00:00.113s)
2015-02-25 11:35:01.710 INFO http-nio-8084-exec-11 org.flywaydb.core.internal.command.DbMigrate -  - Current version of schema [dbo]: 1.0.0.3
2015-02-25 11:35:01.710 WARN http-nio-8084-exec-11 org.flywaydb.core.internal.command.DbMigrate -  - outOfOrder mode is active. Migration of schema [dbo] may not be reproducible.
2015-02-25 11:35:01.710 WARN http-nio-8084-exec-11 org.flywaydb.core.internal.command.DbMigrate -  - Schema [dbo] has a version (1.0.0.3) that is newer than the latest available migration (1.0.0.2.1) !
2015-02-25 11:35:01.730 INFO http-nio-8084-exec-11 org.flywaydb.core.internal.command.DbMigrate -  - Schema [dbo] is up to date. No migration necessary.

So, good news is: no error is raised so the application will continue to load. Also good news is that it tells you which schemas are migrated which you don’t have a migration file for (this is a good hint). So, I wanted to just share this with anyone who is going to be developing new schema changes on a feature branch that involves flyway migrations.

Chris_Knoll · February 25, 2015, 4:47pm

Alex, on speed: I notice that when the app starts up in netbeans, it seems like it’s initializing spring boot 3 times. In the logs, I see a banner that looks like this:

  .   ____          _            __ _ _
 /\\ / ___'_ __ _ _(_)_ __  __ _ \ \ \ \
( ( )\___ | '_ | '_| | '_ \/ _` | \ \ \ \
 \\/  ___)| |_)| | | | | || (_| |  ) ) ) )
  '  |____| .__|_| |_|_| |_\__, | / / / /
 =========|_|==============|___/=/_/_/_/
 :: Spring Boot ::        (v1.2.1.RELEASE)

When the app launches normally I see it two (or 3) times.

When there’s an error tho, it happens about 9 times. Seems like spring boot wants to get invovled druing different phases of the maven life cycle (even after i skip tests). and especially during build failures. I’m seeing this in the Tomcat logs. I don’t know if something is causing the webAPI to be redeployed multiple times during building and if it is interrupted on error, it does some revert or something back onto tomcat. Not sure.

cahilton · February 25, 2015, 4:58pm

I looked back at a log I had from yesterday, having the error Frank had.

It started and died in about 5s, but that is using STS on a Mac.

alfranke · February 25, 2015, 5:55pm

@Chris_Knoll it’s not spring boot, it’s netbeans that is triggering the application to start more than once (either via mvn goals or tomcat processes). If you look at my snippet above from netbeans default run action, it starts tomcat (which will start WebApi), then undeploys WebApi, then deploys (starting up WebApi). My run configuration consistently starts up WebApi twice (note that I stop the process before clicking run again). Not sure what happens on error. If you want to see why yours happens a third time, you could post your ‘output’ from the run command.

I highly recommend the switch to Eclipse or STS. The maven support seems much better (less invasive), in my opinion. Again, if you stay in netbeans, I would use the Navigator->spring-boot:run option, and/or change your run actions to use spring-boot:run, or ?

Chris_Knoll · February 25, 2015, 8:55pm

I wish I could dictate the IDE, platform and DBMS we use. That would be awesome.

I think the spring-boot:run means that it doesn’t use a hosted environment (the environment that contains the JDBC drivers) and so we’ll have to figure out how to make the app look at additional classpaths/jars during execution. Is there a way to do that? And is there any reason for concern that we’ll be developing in a embedded tomcat mode but normal deploment will be in a ‘container managed mode’?

alfranke · February 26, 2015, 11:06pm

Right Chris, spring-boot:run just runs the WebApi class which bootstraps the embedded tomcat.
Since we’re using Tomcat for both embedded and hosted, I don’t see a big concern. Obviously, testing the application on a stand alone tomcat instance can be done incrementally along the way and is not a bad idea… Note that some opt to run spring boot applications as just jars (w/o a stand alone tomcat).

Regarding driver jars, as you can see in the WebAPI oracle profile, my preference has been to include any dependencies in the project’s pom.xml. I would recommend that you set up a local maven repository or add a private repository to nexus, so that you can have a repo for all artifacts.
Dependencies can be activated for only a select profile.
Dependencies can be scope provided (i.e. available at compile time and runtime but not packaged - implies the container provides them - think servlet api). In your case, the driver jar could be of provided scope. I created the profile-specific dependency on oracle driver with compile scope so that we could use yet another form of running the app (tomcat7:run-war). This is not really necessary.
As a stop gap until you have your artifact in a maven repository, you could consider a ‘system’ scope’d dependency that contains a systemPath (i.e. absolute path to jar).

<dependency>
    <artifactId>..</artifactId>
    <groupId>..</groupId>
    <scope>system</scope>
    <systemPath>${basedir}/lib/driver.jar</systemPath>
</dependency>

On a separate note: i just pushed a change to the miredot plugin to only run during package phase. This should save ~1-2 seconds for netbeans users using spring-boot:run, and is a reasonable phase for CI servers. However, if netbeans users continue to use the default run project (which uses maven package), we may want to consider changing this to phase verify (to be performed with integration tests and still reasonable for CI servers).