Attempt to run multi-node KV plan
I am testing Routable Query API via VerticaRoutableExecutor, getting the following error sometimes:
java.sql.SQLNonTransientException: [Vertica][VJDBC](2242) ROLLBACK: Attempt to run multi-node KV plan
at com.vertica.util.ServerErrorData.buildException(Unknown Source)
at com.vertica.dataengine.VResultSet.fetchChunk(Unknown Source)
at com.vertica.dataengine.VResultSet.initialize(Unknown Source)
at com.vertica.dataengine.VQueryExecutor.readExecuteResponse(Unknown Source)
at com.vertica.dataengine.VQueryExecutor.handleExecuteResponse(Unknown Source)
at com.vertica.dataengine.VQueryExecutor.execute(Unknown Source)
at com.vertica.jdbc.common.SStatement.executeNoParams(Unknown Source)
at com.vertica.jdbc.common.SStatement.executeQuery(Unknown Source)
at com.vertica.jdbc.kv.RoutableQuery.runQueryOnSqlConn(Unknown Source)
at com.vertica.jdbc.kv.RoutableQuery.runQueryOnRoutableConn(Unknown Source)
at com.vertica.jdbc.kv.RoutableQuery.execute(Unknown Source)
at com.vertica.jdbc.kv.RoutableQuery.executeSQLQuery(Unknown Source)
at com.vertica.jdbc.kv.VerticaRoutableExecutorImpl.execute_internal(Unknown Source)
at com.vertica.jdbc.kv.VerticaRoutableExecutorImpl.execute(Unknown Source)...
The table is segmented by appId field and the query always have appId field in the where clause.
Here is the definition:
CREATE TABLE serving_test.serving1_hr
(
applicationid int NOT NULL,
timestamplong int NOT NULL,
dim1 int NOT NULL,
dim2 int NOT NULL,
version int NOT NULL,
sum1 int NOT NULL,
sum2 int NOT NULL,
cnt1 int NOT NULL,
cnt2 int NOT NULL,
cnt3 int NOT NULL
)
PARTITION BY (floor((serving1_hr.timestamplong / 172800000)));
CREATE PROJECTION serving_test.serving1_hr /*+createtype(P)*/
(
applicationid,
timestamplong,
dim1,
dim2,
version,
sum1,
sum2,
cnt1,
cnt2,
cnt3
)
AS
SELECT serving1_hr.applicationid,
serving1_hr.timestamplong,
serving1_hr.dim1,
serving1_hr.dim2,
serving1_hr.version,
serving1_hr.sum1,
serving1_hr.sum2,
serving1_hr.cnt1,
serving1_hr.cnt2,
serving1_hr.cnt3
FROM serving_test.serving1_hr
ORDER BY serving1_hr.applicationid,
serving1_hr.dim1,
serving1_hr.dim2,
serving1_hr.timestamplong
SEGMENTED BY hash(serving1_hr.applicationid) ALL NODES KSAFE 1;
SELECT MARK_DESIGN_KSAFE(1);
The query which fail is very simple:
select dim1, dim2, SUM(sum2) ,SUM(cnt2), sum(cnt3) from serving1_hr where applicationid=? and timestamplong>=? and timestamplong<? and version=? group by applicationid,version,dim1,dim2 order by sum(2) desc limit 1000
maybe it has todo with the 'group/order by' caluses?
Anything I miss here?
Comments
It is a bug which is being worked on and is scheduled to be released in our next major version
Thanks for letting me know - however my original query had inside a hint:
select /*+label (my_label)*/ ...
Once I removed the label I did not get this sporadic error anymore.
Good to know the information