CREATE PIPELINE WITH TRANSFORM fails

Hi,

I’m trying to create custom TRANSFORM for Kafka pipeline but without success. We have to provide Java deserialzation before loading into MemSQL. So I’ve created sample JAR file and manually deployed to Leaf node. For test I’ve created bash script which has to run this Java transfrom

CREATE PIPELINE `quickstart_kafka`
AS LOAD DATA KAFKA 'develop-main-kafka-bootstrap.memsql.svc/memsql_events'
WITH TRANSFORM('file://localhost/tmp/pipeline/transform.sh','','')
INTO TABLE `messages`
[HY000][1706] Feature 'User defined transforms when running MemSQL as a service' is not supported by MemSQL.

What I have missed?

What does this query return on your cluster:

select @@local_file_system_access_restricted

I assume is a self-hosted install and not running Helios?

It returns 1
Yes it is self-hosted install via docker.

You will need to disable that in order to use pipelines transforms. Run this against your master aggregator:

SET GLOBAL local_file_system_access_restricted = 0

I’m looking into why the docker image has that enabled by default.

I’ve tried several times to run

SET GLOBAL local_file_system_access_restricted = 0

But

select @@local_file_system_access_restricted

Always return 1

Also i have the same error:

SET GLOBAL local_file_system_access_restricted = 0
[2020-08-10 17:44:02] [HY000][1706] Feature 'User defined transforms when running MemSQL as a service' is not supported by MemSQL.
[2020-08-10 17:44:02] completed in 466 ms

That’s odd. You’re running inside of docker stand alone and not using kubernetes? Something external to the engine must be setting the value of that variable back to 1 on you (the database engine itself won’t do that).

We run our test cluster on OpenShift. Something weird here that setting @@local_file_system_access_restricted produces the same error in output.

It’s the MemSQL kubernetes operator that is forcing this system variable to be set on. I’ve opened a task internally to check if there is a way to disable it.

Hi sdanyliv,

This particular variable will be reset as part of the Operator check on the cluster.

We are discussing making the necessary changes so this restriction can be configured.

Likely we will provide a configuration toggle in the next MemSQL Operator release that can be set to either enable or disable the local file system access restriction.

Stay tuned.

Kind regards,
Cindy

Do we have any ETA to fixed the same in new operator release ? currently we are using 1.2.0

  • Prior to Operator 1.2.0, the supported engine variables are:
  • default_partitions_per_leaf
  • columnstore_segment_rows
  • columnstore_flush_bytes
  • columnstore_window_size
  • transaction_buffer
  • snapshot_trigger_size
  • minimal_disk_space
  • pipelines_max_concurrent
  • auditlog_level
  • As of Operator 1.2.0, almost all engine variables are supported except for:
  • redundancy_level
  • sync_permissions
  • local_file_system_access_restricted

would be great if you can share the temporary fix via operator config, looking forward to hear back from you

Hi aherkarsatish1,

Right now there isn’t a workaround to bypass resetting the restriction.

We are targeting to provide a new Operator release as early as next week.

I will update this thread once we push the next release.

Thank you, Cynn. we shall wait for operator fix

We just published the latest Operator Mainteance release 1.2.1.

2020-08-18 Version 1.2.1

  • Adds support for setting local_file_system_access_restricted global variable.
  • Adds support for setting priorityClassName via an Operator command-line parameter. The priorityClassName will be passed to all StatefulSet’s PodSpec.
  • Re-adds the Operator command-line parameters backup-s3-endpoint and backup-compatibility-mode that were inadvertently removed from the Operator 1.2.0 release. Refer to the Backups reference for more information.

Hey Cynn, I see one more issue. when I applied global variable “local_file_system_access_restricted” to 0 then after 60 second value is again getting replaced with 1 … Not sure is that operator bug or dependencies with other environment variable. can you please help on this ?
update with below command :
memsqlctl update-config --key local_file_system_access_restricted --value 0 --set-global

manual query : SET GLOBAL local_file_system_access_restricted = 0

Hi @aherkarsatish1,

You will need to configure server variable changes in the MemSQL CR.

To configure / set server variables you will need to add the optional section globalVariables to the memsql-cluster.yaml configuration. In the globalVariables section identify the server variable you want to set and the desired value and the Operator will manage the change.

globalVariables:
  local_file_system_access_restricted: "0"

You should avoid making changes to server variables using memsqlctl, there is no guarantee they will persist.

Ref:

Yeah, it’s working now. Thank you!