Skip to content
This repository has been archived by the owner on Aug 2, 2022. It is now read-only.

Opendistro SQL settings not being read from elasticsearch.yml #538

Open
sabflik opened this issue Jun 29, 2020 · 12 comments
Open

Opendistro SQL settings not being read from elasticsearch.yml #538

sabflik opened this issue Jun 29, 2020 · 12 comments
Labels
bug Something isn't working SQL

Comments

@sabflik
Copy link

sabflik commented Jun 29, 2020 •
edited
Loading

When running the opendistro docker image, the opendistro.sql.* settings are not being propagated. Instead, I'm having to resort to creating an init container that curls the settings in once the image has started up.

E.g.
curl -H 'Content-Type: application/json' -X PUT localhost:9200/_opendistro/_sql/settings -d '{"transient" : {"opendistro.sql.cursor.enabled": "true", "opendistro.sql.cursor.keep_alive": "5m"}}'
@dai-chen
Copy link
Member

Hi @sabflik , thanks for reporting the issue! Could you provide more details? Such as the docker image version you used and which SQL plugin setting was unavailable after started up? Thanks!

@dai-chen dai-chen added build Issue and question regarding plugin build question Further information is requested labels Jun 30, 2020
@sabflik
Copy link
Author

sabflik commented Jun 30, 2020

Yes of course!
The image we're using is amazon/opendistro-for-elasticsearch:1.8.0 and the specific settings are

"opendistro.sql.cursor.enabled": "true",
"opendistro.sql.cursor.keep_alive": "5m",
"opendistro.sql.query.analysis.enabled":"false"

Cheers!

@dai-chen
Copy link
Member

dai-chen commented Jun 30, 2020 •
edited
Loading

Thanks @sabflik. I launched the 1.8 docker image and checked SQL setting by curl -s -H 'Content-Type: application/json' -u admin:admin --insecure "https://localhost:9200/_cluster/settings?include_defaults&flat_settings". I didn't see any issue and all settings were just default values. Could you let me know what do you expect to see in the cluster setting when a new docker image started?

..."opendistro.sql.cursor.enabled":"false","opendistro.sql.cursor.fetch_size":"1000","opendistro.sql.cursor.keep_alive":"1m","opendistro.sql.enabled":"true","opendistro.sql.metrics.rollinginterval":"60","opendistro.sql.metrics.rollingwindow":"3600","opendistro.sql.query.analysis.enabled":"true","opendistro.sql.query.analysis.semantic.suggestion":"false","opendistro.sql.query.analysis.semantic.threshold":"200","opendistro.sql.query.response.format":"jdbc","opendistro.sql.query.slowlog":"2"...

@sabflik
Copy link
Author

sabflik commented Jul 1, 2020

Hi @dai-chen so I started up the container with the following elasticsearch config:

elasticsearch.yml

cluster.name: elastic
network.host: 0.0.0.0
opendistro_security.disabled: true
opendistro.sql.cursor.enabled: true
opendistro.sql.cursor.keep_alive: 5m
opendistro.sql.query.analysis.enabled: false

When I run your curl command (http instead of https 😁 ) I see that the properties are set correctly...

curl -s -H 'Content-Type: application/json' -u admin:admin --insecure "http://localhost:9200/_cluster/settings?include_defaults&flat_settings"
{
...
    "opendistro.sql.cursor.enabled": "true",
    "opendistro.sql.cursor.keep_alive": "5m",
    "opendistro.sql.query.analysis.enabled": "false",
...
}

However, the behaviour of the service does not reflect these changes.
E.g. The "opendistro.sql.query.analysis.enabled": "false" setting was used to allow the ability for us to query indices with '.' characters in their names using sql syntax.
I created an index named "simpleparty.person". Querying this resulted in a semantic error:

curl -XPOST 'localhost:9200/_opendistro/_sql' -H 'Content-Type: application/json' -d '{"query": "select * from simpleparty.person"}'
{
  "error": {
    "reason": "Invalid SQL query",
    "details": "Field [simpleparty.person] cannot be found or used here.",
    "type": "SemanticAnalysisException"
  },
  "status": 400
}

I then explicitly curled in the setting

curl -H 'Content-Type: application/json' -X PUT localhost:9200/_cluster/settings -d '{                                                                                                                
  "transient" : {
    "opendistro.sql.query.analysis.enabled" : "false"
  }
}'
{"acknowledged":true,"persistent":{},"transient":{"opendistro":{"sql":{"query":{"analysis":{"enabled":"false"}}}}}}

...But still got the semantic error

HOWEVER, if I first set it to true, and then false, then the behaviour is reflected correctly

curl -H 'Content-Type: application/json' -X PUT localhost:9200/_cluster/settings -d '{                                                                                                                 
  "transient" : {
    "opendistro.sql.query.analysis.enabled" : "true"
  }
}'
{"acknowledged":true,"persistent":{},"transient":{"opendistro":{"sql":{"query":{"analysis":{"enabled":"true"}}}}}}

curl -H 'Content-Type: application/json' -X PUT localhost:9200/_cluster/settings -d '{          
  "transient" : {
    "opendistro.sql.query.analysis.enabled" : "false"
  }
}'
{"acknowledged":true,"persistent":{},"transient":{"opendistro":{"sql":{"query":{"analysis":{"enabled":"false"}}}}}}

curl -XPOST 'localhost:9200/_opendistro/_sql' -H 'Content-Type: application/json' -d '{"query": "select * from simpleparty.person"}' 
{
  "schema": [
    {
      "name": "nationalId",
      "type": "keyword"
...
    }],
  "total": 0,
  "datarows": [],
  "size": 0,
  "status": 200

@dai-chen
Copy link
Member

dai-chen commented Jul 1, 2020

@sabflik Thanks for the details! Will try it out and investigate on my side.

@sabflik
Copy link
Author

sabflik commented Jul 1, 2020

Thank you!

@dai-chen
Copy link
Member

dai-chen commented Jul 1, 2020

I tried to start docker image with the elasticsearch.yml given. In this case, curl ES at 9200 returns nothing. Not sure if I missed anything.

$ cat ~/Temp/elasticsearch.yml
cluster.name: elastic
network.host: 0.0.0.0
opendistro_security.disabled: true
opendistro.sql.cursor.enabled: true
opendistro.sql.cursor.keep_alive: 5m
opendistro.sql.query.analysis.enabled: false

$ docker run -p 9200:9200 -p 9600:9600 -e "discovery.type=single-node" -v ~/Temp/elasticsearch.yml:/usr/share/elasticsearch/config/elasticsearch.yml amazon/opendistro-for-elasticsearch:1.8.0

$ curl -s -H 'Content-Type: application/json' -u admin:admin --insecure "https://localhost:9200"
# return nothing

Without the custom elasticsearch.yml, ES works as normal:

curl -s -H 'Content-Type: application/json' -u admin:admin --insecure "https://localhost:9200"
{
  "name" : "cdc039e4cd9c",
  "cluster_name" : "docker-cluster",
  "cluster_uuid" : "6r3c3skGTLafoewv34JHHA",
  "version" : {
    "number" : "7.7.0",
    "build_flavor" : "oss",
    "build_type" : "tar",
    "build_hash" : "81a1e9eda8e6183f5237786246f6dced26a10eaf",
    "build_date" : "2020-05-12T02:01:37.602180Z",
    "build_snapshot" : false,
    "lucene_version" : "8.5.1",
    "minimum_wire_compatibility_version" : "6.8.0",
    "minimum_index_compatibility_version" : "6.0.0-beta1"
  },
  "tagline" : "You Know, for Search"
}

@sabflik
Copy link
Author

sabflik commented Jul 2, 2020 •
edited
Loading

@dai-chen hey sorry, the settings turns off security, so the endpoint should be http rather than https.

@dai-chen Try this:
curl -s -H 'Content-Type: application/json' "http://localhost:9200"

@dai-chen
Copy link
Member

dai-chen commented Jul 7, 2020

@sabflik Thanks! I missed that. It works for me now. But I'm unable to reproduce the issue. I started docker image first without custom config and check SQL settings. And then I restarted docker image with the custom config you provided. In both cases, the SQL settings were consistent with ES config.

First time without custom ES config:

$ docker run -p 9200:9200 -p 9600:9600 -e "discovery.type=single-node" amazon/opendistro-for-elasticsearch:1.8.0

$ curl -s -H 'Content-Type: application/json' -u admin:admin --insecure "https://localhost:9200/_cluster/settings?include_defaults&flat_settings"
"opendistro.sql.cursor.enabled":"false","opendistro.sql.cursor.fetch_size":"1000","opendistro.sql.cursor.keep_alive":"1m","opendistro.sql.enabled":"true","opendistro.sql.metrics.rollinginterval":"60","opendistro.sql.metrics.rollingwindow":"3600","opendistro.sql.query.analysis.enabled":"true","opendistro.sql.query.analysis.semantic.suggestion":"false","opendistro.sql.query.analysis.semantic.threshold":"200","opendistro.sql.query.response.format":"jdbc","opendistro.sql.query.slowlog":"2"

Second time with custom config:

$ docker run -p 9200:9200 -p 9600:9600 -e "discovery.type=single-node" -v ~/Temp/elasticsearch.yml:/usr/share/elasticsearch/config/elasticsearch.yml amazon/opendistro-for-elasticsearch:1.8.0

$ curl -s -H 'Content-Type: application/json' -u admin:admin --insecure "http://localhost:9200/_cluster/settings?include_defaults&flat_settings"
"opendistro.sql.cursor.enabled":"true","opendistro.sql.cursor.fetch_size":"1000","opendistro.sql.cursor.keep_alive":"5m","opendistro.sql.enabled":"true","opendistro.sql.metrics.rollinginterval":"60","opendistro.sql.metrics.rollingwindow":"3600","opendistro.sql.query.analysis.enabled":"false","opendistro.sql.query.analysis.semantic.suggestion":"false","opendistro.sql.query.analysis.semantic.threshold":"200","opendistro.sql.query.response.format":"jdbc","opendistro.sql.query.slowlog":"2"

@sabflik
Copy link
Author

sabflik commented Jul 8, 2020

@dai-chen the issue doesn't seem to be with the values of the settings themselves being propagated, rather, the settings are misleading.

In order to reproduce the issue, try starting up the service with the custom config I provided. One of the settings I've put in there is to enable the cursor. On startup, the cluster settings will report that this setting is enabled. However, if you actually put data in an index and then do a SQL search, the cursor will not be returned in the response. It appears that although the cluster settings report that the cursor is enabled, the behaviour of the SQL endpoint does not reflect it.

In order to get the cursor to actually be enabled, I've got to execute the following curl commands in the given order:

curl -H 'Content-Type: application/json' -X PUT localhost:9200/_cluster/settings -d '{                                                                                                                 
  "transient" : {
    "opendistro.sql.cursor.enabled":"false"
  }
}'

curl -H 'Content-Type: application/json' -X PUT localhost:9200/_cluster/settings -d '{          
  "transient" : {
    "opendistro.sql.cursor.enabled":"true"
  }
}'

If I now do a search, the cursor is returned.
So far this only seems to be the case for SQL properties.

@dai-chen
Copy link
Member

dai-chen commented Jul 9, 2020

@sabflik Sorry that I missed what you mentioned earlier. I can reproduce the issue now. It seems in this case when Elasticsearch starts, it won't send cluster setting event to our listener in plugin. So we won't read the setting value in yml until the cluster changes triggering an event. I'm still investigating and will make this a bug to fix once confirmed. Thanks!

@sabflik
Copy link
Author

sabflik commented Jul 9, 2020

Awesome, thanks for that!

@dai-chen dai-chen added bug Something isn't working and removed question Further information is requested build Issue and question regarding plugin build labels Jul 10, 2020
@dai-chen dai-chen added the SQL label Sep 11, 2020
Sign up for free to subscribe to this conversation on GitHub. Already have an account? Sign in.
Assignees
No one assigned
Labels
bug Something isn't working SQL
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
2 participants
@sabflik @dai-chen