[
https://issues.apache.org/jira/browse/PHOENIX-3056?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15366284#comment-15366284
]
James Taylor commented on PHOENIX-3056:
---------------------------------------
I've investigated this a bit and found the cause. It has to do with the way
delete over immutable indexes is implemented and the check we have to prevent
deletions that can't be handled. When an index is in a BUILDING state (which it
will be if the index is declared to be build asynchronously), then the index
isn't considered and we mistakenly think we have an delete statement that can't
be handled.
The easiest fix I can think of would be to check if the index is in a building
state and in that case allow the delete only if the indexes contains only PK
columns (in which case we can build the index row based on the results of
deleting from the data table). We could get fancier here, but it would require
more changes, and I'm not sure it's necessary. The approach of running a
separate delete statement over the immutable index won't work because the index
rows don't yet exist. Issuing delete markers will work, though, as when the
index becomes active, the delete markers have later timestamps than the rows
being built so will overlay them.
Let me work up a patch and then perhaps you can take it from there, [~tdsilva]?
> Incorrect error message when deleting a record from table with async index
> creation in progress
> -----------------------------------------------------------------------------------------------
>
> Key: PHOENIX-3056
> URL: https://issues.apache.org/jira/browse/PHOENIX-3056
> Project: Phoenix
> Issue Type: Bug
> Affects Versions: 4.7.0
> Reporter: Brian Esserlieu
> Assignee: Thomas D'Silva
> Priority: Minor
> Fix For: 4.8.0
>
>
> Repro:
> DROP TABLE IF EXISTS TEST_TABLE;
> CREATE TABLE IF NOT EXISTS TEST_TABLE (
> pk1 VARCHAR NOT NULL,
> pk2 VARCHAR NOT NULL,
> pk3 VARCHAR
> CONSTRAINT PK PRIMARY KEY
> (
> pk1,
> pk2,
> pk3
> )
> ) MULTI_TENANT=true,IMMUTABLE_ROWS=true;
> CREATE INDEX TEST_INDEX ON TEST_TABLE (pk3, pk2) ASYNC;
> upsert into TEST_TABLE (pk1, pk2, pk3) values ('a', '1', 'value1');
> upsert into TEST_TABLE (pk1, pk2, pk3) values ('a', '2', 'value2');
> select * from test_table;
> delete from TEST_TABLE where pk1 = 'a';
> When I run the above I get the following error on the delete statement:
> "Error: ERROR 1027 (42Y86): All columns referenced in a WHERE clause must be
> available in every index for a table with immutable rows. tableName=TEST_TABLE
> SQLState: 42Y86
> ErrorCode: 1027
> Error occurred in:
> delete from TEST_TABLE where pk1 = 'a'"
> Notice the SQL works simply by removing the ASYNC keyword from the index
> creation statement.
> The error message should reflect that the index is being created so deletes
> are blocked until that completes.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
