Adding new Aurora MySql DB is throwing error

When trying to add new Aurora MySql datasource it says “Retrieving details for larger sources may take a few minutes…” for couple of minutes and after that it is throwing error as “Failure while configuring source…”.
Attached the images.

Hi @userh009,

What hostname is your Amazon Aurora cluster?

The MariaDB JDBC connector that Dremio uses tries to parse “amazonaws.com” out of the hostname to detect and tailor the connection to Amazon Aurora (discussed during this thread: 2+ hours loading mysql/Aurora source).

Hi @jduong

Yes. the hostname does have “rds.amazonaws.com” in the end. still I am getting this error. What might be the issue?

Let’s see if the logs can reveal any information.

You can edit logback.xml in the conf directory.
Find this section:

And edit -info to be -debug. Afterwards, make sure to restart all nodes.

@jduong
After replacing with -debug here is the exception I see in one of the log file server.json

{

“timestamp”: “2018-06-11 20:47:13,328”,
“host”: “ip-172-30-1-116”,
“thread”: “qtp1823472420-198”,
“levelName”: “INFO”,
“levelValue”: 20000,
“logger”: “com.dremio.exec.catalog.CatalogServiceImpl”,
“message”: “User Error Occurred [ErrorId: 7f352878-5af5-4d9c-a4f1-01a1e59613a9]”,
“stack_trace”: “com.dremio.common.exceptions.UserException: Failure while configuring source [Miscellaneous DB]\n\tat com.dremio.common.exceptions.UserException$Builder.build(UserException.java:746) ~[dremio-common-2.0.1-201804132205050000-10b1de0.jar:2.0.1-201804132205050000-10b1de0]\n\tat com.dremio.exec.catalog.CatalogServiceImpl.createOrUpdateSource(CatalogServiceImpl.java:629) [dremio-sabot-kernel-2.0.1-201804132205050000-10b1de0.jar:2.0.1-201804132205050000-10b1de0]\n\tat com.dremio.exec.catalog.CatalogServiceImpl.createSource(CatalogServiceImpl.java:376) [dremio-sabot-kernel-2.0.1-201804132205050000-10b1de0.jar:2.0.1-201804132205050000-10b1de0]\n\tat com.dremio.exec.catalog.CatalogServiceImpl.access$600(CatalogServiceImpl.java:100) [dremio-sabot-kernel-2.0.1-201804132205050000-10b1de0.jar:2.0.1-201804132205050000-10b1de0]\n\tat com.dremio.exec.catalog.CatalogServiceImpl$SourceModifier.createSource(CatalogServiceImpl.java:946) [dremio-sabot-kernel-2.0.1-201804132205050000-10b1de0.jar:2.0.1-201804132205050000-10b1de0]\n\tat com.dremio.exec.catalog.CatalogImpl.createSource(CatalogImpl.java:529) [dremio-sabot-kernel-2.0.1-201804132205050000-10b1de0.jar:2.0.1-201804132205050000-10b1de0]\n\tat com.dremio.exec.catalog.DelegatingCatalog.createSource(DelegatingCatalog.java:182) [dremio-sabot-kernel-2.0.1-201804132205050000-10b1de0.jar:2.0.1-201804132205050000-10b1de0]\n\tat com.dremio.dac.service.source.SourceService.registerSourceWithRuntime(SourceService.java:149) [dremio-dac-backend-2.0.1-201804132205050000-10b1de0.jar:2.0.1-201804132205050000-10b1de0]\n\tat com.dremio.dac.service.source.SourceService.registerSourceWithRuntime(SourceService.java:140) [dremio-dac-backend-2.0.1-201804132205050000-10b1de0.jar:2.0.1-201804132205050000-10b1de0]\n\tat com.dremio.dac.service.source.SourceService.registerSourceWithRuntime(SourceService.java:136) [dremio-dac-backend-2.0.1-201804132205050000-10b1de0.jar:2.0.1-201804132205050000-10b1de0]\n\tat com.dremio.dac.resource.PutSourceResource.putSource(PutSourceResource.java:80) [dremio-dac-backend-2.0.1-201804132205050000-10b1de0.jar:2.0.1-201804132205050000-10b1de0]\n\tat sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_171]\n\tat sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_171]\n\tat sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_171]\n\tat java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_171]\n\tat org.glassfish.jersey.server.model.internal.ResourceMethodInvocationHandlerFactory$1.invoke(ResourceMethodInvocationHandlerFactory.java:81) [jersey-server-2.25.1.jar:na]\n\tat org.glassfish.jersey.server.model.internal.AbstractJavaResourceMethodDispatcher$1.run(AbstractJavaResourceMethodDispatcher.java:144) [jersey-server-2.25.1.jar:na]\n\tat org.glassfish.jersey.server.model.internal.AbstractJavaResourceMethodDispatcher.invoke(AbstractJavaResourceMethodDispatcher.java:161) [jersey-server-2.25.1.jar:na]\n\tat org.glassfish.jersey.server.model.internal.JavaResourceMethodDispatcherProvider$TypeOutInvoker.doDispatch(JavaResourceMethodDispatcherProvider.java:205) [jersey-server-2.25.1.jar:na]\n\tat org.glassfish.jersey.server.model.internal.AbstractJavaResourceMethodDispatcher.dispatch(AbstractJavaResourceMethodDispatcher.java:99) [jersey-server-2.25.1.jar:na]\n\tat org.glassfish.jersey.server.model.ResourceMethodInvoker.invoke(ResourceMethodInvoker.java:389) [jersey-server-2.25.1.jar:na]\n\tat org.glassfish.jersey.server.model.ResourceMethodInvoker.apply(ResourceMethodInvoker.java:347) [jersey-server-2.25.1.jar:na]\n\tat org.glassfish.jersey.server.model.ResourceMethodInvoker.apply(ResourceMethodInvoker.java:102) [jersey-server-2.25.1.jar:na]\n\tat org.glassfish.jersey.server.ServerRuntime$2.run(ServerRuntime.java:326) [jersey-server-2.25.1.jar:na]\n\tat org.glassfish.jersey.internal.Errors$1.call(Errors.java:271) [jersey-common-2.25.1.jar:na]\n\tat org.glassfish.jersey.internal.Errors$1.call(Errors.java:267) [jersey-common-2.25.1.jar:na]\n\tat org.glassfish.jersey.internal.Errors.process(Errors.java:315) [jersey-common-2.25.1.jar:na]\n\tat org.glassfish.jersey.internal.Errors.process(Errors.java:297) [jersey-common-2.25.1.jar:na]\n\tat org.glassfish.jersey.internal.Errors.process(Errors.java:267) [jersey-common-2.25.1.jar:na]\n\tat org.glassfish.jersey.process.internal.RequestScope.runInScope(RequestScope.java:317) [jersey-common-2.25.1.jar:na]\n\tat org.glassfish.jersey.server.ServerRuntime.process(ServerRuntime.java:305) [jersey-server-2.25.1.jar:na]\n\tat org.glassfish.jersey.server.ApplicationHandler.handle(ApplicationHandler.java:1154) [jersey-server-2.25.1.jar:na]\n\tat org.glassfish.jersey.servlet.WebComponent.serviceImpl(WebComponent.java:473) [jersey-container-servlet-core-2.25.1.jar:na]\n\tat org.glassfish.jersey.servlet.WebComponent.service(WebComponent.java:427) [jersey-container-servlet-core-2.25.1.jar:na]\n\tat org.glassfish.jersey.servlet.ServletContainer.service(ServletContainer.java:388) [jersey-container-servlet-core-2.25.1.jar:na]\n\tat org.glassfish.jersey.servlet.ServletContainer.service(ServletContainer.java:341) [jersey-container-servlet-core-2.25.1.jar:na]\n\tat org.glassfish.jersey.servlet.ServletContainer.service(ServletContainer.java:228) [jersey-container-servlet-core-2.25.1.jar:na]\n\tat org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:812) [jetty-servlet-9.2.22.v20170606.jar:9.2.22.v20170606]\n\tat org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1669) [jetty-servlet-9.2.22.v20170606.jar:9.2.22.v20170606]\n\tat org.eclipse.jetty.servlets.UserAgentFilter.doFilter(UserAgentFilter.java:83) [jetty-servlets-9.2.22.v20170606.jar:9.2.22.v20170606]\n\tat org.eclipse.jetty.servlets.GzipFilter.doFilter(GzipFilter.java:301) [jetty-servlets-9.2.22.v20170606.jar:9.2.22.v20170606]\n\tat org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1652) [jetty-servlet-9.2.22.v20170606.jar:9.2.22.v20170606]\n\tat org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:585) [jetty-servlet-9.2.22.v20170606.jar:9.2.22.v20170606]\n\tat org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1127) [jetty-server-9.2.22.v20170606.jar:9.2.22.v20170606]\n\tat org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:515) [jetty-servlet-9.2.22.v20170606.jar:9.2.22.v20170606]\n\tat org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1061) [jetty-server-9.2.22.v20170606.jar:9.2.22.v20170606]\n\tat org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:141) [jetty-server-9.2.22.v20170606.jar:9.2.22.v20170606]\n\tat org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:97) [jetty-server-9.2.22.v20170606.jar:9.2.22.v20170606]\n\tat org.eclipse.jetty.server.handler.RequestLogHandler.handle(RequestLogHandler.java:95) [jetty-server-9.2.22.v20170606.jar:9.2.22.v20170606]\n\tat org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:97) [jetty-server-9.2.22.v20170606.jar:9.2.22.v20170606]\n\tat org.eclipse.jetty.server.Server.handle(Server.java:499) [jetty-server-9.2.22.v20170606.jar:9.2.22.v20170606]\n\tat org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:311) [jetty-server-9.2.22.v20170606.jar:9.2.22.v20170606]\n\tat org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:258) [jetty-server-9.2.22.v20170606.jar:9.2.22.v20170606]\n\tat org.eclipse.jetty.io.AbstractConnection$2.run(AbstractConnection.java:544) [jetty-io-9.2.22.v20170606.jar:9.2.22.v20170606]\n\tat org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:635) [jetty-util-9.2.22.v20170606.jar:9.2.22.v20170606]\n\tat org.eclipse.jetty.util.thread.QueuedThreadPool$3.run(QueuedThreadPool.java:555) [jetty-util-9.2.22.v20170606.jar:9.2.22.v20170606]\n\tat java.lang.Thread.run(Thread.java:748) [na:1.8.0_171]\nCaused by: java.util.concurrent.TimeoutException: Timeout waiting for task.\n\tat com.google.common.util.concurrent.AbstractFuture$Sync.get(AbstractFuture.java:269) ~[guava-18.0.jar:na]\n\tat com.google.common.util.concurrent.AbstractFuture.get(AbstractFuture.java:96) ~[guava-18.0.jar:na]\n\tat com.google.common.util.concurrent.ForwardingFuture.get(ForwardingFuture.java:69) ~[guava-18.0.jar:na]\n\tat com.google.common.util.concurrent.AbstractCheckedFuture.checkedGet(AbstractCheckedFuture.java:107) ~[guava-18.0.jar:na]\n\tat com.dremio.exec.catalog.CatalogServiceImpl.createOrUpdateSource(CatalogServiceImpl.java:617) [dremio-sabot-kernel-2.0.1-201804132205050000-10b1de0.jar:2.0.1-201804132205050000-10b1de0]\n\t… 55 common frames omitted\n”
}

@jduong After replacing with -debug I see exceptions in server.json file. Attached the file.server.json.zip (14.2 KB)

It seems to be timing out.

Are you able to connect to this Aurora instance using other JDBC tools on the same cluster?

@jduong Yes. I am able to connect to this aurora mysql db using MySqlWorkbench successfully.

Hi @userh009,

I don’t think MySQL Workbench uses JDBC. Would you be able to try another tool like Oracle SQL Developer, or DBVisualizer. Trying both the MariaDB JDBC driver and the MySQL JDBC driver.

I’m trying to identify if this is a problem with the JDBC driver or Dremio’s particular usage of it.

@jduong

I was able to connect using DBVisualizer using both MySql and MariaDB JDBC driver successfully. attached the sanpshot using MariaDB JDBC driver.

Do you have a large number of tables and columns in your instance? Perhaps the problem is the metadata retrieval itself is taking so long that it times out.

@jduong Yes. We do have more table and more data. Will that effect adding data source to Dremio? How can we handle this scenario?

Looks like the default timeout for building the list of tables is 2 minutes.

Let’s try upping it to 2 hours.
In Dremio, click Admin and go to Advanced Settings.
At the line Dremio Support, add the following support key: store.start.wait_millis

You can then enter the timeout duration (in milliseconds). So 2 hours would be 7200000 milliseconds.

@jduong even after adding this I see the same issue. Not able to connect successfully. Are there any other options to try?

The JDBC URL that Dremio generates is of this simple format:
jdbc:mariadb://[hostname]:[port]?useJDBCCompliantTimezoneShift=true

We can try using this URL in Oracle SQL Developer.

It enters the hostname and port parameters as-is into this URL. Are there any unusual characters that might need escaping in the hostname?

@userh009

Kindly spin up an EC2 instance for Dremio on the same VPC as the MySQL one. Then try and add the MySQL source to Dremio and keep us updated

Thanks
@balaji.ramaswamy