Index on SYSTEM.LOG failed

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

Index on SYSTEM.LOG failed

Aleksandr Saraseka-2
Hello. We're logging queries in Phoenix.
Main criteria can be a start_time (to investigate possible performance problems in some particular time).
Execution plan for the query shows full scan - that could cause problems with a lot of data^
explain select query from system.LOG order by start_time;
+------------------------------------------------------------+-----------------+----------------+--------------+
|                            PLAN                            | EST_BYTES_READ  | EST_ROWS_READ  | EST_INFO_TS  |
+------------------------------------------------------------+-----------------+----------------+--------------+
| CLIENT 32-CHUNK PARALLEL 32-WAY FULL SCAN OVER SYSTEM:LOG  | null            | null           | null         |
| CLIENT MERGE SORT                                          | null            | null           | null         |
+------------------------------------------------------------+-----------------+----------------+--------------+
2 rows selected

So I'm trying to create local index on start_time field, but getting an exception. Is this "by design" that you can not create index on SYSTEM tables or I need to do this in some another way ?
CREATE LOCAL INDEX "system_log_start_time_idx" ON SYSTEM.LOG ("START_TIME");
Error: Error -1 (00000) : Error while executing SQL "CREATE LOCAL INDEX "system_log_start_time_idx" ON SYSTEM.LOG ("START_TIME")": Remote driver error: IndexOutOfBoundsException: Index: 0 (state=00000,code=-1)
org.apache.calcite.avatica.AvaticaSqlException: Error -1 (00000) : Error while executing SQL "CREATE LOCAL INDEX "system_log_start_time_idx" ON SYSTEM.LOG ("START_TIME")": Remote driver error: IndexOutOfBoundsException: Index: 0
        at org.apache.phoenix.shaded.org.apache.calcite.avatica.Helper.createException(Helper.java:54)
        at org.apache.phoenix.shaded.org.apache.calcite.avatica.Helper.createException(Helper.java:41)
        at org.apache.phoenix.shaded.org.apache.calcite.avatica.AvaticaStatement.executeInternal(AvaticaStatement.java:163)
        at org.apache.phoenix.shaded.org.apache.calcite.avatica.AvaticaStatement.execute(AvaticaStatement.java:217)
        at sqlline.Commands.execute(Commands.java:822)
        at sqlline.Commands.sql(Commands.java:732)
        at sqlline.SqlLine.dispatch(SqlLine.java:813)
        at sqlline.SqlLine.begin(SqlLine.java:686)
        at sqlline.SqlLine.start(SqlLine.java:398)
        at sqlline.SqlLine.main(SqlLine.java:291)
        at org.apache.phoenix.queryserver.client.SqllineWrapper.main(SqllineWrapper.java:93)
java.lang.IllegalAccessError: org/apache/phoenix/shaded/org/apache/calcite/avatica/AvaticaSqlException$PrintStreamOrWriter
        at org.apache.calcite.avatica.AvaticaSqlException.printStackTrace(AvaticaSqlException.java:75)
        at java.lang.Throwable.printStackTrace(Throwable.java:634)
        at sqlline.SqlLine.handleSQLException(SqlLine.java:1540)
        at sqlline.SqlLine.handleException(SqlLine.java:1505)
        at sqlline.SqlLine.error(SqlLine.java:905)
        at sqlline.Commands.execute(Commands.java:860)
        at sqlline.Commands.sql(Commands.java:732)
        at sqlline.SqlLine.dispatch(SqlLine.java:813)
        at sqlline.SqlLine.begin(SqlLine.java:686)
        at sqlline.SqlLine.start(SqlLine.java:398)
        at sqlline.SqlLine.main(SqlLine.java:291)
        at org.apache.phoenix.queryserver.client.SqllineWrapper.main(SqllineWrapper.java:93)


--
Aleksandr Saraseka
DBA
<a href="tel:380997600401" style="color:#505050;text-decoration:none;font-family:Arial" target="_blank">380997600401
   [hidden email]    eztexting.com
Reply | Threaded
Open this post in threaded view
|

Re: Index on SYSTEM.LOG failed

Josh Elser-2
Do you have a mismatch of Phoenix thinclient jars and Phoenix
QueryServer versions?

You're getting a classpath-type error, not some Phoenix internal error.

On 10/24/19 10:01 AM, Aleksandr Saraseka wrote:

> Hello. We're logging queries in Phoenix.
> Main criteria can be a start_time (to investigate possible performance
> problems in some particular time).
> Execution plan for the query shows full scan - that could cause problems
> with a lot of data^
> explain select query from system.LOG order by start_time;
> +------------------------------------------------------------+-----------------+----------------+--------------+
> |                            PLAN                            |
> EST_BYTES_READ  | EST_ROWS_READ  | EST_INFO_TS  |
> +------------------------------------------------------------+-----------------+----------------+--------------+
> | CLIENT 32-CHUNK PARALLEL 32-WAY FULL SCAN OVER SYSTEM:LOG  | null    
>         | null           | null         |
> | CLIENT MERGE SORT                                          | null    
>         | null           | null         |
> +------------------------------------------------------------+-----------------+----------------+--------------+
> 2 rows selected
>
> So I'm trying to create local index on start_time field, but getting an
> exception. Is this "by design" that you can not create index on SYSTEM
> tables or I need to do this in some another way ?
> CREATE LOCAL INDEX "system_log_start_time_idx" ON SYSTEM.LOG ("START_TIME");
> Error: Error -1 (00000) : Error while executing SQL "CREATE LOCAL INDEX
> "system_log_start_time_idx" ON SYSTEM.LOG ("START_TIME")": Remote driver
> error: IndexOutOfBoundsException: Index: 0 (state=00000,code=-1)
> org.apache.calcite.avatica.AvaticaSqlException: Error -1 (00000) : Error
> while executing SQL "CREATE LOCAL INDEX "system_log_start_time_idx" ON
> SYSTEM.LOG ("START_TIME")": Remote driver error:
> IndexOutOfBoundsException: Index: 0
>          at
> org.apache.phoenix.shaded.org.apache.calcite.avatica.Helper.createException(Helper.java:54)
>          at
> org.apache.phoenix.shaded.org.apache.calcite.avatica.Helper.createException(Helper.java:41)
>          at
> org.apache.phoenix.shaded.org.apache.calcite.avatica.AvaticaStatement.executeInternal(AvaticaStatement.java:163)
>          at
> org.apache.phoenix.shaded.org.apache.calcite.avatica.AvaticaStatement.execute(AvaticaStatement.java:217)
>          at sqlline.Commands.execute(Commands.java:822)
>          at sqlline.Commands.sql(Commands.java:732)
>          at sqlline.SqlLine.dispatch(SqlLine.java:813)
>          at sqlline.SqlLine.begin(SqlLine.java:686)
>          at sqlline.SqlLine.start(SqlLine.java:398)
>          at sqlline.SqlLine.main(SqlLine.java:291)
>          at
> org.apache.phoenix.queryserver.client.SqllineWrapper.main(SqllineWrapper.java:93)
> java.lang.IllegalAccessError:
> org/apache/phoenix/shaded/org/apache/calcite/avatica/AvaticaSqlException$PrintStreamOrWriter
>          at
> org.apache.calcite.avatica.AvaticaSqlException.printStackTrace(AvaticaSqlException.java:75)
>          at java.lang.Throwable.printStackTrace(Throwable.java:634)
>          at sqlline.SqlLine.handleSQLException(SqlLine.java:1540)
>          at sqlline.SqlLine.handleException(SqlLine.java:1505)
>          at sqlline.SqlLine.error(SqlLine.java:905)
>          at sqlline.Commands.execute(Commands.java:860)
>          at sqlline.Commands.sql(Commands.java:732)
>          at sqlline.SqlLine.dispatch(SqlLine.java:813)
>          at sqlline.SqlLine.begin(SqlLine.java:686)
>          at sqlline.SqlLine.start(SqlLine.java:398)
>          at sqlline.SqlLine.main(SqlLine.java:291)
>          at
> org.apache.phoenix.queryserver.client.SqllineWrapper.main(SqllineWrapper.java:93)
>
>
> --
> Aleksandr Saraseka
> DBA
> 380997600401
> <tel:380997600401> *•* [hidden email]
> <mailto:[hidden email]> *•* eztexting.com
> <http://eztexting.com?utm_source=WiseStamp&utm_medium=email&utm_term=&utm_content=&utm_campaign=signature>
>
>
> <http://facebook.com/eztexting?utm_source=WiseStamp&utm_medium=email&utm_term=&utm_content=&utm_campaign=signature>
> <http://linkedin.com/company/eztexting/?utm_source=WiseStamp&utm_medium=email&utm_term=&utm_content=&utm_campaign=signature>
> <http://twitter.com/eztexting?utm_source=WiseStamp&utm_medium=email&utm_term=&utm_content=&utm_campaign=signature>
> <https://www.youtube.com/eztexting?utm_source=WiseStamp&utm_medium=email&utm_term=&utm_content=&utm_campaign=signature>
> <https://www.instagram.com/ez_texting/?utm_source=WiseStamp&utm_medium=email&utm_term=&utm_content=&utm_campaign=signature>
> <https://www.facebook.com/alex.saraseka?utm_source=WiseStamp&utm_medium=email&utm_term=&utm_content=&utm_campaign=signature>
> <https://www.linkedin.com/in/alexander-saraseka-32616076/?utm_source=WiseStamp&utm_medium=email&utm_term=&utm_content=&utm_campaign=signature>
>
Reply | Threaded
Open this post in threaded view
|

Re: Index on SYSTEM.LOG failed

Aleksandr Saraseka-2
Hello.
Indexes on other tables created without any problems.
0: jdbc:phoenix:thin:url=http://localhost:876> create local index alex_test_data_idx on alex.test (data);
No rows affected (10.93 seconds)
0: jdbc:phoenix:thin:url=http://localhost:876

On Thu, Oct 24, 2019 at 8:18 PM Josh Elser <[hidden email]> wrote:
Do you have a mismatch of Phoenix thinclient jars and Phoenix
QueryServer versions?

You're getting a classpath-type error, not some Phoenix internal error.

On 10/24/19 10:01 AM, Aleksandr Saraseka wrote:
> Hello. We're logging queries in Phoenix.
> Main criteria can be a start_time (to investigate possible performance
> problems in some particular time).
> Execution plan for the query shows full scan - that could cause problems
> with a lot of data^
> explain select query from system.LOG order by start_time;
> +------------------------------------------------------------+-----------------+----------------+--------------+
> |                            PLAN                            |
> EST_BYTES_READ  | EST_ROWS_READ  | EST_INFO_TS  |
> +------------------------------------------------------------+-----------------+----------------+--------------+
> | CLIENT 32-CHUNK PARALLEL 32-WAY FULL SCAN OVER SYSTEM:LOG  | null     
>         | null           | null         |
> | CLIENT MERGE SORT                                          | null     
>         | null           | null         |
> +------------------------------------------------------------+-----------------+----------------+--------------+
> 2 rows selected
>
> So I'm trying to create local index on start_time field, but getting an
> exception. Is this "by design" that you can not create index on SYSTEM
> tables or I need to do this in some another way ?
> CREATE LOCAL INDEX "system_log_start_time_idx" ON SYSTEM.LOG ("START_TIME");
> Error: Error -1 (00000) : Error while executing SQL "CREATE LOCAL INDEX
> "system_log_start_time_idx" ON SYSTEM.LOG ("START_TIME")": Remote driver
> error: IndexOutOfBoundsException: Index: 0 (state=00000,code=-1)
> org.apache.calcite.avatica.AvaticaSqlException: Error -1 (00000) : Error
> while executing SQL "CREATE LOCAL INDEX "system_log_start_time_idx" ON
> SYSTEM.LOG ("START_TIME")": Remote driver error:
> IndexOutOfBoundsException: Index: 0
>          at
> org.apache.phoenix.shaded.org.apache.calcite.avatica.Helper.createException(Helper.java:54)
>          at
> org.apache.phoenix.shaded.org.apache.calcite.avatica.Helper.createException(Helper.java:41)
>          at
> org.apache.phoenix.shaded.org.apache.calcite.avatica.AvaticaStatement.executeInternal(AvaticaStatement.java:163)
>          at
> org.apache.phoenix.shaded.org.apache.calcite.avatica.AvaticaStatement.execute(AvaticaStatement.java:217)
>          at sqlline.Commands.execute(Commands.java:822)
>          at sqlline.Commands.sql(Commands.java:732)
>          at sqlline.SqlLine.dispatch(SqlLine.java:813)
>          at sqlline.SqlLine.begin(SqlLine.java:686)
>          at sqlline.SqlLine.start(SqlLine.java:398)
>          at sqlline.SqlLine.main(SqlLine.java:291)
>          at
> org.apache.phoenix.queryserver.client.SqllineWrapper.main(SqllineWrapper.java:93)
> java.lang.IllegalAccessError:
> org/apache/phoenix/shaded/org/apache/calcite/avatica/AvaticaSqlException$PrintStreamOrWriter
>          at
> org.apache.calcite.avatica.AvaticaSqlException.printStackTrace(AvaticaSqlException.java:75)
>          at java.lang.Throwable.printStackTrace(Throwable.java:634)
>          at sqlline.SqlLine.handleSQLException(SqlLine.java:1540)
>          at sqlline.SqlLine.handleException(SqlLine.java:1505)
>          at sqlline.SqlLine.error(SqlLine.java:905)
>          at sqlline.Commands.execute(Commands.java:860)
>          at sqlline.Commands.sql(Commands.java:732)
>          at sqlline.SqlLine.dispatch(SqlLine.java:813)
>          at sqlline.SqlLine.begin(SqlLine.java:686)
>          at sqlline.SqlLine.start(SqlLine.java:398)
>          at sqlline.SqlLine.main(SqlLine.java:291)
>          at
> org.apache.phoenix.queryserver.client.SqllineWrapper.main(SqllineWrapper.java:93)
>
>
> --
>               Aleksandr Saraseka
> DBA
> 380997600401
> <tel:380997600401> *•* [hidden email]
> <mailto:[hidden email]> *•* eztexting.com
> <http://eztexting.com?utm_source=WiseStamp&utm_medium=email&utm_term=&utm_content=&utm_campaign=signature>
>
>
> <http://facebook.com/eztexting?utm_source=WiseStamp&utm_medium=email&utm_term=&utm_content=&utm_campaign=signature>
> <http://linkedin.com/company/eztexting/?utm_source=WiseStamp&utm_medium=email&utm_term=&utm_content=&utm_campaign=signature>
> <http://twitter.com/eztexting?utm_source=WiseStamp&utm_medium=email&utm_term=&utm_content=&utm_campaign=signature>
> <https://www.youtube.com/eztexting?utm_source=WiseStamp&utm_medium=email&utm_term=&utm_content=&utm_campaign=signature>
> <https://www.instagram.com/ez_texting/?utm_source=WiseStamp&utm_medium=email&utm_term=&utm_content=&utm_campaign=signature>
> <https://www.facebook.com/alex.saraseka?utm_source=WiseStamp&utm_medium=email&utm_term=&utm_content=&utm_campaign=signature>
> <https://www.linkedin.com/in/alexander-saraseka-32616076/?utm_source=WiseStamp&utm_medium=email&utm_term=&utm_content=&utm_campaign=signature>
>


--
Aleksandr Saraseka
DBA
<a href="tel:380997600401" style="color:rgb(80,80,80);text-decoration:none;font-family:Arial" target="_blank">380997600401
   [hidden email]    eztexting.com
Reply | Threaded
Open this post in threaded view
|

Re: Index on SYSTEM.LOG failed

Josh Elser-2
My question was not meant to imply that creating any index should fail
in the same manner as what you see here.

https://docs.oracle.com/javase/8/docs/api/java/lang/IllegalAccessError.html

An IllegalAccessError means that the PQS tried to access this class but
it failed (for some reason). Of course, the thin client is not going to
be trying to access code that it can't.

It could be that there is an issue in creating a local index against the
system.log table, but this real error is being lost because of your
classpath issue. Can't really say anything definitively with the
information you've provided.

On 10/25/19 8:21 AM, Aleksandr Saraseka wrote:

> Hello.
> Indexes on other tables created without any problems.
> 0: jdbc:phoenix:thin:url=http://localhost:876> create local index
> alex_test_data_idx on alex.test (data);
> No rows affected (10.93 seconds)
> 0: jdbc:phoenix:thin:url=http://localhost:876>
>
> On Thu, Oct 24, 2019 at 8:18 PM Josh Elser <[hidden email]
> <mailto:[hidden email]>> wrote:
>
>     Do you have a mismatch of Phoenix thinclient jars and Phoenix
>     QueryServer versions?
>
>     You're getting a classpath-type error, not some Phoenix internal error.
>
>     On 10/24/19 10:01 AM, Aleksandr Saraseka wrote:
>      > Hello. We're logging queries in Phoenix.
>      > Main criteria can be a start_time (to investigate possible
>     performance
>      > problems in some particular time).
>      > Execution plan for the query shows full scan - that could cause
>     problems
>      > with a lot of data^
>      > explain select query from system.LOG order by start_time;
>      >
>     +------------------------------------------------------------+-----------------+----------------+--------------+
>      > |                            PLAN                            |
>      > EST_BYTES_READ  | EST_ROWS_READ  | EST_INFO_TS  |
>      >
>     +------------------------------------------------------------+-----------------+----------------+--------------+
>      > | CLIENT 32-CHUNK PARALLEL 32-WAY FULL SCAN OVER SYSTEM:LOG  | null
>      >         | null           | null         |
>      > | CLIENT MERGE SORT                                          | null
>      >         | null           | null         |
>      >
>     +------------------------------------------------------------+-----------------+----------------+--------------+
>      > 2 rows selected
>      >
>      > So I'm trying to create local index on start_time field, but
>     getting an
>      > exception. Is this "by design" that you can not create index on
>     SYSTEM
>      > tables or I need to do this in some another way ?
>      > CREATE LOCAL INDEX "system_log_start_time_idx" ON SYSTEM.LOG
>     ("START_TIME");
>      > Error: Error -1 (00000) : Error while executing SQL "CREATE LOCAL
>     INDEX
>      > "system_log_start_time_idx" ON SYSTEM.LOG ("START_TIME")": Remote
>     driver
>      > error: IndexOutOfBoundsException: Index: 0 (state=00000,code=-1)
>      > org.apache.calcite.avatica.AvaticaSqlException: Error -1 (00000)
>     : Error
>      > while executing SQL "CREATE LOCAL INDEX
>     "system_log_start_time_idx" ON
>      > SYSTEM.LOG ("START_TIME")": Remote driver error:
>      > IndexOutOfBoundsException: Index: 0
>      >          at
>      > org.apache.phoenix.shaded.org
>     <http://org.apache.phoenix.shaded.org>.apache.calcite.avatica.Helper.createException(Helper.java:54)
>      >          at
>      > org.apache.phoenix.shaded.org
>     <http://org.apache.phoenix.shaded.org>.apache.calcite.avatica.Helper.createException(Helper.java:41)
>      >          at
>      > org.apache.phoenix.shaded.org
>     <http://org.apache.phoenix.shaded.org>.apache.calcite.avatica.AvaticaStatement.executeInternal(AvaticaStatement.java:163)
>      >          at
>      > org.apache.phoenix.shaded.org
>     <http://org.apache.phoenix.shaded.org>.apache.calcite.avatica.AvaticaStatement.execute(AvaticaStatement.java:217)
>      >          at sqlline.Commands.execute(Commands.java:822)
>      >          at sqlline.Commands.sql(Commands.java:732)
>      >          at sqlline.SqlLine.dispatch(SqlLine.java:813)
>      >          at sqlline.SqlLine.begin(SqlLine.java:686)
>      >          at sqlline.SqlLine.start(SqlLine.java:398)
>      >          at sqlline.SqlLine.main(SqlLine.java:291)
>      >          at
>      >
>     org.apache.phoenix.queryserver.client.SqllineWrapper.main(SqllineWrapper.java:93)
>      > java.lang.IllegalAccessError:
>      >
>     org/apache/phoenix/shaded/org/apache/calcite/avatica/AvaticaSqlException$PrintStreamOrWriter
>      >          at
>      >
>     org.apache.calcite.avatica.AvaticaSqlException.printStackTrace(AvaticaSqlException.java:75)
>      >          at java.lang.Throwable.printStackTrace(Throwable.java:634)
>      >          at sqlline.SqlLine.handleSQLException(SqlLine.java:1540)
>      >          at sqlline.SqlLine.handleException(SqlLine.java:1505)
>      >          at sqlline.SqlLine.error(SqlLine.java:905)
>      >          at sqlline.Commands.execute(Commands.java:860)
>      >          at sqlline.Commands.sql(Commands.java:732)
>      >          at sqlline.SqlLine.dispatch(SqlLine.java:813)
>      >          at sqlline.SqlLine.begin(SqlLine.java:686)
>      >          at sqlline.SqlLine.start(SqlLine.java:398)
>      >          at sqlline.SqlLine.main(SqlLine.java:291)
>      >          at
>      >
>     org.apache.phoenix.queryserver.client.SqllineWrapper.main(SqllineWrapper.java:93)
>      >
>      >
>      > --
>      >               Aleksandr Saraseka
>      > DBA
>      > 380997600401
>      > <tel:380997600401> *•* [hidden email]
>     <mailto:[hidden email]>
>      > <mailto:[hidden email] <mailto:[hidden email]>>
>     *•* eztexting.com <http://eztexting.com>
>      >
>     <http://eztexting.com?utm_source=WiseStamp&utm_medium=email&utm_term=&utm_content=&utm_campaign=signature>
>
>      >
>      >
>      >
>     <http://facebook.com/eztexting?utm_source=WiseStamp&utm_medium=email&utm_term=&utm_content=&utm_campaign=signature>
>
>      >
>     <http://linkedin.com/company/eztexting/?utm_source=WiseStamp&utm_medium=email&utm_term=&utm_content=&utm_campaign=signature>
>
>      >
>     <http://twitter.com/eztexting?utm_source=WiseStamp&utm_medium=email&utm_term=&utm_content=&utm_campaign=signature>
>
>      >
>     <https://www.youtube.com/eztexting?utm_source=WiseStamp&utm_medium=email&utm_term=&utm_content=&utm_campaign=signature>
>
>      >
>     <https://www.instagram.com/ez_texting/?utm_source=WiseStamp&utm_medium=email&utm_term=&utm_content=&utm_campaign=signature>
>
>      >
>     <https://www.facebook.com/alex.saraseka?utm_source=WiseStamp&utm_medium=email&utm_term=&utm_content=&utm_campaign=signature>
>
>      >
>     <https://www.linkedin.com/in/alexander-saraseka-32616076/?utm_source=WiseStamp&utm_medium=email&utm_term=&utm_content=&utm_campaign=signature>
>      >
>
>
>
> --
> Aleksandr Saraseka
> DBA
> 380997600401
> <tel:380997600401> *•* [hidden email]
> <mailto:[hidden email]> *•* eztexting.com
> <http://eztexting.com?utm_source=WiseStamp&utm_medium=email&utm_term=&utm_content=&utm_campaign=signature>
>
>
> <http://facebook.com/eztexting?utm_source=WiseStamp&utm_medium=email&utm_term=&utm_content=&utm_campaign=signature>
> <http://linkedin.com/company/eztexting/?utm_source=WiseStamp&utm_medium=email&utm_term=&utm_content=&utm_campaign=signature>
> <http://twitter.com/eztexting?utm_source=WiseStamp&utm_medium=email&utm_term=&utm_content=&utm_campaign=signature>
> <https://www.youtube.com/eztexting?utm_source=WiseStamp&utm_medium=email&utm_term=&utm_content=&utm_campaign=signature>
> <https://www.instagram.com/ez_texting/?utm_source=WiseStamp&utm_medium=email&utm_term=&utm_content=&utm_campaign=signature>
> <https://www.facebook.com/alex.saraseka?utm_source=WiseStamp&utm_medium=email&utm_term=&utm_content=&utm_campaign=signature>
> <https://www.linkedin.com/in/alexander-saraseka-32616076/?utm_source=WiseStamp&utm_medium=email&utm_term=&utm_content=&utm_campaign=signature>
>