Error: Multi-Table UPDATE/DELETE Is Not Supported by SingeStore

Issue

Integrate jobs fail while attempting multi-table UPDATE and DELETE operations in SingleStore. The following error appears:

Feature 'Multi-table UPDATE/DELETE with a reference table as target table' is not supported by SingleStore

Environment

• HVR 6
• Target: SingleStore
• Integrate method: BURST

Resolution

To resolve this issue, use one of the following options.

Option 1: Isolate the affected table groups

1. Move tables that require multi-table operations to a dedicated table group.
2. Duplicate the existing target location.
3. Add the duplicated target location to the channel and assign it a distinct target group.
4. Define a new Integrate action that uses Continuous mode and applies to the new table and target groups.
5. Activate the channel to apply the changes.
6. Run the following command to exclude the affected reference tables from the original target location:

   hvrcontrol -i -l <old_target_location> -s -t<table_1> -t<table_2> <hubname> <channel>
   

7. Refresh the reference tables to the new target location.

Option 2: Change the storage type

Change the affected tables' storage type from Reference to Sharded and configure a suitable shard key, such as workflowid.

Cause

This issue occurs because SingleStore doesn't support multi-table UPDATE or DELETE operations that target a reference table.