You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@phoenix.apache.org by James Heather <ja...@mendeley.com> on 2015/09/11 11:56:42 UTC
index creation partly succeeds if it times out
I just tried to create an index on a column for a table with 200M rows.
Creating the index timed out:
0: jdbc:phoenix:172.31.31.143> CREATE INDEX idx_lastname ON loadtest.testing (lastname);
Error: Operation timed out (state=TIM01,code=6000)
java.sql.SQLTimeoutException: Operation timed out
at org.apache.phoenix.exception.SQLExceptionCode$14.newException(SQLExceptionCode.java:314)
at org.apache.phoenix.exception.SQLExceptionInfo.buildException(SQLExceptionInfo.java:133)
I bumped up the timeout and tried again, but it failed, and it tells me
the index already exists:
0: jdbc:phoenix:172.31.31.143> CREATE INDEX idx_lastname ON loadtest.testing (lastname);
Error: ERROR 1013 (42M04): Table already exists. tableName=LOADTEST.IDX_LASTNAME (state=42M04,code=1013)
org.apache.phoenix.schema.TableAlreadyExistsException: ERROR 1013 (42M04): Table already exists. tableName=LOADTEST.IDX_LASTNAME
at org.apache.phoenix.schema.MetaDataClient.createTableInternal(MetaDataClient.java:1692)
at org.apache.phoenix.schema.MetaDataClient.createIndex(MetaDataClient.java:1118)
at org.apache.phoenix.compile.CreateIndexCompiler$1.execute(CreateIndexCompiler.java:95)
at org.apache.phoenix.jdbc.PhoenixStatement$2.call(PhoenixStatement.java:280)
at org.apache.phoenix.jdbc.PhoenixStatement$2.call(PhoenixStatement.java:272)
at org.apache.phoenix.call.CallRunner.run(CallRunner.java:53)
at org.apache.phoenix.jdbc.PhoenixStatement.executeMutation(PhoenixStatement.java:271)
at org.apache.phoenix.jdbc.PhoenixStatement.execute(PhoenixStatement.java:1063)
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)
0: jdbc:phoenix:172.31.31.143> !indexes loadtest.testing
+------------------------------------------+------------------------------------------+------------------------------------------+------------------------------------------+-----------------+------------------------------------------+------------+---------------------------+
| TABLE_CAT | TABLE_SCHEM | TABLE_NAME | NON_UNIQUE | INDEX_QUALIFIER | INDEX_NAME | TYPE | ORDINAL_POSIT |
+------------------------------------------+------------------------------------------+------------------------------------------+------------------------------------------+-----------------+------------------------------------------+------------+---------------------------+
| | LOADTEST | TESTING | true | | IDX_LASTNAME | 3 | 1 |
| | LOADTEST | TESTING | true | | IDX_LASTNAME | 3 | 2 |
+------------------------------------------+------------------------------------------+------------------------------------------+------------------------------------------+-----------------+------------------------------------------+------------+---------------------------+
0: jdbc:phoenix:172.31.31.143>
Is this a bug? I don't really see how the index can be in a usable
state. If I 'explain' a query that ought to use the index, it tells me
it's going to do a full scan anyway.
James
Re: index creation partly succeeds if it times out
Posted by James Heather <ja...@mendeley.com>.
Ah, too late, I'm afraid. I dropped it.
James
On 11/09/15 11:41, rajeshbabu@apache.org wrote:
> James,
> It should be in building state. Can you check what's the state of it?
>
> Thanks,
> Rajeshbabu.
>
> On Fri, Sep 11, 2015 at 4:04 PM, James Heather
> <james.heather@mendeley.com <ma...@mendeley.com>> wrote:
>
> Hi Rajeshbabu,
>
> Thanks--yes--I've done that. I'm now recreating the index with a
> long timeout.
>
> I reported it because it seemed to me to be a bug: Phoenix thinks
> that the index is there, but it's not. It ought to get cleaned up
> after a timeout.
>
> James
>
>
> On 11/09/15 11:32, rajeshbabu@apache.org
> <ma...@apache.org> wrote:
>> Hi James,
>>
>> You can drop the partially created index and try following steps
>>
>> 1) Add the following property to hbase-site.xml at phoenix client
>> side.
>> <property>
>> <name>phoenix.query.timeoutMs</name>
>> <value>double of default value</value>
>> </property>
>> 2) Export the HBASE_CONF_PATH with the configuration directory
>> where hbase-site.xml present.
>> 3) then start sqlline.py command prompt
>> 4) Then run create index query.
>>
>>
>> Thanks,
>> Rajeshbabu.
>>
>> On Fri, Sep 11, 2015 at 3:26 PM, James Heather
>> <james.heather@mendeley.com <ma...@mendeley.com>>
>> wrote:
>>
>> I just tried to create an index on a column for a table with
>> 200M rows. Creating the index timed out:
>>
>> 0: jdbc:phoenix:172.31.31.143> CREATE INDEX idx_lastname ON loadtest.testing (lastname);
>>
>> Error: Operation timed out (state=TIM01,code=6000)
>>
>> java.sql.SQLTimeoutException: Operation timed out
>>
>> at org.apache.phoenix.exception.SQLExceptionCode$14.newException(SQLExceptionCode.java:314)
>>
>> at org.apache.phoenix.exception.SQLExceptionInfo.buildException(SQLExceptionInfo.java:133)
>>
>>
>> I bumped up the timeout and tried again, but it failed, and
>> it tells me the index already exists:
>>
>> 0: jdbc:phoenix:172.31.31.143> CREATE INDEX idx_lastname ON loadtest.testing (lastname);
>>
>> Error: ERROR 1013 (42M04): Table already exists. tableName=LOADTEST.IDX_LASTNAME (state=42M04,code=1013)
>>
>> org.apache.phoenix.schema.TableAlreadyExistsException: ERROR 1013 (42M04): Table already exists. tableName=LOADTEST.IDX_LASTNAME
>>
>> at org.apache.phoenix.schema.MetaDataClient.createTableInternal(MetaDataClient.java:1692)
>>
>> at org.apache.phoenix.schema.MetaDataClient.createIndex(MetaDataClient.java:1118)
>>
>> at org.apache.phoenix.compile.CreateIndexCompiler$1.execute(CreateIndexCompiler.java:95)
>>
>> at org.apache.phoenix.jdbc.PhoenixStatement$2.call(PhoenixStatement.java:280)
>>
>> at org.apache.phoenix.jdbc.PhoenixStatement$2.call(PhoenixStatement.java:272)
>>
>> at org.apache.phoenix.call.CallRunner.run(CallRunner.java:53)
>>
>> at org.apache.phoenix.jdbc.PhoenixStatement.executeMutation(PhoenixStatement.java:271)
>>
>> at org.apache.phoenix.jdbc.PhoenixStatement.execute(PhoenixStatement.java:1063)
>>
>> 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)
>>
>> 0: jdbc:phoenix:172.31.31.143> !indexes loadtest.testing
>>
>> +------------------------------------------+------------------------------------------+------------------------------------------+------------------------------------------+-----------------+------------------------------------------+------------+---------------------------+
>>
>> | TABLE_CAT | TABLE_SCHEM | TABLE_NAME | NON_UNIQUE | INDEX_QUALIFIER | INDEX_NAME | TYPE | ORDINAL_POSIT |
>>
>> +------------------------------------------+------------------------------------------+------------------------------------------+------------------------------------------+-----------------+------------------------------------------+------------+---------------------------+
>>
>> | | LOADTEST | TESTING | true | | IDX_LASTNAME | 3 | 1 |
>>
>> | | LOADTEST | TESTING | true | | IDX_LASTNAME | 3 | 2 |
>>
>> +------------------------------------------+------------------------------------------+------------------------------------------+------------------------------------------+-----------------+------------------------------------------+------------+---------------------------+
>>
>> 0: jdbc:phoenix:172.31.31.143>
>>
>>
>> Is this a bug? I don't really see how the index can be in a
>> usable state. If I 'explain' a query that ought to use the
>> index, it tells me it's going to do a full scan anyway.
>>
>> James
>>
>>
>
>
Re: index creation partly succeeds if it times out
Posted by "rajeshbabu@apache.org" <ch...@gmail.com>.
James,
It should be in building state. Can you check what's the state of it?
Thanks,
Rajeshbabu.
On Fri, Sep 11, 2015 at 4:04 PM, James Heather <ja...@mendeley.com>
wrote:
> Hi Rajeshbabu,
>
> Thanks--yes--I've done that. I'm now recreating the index with a long
> timeout.
>
> I reported it because it seemed to me to be a bug: Phoenix thinks that the
> index is there, but it's not. It ought to get cleaned up after a timeout.
>
> James
>
>
> On 11/09/15 11:32, rajeshbabu@apache.org wrote:
>
> Hi James,
>
> You can drop the partially created index and try following steps
>
> 1) Add the following property to hbase-site.xml at phoenix client side.
> <property>
> <name>phoenix.query.timeoutMs</name>
> <value>double of default value</value>
> </property>
> 2) Export the HBASE_CONF_PATH with the configuration directory where
> hbase-site.xml present.
> 3) then start sqlline.py command prompt
> 4) Then run create index query.
>
>
> Thanks,
> Rajeshbabu.
>
> On Fri, Sep 11, 2015 at 3:26 PM, James Heather <james.heather@mendeley.com
> > wrote:
>
>> I just tried to create an index on a column for a table with 200M rows.
>> Creating the index timed out:
>>
>> 0: jdbc:phoenix:172.31.31.143> CREATE INDEX idx_lastname ON loadtest.testing (lastname);
>>
>> Error: Operation timed out (state=TIM01,code=6000)
>>
>> java.sql.SQLTimeoutException: Operation timed out
>>
>> at org.apache.phoenix.exception.SQLExceptionCode$14.newException(SQLExceptionCode.java:314)
>>
>> at org.apache.phoenix.exception.SQLExceptionInfo.buildException(SQLExceptionInfo.java:133)
>>
>>
>> I bumped up the timeout and tried again, but it failed, and it tells me
>> the index already exists:
>>
>> 0: jdbc:phoenix:172.31.31.143> CREATE INDEX idx_lastname ON loadtest.testing (lastname);
>>
>> Error: ERROR 1013 (42M04): Table already exists. tableName=LOADTEST.IDX_LASTNAME (state=42M04,code=1013)
>>
>> org.apache.phoenix.schema.TableAlreadyExistsException: ERROR 1013 (42M04): Table already exists. tableName=LOADTEST.IDX_LASTNAME
>>
>> at org.apache.phoenix.schema.MetaDataClient.createTableInternal(MetaDataClient.java:1692)
>>
>> at org.apache.phoenix.schema.MetaDataClient.createIndex(MetaDataClient.java:1118)
>>
>> at org.apache.phoenix.compile.CreateIndexCompiler$1.execute(CreateIndexCompiler.java:95)
>>
>> at org.apache.phoenix.jdbc.PhoenixStatement$2.call(PhoenixStatement.java:280)
>>
>> at org.apache.phoenix.jdbc.PhoenixStatement$2.call(PhoenixStatement.java:272)
>>
>> at org.apache.phoenix.call.CallRunner.run(CallRunner.java:53)
>>
>> at org.apache.phoenix.jdbc.PhoenixStatement.executeMutation(PhoenixStatement.java:271)
>>
>> at org.apache.phoenix.jdbc.PhoenixStatement.execute(PhoenixStatement.java:1063)
>>
>> 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)
>>
>> 0: jdbc:phoenix:172.31.31.143> !indexes loadtest.testing
>>
>> +------------------------------------------+------------------------------------------+------------------------------------------+------------------------------------------+-----------------+------------------------------------------+------------+---------------------------+
>>
>> | TABLE_CAT | TABLE_SCHEM | TABLE_NAME | NON_UNIQUE | INDEX_QUALIFIER | INDEX_NAME | TYPE | ORDINAL_POSIT |
>>
>> +------------------------------------------+------------------------------------------+------------------------------------------+------------------------------------------+-----------------+------------------------------------------+------------+---------------------------+
>>
>> | | LOADTEST | TESTING | true | | IDX_LASTNAME | 3 | 1 |
>>
>> | | LOADTEST | TESTING | true | | IDX_LASTNAME | 3 | 2 |
>>
>> +------------------------------------------+------------------------------------------+------------------------------------------+------------------------------------------+-----------------+------------------------------------------+------------+---------------------------+
>>
>> 0: jdbc:phoenix:172.31.31.143>
>>
>>
>> Is this a bug? I don't really see how the index can be in a usable state.
>> If I 'explain' a query that ought to use the index, it tells me it's going
>> to do a full scan anyway.
>>
>> James
>>
>
>
>
Re: index creation partly succeeds if it times out
Posted by James Heather <ja...@mendeley.com>.
Hi Rajeshbabu,
Thanks--yes--I've done that. I'm now recreating the index with a long
timeout.
I reported it because it seemed to me to be a bug: Phoenix thinks that
the index is there, but it's not. It ought to get cleaned up after a
timeout.
James
On 11/09/15 11:32, rajeshbabu@apache.org wrote:
> Hi James,
>
> You can drop the partially created index and try following steps
>
> 1) Add the following property to hbase-site.xml at phoenix client side.
> <property>
> <name>phoenix.query.timeoutMs</name>
> <value>double of default value</value>
> </property>
> 2) Export the HBASE_CONF_PATH with the configuration directory where
> hbase-site.xml present.
> 3) then start sqlline.py command prompt
> 4) Then run create index query.
>
>
> Thanks,
> Rajeshbabu.
>
> On Fri, Sep 11, 2015 at 3:26 PM, James Heather
> <james.heather@mendeley.com <ma...@mendeley.com>> wrote:
>
> I just tried to create an index on a column for a table with 200M
> rows. Creating the index timed out:
>
> 0: jdbc:phoenix:172.31.31.143> CREATE INDEX idx_lastname ON loadtest.testing (lastname);
>
> Error: Operation timed out (state=TIM01,code=6000)
>
> java.sql.SQLTimeoutException: Operation timed out
>
> at org.apache.phoenix.exception.SQLExceptionCode$14.newException(SQLExceptionCode.java:314)
>
> at org.apache.phoenix.exception.SQLExceptionInfo.buildException(SQLExceptionInfo.java:133)
>
>
> I bumped up the timeout and tried again, but it failed, and it
> tells me the index already exists:
>
> 0: jdbc:phoenix:172.31.31.143> CREATE INDEX idx_lastname ON loadtest.testing (lastname);
>
> Error: ERROR 1013 (42M04): Table already exists. tableName=LOADTEST.IDX_LASTNAME (state=42M04,code=1013)
>
> org.apache.phoenix.schema.TableAlreadyExistsException: ERROR 1013 (42M04): Table already exists. tableName=LOADTEST.IDX_LASTNAME
>
> at org.apache.phoenix.schema.MetaDataClient.createTableInternal(MetaDataClient.java:1692)
>
> at org.apache.phoenix.schema.MetaDataClient.createIndex(MetaDataClient.java:1118)
>
> at org.apache.phoenix.compile.CreateIndexCompiler$1.execute(CreateIndexCompiler.java:95)
>
> at org.apache.phoenix.jdbc.PhoenixStatement$2.call(PhoenixStatement.java:280)
>
> at org.apache.phoenix.jdbc.PhoenixStatement$2.call(PhoenixStatement.java:272)
>
> at org.apache.phoenix.call.CallRunner.run(CallRunner.java:53)
>
> at org.apache.phoenix.jdbc.PhoenixStatement.executeMutation(PhoenixStatement.java:271)
>
> at org.apache.phoenix.jdbc.PhoenixStatement.execute(PhoenixStatement.java:1063)
>
> 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)
>
> 0: jdbc:phoenix:172.31.31.143> !indexes loadtest.testing
>
> +------------------------------------------+------------------------------------------+------------------------------------------+------------------------------------------+-----------------+------------------------------------------+------------+---------------------------+
>
> | TABLE_CAT | TABLE_SCHEM | TABLE_NAME | NON_UNIQUE | INDEX_QUALIFIER | INDEX_NAME | TYPE | ORDINAL_POSIT |
>
> +------------------------------------------+------------------------------------------+------------------------------------------+------------------------------------------+-----------------+------------------------------------------+------------+---------------------------+
>
> | | LOADTEST | TESTING | true | | IDX_LASTNAME | 3 | 1 |
>
> | | LOADTEST | TESTING | true | | IDX_LASTNAME | 3 | 2 |
>
> +------------------------------------------+------------------------------------------+------------------------------------------+------------------------------------------+-----------------+------------------------------------------+------------+---------------------------+
>
> 0: jdbc:phoenix:172.31.31.143>
>
>
> Is this a bug? I don't really see how the index can be in a usable
> state. If I 'explain' a query that ought to use the index, it
> tells me it's going to do a full scan anyway.
>
> James
>
>
Re: index creation partly succeeds if it times out
Posted by "rajeshbabu@apache.org" <ch...@gmail.com>.
Hi James,
You can drop the partially created index and try following steps
1) Add the following property to hbase-site.xml at phoenix client side.
<property>
<name>phoenix.query.timeoutMs</name>
<value>double of default value</value>
</property>
2) Export the HBASE_CONF_PATH with the configuration directory where
hbase-site.xml present.
3) then start sqlline.py command prompt
4) Then run create index query.
Thanks,
Rajeshbabu.
On Fri, Sep 11, 2015 at 3:26 PM, James Heather <ja...@mendeley.com>
wrote:
> I just tried to create an index on a column for a table with 200M rows.
> Creating the index timed out:
>
> 0: jdbc:phoenix:172.31.31.143> CREATE INDEX idx_lastname ON loadtest.testing (lastname);
>
> Error: Operation timed out (state=TIM01,code=6000)
>
> java.sql.SQLTimeoutException: Operation timed out
>
> at org.apache.phoenix.exception.SQLExceptionCode$14.newException(SQLExceptionCode.java:314)
>
> at org.apache.phoenix.exception.SQLExceptionInfo.buildException(SQLExceptionInfo.java:133)
>
>
> I bumped up the timeout and tried again, but it failed, and it tells me
> the index already exists:
>
> 0: jdbc:phoenix:172.31.31.143> CREATE INDEX idx_lastname ON loadtest.testing (lastname);
>
> Error: ERROR 1013 (42M04): Table already exists. tableName=LOADTEST.IDX_LASTNAME (state=42M04,code=1013)
>
> org.apache.phoenix.schema.TableAlreadyExistsException: ERROR 1013 (42M04): Table already exists. tableName=LOADTEST.IDX_LASTNAME
>
> at org.apache.phoenix.schema.MetaDataClient.createTableInternal(MetaDataClient.java:1692)
>
> at org.apache.phoenix.schema.MetaDataClient.createIndex(MetaDataClient.java:1118)
>
> at org.apache.phoenix.compile.CreateIndexCompiler$1.execute(CreateIndexCompiler.java:95)
>
> at org.apache.phoenix.jdbc.PhoenixStatement$2.call(PhoenixStatement.java:280)
>
> at org.apache.phoenix.jdbc.PhoenixStatement$2.call(PhoenixStatement.java:272)
>
> at org.apache.phoenix.call.CallRunner.run(CallRunner.java:53)
>
> at org.apache.phoenix.jdbc.PhoenixStatement.executeMutation(PhoenixStatement.java:271)
>
> at org.apache.phoenix.jdbc.PhoenixStatement.execute(PhoenixStatement.java:1063)
>
> 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)
>
> 0: jdbc:phoenix:172.31.31.143> !indexes loadtest.testing
>
> +------------------------------------------+------------------------------------------+------------------------------------------+------------------------------------------+-----------------+------------------------------------------+------------+---------------------------+
>
> | TABLE_CAT | TABLE_SCHEM | TABLE_NAME | NON_UNIQUE | INDEX_QUALIFIER | INDEX_NAME | TYPE | ORDINAL_POSIT |
>
> +------------------------------------------+------------------------------------------+------------------------------------------+------------------------------------------+-----------------+------------------------------------------+------------+---------------------------+
>
> | | LOADTEST | TESTING | true | | IDX_LASTNAME | 3 | 1 |
>
> | | LOADTEST | TESTING | true | | IDX_LASTNAME | 3 | 2 |
>
> +------------------------------------------+------------------------------------------+------------------------------------------+------------------------------------------+-----------------+------------------------------------------+------------+---------------------------+
>
> 0: jdbc:phoenix:172.31.31.143>
>
>
> Is this a bug? I don't really see how the index can be in a usable state.
> If I 'explain' a query that ought to use the index, it tells me it's going
> to do a full scan anyway.
>
> James
>