0

I am facing a very bizarre problem where my App Engine server can't load its Cloud SQL's GoogleDriver, here's the error (It happens right after I run "mvn appengine:update").

org.springframework.transaction.CannotCreateTransactionException: Could not open Hibernate Session for transaction; nested exception is org.hibernate.exception.GenericJDBCException: Cannot open connection
        at org.springframework.orm.hibernate3.HibernateTransactionManager.doBegin(HibernateTransactionManager.java:596)
        ...
Caused by: org.apache.commons.dbcp.SQLNestedException: Cannot load JDBC driver class 'com.mysql.jdbc.GoogleDriver'
        at org.apache.commons.dbcp.BasicDataSource.createConnectionFactory(BasicDataSource.java:1429)
        at org.apache.commons.dbcp.BasicDataSource.createDataSource(BasicDataSource.java:1371)
        at org.apache.commons.dbcp.BasicDataSource.getConnection(BasicDataSource.java:1044)
        at org.springframework.orm.hibernate3.LocalDataSourceConnectionProvider.getConnection(LocalDataSourceConnectionProvider.java:81)
        at org.hibernate.jdbc.ConnectionManager.openConnection(ConnectionManager.java:446)
        ... 48 more
Caused by: java.lang.ClassNotFoundException: com.mysql.jdbc.GoogleDriver

appengine.properties

################### MySQL Configuration - Google Cloud App Engine ##########################
jdbc.driverClassName=com.mysql.jdbc.GoogleDriver
jdbc.url=jdbc:google:mysql://mytestapp:testdb?user=someuser
jdbc.username=someuser
jdbc.password=******
jdbc.dialect=org.hibernate.dialect.MySQLDialect

In my Spring context file I have:

<context:property-placeholder location="classpath:appengine.properties" />
<bean
    id="dataSource"
    class="org.apache.commons.dbcp.BasicDataSource"
    destroy-method="close" >

    <property
        name="driverClassName"
        value="${jdbc.driverClassName}" />
    <property
        name="url"
        value="${jdbc.url}" />
    ...

Any ideas?

==

Just to confirm, I had already configured my appengine-web.xml and it doesn't help at all:

<?xml version="1.0" encoding="utf-8"?>
<appengine-web-app xmlns="http://appengine.google.com/ns/1.0">
    <application>mytestapp</application>
    <version>1</version>
    <threadsafe>true</threadsafe>
    <sessions-enabled>true</sessions-enabled>

    <system-properties>
        <property name="java.util.logging.config.file" value="WEB-INF/logging.properties"/>
    </system-properties>

    <use-google-connector-j>true</use-google-connector-j>
</appengine-web-app>

--

I couldn't move forward due to some incompatibility between hibernate jar versions so I kept trying through the actual release pipeline (Jenkins + Maven + build + test + deploy), I had to add the Compute Engine VM's IP address to the list of authorized IPs of the Cloud SQL Instance in order to run my unit tests and deploy it to the application (but, if I keep this approach, I can only use the MySQL driver and URL instead of the GoogleDriver and URL. So this is getting tricky...).

I have these properties in a Production.properties file that is loaded by my Spring MVC config file:

################### MySQL Configuration - Google Cloud App Engine ##########################
jdbc.driverClassName=com.mysql.jdbc.GoogleDriver
jdbc.url=jdbc:google:mysql://*******testapp:testsqldb?user=root
jdbc.username=root
jdbc.password=*****
jdbc.dialect=org.hibernate.dialect.MySQLDialect

Is there an easy way to dynamically switch between external and GAE Cloud SQL connection details without using this approach?

if (SystemProperty.environment.value() ==
SystemProperty.Environment.Value.Production) {
    // Connecting from App Engine.

Any ideas?

the_marcelo_r
  • 1,847
  • 22
  • 35
  • 1
    The MySQL JDBC driver JAR must not contain a class GoogleDriver. – duffymo Dec 23 '14 at 15:32
  • Just checked and the jar doesn't contain this class. I'm using "mysql-connector-java-5.1.14" (specified in my pom.xml) and it works fine with "mvn appengine:devserver", but my understanding is that it will use a completely different drive once it is deployed to the actual (remote) appengine within Google's Cloud (i.e., com.mysql.jdbc.GoogleDriver), I got this info from the instructions in my Google Cloud Developer Console - Cloud SQL. – the_marcelo_r Dec 23 '14 at 15:38
  • Well, looks like you're missing some library in your project classpath. The error is very specific: **java.lang.ClassNotFoundException: com.mysql.jdbc.GoogleDriver** – Luiggi Mendoza Dec 23 '14 at 15:39
  • Open the JAR - do you see that class? If not, you have your answer. I think the MySQL JAR has com.mysql.jdbc.Driver for connecting to that database. Try changing the name. – duffymo Dec 23 '14 at 15:40
  • 1
    Guys, you are missing the point, the Appengine is supposed to provide the jar once you deploy to Google's cloud, all these comments are assuming this is a normal web app. – the_marcelo_r Dec 23 '14 at 15:48
  • No, we're not assuming that. What we're telling you is that your application doesn't have such jar when being deployed, which means your app server doesn't provide the library or you're doing something wrong, but the problem remains the same. – Luiggi Mendoza Dec 23 '14 at 16:11
  • Thanks for your comment Luiggi, I'm very interested in gathering more information about this "which means your app server doesn't provide the library or you're doing something wrong", any ideas? – the_marcelo_r Dec 23 '14 at 16:16
  • I was able to upload my application to Google Cloud's App Engine (i.e., remote app server) after skipping the tests (So it won't try to initialize the hibernate session for the unit tests): mvn appengine:update -DskipTests. However, now the application is throwing a weird "java.lang.NoSuchFieldError: INSTANCE" – the_marcelo_r Dec 23 '14 at 17:27

1 Answers1

2

Edited to add (from comments below):

  • Also, when connecting to Cloud SQL from GAE you should leave the password field empty.
  • You should also make sure that if your code is running outside GAE (e.g. on your workstation, on GCE, on a Jenkins build) it uses stock MySQL connector as the Google connector is only available on GAE.
  • You might also want too look into using the stock MySQL driver, which works both from GAE and other connections. There is a demo of this at https://github.com/GoogleCloudPlatform/appengine-cloudsql-native-mysql-hibernate-jpa-demo-java
David
  • 9,288
  • 1
  • 20
  • 52
  • Thanks for your answer David, this was already configured and it didn't help, even after deploying with both "mvn appengine:update" and through the release pipeline, I get the same error: "Cannot load JDBC driver class 'com.mysql.jdbc.GoogleDriver'", do you have any other suggestions? Is there a way for me to ssh into the appengine box to do some troubleshooting? – the_marcelo_r Dec 23 '14 at 22:05
  • (1) You cannot SSH to App Engine. (2) Please confirm this is an error you are getting on GAE, not the dev server. (3) Try explicitly calling `Class.forName("com.mysql.jdbc.GoogleDriver")` before the spring/hibernate dance. (4) Can you get a more complete backtrace so I can see how your stack is trying to load the class? – David Dec 23 '14 at 23:41
  • (1) ok then. (2) I couldn't see this error while running " mvn appengine:update -DenvTarget=Production -DskipTests", I was getting a weird "java.lang.NoSuchFieldError: INSTANCE" (which was due to some incompatibility with Hibernate jar versions), I solved this after introducing "mvn clean .." to the command (I thought the appengine:update would implicitly do that for me). (3) I've set the driver and it seems to be loading it correctly now, but I'm getting a new error: "org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (Access denied for user 'root'@'localhost" – the_marcelo_r Dec 24 '14 at 00:29
  • From (2) it sounds like this code is running on your workstation rather than on app engine, possibly because tests are running. As mentioned on the doc page I linked to the GoogleDriver only works on GAE, not in the dev environment; locally you should use stock MySQL driver. The example code checks this via `com.google.appengine.api.utils.SystemProperty.environment.value() == SystemProperty.Environment.Value.Production`; I don't know how to do this in spring/hibernate world – David Dec 24 '14 at 00:52
  • Once again, thanks a million for your comments David, appreciate the attention. Would you know how to customize pipeline instances/jobs that the Compute Engine VM is running for every git push I perform? I can SSH into it but it doesn't recognize my environment variables and I don't have access to the Jenkins Web Console either :( Any ideas on how to customize the mvn call or at least set an environment variable that would be recognized by the jenkins job? – the_marcelo_r Dec 24 '14 at 01:26
  • Wow @David, looks like the documentation could emphasize this a little bit more: the error I was facing before ""org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (Access denied for user 'root'@'localhost" -- was happening because I was trying to connect with the root password I had set for the Cloud SQL instance, the connection only works ******if we do not set the password***** now it's working perfectly! :) just need to solve this crazy JDK8 compilation issue in the Compute Engine VM. But anyway, thanks for your comments! – the_marcelo_r Dec 24 '14 at 03:31
  • I have updated my answer with the stuff from these comments. I'm not sure what's going on with your GCE set-up. It might be worthwhile creating a separate stack overflow question as GCE and GAE are very different. – David Dec 29 '14 at 19:35
  • Any idea [why it's not working for me](http://stackoverflow.com/questions/37644148/java-lang-illegalstateexception-could-not-load-jdbc-driver-class-com-mysql-jdb)? – Stefan Falk Jun 05 '16 at 16:44
  • @theMarceloR how did you get the driver to load correctly? – Ricardo Meneghin Filho Aug 26 '16 at 01:43