You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@phoenix.apache.org by "Amarnath Ramamoorthi (JIRA)" <ji...@apache.org> on 2018/12/21 17:34:00 UTC
[jira] [Comment Edited] (PHOENIX-5068) Autocommit off is not
working as expected might be a bug!?
[ https://issues.apache.org/jira/browse/PHOENIX-5068?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16726923#comment-16726923 ]
Amarnath Ramamoorthi edited comment on PHOENIX-5068 at 12/21/18 5:33 PM:
-------------------------------------------------------------------------
DDL
{code:java}
CREATE TABLE IF NOT EXISTS "FOO"."TEST" (
"id_x" BIGINT NOT NULL,
"id_y" BIGINT NOT NULL,
CONSTRAINT "demo_pk" PRIMARY KEY ("id_x", "id_y")
)SALT_BUCKETS=1, DEFAULT_COLUMN_FAMILY='FD';
CREATE TABLE IF NOT EXISTS "FOO"."DEMO" (
"id_x" BIGINT NOT NULL,
"id_y" BIGINT NOT NULL,
CONSTRAINT "demo_pk" PRIMARY KEY ("id_x", "id_y")
)SALT_BUCKETS=1, DEFAULT_COLUMN_FAMILY='FD';
{code}
DML
Python script to generate data
{code:java}
import sys
old_stdout = sys.stdout
myfile = open("test_foo_data.sql","w")
sys.stdout = myfile
for i in range(1, 2001):
print('UPSERT INTO "FOO".TEST ("id_x", "id_y") VALUES (' + str(i) + ', ' + str(i+1) + ');')
sys.stdout = old_stdout
myfile.close()
{code}
The below command will update the table "TEST" with 2000 rows
{code:java}
phoenix test_foo_data.sql
{code}
phoenix commands
{code:java}
!autocommit off
UPSERT INTO "FOO".DEMO SELECT * FROM "FOO".TEST limit 100;
UPSERT INTO "FOO".DEMO SELECT * FROM "FOO".TEST limit 99;
UPSERT INTO "FOO".DEMO SELECT * FROM "FOO".TEST limit 500;
{code}
[~tdsilva] Hope this helps
was (Author: aramamoorthi):
DDL
{code:java}
CREATE TABLE IF NOT EXISTS "FOO"."TEST" (
"id_x" BIGINT NOT NULL,
"id_y" BIGINT NOT NULL,
CONSTRAINT "demo_pk" PRIMARY KEY ("id_x", "id_y")
)SALT_BUCKETS=1, DEFAULT_COLUMN_FAMILY='FD';
CREATE TABLE IF NOT EXISTS "FOO"."DEMO" (
"id_x" BIGINT NOT NULL,
"id_y" BIGINT NOT NULL,
CONSTRAINT "demo_pk" PRIMARY KEY ("id_x", "id_y")
)SALT_BUCKETS=1, DEFAULT_COLUMN_FAMILY='FD';
{code}
DML
Python script to generate data
{code:java}
import sys
old_stdout = sys.stdout
myfile = open("test_foo_data.sql","w")
sys.stdout = log_file
for i in range(1, 2001):
print('UPSERT INTO "FOO".TEST ("id_x", "id_y") VALUES (' + str(i) + ', ' + str(i+1) + ');')
sys.stdout = old_stdout
myfile.close()
{code}
The below command will update the table "TEST" with 2000 rows
{code:java}
phoenix test_foo_data.sql
{code}
phoenix commands
{code:java}
!autocommit off
UPSERT INTO "FOO".DEMO SELECT * FROM "FOO".TEST limit 100;
UPSERT INTO "FOO".DEMO SELECT * FROM "FOO".TEST limit 99;
UPSERT INTO "FOO".DEMO SELECT * FROM "FOO".TEST limit 500;
{code}
[~tdsilva] Hope this helps
> Autocommit off is not working as expected might be a bug!?
> ----------------------------------------------------------
>
> Key: PHOENIX-5068
> URL: https://issues.apache.org/jira/browse/PHOENIX-5068
> Project: Phoenix
> Issue Type: Bug
> Reporter: Amarnath Ramamoorthi
> Priority: Minor
>
> Autocommit off is working strange might be a bug!?
> Here is what we found when using autocommit off.
> A table has only 2 int columns and both set as primary key, containing 100 rows in total.
> On *"autocommit off"* when we try to upsert values in to same table, it says 200 rows affected.
> Works fine when we run the same Upsert command but with less than 100 rows using WHERE command as you can see below.
> There is something wrong with auto commit off with >= 100 rows upsert`s.
> {code:java}
> 0: jdbc:phoenix:XXYYZZ> select count(*) from "FOO".DEMO;
> +-----------+
> | COUNT(1) |
> +-----------+
> | 100 |
> +-----------+
> 1 row selected (0.025 seconds)
> 0: jdbc:phoenix:XXYYZZ> SELECT * FROM "FOO".DEMO WHERE "id_x"=9741;
> +--------+-----------+
> | id_x | id_y |
> +--------+-----------+
> | 9741 | 63423770 |
> +--------+-----------+
> 1 row selected (0.04 seconds)
> 0: jdbc:phoenix:XXYYZZ> !autocommit off
> Autocommit status: false
> 0: jdbc:phoenix:XXYYZZ> UPSERT INTO "FOO".DEMO SELECT * FROM "FOO".DEMO;
> 200 rows affected (0.023 seconds)
> 0: jdbc:phoenix:XXYYZZ>
> 0: jdbc:phoenix:XXYYZZ> UPSERT INTO "FOO".DEMO SELECT * FROM "FOO".DEMO WHERE "id_x"=9741;
> 1 row affected (0.014 seconds)
> 0: jdbc:phoenix:XXYYZZ> UPSERT INTO "FOO".DEMO SELECT * FROM "FOO".DEMO WHERE "id_x"!=9741;
> 99 rows affected (0.045 seconds)
> 0: jdbc:phoenix:XXYYZZ>
> 0: jdbc:phoenix:XXYYZZ> !autocommit on
> Autocommit status: true
> 0: jdbc:phoenix:XXYYZZ> UPSERT INTO "FOO".DEMO SELECT * FROM "FOO".DEMO;
> 100 rows affected (0.065 seconds)
> {code}
> Tested once again, but now select from different table
> {code:java}
> 0: jdbc:phoenix:XXYYZZ> !autocommit off
> Autocommit status: false
> 0: jdbc:phoenix:XXYYZZ> UPSERT INTO "FOO".DEMO SELECT * FROM "FOO".TEST limit 100;
> 200 rows affected (0.052 seconds)
> 0: jdbc:phoenix:XXYYZZ> UPSERT INTO "FOO".DEMO SELECT * FROM "FOO".TEST limit 99;
> 99 rows affected (0.029 seconds)
> 0: jdbc:phoenix:XXYYZZ> UPSERT INTO "FOO".DEMO SELECT * FROM "FOO".TEST limit 500;
> 1,000 rows affected (0.041 seconds)
> {code}
> Still the same, It shows the rows affected is 1,000 even though we have it limited to 500. It keeps doubling up.
> Would be really helpful if someone could help on this please.
>
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)