TechQA.

Google cloud sql connection error from localhost (Java eclipse)

629 views Asked by At

I'm trying to connect to Google Cloud SQL database from a servlet hosted in local AppEngine Server. I'm able to connect when project is deployed on Google app engine but not from localhost appengine server. I'm getting the below error messages. 

HTTP ERROR 500

Problem accessing /. Reason: 
    java.lang.IllegalAccessError: com/google/appengine/repackaged/com/google/common/base/Platform


Caused by:
java.sql.SQLException: java.lang.IllegalAccessError: com/google/appengine/repackaged/com/google/common/base/Platform
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:964)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:897)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:886)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:860)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:877)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:873)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:443)
    at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:410)
    at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:328)
    at java.sql.DriverManager.getConnection(DriverManager.java:571)
    at java.sql.DriverManager.getConnection(DriverManager.java:233)
    at com.example.appengine.cloudsql.CloudSqlServlet.doGet(CloudSqlServlet.java:88)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:617)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
    at org.mortbay.jetty.servlet.ServletHolder.handle(ServletHolder.java:511)
    at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1166)
    at com.google.appengine.api.socket.dev.DevSocketFilter.doFilter(DevSocketFilter.java:74)
    at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1157)
    at com.google.appengine.tools.development.ResponseRewriterFilter.doFilter(ResponseRewriterFilter.java:134)
    at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1157)
    at com.google.appengine.tools.development.HeaderVerificationFilter.doFilter(HeaderVerificationFilter.java:34)
    at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1157)
    at com.google.appengine.api.blobstore.dev.ServeBlobFilter.doFilter(ServeBlobFilter.java:63)
    at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1157)
    at com.google.apphosting.utils.servlet.TransactionCleanupFilter.doFilter(TransactionCleanupFilter.java:48)
    at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1157)
    at com.google.appengine.tools.development.StaticFileFilter.doFilter(StaticFileFilter.java:122)
    at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1157)
    at com.google.appengine.tools.development.DevAppServerModulesFilter.doDirectRequest(DevAppServerModulesFilter.java:366)
    at com.google.appengine.tools.development.DevAppServerModulesFilter.doDirectModuleRequest(DevAppServerModulesFilter.java:349)
    at com.google.appengine.tools.development.DevAppServerModulesFilter.doFilter(DevAppServerModulesFilter.java:116)
    at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1157)
    at org.mortbay.jetty.servlet.ServletHandler.handle(ServletHandler.java:388)
    at org.mortbay.jetty.security.SecurityHandler.handle(SecurityHandler.java:216)
    at org.mortbay.jetty.servlet.SessionHandler.handle(SessionHandler.java:182)
    at org.mortbay.jetty.handler.ContextHandler.handle(ContextHandler.java:765)
    at org.mortbay.jetty.webapp.WebAppContext.handle(WebAppContext.java:418)
    at com.google.appengine.tools.development.DevAppEngineWebAppContext.handle(DevAppEngineWebAppContext.java:95)
    at org.mortbay.jetty.handler.HandlerWrapper.handle(HandlerWrapper.java:152)
    at com.google.appengine.tools.development.JettyContainerService$ApiProxyHandler.handle(JettyContainerService.java:508)
    at org.mortbay.jetty.handler.HandlerWrapper.handle(HandlerWrapper.java:152)
    at org.mortbay.jetty.Server.handle(Server.java:326)
    at org.mortbay.jetty.HttpConnection.handleRequest(HttpConnection.java:542)
    at org.mortbay.jetty.HttpConnection$RequestHandler.headerComplete(HttpConnection.java:923)
    at org.mortbay.jetty.HttpParser.parseNext(HttpParser.java:547)
    at org.mortbay.jetty.HttpParser.parseAvailable(HttpParser.java:212)
    at org.mortbay.jetty.HttpConnection.handle(HttpConnection.java:404)
    at org.mortbay.io.nio.SelectChannelEndPoint.run(SelectChannelEndPoint.java:409)
    at org.mortbay.thread.QueuedThreadPool$PoolThread.run(QueuedThreadPool.java:582)
Caused by: java.lang.IllegalAccessError: com/google/appengine/repackaged/com/google/common/base/Platform
    at com.google.appengine.repackaged.com.google.common.base.Ticker$1.read(Ticker.java:69)
    at com.google.appengine.repackaged.com.google.common.base.Stopwatch.elapsedNanos(Stopwatch.java:178)
    at com.google.appengine.repackaged.com.google.common.base.Stopwatch.elapsed(Stopwatch.java:191)
    at com.google.appengine.repackaged.com.google.common.cache.LocalCache$LoadingValueReference.elapsedNanos(LocalCache.java:3665)
    at com.google.appengine.repackaged.com.google.common.cache.LocalCache$Segment.getAndRecordStats(LocalCache.java:2382)
    at com.google.appengine.repackaged.com.google.common.cache.LocalCache$Segment.loadSync(LocalCache.java:2337)
    at com.google.appengine.repackaged.com.google.common.cache.LocalCache$Segment.lockedGetOrLoad(LocalCache.java:2295)
    at com.google.appengine.repackaged.com.google.common.cache.LocalCache$Segment.get(LocalCache.java:2208)
    at com.google.appengine.repackaged.com.google.common.cache.LocalCache.get(LocalCache.java:4062)
    at com.google.appengine.repackaged.com.google.common.cache.LocalCache.getOrLoad(LocalCache.java:4066)
    at com.google.appengine.repackaged.com.google.common.cache.LocalCache$LocalLoadingCache.get(LocalCache.java:4995)
    at com.google.appengine.repackaged.com.google.common.cache.LocalCache$LocalLoadingCache.getUnchecked(LocalCache.java:5001)
    at com.google.appengine.api.appidentity.AppIdentityServiceImpl.getAccessToken(AppIdentityServiceImpl.java:272)
    at com.google.api.client.googleapis.extensions.appengine.auth.oauth2.AppIdentityCredential.intercept(AppIdentityCredential.java:98)
    at com.google.api.client.googleapis.extensions.appengine.auth.oauth2.AppIdentityCredential$AppEngineCredentialWrapper.intercept(AppIdentityCredential.java:243)
    at com.google.api.client.http.HttpRequest.execute(HttpRequest.java:859)
    at com.google.api.client.googleapis.services.AbstractGoogleClientRequest.executeUnparsed(AbstractGoogleClientRequest.java:410)
    at com.google.api.client.googleapis.services.AbstractGoogleClientRequest.executeUnparsed(AbstractGoogleClientRequest.java:343)
    at com.google.api.client.googleapis.services.AbstractGoogleClientRequest.execute(AbstractGoogleClientRequest.java:460)
    at com.google.cloud.sql.mysql.SslSocketFactory.obtainInstanceMetadata(SslSocketFactory.java:370)
    at com.google.cloud.sql.mysql.SslSocketFactory.fetchInstanceSslInfo(SslSocketFactory.java:282)
    at com.google.cloud.sql.mysql.SslSocketFactory.getInstanceSslInfo(SslSocketFactory.java:262)
    at com.google.cloud.sql.mysql.SslSocketFactory.createAndConfigureSocket(SslSocketFactory.java:181)
    at com.google.cloud.sql.mysql.SslSocketFactory.create(SslSocketFactory.java:150)
    at com.google.cloud.sql.mysql.SocketFactory.connect(SocketFactory.java:47)
    at com.mysql.jdbc.MysqlIO.<init>(MysqlIO.java:300)
    at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2251)
    at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2284)
    at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2083)
    at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:806)
    at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:47)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:526)
    at com.google.appengine.tools.development.agent.runtime.Runtime.newInstance_(Runtime.java:140)
    at com.google.appengine.tools.development.agent.runtime.Runtime.newInstance(Runtime.java:148)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
    ... 42 more


Caused by:
java.lang.IllegalAccessError: com/google/appengine/repackaged/com/google/common/base/Platform
    at com.google.appengine.repackaged.com.google.common.base.Ticker$1.read(Ticker.java:69)
    at com.google.appengine.repackaged.com.google.common.base.Stopwatch.elapsedNanos(Stopwatch.java:178)
    at com.google.appengine.repackaged.com.google.common.base.Stopwatch.elapsed(Stopwatch.java:191)
    at com.google.appengine.repackaged.com.google.common.cache.LocalCache$LoadingValueReference.elapsedNanos(LocalCache.java:3665)
    at com.google.appengine.repackaged.com.google.common.cache.LocalCache$Segment.getAndRecordStats(LocalCache.java:2382)
    at com.google.appengine.repackaged.com.google.common.cache.LocalCache$Segment.loadSync(LocalCache.java:2337)
    at com.google.appengine.repackaged.com.google.common.cache.LocalCache$Segment.lockedGetOrLoad(LocalCache.java:2295)
    at com.google.appengine.repackaged.com.google.common.cache.LocalCache$Segment.get(LocalCache.java:2208)
    at com.google.appengine.repackaged.com.google.common.cache.LocalCache.get(LocalCache.java:4062)
    at com.google.appengine.repackaged.com.google.common.cache.LocalCache.getOrLoad(LocalCache.java:4066)
    at com.google.appengine.repackaged.com.google.common.cache.LocalCache$LocalLoadingCache.get(LocalCache.java:4995)
    at com.google.appengine.repackaged.com.google.common.cache.LocalCache$LocalLoadingCache.getUnchecked(LocalCache.java:5001)
    at com.google.appengine.api.appidentity.AppIdentityServiceImpl.getAccessToken(AppIdentityServiceImpl.java:272)
    at com.google.api.client.googleapis.extensions.appengine.auth.oauth2.AppIdentityCredential.intercept(AppIdentityCredential.java:98)
    at com.google.api.client.googleapis.extensions.appengine.auth.oauth2.AppIdentityCredential$AppEngineCredentialWrapper.intercept(AppIdentityCredential.java:243)
    at com.google.api.client.http.HttpRequest.execute(HttpRequest.java:859)
    at com.google.api.client.googleapis.services.AbstractGoogleClientRequest.executeUnparsed(AbstractGoogleClientRequest.java:410)
    at com.google.api.client.googleapis.services.AbstractGoogleClientRequest.executeUnparsed(AbstractGoogleClientRequest.java:343)
    at com.google.api.client.googleapis.services.AbstractGoogleClientRequest.execute(AbstractGoogleClientRequest.java:460)
    at com.google.cloud.sql.mysql.SslSocketFactory.obtainInstanceMetadata(SslSocketFactory.java:370)
    at com.google.cloud.sql.mysql.SslSocketFactory.fetchInstanceSslInfo(SslSocketFactory.java:282)
    at com.google.cloud.sql.mysql.SslSocketFactory.getInstanceSslInfo(SslSocketFactory.java:262)
    at com.google.cloud.sql.mysql.SslSocketFactory.createAndConfigureSocket(SslSocketFactory.java:181)
    at com.google.cloud.sql.mysql.SslSocketFactory.create(SslSocketFactory.java:150)
    at com.google.cloud.sql.mysql.SocketFactory.connect(SocketFactory.java:47)
    at com.mysql.jdbc.MysqlIO.<init>(MysqlIO.java:300)
    at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2251)
    at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2284)
    at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2083)
    at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:806)
    at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:47)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:526)
    at com.google.appengine.tools.development.agent.runtime.Runtime.newInstance_(Runtime.java:140)
    at com.google.appengine.tools.development.agent.runtime.Runtime.newInstance(Runtime.java:148)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
    at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:410)
    at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:328)
    at java.sql.DriverManager.getConnection(DriverManager.java:571)
    at java.sql.DriverManager.getConnection(DriverManager.java:233)
    at com.example.appengine.cloudsql.CloudSqlServlet.doGet(CloudSqlServlet.java:88)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:617)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
    at org.mortbay.jetty.servlet.ServletHolder.handle(ServletHolder.java:511)
    at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1166)
    at com.google.appengine.api.socket.dev.DevSocketFilter.doFilter(DevSocketFilter.java:74)
    at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1157)
    at com.google.appengine.tools.development.ResponseRewriterFilter.doFilter(ResponseRewriterFilter.java:134)
    at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1157)
    at com.google.appengine.tools.development.HeaderVerificationFilter.doFilter(HeaderVerificationFilter.java:34)
    at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1157)
    at com.google.appengine.api.blobstore.dev.ServeBlobFilter.doFilter(ServeBlobFilter.java:63)
    at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1157)
    at com.google.apphosting.utils.servlet.TransactionCleanupFilter.doFilter(TransactionCleanupFilter.java:48)
    at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1157)
    at com.google.appengine.tools.development.StaticFileFilter.doFilter(StaticFileFilter.java:122)
    at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1157)
    at com.google.appengine.tools.development.DevAppServerModulesFilter.doDirectRequest(DevAppServerModulesFilter.java:366)
    at com.google.appengine.tools.development.DevAppServerModulesFilter.doDirectModuleRequest(DevAppServerModulesFilter.java:349)
    at com.google.appengine.tools.development.DevAppServerModulesFilter.doFilter(DevAppServerModulesFilter.java:116)
    at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1157)
    at org.mortbay.jetty.servlet.ServletHandler.handle(ServletHandler.java:388)
    at org.mortbay.jetty.security.SecurityHandler.handle(SecurityHandler.java:216)
    at org.mortbay.jetty.servlet.SessionHandler.handle(SessionHandler.java:182)
    at org.mortbay.jetty.handler.ContextHandler.handle(ContextHandler.java:765)
    at org.mortbay.jetty.webapp.WebAppContext.handle(WebAppContext.java:418)
    at com.google.appengine.tools.development.DevAppEngineWebAppContext.handle(DevAppEngineWebAppContext.java:95)
    at org.mortbay.jetty.handler.HandlerWrapper.handle(HandlerWrapper.java:152)
    at com.google.appengine.tools.development.JettyContainerService$ApiProxyHandler.handle(JettyContainerService.java:508)
    at org.mortbay.jetty.handler.HandlerWrapper.handle(HandlerWrapper.java:152)
    at org.mortbay.jetty.Server.handle(Server.java:326)
    at org.mortbay.jetty.HttpConnection.handleRequest(HttpConnection.java:542)
    at org.mortbay.jetty.HttpConnection$RequestHandler.headerComplete(HttpConnection.java:923)
    at org.mortbay.jetty.HttpParser.parseNext(HttpParser.java:547)
    at org.mortbay.jetty.HttpParser.parseAvailable(HttpParser.java:212)
    at org.mortbay.jetty.HttpConnection.handle(HttpConnection.java:404)
    at org.mortbay.io.nio.SelectChannelEndPoint.run(SelectChannelEndPoint.java:409)
    at org.mortbay.thread.QueuedThreadPool$PoolThread.run(QueuedThreadPool.java:582)


Powered by Jetty://

Here is the servlet code

package com.example.appengine.cloudsql;

import java.io.IOException;
import java.io.PrintWriter;
import java.net.Inet4Address;
import java.net.Inet6Address;
import java.net.InetAddress;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.Date;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import com.google.api.client.googleapis.auth.oauth2.GoogleCredential;

// [START example]
@SuppressWarnings("serial")
public class CloudSqlServlet extends HttpServlet {

  @Override
  public void doGet(HttpServletRequest req, HttpServletResponse resp) throws IOException,
      ServletException {
    String path = req.getRequestURI();
    if (path.startsWith("/favicon.ico")) {
      return; // ignore the request for favicon.ico
    }
    // store only the first two octets of a users ip address
    String userIp = req.getRemoteAddr();
    InetAddress address = InetAddress.getByName(userIp);
    if (address instanceof Inet6Address) {
      // nest indexOf calls to find the second occurrence of a character in a string
      // an alternative is to use Apache Commons Lang: StringUtils.ordinalIndexOf()
      userIp = userIp.substring(0, userIp.indexOf(":", userIp.indexOf(":") + 1)) + ":*:*:*:*:*:*";
    } else if (address instanceof Inet4Address) {
      userIp = userIp.substring(0, userIp.indexOf(".", userIp.indexOf(".") + 1)) + ".*.*";
    }
    GoogleCredential credential = GoogleCredential.getApplicationDefault();

    final String createTableSql = "CREATE TABLE IF NOT EXISTS visits ( visit_id INT NOT NULL "
        + "AUTO_INCREMENT, user_ip VARCHAR(46) NOT NULL, timestamp DATETIME NOT NULL, "
        + "PRIMARY KEY (visit_id) )";
    final String createVisitSql = "INSERT INTO visits (user_ip, timestamp) VALUES (?, ?)";
    final String selectSql = "SELECT user_ip, timestamp FROM visits ORDER BY timestamp DESC "
        + "LIMIT 10";

    PrintWriter out = resp.getWriter();
    resp.setContentType("text/plain");
    String url;
    if (System
        .getProperty("com.google.appengine.runtime.version").startsWith("Google App Engine/")) {
      // Check the System properties to determine if we are running on appengine or not
      // Google App Engine sets a few system properties that will reliably be present on a remote
      // instance.
      url = System.getProperty("ae-cloudsql.cloudsql-database-url");
      try {
        // Load the class that provides the new "jdbc:google:mysql://" prefix.
        Class.forName("com.mysql.jdbc.GoogleDriver");
      } catch (ClassNotFoundException e) {
        throw new ServletException("Error loading Google JDBC Driver", e);
      }
    } else {
      // Set the url with the local MySQL database connection url when running locally
      url = System.getProperty("ae-cloudsql.local-database-url");
    }
    log("connecting to: " + url);
    try (Connection conn = DriverManager.getConnection(url);
        PreparedStatement statementCreateVisit = conn.prepareStatement(createVisitSql)) {
      conn.createStatement().executeUpdate(createTableSql);
      statementCreateVisit.setString(1, userIp);
      statementCreateVisit.setTimestamp(2, new Timestamp(new Date().getTime()));
      statementCreateVisit.executeUpdate();

      try (ResultSet rs = conn.prepareStatement(selectSql).executeQuery()) {
        out.print("Last 10 visits:\n");
        while (rs.next()) {
          String savedIp = rs.getString("user_ip");
          String timeStamp = rs.getString("timestamp");
          out.print("Time: " + timeStamp + " Addr: " + savedIp + "\n");
        }
      }
    } catch (SQLException e) {
      throw new ServletException("SQL error", e);
    }
  }
}
// [END example]
java eclipse google-app-engine google-cloud-sql google-cloud-tools
Original Q&A
1

There are 1 answers

0
Les Vogel On

When developing and testing locally, you have a few choices:

  1. access a local MySQL server
  2. access a Cloud SQL instance

The J7 example is configured to use the Google MySQL driver in the cloud injected by the <use-google-connector-j>true</use-google-connector-j> in appengine-web.xml and to use the standard MySQL driver with the Google SocketFactory when running locally.

To run using your own local MySQL instance, in WEB-INF/appengine-web.xml change the ae-cloudsql.local-database-url property to the connection string for your database, probably something like: jdbc:mysql:localhost:3306/${database}?user=${user}&amp;password=${password}&amp;useSSL=false (assuming you update the properties in your pom.xml or mvn appengine:devserver -Duser=... -Dpassword=... -Ddatabase=... -D INSTANCE_CONNECTION_NAME=projectID:region:instance

The error you are seeing is saying that your connection string is specifying a driver that isn't recognized. If you are sure that all is well, then you might try adding the following (which shouldn't be needed anymore): try { Class.forName("com.mysql.jdbc.Driver") } catch (ClassNotFoundException e) { ... }

I will run the driver example, but it should work.

Related Questions in JAVA

Related Questions in ECLIPSE

Related Questions in GOOGLE-APP-ENGINE

Related Questions in GOOGLE-CLOUD-SQL

Related Questions in GOOGLE-CLOUD-TOOLS

Popular Questions

Popular Tags

javascript python java c# php android html jquery c++ css ios sql mysql r reactjs node.js arrays c asp.net json python-3.x ruby-on-rails .net sql-server swift django angular objective-c pandas excel

Trending Questions