Hi Nanda, In 4.4.0 version we are creating indexes as mutable indexes even the table has immutable rows. So delete is not getting allowed. PHOENIX-2616 fixed to make indexes immutable when data table has immutable rows. As a work around you can create indexes also with IMMUTABLE_ROWS=true option then deletes should work fine.
Thanks, Rajeshbabu. On Fri, Feb 26, 2016 at 12:15 PM, Nanda <tnkish...@gmail.com> wrote: > Hi James, > > Thanks for the reply. > > I was not able to find the exact corner case of the issue when tried with > a sample table. > > Can you please let me know if there is something i am doing wrong? > > > > But in our project we are facing this issue when we try to delete the data > from the below table, > > Below are the tables and indexes we have in our project, > > CREATE TABLE four_g.s11_lusr_hourly ( > subscriber.msisdn VARCHAR, > subscriber.plan VARCHAR, > subscriber.imei VARCHAR, > basestation.e_node_b_id VARCHAR, > session_data.mme_ip VARCHAR, > session_data.sgw_ip VARCHAR, > session_data.user_ip VARCHAR, > session_data.apn VARCHAR, > session_data.rat VARCHAR, > session_data.start_time TIMESTAMP, > session_data.end_time TIMESTAMP, > session_data.teid_uplink VARCHAR, > session_data.teid_downlink VARCHAR, > session_data.ipv4_uplink VARCHAR, > session_data.ipv4_downlink VARCHAR, > report_time TIMESTAMP NOT NULL, > imsi VARCHAR NOT NULL, > default_bearer_id BIGINT NOT NULL, > dedicated_bearer_id BIGINT NOT NULL, > rolled_up_count INTEGER, > basestation.name VARCHAR, > basestation.latitude FLOAT, > basestation.longitude FLOAT, > basestation.rnc_id VARCHAR, > basestation.mobile_operator_name VARCHAR, > basestation.mobile_operator_country VARCHAR, > basestation.sector_id VARCHAR, > subscriber.device_type VARCHAR, > subscriber.device_model VARCHAR, > subscriber.device_vendor VARCHAR, > subscriber.mobile_operator_name VARCHAR, > subscriber.mobile_operator_country VARCHAR, > subscriber.roaming_status VARCHAR > CONSTRAINT s11_lusr_hourly_pk PRIMARY KEY ( > report_time, > imsi, > default_bearer_id, > dedicated_bearer_id > ) > ) > SALT_BUCKETS = 4, > IMMUTABLE_ROWS = true, > COMPRESSION = 'GZ'; > > > Below are the secondary indexes i have, > > CREATE INDEX LUSR_HOURLY_INDEX_DASHBOARD ON FOUR_G.S11_LUSR_HOURLY > (report_time, start_time, end_time, sgw_ip, user_ip, e_node_b_id, apn, rat, > device_type, device_model, device_vendor); > CREATE INDEX LUSR_HOURLY_INDEX_DASHBOARD_MAP ON FOUR_G.S11_LUSR_HOURLY > (report_time, start_time, end_time, sgw_ip, user_ip, e_node_b_id, apn, rat, > device_type, device_model, device_vendor) INCLUDE (name, latitude, > longitude, sector_id, imsi); > CREATE INDEX LUSR_HOURLY_INDEX_HISTORY ON FOUR_G.S11_LUSR_HOURLY > (report_time, start_time, end_time, sgw_ip, user_ip, roaming_status) > INCLUDE (apn, rat, e_node_b_id, device_type, device_vendor, device_model); > CREATE INDEX LUSR_HOURLY_INDEX_TRENDS ON FOUR_G.S11_LUSR_HOURLY > (report_time, start_time, end_time, sgw_ip, user_ip, roaming_status, name, > device_type, apn, rat) include (msisdn); > CREATE INDEX LUSR_HOURLY_INDEX_CATEGORY ON FOUR_G.S11_LUSR_HOURLY > (report_time, start_time, end_time, sgw_ip, user_ip, msisdn, e_node_b_id, > device_type) include (device_vendor, device_model); > CREATE INDEX LUSR_HOURLY_INDEX_KPI ON FOUR_G.S11_LUSR_HOURLY (report_time, > start_time, end_time, sgw_ip, user_ip,roaming_status, apn, rat, name, > device_type) include (msisdn, plan, imsi); > > > Below is the exception i am getting, > > Caused by: java.sql.SQLException: ERROR 1027 (42Y86): All columns referenced > in a WHERE clause must be available in every index for a table with immutable > rows. tableName=FOUR_G.S11_LUSR_HOURLY > at > org.apache.phoenix.exception.SQLExceptionCode$Factory$1.newException(SQLExceptionCode.java:386) > at > org.apache.phoenix.exception.SQLExceptionInfo.buildException(SQLExceptionInfo.java:145) > at > org.apache.phoenix.compile.DeleteCompiler.compile(DeleteCompiler.java:389) > at > org.apache.phoenix.jdbc.PhoenixStatement$ExecutableDeleteStatement.compilePlan(PhoenixStatement.java:546) > at > org.apache.phoenix.jdbc.PhoenixStatement$ExecutableDeleteStatement.compilePlan(PhoenixStatement.java:534) > at > org.apache.phoenix.jdbc.PhoenixStatement$2.call(PhoenixStatement.java:302) > at > org.apache.phoenix.jdbc.PhoenixStatement$2.call(PhoenixStatement.java:295) > at org.apache.phoenix.call.CallRunner.run(CallRunner.java:53) > at > org.apache.phoenix.jdbc.PhoenixStatement.executeMutation(PhoenixStatement.java:293) > at > org.apache.phoenix.jdbc.PhoenixStatement.execute(PhoenixStatement.java:225) > at > org.apache.phoenix.jdbc.PhoenixPreparedStatement.execute(PhoenixPreparedStatement.java:173) > at > org.apache.phoenix.jdbc.PhoenixStatement.executeBatch(PhoenixStatement.java:1130) > > > > > > Thanks, > > Nanda > > > > > > On Sun, Feb 21, 2016 at 7:51 AM, James Taylor <jamestay...@apache.org> > wrote: > >> Hi Nanda, >> This error occurs if your table is immutable, you have an index on the >> table, and your WHERE clause is filtering on a column not contained in all >> of the indexes. If that's not the case, would you mind posting a complete >> end-to-end test as it's possible you're hitting a bug. >> Thanks, >> James >> >> On Sat, Feb 20, 2016 at 10:25 AM, Nanda <tnkish...@gmail.com> wrote: >> >>> Hi, >>> >>> I am using Phoenix 4.4 and when executing the below query i get an >>> exception as below, >>> I have indexes on the below "some_table" on column report_time in all >>> the indexes created. >>> >>> Delete from some_table where TO_NUMBER(report_time) <= 1455937500000 >>> >>> >>> >>> java.sql.SQLException: ERROR 1027 (42Y86): All columns referenced in a >>> WHERE clause must be available in every index for a table with immutable >>> rows. tableName=some_table >>> at >>> org.apache.phoenix.exception.SQLExceptionCode$Factory$1.newException(SQLExceptionCode.java:386) >>> at >>> org.apache.phoenix.exception.SQLExceptionInfo.buildException(SQLExceptionInfo.java:145) >>> at >>> org.apache.phoenix.compile.DeleteCompiler.compile(DeleteCompiler.java:389) >>> at >>> org.apache.phoenix.jdbc.PhoenixStatement$ExecutableDeleteStatement.compilePlan(PhoenixStatement.java:546) >>> at >>> org.apache.phoenix.jdbc.PhoenixStatement$ExecutableDeleteStatement.compilePlan(PhoenixStatement.java:534) >>> at >>> org.apache.phoenix.jdbc.PhoenixStatement$2.call(PhoenixStatement.java:302) >>> at >>> org.apache.phoenix.jdbc.PhoenixStatement$2.call(PhoenixStatement.java:295) >>> at org.apache.phoenix.call.CallRunner.run(CallRunner.java:53) >>> at >>> org.apache.phoenix.jdbc.PhoenixStatement.executeMutation(PhoenixStatement.java:293) >>> at >>> org.apache.phoenix.jdbc.PhoenixStatement.execute(PhoenixStatement.java:1247) >>> at sqlline.Commands.execute(Commands.java:822) >>> at sqlline.Commands.sql(Commands.java:732) >>> at sqlline.SqlLine.dispatch(SqlLine.java:808) >>> at sqlline.SqlLine.begin(SqlLine.java:681) >>> at sqlline.SqlLine.start(SqlLine.java:398) >>> at sqlline.SqlLine.main(SqlLine.java:292) >>> >>> >>> >>> Any Idea what that error exactly meant? >>> >>> Thanks, >>> Nanda >>> >> >> >
