I am attempting to use the h2o package in R to query a MySQL database and store the results directly in an h2o instance I have running on my local machine.
Provided below is all of my code and errors received, I would very much appreciate a new set of eyes to help me troubleshoot - thanks!
Via Windows Command Prompt:
java -cp "C:\Program Files\h2o-3.8.2.6\h2o.jar;C:\Program Files\MySQL\mysql-connector-java-5.1.39\mysql-connector-java-5.1.39-bin.jar" water.H2OApp
Which returns:
05-31 12:20:41.601 127.0.0.1:54321 5280 main INFO: HDFS subsystem successfully initialized
05-31 12:20:41.602 127.0.0.1:54321 5280 main INFO: S3 subsystem successfully initialized
05-31 12:20:41.604 127.0.0.1:54321 5280 main INFO: Flow dir: 'C:/Users/ekorne201/h2oflows'
05-31 12:20:41.925 127.0.0.1:54321 5280 main INFO: Cloud of size 1 formed [/127.0.0.1:54321]
05-31 12:20:41.931 127.0.0.1:54321 5280 main INFO: Registered 0 extensions in: 531mS
05-31 12:20:42.525 127.0.0.1:54321 5280 main INFO: Registered: 124 REST APIs in: 553mS
05-31 12:20:42.995 127.0.0.1:54321 5280 main INFO: Registered: 193 schemas in: 468mS
05-31 12:20:42.995 127.0.0.1:54321 5280 main INFO:
05-31 12:20:42.996 127.0.0.1:54321 5280 main INFO: Open H2O Flow in your web browser: http://127.0.0.1:54321/
05-31 12:20:42.998 127.0.0.1:54321 5280 main INFO:
Then, via R I run the following (db/server details masked):
#packages
require(h2o)
ip <- '127.0.0.1'
port <- 54321
h2o.init(ip = ip, port = port)
#db details
dbip <- 'XX.XX.XX.XX'
dbport <- 'XXXX'
dblogin <- 'login'
dbpass <- 'password'
#test connection
test <- h2o.import_sql_select(
connection_url = paste("jdbc:mysql://", dbip, ":", dbport, "/myDB?useSSL=false", sep = ''),
select_query = 'select * from myTable limit 1000',
username = dblogin,
password = dbpass)
which returns the following errors:
ERROR: Unexpected HTTP Status code: 500 Server Error (url = http://127.0.0.1:54321/99/ImportSQLTable)
java.lang.RuntimeException
[1] "java.lang.RuntimeException: SQLException: Communications link failure\n\nThe last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.\nFailed to connect and read from SQL database with connection_url: jdbc:mysql://XX.XX.XX.XX:XXXX/assumption_matrix?useSSL=false"
[2] " water.jdbc.SQLManager.importSqlTable(SQLManager.java:135)"
[3] " water.api.ImportSQLTableHandler.importSQLTable(ImportSQLTableHandler.java:15)"
[4] " sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)"
[5] " sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)"
[6] " sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)"
[7] " java.lang.reflect.Method.invoke(Unknown Source)"
[8] " water.api.Handler.handle(Handler.java:62)"
[9] " water.api.RequestServer.handle(RequestServer.java:655)"
[10] " water.api.RequestServer.serve(RequestServer.java:596)"
[11] " water.JettyHTTPD$H2oDefaultServlet.doGeneric(JettyHTTPD.java:745)"
[12] " water.JettyHTTPD$H2oDefaultServlet.doPost(JettyHTTPD.java:681)"
[13] " javax.servlet.http.HttpServlet.service(HttpServlet.java:755)"
[14] " javax.servlet.http.HttpServlet.service(HttpServlet.java:848)"
[15] " org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:684)"
[16] " org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:503)"
[17] " org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:137)"
[18] " org.eclipse.jetty.security.SecurityHandler.handle(SecurityHandler.java:557)"
[19] " org.eclipse.jetty.server.session.SessionHandler.doHandle(SessionHandler.java:231)"
[20] " org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1086)"
[21] " org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:429)"
[22] " org.eclipse.jetty.server.session.SessionHandler.doScope(SessionHandler.java:193)"
[23] " org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1020)"
[24] " org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:135)"
[25] " org.eclipse.jetty.server.handler.HandlerCollection.handle(HandlerCollection.java:154)"
[26] " org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:116)"
[27] " org.eclipse.jetty.server.Server.handle(Server.java:370)"
[28] " org.eclipse.jetty.server.AbstractHttpConnection.handleRequest(AbstractHttpConnection.java:494)"
[29] " org.eclipse.jetty.server.BlockingHttpConnection.handleRequest(BlockingHttpConnection.java:53)"
[30] " org.eclipse.jetty.server.AbstractHttpConnection.content(AbstractHttpConnection.java:982)"
[31] " org.eclipse.jetty.server.AbstractHttpConnection$RequestHandler.content(AbstractHttpConnection.java:1043)"
[32] " org.eclipse.jetty.http.HttpParser.parseNext(HttpParser.java:865)"
[33] " org.eclipse.jetty.http.HttpParser.parseAvailable(HttpParser.java:240)"
[34] " org.eclipse.jetty.server.BlockingHttpConnection.handle(BlockingHttpConnection.java:72)"
[35] " org.eclipse.jetty.server.bio.SocketConnector$ConnectorEndPoint.run(SocketConnector.java:264)"
[36] " org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:608)"
[37] " org.eclipse.jetty.util.thread.QueuedThreadPool$3.run(QueuedThreadPool.java:543)"
[38] " java.lang.Thread.run(Unknown Source)"
Error in .h2o.doSafeREST(h2oRestApiVersion = h2oRestApiVersion, urlSuffix = page, :
ERROR MESSAGE:
SQLException: Communications link failure
The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.
Failed to connect and read from SQL database with connection_url: jdbc:mysql://XX.XX.XX.XX:XXXX/myDB?useSSL=false
I'm stumped and would appreciate any help. I am able to connect to this server via ODBC without trouble. Thanks!
