Closed
Description
Issue 1 : Description:
While creating Database for Voter Benchmark in Oracle Database 19c/23c, since bigint datatype is not natively supported by Oracle sql, the following error occurs :
[INFO ] 2023-07-13 01:27:58,225 [main] com.oltpbenchmark.DBWorkload main (181) - ======================================================================
[INFO ] 2023-07-13 01:27:58,293 [main] com.oltpbenchmark.DBWorkload main (406) - Creating new VOTER database...
[ERROR] 2023-07-13 01:28:06,440 [main] com.oltpbenchmark.DBWorkload main (411) - Unexpected error when creating benchmark database tables.
java.sql.SQLSyntaxErrorException: ORA-00902: invalid datatype
at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:629)
at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:563)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1230)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:771)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:298)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:511)
at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:122)
at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:1199)
at oracle.jdbc.driver.OracleStatement.executeSQLStatement(OracleStatement.java:1819)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1471)
at oracle.jdbc.driver.OracleStatement.executeInternal(OracleStatement.java:2504)
at oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:2459)
at oracle.jdbc.driver.OracleStatementWrapper.execute(OracleStatementWrapper.java:327)
at com.oltpbenchmark.util.ScriptRunner.runScript(ScriptRunner.java:125)
at com.oltpbenchmark.util.ScriptRunner.runScript(ScriptRunner.java:82)
at com.oltpbenchmark.util.ScriptRunner.runScript(ScriptRunner.java:71)
at com.oltpbenchmark.api.BenchmarkModule.createDatabase(BenchmarkModule.java:234)
at com.oltpbenchmark.api.BenchmarkModule.createDatabase(BenchmarkModule.java:213)
at com.oltpbenchmark.DBWorkload.runCreator(DBWorkload.java:628)
at com.oltpbenchmark.DBWorkload.main(DBWorkload.java:407)
Caused by: Error : 902, Position : 42, Sql = CREATE TABLE VOTES ( vote_id bigint NOT NULL, phone_number bigint NOT NULL, state varchar(2) NOT NULL, contestant_number integer NOT NULL REFERENCES CONTESTANTS (contestant_number), created timestamp NOT NULL ), OriginalSql = CREATE TABLE VOTES ( vote_id bigint NOT NULL, phone_number bigint NOT NULL, state varchar(2) NOT NULL, contestant_number integer NOT NULL REFERENCES CONTESTANTS (contestant_number), created timestamp NOT NULL ), Error Msg = ORA-00902: invalid datatype
Description:
We add a new oracle specific sql file i.e. benchmarks/voter/ddl-oracle.sql as it's not already present. Here phone_number will not exceed 10 digits so we are using NUMBER(11, 0) and for vote_id we use NUMBER(19, 0), since its range is considered to be equivalent to that of bigint. (For Oracle Sql)
-- Before changes
-- votes table holds every valid vote.
-- voters are not allowed to submit more than <x> votes, x is passed to client application
CREATE TABLE VOTES
(
vote_id bigint NOT NULL,
phone_number bigint NOT NULL,
state varchar(2) NOT NULL,
contestant_number integer NOT NULL REFERENCES CONTESTANTS (contestant_number),
created timestamp NOT NULL
);
CREATE INDEX idx_votes_phone_number ON VOTES (phone_number);
-- After changes (new file - ddl-oracle.sql)
-- votes table holds every valid vote.
-- voters are not allowed to submit more than <x> votes, x is passed to client application
CREATE TABLE VOTES
(
vote_id NUMBER(19, 0) NOT NULL,
phone_number NUMBER(11, 0) NOT NULL,
state varchar(2) NOT NULL,
contestant_number integer NOT NULL REFERENCES CONTESTANTS (contestant_number),
created timestamp NOT NULL
);
CREATE INDEX idx_votes_phone_number ON VOTES (phone_number);Issue 2 : Description:
Create step works fine following the above resolution. But at the execute step, the following error is displayed repeatedly. This is because NOW() function is not available in Oracle sql and therefore we use SYSTIMESTAMP which is an equivalent for NOW() and this ensures that all errors are resolved and proper timestamps are stored in the votes table.
[INFO ] 2023-07-13 01:31:35,716 [main] com.oltpbenchmark.DBWorkload main (181) - ======================================================================
[INFO ] 2023-07-13 01:31:46,743 [main] com.oltpbenchmark.DBWorkload runWorkload (640) - Creating 10 virtual terminals...
[INFO ] 2023-07-13 01:32:19,465 [main] com.oltpbenchmark.DBWorkload runWorkload (644) - Launching the VOTER Benchmark with 1 Phase...
[INFO ] 2023-07-13 01:32:19,476 [main] com.oltpbenchmark.ThreadBench runRateLimitedMultiPhase (128) - PHASE START :: [Workload=VOTER] [Serial=false] [Time=300] [WarmupTime=0] [Rate=10000] [Arrival=REGULAR] [Ratios=[100.0]] [ActiveWorkers=10]
[INFO ] 2023-07-13 01:32:19,483 [main] com.oltpbenchmark.ThreadBench runRateLimitedMultiPhase (280) - MEASURE :: Warmup complete, starting measurements.
[WARN ] 2023-07-13 01:32:21,624 [VoterWorker<000>] com.oltpbenchmark.api.Worker doWork (449) - SQLException occurred during [com.oltpbenchmark.benchmarks.voter.procedures.Vote/01] and will not be retried... sql state [42000], error code [904].
java.sql.SQLSyntaxErrorException: ORA-00904: "NOW": invalid identifier
at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:629)
at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:563)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1230)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:771)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:298)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:511)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:162)
at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:1240)
at oracle.jdbc.driver.OracleStatement.executeSQLStatement(OracleStatement.java:1819)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1471)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3760)
at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:4135)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:1013)
at com.oltpbenchmark.benchmarks.voter.procedures.Vote.run(Vote.java:129)
at com.oltpbenchmark.benchmarks.voter.VoterWorker.executeWork(VoterWorker.java:46)
at com.oltpbenchmark.api.Worker.doWork(Worker.java:416)
at com.oltpbenchmark.api.Worker.run(Worker.java:282)
at java.base/java.lang.Thread.run(Thread.java:833)
Caused by: Error : 904, Position : 105, Sql = INSERT INTO votes (vote_id, phone_number, state, contestant_number, created) VALUES (:1 , :2 , :3 , :4 , NOW()), OriginalSql = INSERT INTO votes (vote_id, phone_number, state, contestant_number, created) VALUES (?, ?, ?, ?, NOW()), Error Msg = ORA-00904: "NOW": invalid identifier
Description:
We create a dialect file i.e. benchmarks/voter/dialect-oracle.xml which is used to execute the corrected insertVoteStmt statement with SYSTIMESTAMP.
<?xml version="1.0"?>
<dialects>
<dialect type="ORACLE">
<procedure name="Vote">
<statement name="insertVoteStmt">
INSERT INTO votes (vote_id, phone_number, state, contestant_number, created) VALUES (?, ?, ?, ?, SYSTIMESTAMP)
</statement>
</procedure>
</dialect>
</dialects>Also remove semi-colons from all the statements inside SQLStmt() in Vote.java.
Otherwise we get this : Error Msg = ORA-00933: unexpected token at or near
-- After changes
public final SQLStmt checkContestantStmt = new SQLStmt(
"SELECT contestant_number FROM " + TABLENAME_CONTESTANTS + " WHERE contestant_number = ?"
);
// Checks if the voter has exceeded their allowed number of votes
public final SQLStmt checkVoterStmt = new SQLStmt(
"SELECT COUNT(*) FROM " + TABLENAME_VOTES + " WHERE phone_number = ?"
);
// Checks an area code to retrieve the corresponding state
public final SQLStmt checkStateStmt = new SQLStmt(
"SELECT state FROM " + TABLENAME_LOCATIONS + " WHERE area_code = ?"
);
// Records a vote
public final SQLStmt insertVoteStmt = new SQLStmt(
"INSERT INTO " + TABLENAME_VOTES + " (vote_id, phone_number, state, contestant_number, created) " +
"VALUES (?, ?, ?, ?, NOW())"
);