Play Siena failing to connect to MySQL on GAE

Question

I am using play framework 1.2.7, gae module 1.6.0 and siena module 2.0.7 (also tested 2.0.6). This is a simple project that should run in play deployed on App Engine and connect to a MySQL database in Google Cloud SQL. My project runs fine locally but fails to connect to the database in production. Looking at the logs it looks like it is using the postgresql driver instead of the mysql one.

Application.conf

# db=mem
db.url=jdbc:google:mysql://PROJECT_ID:sienatest/sienatest
db.driver=com.mysql.jdbc.GoogleDriver
db.user=root
db.pass=root

This is the crash stack trace

play.Logger niceThrowable: Cannot connected to the database : null
java.lang.NullPointerException
    at com.google.appengine.runtime.Request.process-a3b6145d1dbbd04d(Request.java)
    at java.util.Hashtable.put(Hashtable.java:432)
    at java.util.Properties.setProperty(Properties.java:161)
    at org.postgresql.Driver.loadDefaultProperties(Driver.java:121)
    at org.postgresql.Driver.access$000(Driver.java:47)
    at org.postgresql.Driver$1.run(Driver.java:88)
    at java.security.AccessController.doPrivileged(AccessController.java:63)
    at org.postgresql.Driver.getDefaultProperties(Driver.java:85)
    at org.postgresql.Driver.connect(Driver.java:231)
    at java.sql.DriverManager.getConnection(DriverManager.java:571)
    at java.sql.DriverManager.getConnection(DriverManager.java:215)
    at play.modules.siena.GoogleSqlDBPlugin.onApplicationStart(GoogleSqlDBPlugin.java:103)
    at play.plugins.PluginCollection.onApplicationStart(PluginCollection.java:525)
    at play.Play.start(Play.java:533)
    at play.Play.init(Play.java:305)

What is going on here? I am specifying the correct driver and url schema and it's using postgresql driver. Google Cloud SQL API access is enabled, the app is allowed to connect to the mysql instance, I am not using db=mem, ... I am stuck and can't figure out how to move forward! :-((

UPDATE: I thought I found the solution, but that was not the case. If I keep the %prod. prefix and create a war normally (or just don't define any DB properties), then the application will use Google DataStore instead of the Cloud SQL. If I create the war file adding --%prod at the end (or just delete the %prod. prefix in the application.conf), then it will keep failing to connect to the database showing the same initial error.

Any ideas please?

Answer 1

At the end I ended doing a slight modification in play siena module source code and recompiling it.

In case anyone is interested, you will need to remove/comment/catch exception in this code around line 97 in GoogleSqlDBPlugin class:

// Try the connection
                Connection fake = null;
                try {
                    if (p.getProperty("db.user") == null) {
                        fake = DriverManager.getConnection(p.getProperty("db.url"));
                    } else {
                        fake = DriverManager.getConnection(p.getProperty("db.url"), p.getProperty("db.user"), p.getProperty("db.pass"));
                    }
                } finally {
                    if (fake != null) {
                        fake.close();
                    }
                }

For some reason the connection fails when initiated with DriverManager.getConnection() but it works when initiated with basicDatasource.getConnection(); which apparently is the way used by the module in the rest of the code. So if you delete the above block, and recompile the module everything will work as expected. If you are compiling with JDK 7, you will also need to implement public Logger getParentLogger() throws SQLFeatureNotSupportedException in the ProxyDriver inner class at the end of GoogleSqlDBPlugin file.

Strangely, I digged into the DriverManager.getConnection() and it looked like some postgresql driver is registered somehow, because otherwise I can't see why DriverManager.getConnection() would call to org.postgresql.Driver.connect().

Answer 2

After being stuck for so long on this I just found the solution in no time after posting the question. Quite stupid actually.

The production environment properties in the application.conf file must be preceded by %prod. so the database config should read

%prod.db.url=jdbc:google:mysql://PROJECT_ID:sienatest/sienatest
%prod.db.driver=com.mysql.jdbc.GoogleDriver
%prod.db.user=root
%prod.db.pass=root

And everything runs fine.

EDIT: This is NOT the solution. The problem went away, but the app is using the DataStore instead of the Cloud SQL