Clever Geek Handbook

Error connecting to sql google cloud from localhost (Java eclipse)

I am trying to connect to a Google Cloud SQL database from a servlet hosted on my local AppEngine server. I can connect when the project is deployed to google, but not from the localhost appengine server. I am getting the following 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]
+3


source to share


1 answer


When developing and testing locally, you have several options:

  • access to local MySQL server
  • access to a Cloud SQL instance

The J7 example is configured to use the Google MySQL driver in the cloud injected <use-google-connector-j>true</use-google-connector-j>

in appengine-web.xml and use the standard MySQL driver with Google SocketFactory when run locally.



To run using your own local MySQL instance in, WEB-INF/appengine-web.xml

change the property ae-cloudsql.local-database-url

to the connection string for your database, perhaps something like: jdbc:mysql:localhost:3306/${database}?user=${user}&amp;password=${password}&amp;useSSL=false

(assuming you update the properties in pom.xml

ormvn appengine:devserver -Duser=... -Dpassword=... -Ddatabase=... -D INSTANCE_CONNECTION_NAME=projectID:region:instance

The error you are seeing says that your connection string indicates a driver that is not recognized. If you are sure everything is ok, then you can try adding the following (which is no longer needed): try { Class.forName("com.mysql.jdbc.Driver") } catch (ClassNotFoundException e) { ... }

I ran the sample driver, but it should work.

+2


source






More articles:


All Articles