Phoenix Query Server - upsert fail

[#1]

Hi,

I'm testing phoenix query server, working with a prospect that wants to use phoenix + .NET.

I'm not being able to succeed with upsert thru phoenix query server. curl response seems to be ok, but new data WAS NOT COMMITTED. It might be related to those two jiras:

https://issues.apache.org/jira/browse/PHOENIX-2320

https://issues.apache.org/jira/browse/PHOENIX-234

my create table is:

create table teste(
  id bigint not null,
  text varchar 
  constraint pk primary key (id)
) ;

Here is a select statement that works thru phoenix query server:

[root@hdp23 ~]# curl -XPOST -H 'request: {"request":"prepareAndExecute","connectionId":"aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa","sql":"select * from teste","maxRowCount":-1}' http://localhost:8765/
{"response":"Service$ExecuteResponse","results":[{"response":"resultSet","connectionId":"aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa","statementId":1955331455,"ownStatement":true,"signature":{"columns":[{"ordinal":0,"autoIncrement":false,"caseSensitive":false,"searchable":true,"currency":false,"nullable":0,"signed":true,"displaySize":40,"label":"ID","columnName":"ID","schemaName":"","precision":0,"scale":0,"tableName":"TESTE","catalogName":"","type":{"type":"scalar","id":-5,"name":"BIGINT","rep":"PRIMITIVE_LONG"},"readOnly":true,"writable":false,"definitelyWritable":false,"columnClassName":"java.lang.Long"},{"ordinal":1,"autoIncrement":false,"caseSensitive":false,"searchable":true,"currency":false,"nullable":1,"signed":false,"displaySize":40,"label":"TEXT","columnName":"TEXT","schemaName":"","precision":0,"scale":0,"tableName":"TESTE","catalogName":"","type":{"type":"scalar","id":12,"name":"VARCHAR","rep":"STRING"},"readOnly":true,"writable":false,"definitelyWritable":false,"columnClassName":"java.lang.String"}],"sql":null,"parameters":[],"cursorFactory":{"style":"LIST","clazz":null,"fieldNames":null}},"firstFrame":{"offset":0,"done":true,"rows":[[1,"guilherme"],[2,"isabela"],[3,"rogerio"],[4,null]]},"updateCount":-1}]}

And here insert statement, it says it worked, "updateCount=1"

[root@hdp23 log]# curl -XPOST -H 'request: {"request":"prepareAndExecute","connectionId":"aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa","sql":"upsert into teste (id) values (10)","maxRowCount":-1}' http://localhost:8765/
{"response":"Service$ExecuteResponse","results":[{"response":"resultSet","connectionId":"aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa","statementId":1069768164,"ownStatement":false,"signature":null,"firstFrame":null,"updateCount":1}]}

If I select table "teste" the new line is not there. I was not committed to phoenix table.

Additionally, /var/log/hbase/phoenix-hbase-server.log does show any message after command above.

Anyone has any idea what is going on and/or how to debug?

Thanks.

Guilherme

[#2]

This is what the JSON looks like when I'm running the query locally with sqlline. It also is a bleeding-edge build of Calcite, so there are some differences over what is in HDP2.3 presently (e.g. executeResults instead of Service$ExecuteResponse). The whole difference I see is that you were missing statementId in the prepareAndExecute request for the upsert.

The create table:

$ curl -XPOST -H 'request: {"request":"createStatement","connectionId":"b27cbc83-a514-49f0-9bbe-f15d8bfb3532"}' http://localhost:8765
{"response":"createStatement","connectionId":"b27cbc83-a514-49f0-9bbe-f15d8bfb3532","statementId":22}

$ curl -XPOST -H 'request: {"request":"prepareAndExecute","connectionId":"b27cbc83-a514-49f0-9bbe-f15d8bfb3532","statementId":22,"sql":"CREATE TABLE teste(id bigint not null, text varchar, constraint pk primary key (id))","maxRowCount":-1}' http://localhost:8765
{"response":"executeResults","results":[{"response":"resultSet","connectionId":"b27cbc83-a514-49f0-9bbe-f15d8bfb3532","statementId":22,"ownStatement":false,"signature":null,"firstFrame":null,"updateCount":0}]}

The upsert:

$ curl -XPOST -H 'request: {"request":"createStatement","connectionId":"b27cbc83-a514-49f0-9bbe-f15d8bfb3532"}' http://localhost:8765
{"response":"createStatement","connectionId":"b27cbc83-a514-49f0-9bbe-f15d8bfb3532","statementId":23}

$ curl -XPOST -H 'request: {"request":"prepareAndExecute","connectionId":"b27cbc83-a514-49f0-9bbe-f15d8bfb3532","statementId":23,"sql":"upsert into teste (id) values (10)","maxRowCount":-1}' http://localhost:8765
{"response":"executeResults","results":[{"response":"resultSet","connectionId":"b27cbc83-a514-49f0-9bbe-f15d8bfb3532","statementId":23,"ownStatement":false,"signature":null,"firstFrame":null,"updateCount":1}]}

The select

$ curl -XPOST -H 'request: {"request":"createStatement","connectionId":"b27cbc83-a514-49f0-9bbe-f15d8bfb3532"}' http://localhost:8765 
{"response":"createStatement","connectionId":"b27cbc83-a514-49f0-9bbe-f15d8bfb3532","statementId":24}

$ curl -XPOST -H 'request: {"request":"prepareAndExecute","connectionId":"b27cbc83-a514-49f0-9bbe-f15d8bfb3532","statementId":24,"sql":"select * from teste","maxRowCount":-1}' http://localhost:8765
{"response":"executeResults","results":[{"response":"resultSet","connectionId":"b27cbc83-a514-49f0-9bbe-f15d8bfb3532","statementId":24,"ownStatement":true,"signature":{"columns":[{"ordinal":0,"autoIncrement":false,"caseSensitive":false,"searchable":true,"currency":false,"nullable":0,"signed":true,"displaySize":40,"label":"ID","columnName":"ID","schemaName":"","precision":0,"scale":0,"tableName":"TESTE","catalogName":"","type":{"type":"scalar","id":-5,"name":"BIGINT","rep":"PRIMITIVE_LONG"},"readOnly":true,"writable":false,"definitelyWritable":false,"columnClassName":"java.lang.Long"},{"ordinal":1,"autoIncrement":false,"caseSensitive":false,"searchable":true,"currency":false,"nullable":1,"signed":false,"displaySize":40,"label":"TEXT","columnName":"TEXT","schemaName":"","precision":0,"scale":0,"tableName":"TESTE","catalogName":"","type":{"type":"scalar","id":12,"name":"VARCHAR","rep":"STRING"},"readOnly":true,"writable":false,"definitelyWritable":false,"columnClassName":"java.lang.String"}],"sql":null,"parameters":[],"cursorFactory":{"style":"LIST","clazz":null,"fieldNames":null},"statementType":null},"firstFrame":{"offset":0,"done":true,"rows":[]},"updateCount":-1}]}

[#3 · Accepted solution]

@Josh Elser

statementId only existis in new version of phoenix. I built it locally, replaced phoenix jars and tried with commands you sent, but same issue happened, new rows were not inserted/updated.

I ended finding the solution for both versions (2.3.2 and new phoenix version), we just need to add: phoenix.connection.autoCommit=true to hbase-site.xml (can be set using ambari as well).

Is phoenix server missing the commit?