apache / apache/incubator-seata

A bug that causes SQL execution to fail in Oracle DB due to the cluster column in the vgroup_table table.

Open
#7,869 4 comments 0 reactions 0 assignees View on GitHub
type: bug
Dominant language
Java
Stars
26k
Forks
8.8k
Avg merge
1d 8h
Merged PRs (30d)
4

Description

### 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

In oracle db, cluster is identified as a reserved word.

```
// script/server/db/oracle.sql
CREATE TABLE vgroup_table
(
vGroup VARCHAR2(255) PRIMARY KEY,
namespace VARCHAR2(255),
cluster VARCHAR2(255)
);
```

So when I run the oracle.sql script in the oracle environment, it fails.

```java
// server/src/main/java/org/apache/seata/server/storage/db/store/VGroupMappingDataBaseDAO.java
public boolean insertMappingDO(MappingDO mappingDO) {
String sql = "INSERT INTO " + vMapping + " (vgroup,namespace, cluster) VALUES (?, ?, ?)";
Connection conn = null;
PreparedStatement ps = null;
try {
int index = 1;
conn = vGroupMappingDataSource.getConnection();
conn.setAutoCommit(true);
ps = conn.prepareStatement(sql);
ps.setString(index++, mappingDO.getVGroup());
ps.setString(index++, mappingDO.getNamespace());
ps.setString(index++, mappingDO.getCluster());

return ps.executeUpdate() > 0;
} catch (SQLException e) {
throw new SeataRuntimeException(ErrorCode.ERR_CONFIG, e);
} finally {
IOUtil.close(ps, conn);
}
}
...
```

### Ⅱ. Describe what happened

Therefore, it is expected that the insertMappingDO method of VGroupMappingDataBaseDAO will fail to execute the query when executed in an Oracle environment.

deleteMappingDOByVGroup, queryMappingDO also appear similar.

### Ⅲ. Describe what you expected to happen

I think it would be better to change the cluster column to a keyword other than a reserved word. What do you think?

I find it odd that these queries only cause problems with Oracle, while they work fine with other database vendors.

However, I believe that using double quotes to avoid reserved word issues could potentially affect other database vendors, even if it doesn't cause problems with Oracle.

Please give me your opinion. Thank you.

### Ⅳ. How to reproduce it (as minimally and precisely as possible)

You can reproduce it by setting up an Oracle DB environment and testing it.

### Ⅴ. Anything else we need to know?

NA

### Ⅵ. Environment

NA

Contributor guide

Open the contributing guide

Research direction

Start with script/server/db/oracle.sql and server/src/main/java/org/apache/seata/server/storage/db/store/VGroupMappingDataBaseDAO.java. Review insertMappingDO, deleteMappingDOByVGroup, and queryMappingDO alongside the Oracle reproduction described in the issue. Done means the schema initializes in Oracle and these operations execute without the reserved-word failure while preserving compatibility with other database vendors.

Written by the indexing model from the issue text.

Assessment

Tech stack
java, sql
Domain
databases
Issue type
Bug
Difficulty
3/5
Estimated time
1-2 days
Activity status
Stale
Clarity
Mostly clear
Newbie friendliness
45/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.