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