Elasticsearch: SQL JDBC and CLI fails when ES hosted under path

Created on 22 Jul 2019 · 9Comments · Source: elastic/elasticsearch

Describe the feature:

Elasticsearch version : v6.8.0

Plugins installed: [mapper_size]

JVM version : openjdk version "1.8.0_202"
OpenJDK Runtime Environment (AdoptOpenJDK)(build 1.8.0_202-b08)
OpenJDK 64-Bit Server VM (AdoptOpenJDK)(build 25.202-b08, mixed mode)

OS version : SQL client on Windows 10

Description of the problem including expected versus actual behavior:

The SQL JDBC documentation states the following URL syntax is valid.

jdbc:es://[[http|https]://]*[host[:port]]*/[prefix]*<[?[option=value]&]*

With "Prefix (empty by default). Typically used when hosting Elasticsearch under a certain path. Optional."

When ES is hosted under a different path, the JDBC driver ignores the path and attempts to connect to the / root with a GET /?error_trace.

Steps to reproduce:

ES is running happily behind Nginx, on the path "es_rest" (reverse proxied to localhost:9200). When I configure the JDBC path jdbc:es://https://myhost.example.com:443/es_rest/, the driver ignores /es_rest/ and attempts to connect to jdbc:es://https://myhost.example.com:443/.

:Query LanguageSQL >bug

Source

zofog

All 9 comments

Pinging @elastic/es-search

elasticmachine on 23 Jul 2019

@zofog do you get an error? If so, can you share this along with any stacktraces (if any) in logs?

astefan on 23 Jul 2019

@astefan I get the following error, I'm assuming because the content being returned from the / root site is not what's expected.

Unexpected driver error occurred while connecting to database. Unsupported Content-Type: text/html; charset=utf-8

The error from SQL CLI is:
Exception in thread "main" java.lang.IllegalStateException: Unsupported Content-Type: text/html; charset=utf-8 at org.elasticsearch.xpack.sql.client.HttpClient.readFrom(HttpClient.java:159) at org.elasticsearch.xpack.sql.client.JreHttpUrlConnection.request(JreHttpUrlConnection.java:155) at org.elasticsearch.xpack.sql.client.HttpClient.lambda$get$4(HttpClient.java:129) at org.elasticsearch.xpack.sql.client.JreHttpUrlConnection.http(JreHttpUrlConnection.java:65) at org.elasticsearch.xpack.sql.client.HttpClient.lambda$get$5(HttpClient.java:128) at java.security.AccessController.doPrivileged(Native Method) at org.elasticsearch.xpack.sql.client.HttpClient.get(HttpClient.java:127) at org.elasticsearch.xpack.sql.client.HttpClient.serverInfo(HttpClient.java:60) at org.elasticsearch.xpack.sql.cli.command.CliSession.checkConnection(CliSession.java:63) at org.elasticsearch.xpack.sql.cli.Cli.checkConnection(Cli.java:138) at org.elasticsearch.xpack.sql.cli.Cli.execute(Cli.java:128) at org.elasticsearch.xpack.sql.cli.Cli.execute(Cli.java:110) at org.elasticsearch.cli.Command.mainWithoutErrorHandling(Command.java:124) at org.elasticsearch.cli.Command.main(Command.java:90) at org.elasticsearch.xpack.sql.cli.Cli.main(Cli.java:58)

zofog on 23 Jul 2019

@zofog what was the command line you used to start the CLI?

astefan on 23 Jul 2019

@astefan, just want to be clear that my environment is understood so you're not chasing ghosts.

Elasticsearch (coordinating node) and Kibana run on the same server. Everything is reverse proxied with Nginx. This configuration works perfectly for our needs and has been in production for over a year. No issues with users getting to Kibana and clients connecting to the REST endpoint.

https://myhost.example.com -> Kibana (localhost:5601)
https://myhost.example.com/rest/ -> ES (localhost:9200)

When I configure the JDBC driver as jdbc:es://https://myhost.example.com:443/rest/, I can see in my Nginx logs a GET call to https://myhost.example.com/?error_trace, which is where Kibana is responding from.

I'm using DBeaver 6.1.2 to test the JDBC provider. When configured as above, I get the error :

2019-07-23 08:13:37.325 - Connect with 'jdbc:es://https://myhost.example.com:443/rest/' (elastic_search_jdbc-16c1f652212-154f8454d38c3e5b) 2019-07-23 08:13:38.230 - Connection failed (elastic_search_jdbc-16c1f652212-154f8454d38c3e5b) 2019-07-23 08:13:38.235 - org.jkiss.dbeaver.model.exec.DBCConnectException: Unexpected driver error occurred while connecting to database org.jkiss.dbeaver.model.exec.DBCConnectException: Unexpected driver error occurred while connecting to database at org.jkiss.dbeaver.model.impl.jdbc.JDBCDataSource.openConnection(JDBCDataSource.java:185) at org.jkiss.dbeaver.ext.generic.model.GenericDataSource.openConnection(GenericDataSource.java:124) at org.jkiss.dbeaver.model.impl.jdbc.JDBCExecutionContext.connect(JDBCExecutionContext.java:89) at org.jkiss.dbeaver.model.impl.jdbc.JDBCRemoteInstance.initializeMainContext(JDBCRemoteInstance.java:88) at org.jkiss.dbeaver.model.impl.jdbc.JDBCRemoteInstance.<init>(JDBCRemoteInstance.java:54) at org.jkiss.dbeaver.model.impl.jdbc.JDBCDataSource.initializeRemoteInstance(JDBCDataSource.java:105) at org.jkiss.dbeaver.ext.generic.model.GenericDataSource.<init>(GenericDataSource.java:106) at org.jkiss.dbeaver.ext.generic.model.meta.GenericMetaModel.createDataSourceImpl(GenericMetaModel.java:69) at org.jkiss.dbeaver.ext.generic.GenericDataSourceProvider.openDataSource(GenericDataSourceProvider.java:95) at org.jkiss.dbeaver.registry.DataSourceDescriptor.connect(DataSourceDescriptor.java:778) at org.jkiss.dbeaver.runtime.jobs.ConnectJob.run(ConnectJob.java:70) at org.jkiss.dbeaver.ui.dialogs.connection.ConnectionWizard$ConnectionTester.run(ConnectionWizard.java:237) at org.jkiss.dbeaver.model.runtime.AbstractJob.run(AbstractJob.java:102) at org.eclipse.core.internal.jobs.Worker.run(Worker.java:63) Caused by: java.lang.IllegalStateException: Unsupported Content-Type: text/html; charset=utf-8 at org.elasticsearch.xpack.sql.client.HttpClient.readFrom(HttpClient.java:159) at org.elasticsearch.xpack.sql.client.JreHttpUrlConnection.request(JreHttpUrlConnection.java:155) at org.elasticsearch.xpack.sql.client.HttpClient.lambda$get$4(HttpClient.java:129) at org.elasticsearch.xpack.sql.client.JreHttpUrlConnection.http(JreHttpUrlConnection.java:65) at org.elasticsearch.xpack.sql.client.HttpClient.lambda$get$5(HttpClient.java:128) at java.security.AccessController.doPrivileged(Native Method) at org.elasticsearch.xpack.sql.client.HttpClient.get(HttpClient.java:127) at org.elasticsearch.xpack.sql.client.HttpClient.serverInfo(HttpClient.java:60) at org.elasticsearch.xpack.sql.jdbc.JdbcHttpClient.fetchServerInfo(JdbcHttpClient.java:93) at org.elasticsearch.xpack.sql.jdbc.JdbcHttpClient.<init>(JdbcHttpClient.java:47) at org.elasticsearch.xpack.sql.jdbc.JdbcConnection.<init>(JdbcConnection.java:53) at org.elasticsearch.xpack.sql.jdbc.JdbcConnection.<init>(JdbcConnection.java:48) at org.elasticsearch.xpack.sql.jdbc.EsDriver.connect(EsDriver.java:76) at org.jkiss.dbeaver.model.impl.jdbc.JDBCDataSource.lambda$0(JDBCDataSource.java:148) at org.jkiss.dbeaver.model.impl.jdbc.JDBCDataSource.openConnection(JDBCDataSource.java:157) ... 13 more

When I try the same URL with the SQL CLI: elasticsearch-sql-cli.bat https://myuser:[email protected]:443/rest/. I get the error:

Exception in thread "main" java.lang.IllegalStateException: Unsupported Content-Type: text/html; charset=utf-8 at org.elasticsearch.xpack.sql.client.HttpClient.readFrom(HttpClient.java:159) at org.elasticsearch.xpack.sql.client.JreHttpUrlConnection.request(JreHttpUrlConnection.java:155) at org.elasticsearch.xpack.sql.client.HttpClient.lambda$get$4(HttpClient.java:129) at org.elasticsearch.xpack.sql.client.JreHttpUrlConnection.http(JreHttpUrlConnection.java:65) at org.elasticsearch.xpack.sql.client.HttpClient.lambda$get$5(HttpClient.java:128) at java.security.AccessController.doPrivileged(Native Method) at org.elasticsearch.xpack.sql.client.HttpClient.get(HttpClient.java:127) at org.elasticsearch.xpack.sql.client.HttpClient.serverInfo(HttpClient.java:60) at org.elasticsearch.xpack.sql.cli.command.CliSession.checkConnection(CliSession.java:63) at org.elasticsearch.xpack.sql.cli.Cli.checkConnection(Cli.java:138) at org.elasticsearch.xpack.sql.cli.Cli.execute(Cli.java:128) at org.elasticsearch.xpack.sql.cli.Cli.execute(Cli.java:110) at org.elasticsearch.cli.Command.mainWithoutErrorHandling(Command.java:124) at org.elasticsearch.cli.Command.main(Command.java:90) at org.elasticsearch.xpack.sql.cli.Cli.main(Cli.java:58)

Seems to me, somewhere in the sql client code the /rest/ path is being removed from the URL.

zofog on 23 Jul 2019

Thank you for the detailed additional info. It's a bug, indeed. Managed to reproduce it.

astefan on 23 Jul 2019

👍1

master (8.0.0): https://github.com/elastic/elasticsearch/commit/06dea859e8fddada868941aaae15e83b4f64babe
7.x (7.4.0): https://github.com/elastic/elasticsearch/commit/fd74b63602270e2117392543517bb635dfb3f22a
7.3 (7.3.1): https://github.com/elastic/elasticsearch/commit/9d4f78ff9e5808f884e7eb17b4fd3256707832c0
7.2 (7.2.2): https://github.com/elastic/elasticsearch/commit/64bc3a060d43948d7e6ebc4f04fa8cb046e5036d
6.8 (6.8.3): https://github.com/elastic/elasticsearch/commit/3a7f34f12bcee101c936deb12753231939ce0d09

astefan on 25 Jul 2019

@astefan FYI the release note process relies on having versions on pull requests only, so I removed them from this issue.