Oracle Vert.x client never return error response when auto-generated keys are enabled with Oracle 23
michalvavrik opened this issue · 6 comments
Questions
No question, thanks.
Version
Vert.x 4.4.4.
Context
I started to use docker.io/gvenzl/oracle-free:23-slim-faststart (which is the default Quarkus Oracle dev services image) and saw that when Oracle exception is thrown and auto-generated keys are enabled, response never comes. What it means: if you set OraclePrepareOptions, response never comes, if you don't pass any options, error response is returned. If you set io.vertx.oracleclient.OraclePrepareOptions#setAutoGeneratedKeys to false, error response arrives.
Do you have a reproducer?
https://github.com/michalvavrik/vertx-sql-oracle-opts-reproducer
Steps to reproduce
git clone git@github.com:michalvavrik/vertx-sql-oracle-opts-reproducer.gitcd code-with-quarkusquarkus dev- when you are done waiting for what never comes, kill the app
fuser -k 5005/tcpandpkill -f quarkus
When you run same with Oracle 21, it works: quarkus dev -Dquarkus.datasource.devservices.image-name=docker.io/gvenzl/oracle-xe:21-slim-faststart
Extra
Linux Fedora, OpenJDK 17, I don't use Vert.x directly, I use Quarkus.
@michalvavrik to be sure, are you saying that when the database is changed, the bug goes away?
In this case, can you try without Vert.x, just Oracle JDBC + reactive extensions? If the same problem occurs, then a bug report can be sent to Oracle.
I was able to reproduce the problem with JDBC + Oracle Reactive Extensions:
import oracle.jdbc.OracleConnection;
import oracle.jdbc.OraclePreparedStatement;
import oracle.jdbc.pool.OracleDataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.concurrent.CountDownLatch;
import java.util.concurrent.Flow;
public class ReactiveInsertAndConstraintViolation {
public static void main(String[] args) throws Exception {
String url = "jdbc:oracle:thin:@localhost:1521/FREEPDB1";
OracleDataSource ods = new OracleDataSource();
ods.setUser("test");
ods.setPassword("test");
ods.setURL(url);
Connection conn = ods.getConnection();
try (PreparedStatement ps = conn.prepareStatement("DROP TABLE IF EXISTS passenger")) {
ps.execute();
}
try (PreparedStatement ps = conn.prepareStatement("CREATE TABLE passenger (\n" +
" id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,\n" +
" nif VARCHAR(15) NOT NULL,\n" +
" name VARCHAR(25) NOT NULL,\n" +
" last_name VARCHAR(55) NOT NULL,\n" +
" contact_number VARCHAR(20) NOT NULL,\n" +
" created_at INT NOT NULL,\n" +
" updated_at INT,\n" +
" address_id NUMBER\n" +
")")) {
ps.execute();
}
OracleConnection oc = conn.unwrap(OracleConnection.class);
String sql = "INSERT INTO passenger (" +
"nif," +
"name," +
"last_name," +
"contact_number," +
"created_at," +
"address_id" +
") VALUES (" +
"null," +
"'Walt'," +
"'White'," +
"'+34608554433'," +
"1691060927694,2)";
/*
Replace the following line with:
PreparedStatement ps = oc.prepareStatement(sql);
And you will get the expected:
java.sql.SQLIntegrityConstraintViolationException: ORA-01400: cannot insert NULL into ("TEST"."PASSENGER"."NIF")
You also get the expected exception if your use ojdbc11 21.11.0.0
*/
PreparedStatement ps = oc.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
OraclePreparedStatement ops = ps.unwrap(OraclePreparedStatement.class);
CountDownLatch latch = new CountDownLatch(1);
ops.executeAsyncOracle().subscribe(new Flow.Subscriber<>() {
@Override
public void onSubscribe(Flow.Subscription subscription) {
subscription.request(1);
}
@Override
public void onNext(Boolean item) {
System.out.println("item = " + item);
}
@Override
public void onError(Throwable throwable) {
throwable.printStackTrace();
try {
conn.close();
} catch (SQLException e) {
}
latch.countDown();
}
@Override
public void onComplete() {
System.out.println("Done");
try {
conn.close();
} catch (SQLException e) {
}
latch.countDown();
}
});
latch.await();
}
}POM file:
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>org.example</groupId>
<artifactId>jdbc-reactive-oracle</artifactId>
<version>1.0-SNAPSHOT</version>
<properties>
<maven.compiler.source>11</maven.compiler.source>
<maven.compiler.target>11</maven.compiler.target>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
</properties>
<dependencies>
<dependency>
<groupId>com.oracle.database.jdbc</groupId>
<artifactId>ojdbc11</artifactId>
<version>21.11.0.0</version>
</dependency>
</dependencies>
</project>We must report this to Oracle
sorry @tsegismont for not answering, I was on PTO and then trying to catch up with stuff.
We must report this to Oracle
Well, yes I understand it ultimately needs to be fixed in Oracle DB JDBC, but considering I'm not using it directly but via Eclipse Vert.X, this seems like a right place at least to have it as known issue.
A bug has been created. ETA is 23.4.0.0 version of the JDBC driver.
Thanks for reporting!
Eventually, any 23c FREE related issue (being related to the database or clients, hence including the JDBC driver) can be reported here as well: https://forums.oracle.com/ords/apexds/domain/dev-community/category/oracle-database-free
Thank you @loiclefevre for the update.
Eventually, any 23c FREE related issue (being related to the database or clients, hence including the JDBC driver) can be reported here as well: https://forums.oracle.com/ords/apexds/domain/dev-community/category/oracle-database-free
I will report any problem that may be found later to this forum 👍
Closing, the problem has been reported to Oracle.