maple525866 opened a new issue, #7877: URL: https://github.com/apache/incubator-seata/issues/7877
### Check Ahead - [x] I have searched the [issues](https://github.com/seata/seata/issues) of this repository and believe that this is not a duplicate. - [ ] I am willing to try to fix this bug myself. ### Ⅰ. Issue Description The primary key design of `vgroup_table` in the server/db SQL file is inconsistent. In the `mysql.sql` file: ```sql CREATE TABLE IF NOT EXISTS `vgroup_table` ( `vGroup` VARCHAR(255), `namespace` VARCHAR(255), `cluster` VARCHAR(255), UNIQUE KEY `idx_vgroup_namespace_cluster` (`vGroup`,`namespace`,`cluster`) ) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4; ``` Using a unique index with three columns (vGroup, namespace, cluster). However, in other SQL files, such as `postgresql.sql`: ```sql CREATE TABLE IF NOT EXISTS vgroup_table ( vGroup VARCHAR(255), namespace VARCHAR(255), cluster VARCHAR(255), PRIMARY KEY (vGroup) ); ``` Using only vGroup. Using a single column as the primary key In summary, the MySQL setting should be correct, but the different primary key settings in the SQL files of other databases will, in my opinion, lead to inconsistent business logic behavior across different databases. In a multi-cluster/multi-namespace environment, only one record can exist for the same vGroup name, which does not support multiple environments. For example: ```sql -- 1st record: Insertion successful INSERT INTO vgroup_table VALUES ('order-service-group', 'dev', 'cluster-beijing'); -- Success -- 2nd record: Insertion failed! INSERT INTO vgroup_table VALUES ('order-service-group', 'prod', 'cluster-beijing'); -- Failure: Primary key constraint violation (vGroup already exists) -- 3rd record: Insertion failed! INSERT INTO vgroup_table VALUES ('order-service-group', 'dev', 'cluster-shanghai'); -- Failed: Primary key constraint violation (vGroup already exists) ``` ### Ⅱ. Describe what happened _No response_ ### Ⅲ. Describe what you expected to happen All databases should use a three-column unique constraint. ### Ⅳ. How to reproduce it (as minimally and precisely as possible) _No response_ ### Ⅴ. Anything else we need to know? _No response_ ### Ⅵ. Environment _No response_ -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected] --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
