Installation Error related to JDBC

[JC]

I have followed instructions shown in https://cbioportal.readthedocs.io/en/latest/Deploying.html

Tomcat itself seems to be working, but when I am trying to deploy cBioPortal, it shows errors.

The errors indicates context.xml file.

The original documents recommended to use following context.

     <Resource name="jdbc/cbioportal" auth="Container" type="javax.sql.DataSource"
        maxActive="100" maxIdle="30" maxWait="10000"
        username="cbio_user" password="UserKCR@018" driverClassName="com.mysql.jdbc.Driver"
        connectionProperties="zeroDateTimeBehavior=convertToNull;"
        testOnBorrow="true"
        validationQuery="SELECT 1"
        url="jdbc:mysql://localhost:3306/cbioportal"/>

It showed error as following:

### Error querying database.  Cause: org.springframework.jdbc.CannotGetJdbcConnectionException: Could not get JDBC Connection; nested exception is java.sql.SQLException: Cannot create PoolableConnectionFactory (The connection property 'zeroDateTimeBehavior' acceptable values are: 'CONVERT_TO_NULL', 'EXCEPTION' or 'ROUND'. The value 'convertToNull' is not acceptable.)

Therefore, I changed connectionProperties="zeroDateTimeBehavior=convertToNull;" to connectionProperties="zeroDateTimeBehavior=CONVERT_TO_NULL;"

And then, it shows different error.

### Cause: org.springframework.jdbc.CannotGetJdbcConnectionException: Could not get JDBC Connection; nested exception is java.sql.SQLException: Cannot create PoolableConnectionFactory (The server time zone value 'EDT' is unrecognized or represents more than one time zone. You must configure either the server or JDBC driver (via the serverTimezone configuration property) to use a more specifc time zone value if you want to utilize time zone support.)

Next, I followed https://stackoverflow.com/questions/26515700/mysql-jdbc-driver-5-1-33-time-zone-issue

So, updated the XML file

connectionProperties="zeroDateTimeBehavior=CONVERT_TO_NULL;useUnicode=true;useJDBCCompliantTimezoneShift=true;useLegacyDatetimeCode=false;serverTimezone=UTC;"

Finally, I have got following errors.

2018-10-09 17:40:38 [main] ERROR org.springframework.web.context.ContextLoader - Context initialization failed
org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'xmlModelPlugin': Failed to introspect bean class [springfox.documentation.schema.XmlModelPlugin] for lookup method metadata: could not find class that it depends on; nested exception is java.lang.NoClassDefFoundError: javax/xml/bind/annotation/XmlType

I feel totally lost at this moment.

Could anyone can help me?

Thank you.

[Kelsey Zhu]

Hi, 

thank you for reaching out!

which version of mysql were you using? The reason I am asking is because I once had the same issue with mysql8 and got it solved by making follow changes:

1. changing connection properties FROM zeroDateTimeBehavior=convertToNull TO zeroDateTimeBehavior=CONVERT_TO_NULL (you already did that)

2. changing jdbc driverClassName from driverClassName="com.mysql.jdbc.Driver" to driverClassName="com.mysql.cj.jdbc.Driver"

3. adding a new query parameter to connection URL: serverTimezone=US/Eastern (please use timedatectl to find your system time zone)

my context.xml file is like this:

    <Resource name="jdbc/cbioportal" auth="Container" type="javax.sql.DataSource"

            maxActive="100" maxIdle="30" maxWait="10000"

            username="cbio_user" password="****" driverClassName="com.mysql.cj.jdbc.Driver"

            connectionProperties="zeroDateTimeBehavior=CONVERT_TO_NULL;"

            testOnBorrow="true"

            validationQuery="SELECT 1"

            url="jdbc:mysql://localhost:3306/cbioportal?serverTimezone=US/Eastern"/>

Best!

Kelsey

[Jong Cheol Jeong]

Hi Kelsey, 

Thank you very much for your help. 

I have updated my context.xml as you recommended

     <Resource name="jdbc/cbioportal" auth="Container" type="javax.sql.DataSource"

        maxActive="100" maxIdle="30" maxWait="10000"

        username="cbio_user" password="**********" driverClassName="com.mysql.cj.jdbc.Driver"

connectionProperties="zeroDateTimeBehavior=CONVERT_TO_NULL;"

        testOnBorrow="true"

        validationQuery="SELECT 1"

url="jdbc:mysql://localhost:3306/cbioportal?serverTimezone=US/Eastern”/>

This can fix the previous errors, but I now have faced another error. 

10-Oct-2018 16:55:15.115 INFO [main] org.apache.catalina.core.StandardEngine.startInternal Starting Servlet Engine: Apache Tomcat/9.0.12

10-Oct-2018 16:55:15.149 INFO [main] org.apache.catalina.startup.HostConfig.deployWAR Deploying web application archive [/opt/tomcat/webapps/cbioportal.war]

10-Oct-2018 16:55:15.174 WARNING [main] org.apache.catalina.startup.SetContextPropertiesRule.begin [SetContextPropertiesRule]{Context} Setting property 'antiJARLocking' to 'true' did not find a matching property.

10-Oct-2018 16:55:15.183 INFO [main] org.apache.catalina.startup.ExpandWar.expand An expanded directory [/opt/tomcat/webapps/cbioportal] was found with a last modified time that did not match the associated WAR. It will be deleted.

10-Oct-2018 16:55:21.265 INFO [main] org.apache.catalina.core.ApplicationContext.log No Spring WebApplicationInitializer types detected on classpath

10-Oct-2018 16:55:21.284 INFO [main] org.apache.catalina.core.ApplicationContext.log Initializing Spring root WebApplicationContext

Wed Oct 10 16:55:28 EDT 2018 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.

Wed Oct 10 16:55:28 EDT 2018 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.

10-Oct-2018 16:55:31.155 SEVERE [main] org.apache.catalina.core.StandardContext.listenerStart Exception sending context initialized event to listener instance of class [org.springframework.web.context.ContextLoaderListener]

 org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'xmlModelPlugin': Failed to introspect bean class [springfox.documentation.schema.XmlModelPlugin] for lookup method metadata: could not find class that it depends on; nested exception is java.lang.NoClassDefFoundError: javax/xml/bind/annotation/XmlType

        at org.springframework.beans.factory.annotation.AutowiredAnnotationBeanPostProcessor.determineCandidateConstructors(AutowiredAnnotationBeanPostProcessor.java:269)

        at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.determineConstructorsFromBeanPostProcessors(AbstractAutowireCapableBeanFactory.java:1118)

        at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBeanInstance(AbstractAutowireCapableBeanFactory.java:1091)

        at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:513)

        at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:483)

        at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:306)

I attach log files. 

By the way, here is the information of my system.

Ubuntu 18.04.1 LTS

mysql  Ver 14.14 Distrib 5.7.23, for Linux (x86_64) using  EditLine wrapper

Apache Tomcat/9.0.12

Java Home:             /usr/lib/jvm/java-11-openjdk-amd64

JVM Version:           10.0.2+13-Ubuntu-1ubuntu0.18.04.2

JVM Vendor:            Oracle Corporation

CATALINA_BASE:         /opt/tomcat

CATALINA_HOME:         /opt/tomcat

Thank you. 

JC

[Kelsey Zhu]

Hi Jong Cheol,

thanks for sharing you system information. 

I would suggest you get a JRE-8 installed. Those errors were likely caused by incompatible java versions. cBioPortal does not work with Java9/10 from my testing, and have not got a chance to try Java 11 yet.

Best!

Kelsey

[Kelsey Zhu]

Glad to hear that and thank you for sharing your experience, Jong Cheol!

Kelsey

On Thu, Oct 11, 2018 at 5:50 PM Jong Cheol Jeong <korjc...@gmail.com> wrote:

Thank Kelsey, 

It was problems with dependencies. 

FYI, I added followings into $PORTAL_HOME/core/pom.xml

    <dependency>

      <groupId>javax.xml.bind</groupId>

      <artifactId>jaxb-api</artifactId>

      <version>2.3.0</version>

    </dependency>

Problem solved!! Thank you very much!!

Cheers, 

JC

[Jong Cheol Jeong]

Thank Kelsey, 

It was problems with dependencies. 

FYI, I added followings into $PORTAL_HOME/core/pom.xml

    <dependency>

      <groupId>javax.xml.bind</groupId>

      <artifactId>jaxb-api</artifactId>

      <version>2.3.0</version>

    </dependency>

Problem solved!! Thank you very much!!

Cheers, 

JC

On Oct 11, 2018, at 1:34 PM, Kelsey Zhu <kelse...@gmail.com> wrote: