U0400021: GaussDB does not support INSERT ON DUPLICATE KEY UPDATE for tables with GSIs

Description

Database Type and Version

• Source database type and version: MySQL 5.5, 5.6, 5.7, and 8.0
• Target database type and version: GaussDB Distributed

Syntax Example

This error is reported when UGO detects that the distribution key of a distributed GaussDB database table is not a superset of primary and unique keys.

In a GaussDB database, INSERT ON DUPLICATE KEY UPDATE cannot be used in tables with global secondary indexes (GSIs). In a distributed GaussDB database, if the specified distribution key is not a superset of primary and unique keys, the database automatically creates GSIs. The following example is not supported in a distributed GaussDB database:

CREATE TABLE demo (
    id INT PRIMARY KEY,
    info VARCHAR(10),
    email VARCHAR(10),
    UNIQUE (id, info)
) DISTRIBUTE BY HASH(email);
INSERT INTO demo VALUES (10, 'AA', 'BB'), (4, 'EE', 'EE') ON DUPLICATE KEY UPDATE email = VALUES(email);

Suggestion

For tables with GSIs, you are advised to check whether customer's service data conflicts occur and process the data conflicts instead of using INSERT ON DUPLICATE KEY UPDATE.