Trying a trigger a pipeline whenever a new student getting adding to a college via kafka. This pipeline will invoke a stored procedure which will insert new record in student table and department table based on a validation.
-> Student table contains department Id alone. Department details will be available in department table.
-> If the department unavailable in the table then only insert a new record otherwise assign the existing department id in the students table.
Limitations:
I can’t pre-populate the Department dimension table ahead. It needs to populate during the real time.
Able to insert into department table whenever entry is not available but not able to assign Department Id in the Student table. Facing below error.
Message: Feature ‘Non-partition-local INSERT…SELECT from a table already written to in this transaction’ is not supported by MemSQL Distributed.
MemSQL 6.X has some limitations in our multistmt transactions, which are implicitly used by pipelines (these are addressed in MemSQL 7.0, for what its worth, which will come out later this year!).
You can disable multistmt transactions by setting pipelines_stored_proc_exactly_once to false, which will give you “at least once” semantics. If your SP is also “idempotent”, you’re pipeline will still have exactly once semantics, but those semantics will be enforced by the logic of your SP, not by the underlying transactional system.
Thanks for your reply. I tried by setting up pipelines_stored_proc_exactly_once to false but got the same error.
I followed below steps:
a) Log on to Master Aggregator.
b) Executed “memsql-admin update-config --set-global --key pipelines_stored_proc_exactly_once --value false”.
c) Restarted the PIPELINE.
ERROR:
Non-partition-local INSERT…SELECT from a table already written to in this transaction’ is not supported by MemSQL Distributed
@JoYo I have updated “pipelines_stored_proc_exactly_once” to TRUE but still i am getting the error. is there an alternative way to fix this error ? and Does MemSQL 7.0 beta has a fix for this issue?