SingleStore Managed Service

ERROR 1706 (HY000): Feature Multi-table UPDATE/DELETE with a reference table as target table is not supported by MemSQL

Issue

Updating a reference table based on a join with a sharded table returns an error. This is because reference tables are only updatable on the master aggregator while joins against sharded tables must run on the leaves, so the query cannot run like normal updates.

Solutions

  • Use the same table types that use both the target as well as joined table as sharded tables or as reference tables.

  • Use UPSERT with a reference table that has a primary key. For example, if t1 is a reference table with a primary key, then the following query runs successfully:

    INSERT INTO t1 SELECT t1.* FROM t1 JOIN t2 ON t1.b = t2.b ON DUPLICATE KEY UPDATE t1.b.
    

    For more details, refer to the UPDATE topic.

Warning

Turning off multi-statement transactions makes per-partition components of a distributed transaction commit independently without waiting for other partitions to finish and potentially trigger rollback. This removes the common row lock deadlock (transaction A waiting for transaction B on partition 1, but B waiting for A on partition 2) at the cost of potentially letting failed queries commit partial results.

If the values inserted are dependent on multiple statements executing concurrently, then inconsistent data may get inserted.

In case of failure, it is not possible to rollback the entire multi-upsert inside one transaction.