Issue in Alter Table - Drop Column functionality

classic Classic list List threaded Threaded
8 messages Options
Shravya Nethula Shravya Nethula
Reply | Threaded
Open this post in threaded view
|

Issue in Alter Table - Drop Column functionality

Hi,

When I am trying the following query from GridGain, it works as expected.
ALTER TABLE person DROP COLUMN (age)

But when I try to execute the same query as a thick client with following Java code, its throwing IgniteSQLException.
Java Code:
String sql = "ALTER TABLE person DROP COLUMN (age)";
FieldsQueryCursor cursor = cache.query(new SqlFieldsQuery(sql));

Output:
javax.cache.CacheException
    at org.apache.ignite.internal.processors.cache.IgniteCacheProxyImpl.query(IgniteCacheProxyImpl.java:817)
    at org.apache.ignite.internal.processors.cache.IgniteCacheProxyImpl.query(IgniteCacheProxyImpl.java:750)
    at org.apache.ignite.internal.processors.cache.GatewayProtectedCacheProxy.query(GatewayProtectedCacheProxy.java:424)
    at net.aline.cloudedh.base.database.IgniteTable._alterTable(IgniteTable.java:138)
    at net.aline.cloudedh.base.database.BigTable.alter(BigTable.java:697)
    at net.aline.cloudedh.base.framework.DACEngine.alterTable(DACEngine.java:1015)
    at net.aline.cloudedh.base.framework.DACOperationsTest.main(DACOperationsTest.java:89)
Caused by: class org.apache.ignite.internal.processors.query.IgniteSQLException: null
    at org.apache.ignite.internal.processors.query.h2.CommandProcessor.runCommandH2(CommandProcessor.java:888)
    at org.apache.ignite.internal.processors.query.h2.CommandProcessor.runCommand(CommandProcessor.java:418)
    at org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.executeCommand(IgniteH2Indexing.java:1048)
    at org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.querySqlFields(IgniteH2Indexing.java:1130)
    at org.apache.ignite.internal.processors.query.GridQueryProcessor$3.applyx(GridQueryProcessor.java:2406)
    at org.apache.ignite.internal.processors.query.GridQueryProcessor$3.applyx(GridQueryProcessor.java:2402)
    at org.apache.ignite.internal.util.lang.IgniteOutClosureX.apply(IgniteOutClosureX.java:36)
    at org.apache.ignite.internal.processors.query.GridQueryProcessor.executeQuery(GridQueryProcessor.java:2919)
    at org.apache.ignite.internal.processors.query.GridQueryProcessor.lambda$querySqlFields$1(GridQueryProcessor.java:2422)
    at org.apache.ignite.internal.processors.query.GridQueryProcessor.executeQuerySafe(GridQueryProcessor.java:2460)
    at org.apache.ignite.internal.processors.query.GridQueryProcessor.querySqlFields(GridQueryProcessor.java:2396)
    at org.apache.ignite.internal.processors.query.GridQueryProcessor.querySqlFields(GridQueryProcessor.java:2323)
    at org.apache.ignite.internal.processors.cache.IgniteCacheProxyImpl.query(IgniteCacheProxyImpl.java:802)
    ... 6 more
Caused by: java.lang.NullPointerException
    at org.apache.ignite.internal.processors.query.h2.CommandProcessor.runCommandH2(CommandProcessor.java:834)
    ... 18 more


On the other hand, ALTER TABLE ADD COLUMN functionality is working in both GridGain and also through thick client Java code.
Why is it so? Is there any parameter or configurations that are missing?
Please kindly let me know if you need any more details regarding the failure scenario.




Regards,

Shravya Nethula,

BigData Developer,


Hyderabad.

Denis Mekhanikov Denis Mekhanikov
Reply | Threaded
Open this post in threaded view
|

Re: Issue in Alter Table - Drop Column functionality

Does the DROP TABLE statement work if you specify the schema name explicitly?
For example, if the table is in the PUBLIC schema, try running the following query: ALTER TABLE PUBLIC.person DROP COLUMN (age)

Denis

ср, 2 сент. 2020 г. в 20:27, Shravya Nethula <[hidden email]>:
Hi,

When I am trying the following query from GridGain, it works as expected.
ALTER TABLE person DROP COLUMN (age)

But when I try to execute the same query as a thick client with following Java code, its throwing IgniteSQLException.
Java Code:
String sql = "ALTER TABLE person DROP COLUMN (age)";
FieldsQueryCursor cursor = cache.query(new SqlFieldsQuery(sql));

Output:
javax.cache.CacheException
    at org.apache.ignite.internal.processors.cache.IgniteCacheProxyImpl.query(IgniteCacheProxyImpl.java:817)
    at org.apache.ignite.internal.processors.cache.IgniteCacheProxyImpl.query(IgniteCacheProxyImpl.java:750)
    at org.apache.ignite.internal.processors.cache.GatewayProtectedCacheProxy.query(GatewayProtectedCacheProxy.java:424)
    at net.aline.cloudedh.base.database.IgniteTable._alterTable(IgniteTable.java:138)
    at net.aline.cloudedh.base.database.BigTable.alter(BigTable.java:697)
    at net.aline.cloudedh.base.framework.DACEngine.alterTable(DACEngine.java:1015)
    at net.aline.cloudedh.base.framework.DACOperationsTest.main(DACOperationsTest.java:89)
Caused by: class org.apache.ignite.internal.processors.query.IgniteSQLException: null
    at org.apache.ignite.internal.processors.query.h2.CommandProcessor.runCommandH2(CommandProcessor.java:888)
    at org.apache.ignite.internal.processors.query.h2.CommandProcessor.runCommand(CommandProcessor.java:418)
    at org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.executeCommand(IgniteH2Indexing.java:1048)
    at org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.querySqlFields(IgniteH2Indexing.java:1130)
    at org.apache.ignite.internal.processors.query.GridQueryProcessor$3.applyx(GridQueryProcessor.java:2406)
    at org.apache.ignite.internal.processors.query.GridQueryProcessor$3.applyx(GridQueryProcessor.java:2402)
    at org.apache.ignite.internal.util.lang.IgniteOutClosureX.apply(IgniteOutClosureX.java:36)
    at org.apache.ignite.internal.processors.query.GridQueryProcessor.executeQuery(GridQueryProcessor.java:2919)
    at org.apache.ignite.internal.processors.query.GridQueryProcessor.lambda$querySqlFields$1(GridQueryProcessor.java:2422)
    at org.apache.ignite.internal.processors.query.GridQueryProcessor.executeQuerySafe(GridQueryProcessor.java:2460)
    at org.apache.ignite.internal.processors.query.GridQueryProcessor.querySqlFields(GridQueryProcessor.java:2396)
    at org.apache.ignite.internal.processors.query.GridQueryProcessor.querySqlFields(GridQueryProcessor.java:2323)
    at org.apache.ignite.internal.processors.cache.IgniteCacheProxyImpl.query(IgniteCacheProxyImpl.java:802)
    ... 6 more
Caused by: java.lang.NullPointerException
    at org.apache.ignite.internal.processors.query.h2.CommandProcessor.runCommandH2(CommandProcessor.java:834)
    ... 18 more


On the other hand, ALTER TABLE ADD COLUMN functionality is working in both GridGain and also through thick client Java code.
Why is it so? Is there any parameter or configurations that are missing?
Please kindly let me know if you need any more details regarding the failure scenario.




Regards,

Shravya Nethula,

BigData Developer,


Hyderabad.

Denis Mekhanikov Denis Mekhanikov
Reply | Threaded
Open this post in threaded view
|

Re: Issue in Alter Table - Drop Column functionality

I mean, DROP COLUMN, not DROP TABLE.

Denis

чт, 3 сент. 2020 г. в 11:46, Denis Mekhanikov <[hidden email]>:
Does the DROP TABLE statement work if you specify the schema name explicitly?
For example, if the table is in the PUBLIC schema, try running the following query: ALTER TABLE PUBLIC.person DROP COLUMN (age)

Denis

ср, 2 сент. 2020 г. в 20:27, Shravya Nethula <[hidden email]>:
Hi,

When I am trying the following query from GridGain, it works as expected.
ALTER TABLE person DROP COLUMN (age)

But when I try to execute the same query as a thick client with following Java code, its throwing IgniteSQLException.
Java Code:
String sql = "ALTER TABLE person DROP COLUMN (age)";
FieldsQueryCursor cursor = cache.query(new SqlFieldsQuery(sql));

Output:
javax.cache.CacheException
    at org.apache.ignite.internal.processors.cache.IgniteCacheProxyImpl.query(IgniteCacheProxyImpl.java:817)
    at org.apache.ignite.internal.processors.cache.IgniteCacheProxyImpl.query(IgniteCacheProxyImpl.java:750)
    at org.apache.ignite.internal.processors.cache.GatewayProtectedCacheProxy.query(GatewayProtectedCacheProxy.java:424)
    at net.aline.cloudedh.base.database.IgniteTable._alterTable(IgniteTable.java:138)
    at net.aline.cloudedh.base.database.BigTable.alter(BigTable.java:697)
    at net.aline.cloudedh.base.framework.DACEngine.alterTable(DACEngine.java:1015)
    at net.aline.cloudedh.base.framework.DACOperationsTest.main(DACOperationsTest.java:89)
Caused by: class org.apache.ignite.internal.processors.query.IgniteSQLException: null
    at org.apache.ignite.internal.processors.query.h2.CommandProcessor.runCommandH2(CommandProcessor.java:888)
    at org.apache.ignite.internal.processors.query.h2.CommandProcessor.runCommand(CommandProcessor.java:418)
    at org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.executeCommand(IgniteH2Indexing.java:1048)
    at org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.querySqlFields(IgniteH2Indexing.java:1130)
    at org.apache.ignite.internal.processors.query.GridQueryProcessor$3.applyx(GridQueryProcessor.java:2406)
    at org.apache.ignite.internal.processors.query.GridQueryProcessor$3.applyx(GridQueryProcessor.java:2402)
    at org.apache.ignite.internal.util.lang.IgniteOutClosureX.apply(IgniteOutClosureX.java:36)
    at org.apache.ignite.internal.processors.query.GridQueryProcessor.executeQuery(GridQueryProcessor.java:2919)
    at org.apache.ignite.internal.processors.query.GridQueryProcessor.lambda$querySqlFields$1(GridQueryProcessor.java:2422)
    at org.apache.ignite.internal.processors.query.GridQueryProcessor.executeQuerySafe(GridQueryProcessor.java:2460)
    at org.apache.ignite.internal.processors.query.GridQueryProcessor.querySqlFields(GridQueryProcessor.java:2396)
    at org.apache.ignite.internal.processors.query.GridQueryProcessor.querySqlFields(GridQueryProcessor.java:2323)
    at org.apache.ignite.internal.processors.cache.IgniteCacheProxyImpl.query(IgniteCacheProxyImpl.java:802)
    ... 6 more
Caused by: java.lang.NullPointerException
    at org.apache.ignite.internal.processors.query.h2.CommandProcessor.runCommandH2(CommandProcessor.java:834)
    ... 18 more


On the other hand, ALTER TABLE ADD COLUMN functionality is working in both GridGain and also through thick client Java code.
Why is it so? Is there any parameter or configurations that are missing?
Please kindly let me know if you need any more details regarding the failure scenario.




Regards,

Shravya Nethula,

BigData Developer,


Hyderabad.

Shravya Nethula Shravya Nethula
Reply | Threaded
Open this post in threaded view
|

Re: Issue in Alter Table - Drop Column functionality

Hi,

I have tried "ALTER TABLE PUBLIC.person DROP COLUMN (age)" query as well.
I am getting the same error message.


Regards,

Shravya Nethula,

BigData Developer,


Hyderabad.


From: Denis Mekhanikov <[hidden email]>
Sent: Thursday, September 3, 2020 2:17 PM
To: user <[hidden email]>
Subject: Re: Issue in Alter Table - Drop Column functionality
 
I mean, DROP COLUMN, not DROP TABLE.

Denis

чт, 3 сент. 2020 г. в 11:46, Denis Mekhanikov <[hidden email]>:
Does the DROP TABLE statement work if you specify the schema name explicitly?
For example, if the table is in the PUBLIC schema, try running the following query: ALTER TABLE PUBLIC.person DROP COLUMN (age)

Denis

ср, 2 сент. 2020 г. в 20:27, Shravya Nethula <[hidden email]>:
Hi,

When I am trying the following query from GridGain, it works as expected.
ALTER TABLE person DROP COLUMN (age)

But when I try to execute the same query as a thick client with following Java code, its throwing IgniteSQLException.
Java Code:
String sql = "ALTER TABLE person DROP COLUMN (age)";
FieldsQueryCursor cursor = cache.query(new SqlFieldsQuery(sql));

Output:
javax.cache.CacheException
    at org.apache.ignite.internal.processors.cache.IgniteCacheProxyImpl.query(IgniteCacheProxyImpl.java:817)
    at org.apache.ignite.internal.processors.cache.IgniteCacheProxyImpl.query(IgniteCacheProxyImpl.java:750)
    at org.apache.ignite.internal.processors.cache.GatewayProtectedCacheProxy.query(GatewayProtectedCacheProxy.java:424)
    at net.aline.cloudedh.base.database.IgniteTable._alterTable(IgniteTable.java:138)
    at net.aline.cloudedh.base.database.BigTable.alter(BigTable.java:697)
    at net.aline.cloudedh.base.framework.DACEngine.alterTable(DACEngine.java:1015)
    at net.aline.cloudedh.base.framework.DACOperationsTest.main(DACOperationsTest.java:89)
Caused by: class org.apache.ignite.internal.processors.query.IgniteSQLException: null
    at org.apache.ignite.internal.processors.query.h2.CommandProcessor.runCommandH2(CommandProcessor.java:888)
    at org.apache.ignite.internal.processors.query.h2.CommandProcessor.runCommand(CommandProcessor.java:418)
    at org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.executeCommand(IgniteH2Indexing.java:1048)
    at org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.querySqlFields(IgniteH2Indexing.java:1130)
    at org.apache.ignite.internal.processors.query.GridQueryProcessor$3.applyx(GridQueryProcessor.java:2406)
    at org.apache.ignite.internal.processors.query.GridQueryProcessor$3.applyx(GridQueryProcessor.java:2402)
    at org.apache.ignite.internal.util.lang.IgniteOutClosureX.apply(IgniteOutClosureX.java:36)
    at org.apache.ignite.internal.processors.query.GridQueryProcessor.executeQuery(GridQueryProcessor.java:2919)
    at org.apache.ignite.internal.processors.query.GridQueryProcessor.lambda$querySqlFields$1(GridQueryProcessor.java:2422)
    at org.apache.ignite.internal.processors.query.GridQueryProcessor.executeQuerySafe(GridQueryProcessor.java:2460)
    at org.apache.ignite.internal.processors.query.GridQueryProcessor.querySqlFields(GridQueryProcessor.java:2396)
    at org.apache.ignite.internal.processors.query.GridQueryProcessor.querySqlFields(GridQueryProcessor.java:2323)
    at org.apache.ignite.internal.processors.cache.IgniteCacheProxyImpl.query(IgniteCacheProxyImpl.java:802)
    ... 6 more
Caused by: java.lang.NullPointerException
    at org.apache.ignite.internal.processors.query.h2.CommandProcessor.runCommandH2(CommandProcessor.java:834)
    ... 18 more


On the other hand, ALTER TABLE ADD COLUMN functionality is working in both GridGain and also through thick client Java code.
Why is it so? Is there any parameter or configurations that are missing?
Please kindly let me know if you need any more details regarding the failure scenario.




Regards,

Shravya Nethula,

BigData Developer,


Hyderabad.

Shravya Nethula Shravya Nethula
Reply | Threaded
Open this post in threaded view
|

Re: Issue in Alter Table - Drop Column functionality

In reply to this post by Denis Mekhanikov
Hi,

I have tried "ALTER TABLE PUBLIC.person DROP COLUMN (age)" query as well.
I am getting the same error message.

Regards,

Shravya Nethula,

BigData Developer,


Hyderabad.


From: Denis Mekhanikov <[hidden email]>
Sent: Thursday, September 3, 2020 2:17 PM
To: user <[hidden email]>
Subject: Re: Issue in Alter Table - Drop Column functionality
 
I mean, DROP COLUMN, not DROP TABLE.

Denis

чт, 3 сент. 2020 г. в 11:46, Denis Mekhanikov <[hidden email]>:
Does the DROP TABLE statement work if you specify the schema name explicitly?
For example, if the table is in the PUBLIC schema, try running the following query: ALTER TABLE PUBLIC.person DROP COLUMN (age)

Denis

ср, 2 сент. 2020 г. в 20:27, Shravya Nethula <[hidden email]>:
Hi,

When I am trying the following query from GridGain, it works as expected.
ALTER TABLE person DROP COLUMN (age)

But when I try to execute the same query as a thick client with following Java code, its throwing IgniteSQLException.
Java Code:
String sql = "ALTER TABLE person DROP COLUMN (age)";
FieldsQueryCursor cursor = cache.query(new SqlFieldsQuery(sql));

Output:
javax.cache.CacheException
    at org.apache.ignite.internal.processors.cache.IgniteCacheProxyImpl.query(IgniteCacheProxyImpl.java:817)
    at org.apache.ignite.internal.processors.cache.IgniteCacheProxyImpl.query(IgniteCacheProxyImpl.java:750)
    at org.apache.ignite.internal.processors.cache.GatewayProtectedCacheProxy.query(GatewayProtectedCacheProxy.java:424)
    at net.aline.cloudedh.base.database.IgniteTable._alterTable(IgniteTable.java:138)
    at net.aline.cloudedh.base.database.BigTable.alter(BigTable.java:697)
    at net.aline.cloudedh.base.framework.DACEngine.alterTable(DACEngine.java:1015)
    at net.aline.cloudedh.base.framework.DACOperationsTest.main(DACOperationsTest.java:89)
Caused by: class org.apache.ignite.internal.processors.query.IgniteSQLException: null
    at org.apache.ignite.internal.processors.query.h2.CommandProcessor.runCommandH2(CommandProcessor.java:888)
    at org.apache.ignite.internal.processors.query.h2.CommandProcessor.runCommand(CommandProcessor.java:418)
    at org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.executeCommand(IgniteH2Indexing.java:1048)
    at org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.querySqlFields(IgniteH2Indexing.java:1130)
    at org.apache.ignite.internal.processors.query.GridQueryProcessor$3.applyx(GridQueryProcessor.java:2406)
    at org.apache.ignite.internal.processors.query.GridQueryProcessor$3.applyx(GridQueryProcessor.java:2402)
    at org.apache.ignite.internal.util.lang.IgniteOutClosureX.apply(IgniteOutClosureX.java:36)
    at org.apache.ignite.internal.processors.query.GridQueryProcessor.executeQuery(GridQueryProcessor.java:2919)
    at org.apache.ignite.internal.processors.query.GridQueryProcessor.lambda$querySqlFields$1(GridQueryProcessor.java:2422)
    at org.apache.ignite.internal.processors.query.GridQueryProcessor.executeQuerySafe(GridQueryProcessor.java:2460)
    at org.apache.ignite.internal.processors.query.GridQueryProcessor.querySqlFields(GridQueryProcessor.java:2396)
    at org.apache.ignite.internal.processors.query.GridQueryProcessor.querySqlFields(GridQueryProcessor.java:2323)
    at org.apache.ignite.internal.processors.cache.IgniteCacheProxyImpl.query(IgniteCacheProxyImpl.java:802)
    ... 6 more
Caused by: java.lang.NullPointerException
    at org.apache.ignite.internal.processors.query.h2.CommandProcessor.runCommandH2(CommandProcessor.java:834)
    ... 18 more


On the other hand, ALTER TABLE ADD COLUMN functionality is working in both GridGain and also through thick client Java code.
Why is it so? Is there any parameter or configurations that are missing?
Please kindly let me know if you need any more details regarding the failure scenario.




Regards,

Shravya Nethula,

BigData Developer,


Hyderabad.

ilya.kasnacheev ilya.kasnacheev
Reply | Threaded
Open this post in threaded view
|

Re: Issue in Alter Table - Drop Column functionality

Hello!

I have just validated it on 2.8.1. Seems to work!

~/Downloads/apache-ignite-2.8.1-bin% bin/sqlline.sh 
sqlline version 1.3.0
sqlline> !connect jdbc:ignite:cfg://config/default-config.xml
[12:33:33]    __________  ________________  
[12:33:33]   /  _/ ___/ |/ /  _/_  __/ __/  
[12:33:33]  _/ // (7 7    // /  / / / _/    
[12:33:33] /___/\___/_/|_/___/ /_/ /___/   
[12:33:33]  
[12:33:33] ver. 2.8.1#20200521-sha1:86422096
[12:33:33] 2020 Copyright(C) Apache Software Foundation
[12:33:33]  
[12:33:38] Topology snapshot [ver=2, locNode=16da55e5, servers=1, clients=1, state=ACTIVE, CPUs=4, offheap=3.1GB, heap=6.8GB]
[12:33:38]   ^-- Baseline [id=0, size=1, online=1, offline=0]
Error: Transactions are not supported. (state=,code=0)
0: jdbc:ignite:cfg://config/default-config.xm> CREATE TABLE person (id INT PRIMARY KEY, name VARCHAR, age INT);
0: jdbc:ignite:cfg://config/default-config.xm> INSERT INTO person (id, name, age) VALUES (1, 'Vassisualy', 45);
0: jdbc:ignite:cfg://config/default-config.xm> ALTER TABLE person DROP COLUMN age;
0: jdbc:ignite:cfg://config/default-config.xm> SELECT * FROM person;
+--------------------------------+--------------------------------+
|               ID               |              NAME              |
+--------------------------------+--------------------------------+
| 1                              | Vassisualy                     |
+--------------------------------+--------------------------------+
1 row selected (0,059 seconds)

Do you have steps to reproduce?

Regards,
--
Ilya Kasnacheev


чт, 3 сент. 2020 г. в 20:46, Shravya Nethula <[hidden email]>:
Hi,

I have tried "ALTER TABLE PUBLIC.person DROP COLUMN (age)" query as well.
I am getting the same error message.

Regards,

Shravya Nethula,

BigData Developer,


Hyderabad.


From: Denis Mekhanikov <[hidden email]>
Sent: Thursday, September 3, 2020 2:17 PM
To: user <[hidden email]>
Subject: Re: Issue in Alter Table - Drop Column functionality
 
I mean, DROP COLUMN, not DROP TABLE.

Denis

чт, 3 сент. 2020 г. в 11:46, Denis Mekhanikov <[hidden email]>:
Does the DROP TABLE statement work if you specify the schema name explicitly?
For example, if the table is in the PUBLIC schema, try running the following query: ALTER TABLE PUBLIC.person DROP COLUMN (age)

Denis

ср, 2 сент. 2020 г. в 20:27, Shravya Nethula <[hidden email]>:
Hi,

When I am trying the following query from GridGain, it works as expected.
ALTER TABLE person DROP COLUMN (age)

But when I try to execute the same query as a thick client with following Java code, its throwing IgniteSQLException.
Java Code:
String sql = "ALTER TABLE person DROP COLUMN (age)";
FieldsQueryCursor cursor = cache.query(new SqlFieldsQuery(sql));

Output:
javax.cache.CacheException
    at org.apache.ignite.internal.processors.cache.IgniteCacheProxyImpl.query(IgniteCacheProxyImpl.java:817)
    at org.apache.ignite.internal.processors.cache.IgniteCacheProxyImpl.query(IgniteCacheProxyImpl.java:750)
    at org.apache.ignite.internal.processors.cache.GatewayProtectedCacheProxy.query(GatewayProtectedCacheProxy.java:424)
    at net.aline.cloudedh.base.database.IgniteTable._alterTable(IgniteTable.java:138)
    at net.aline.cloudedh.base.database.BigTable.alter(BigTable.java:697)
    at net.aline.cloudedh.base.framework.DACEngine.alterTable(DACEngine.java:1015)
    at net.aline.cloudedh.base.framework.DACOperationsTest.main(DACOperationsTest.java:89)
Caused by: class org.apache.ignite.internal.processors.query.IgniteSQLException: null
    at org.apache.ignite.internal.processors.query.h2.CommandProcessor.runCommandH2(CommandProcessor.java:888)
    at org.apache.ignite.internal.processors.query.h2.CommandProcessor.runCommand(CommandProcessor.java:418)
    at org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.executeCommand(IgniteH2Indexing.java:1048)
    at org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.querySqlFields(IgniteH2Indexing.java:1130)
    at org.apache.ignite.internal.processors.query.GridQueryProcessor$3.applyx(GridQueryProcessor.java:2406)
    at org.apache.ignite.internal.processors.query.GridQueryProcessor$3.applyx(GridQueryProcessor.java:2402)
    at org.apache.ignite.internal.util.lang.IgniteOutClosureX.apply(IgniteOutClosureX.java:36)
    at org.apache.ignite.internal.processors.query.GridQueryProcessor.executeQuery(GridQueryProcessor.java:2919)
    at org.apache.ignite.internal.processors.query.GridQueryProcessor.lambda$querySqlFields$1(GridQueryProcessor.java:2422)
    at org.apache.ignite.internal.processors.query.GridQueryProcessor.executeQuerySafe(GridQueryProcessor.java:2460)
    at org.apache.ignite.internal.processors.query.GridQueryProcessor.querySqlFields(GridQueryProcessor.java:2396)
    at org.apache.ignite.internal.processors.query.GridQueryProcessor.querySqlFields(GridQueryProcessor.java:2323)
    at org.apache.ignite.internal.processors.cache.IgniteCacheProxyImpl.query(IgniteCacheProxyImpl.java:802)
    ... 6 more
Caused by: java.lang.NullPointerException
    at org.apache.ignite.internal.processors.query.h2.CommandProcessor.runCommandH2(CommandProcessor.java:834)
    ... 18 more


On the other hand, ALTER TABLE ADD COLUMN functionality is working in both GridGain and also through thick client Java code.
Why is it so? Is there any parameter or configurations that are missing?
Please kindly let me know if you need any more details regarding the failure scenario.




Regards,

Shravya Nethula,

BigData Developer,


Hyderabad.

Shravya Nethula Shravya Nethula
Reply | Threaded
Open this post in threaded view
|

Re: Issue in Alter Table - Drop Column functionality

Hi Ilya,

sqlLine is also a thin client if I am not wrong!
From thin clients like GridGain and sqlLine, its working as expected.

It is not working from thick client.
When I try to execute the same query as a thick client with following Java code, its throwing IgniteSQLException.
Java Code:
String sql = "ALTER TABLE person DROP COLUMN (age)";
FieldsQueryCursor cursor = cache.query(new SqlFieldsQuery(sql));


Regards,

Shravya Nethula,

BigData Developer,


Hyderabad.


From: Ilya Kasnacheev <[hidden email]>
Sent: Tuesday, September 15, 2020 3:07 PM
To: [hidden email] <[hidden email]>
Subject: Re: Issue in Alter Table - Drop Column functionality
 
Hello!

I have just validated it on 2.8.1. Seems to work!

~/Downloads/apache-ignite-2.8.1-bin% bin/sqlline.sh 
sqlline version 1.3.0
sqlline> !connect jdbc:ignite:cfg://config/default-config.xml
[12:33:33]    __________  ________________  
[12:33:33]   /  _/ ___/ |/ /  _/_  __/ __/  
[12:33:33]  _/ // (7 7    // /  / / / _/    
[12:33:33] /___/\___/_/|_/___/ /_/ /___/   
[12:33:33]  
[12:33:33] ver. 2.8.1#20200521-sha1:86422096
[12:33:33] 2020 Copyright(C) Apache Software Foundation
[12:33:33]  
[12:33:38] Topology snapshot [ver=2, locNode=16da55e5, servers=1, clients=1, state=ACTIVE, CPUs=4, offheap=3.1GB, heap=6.8GB]
[12:33:38]   ^-- Baseline [id=0, size=1, online=1, offline=0]
Error: Transactions are not supported. (state=,code=0)
0: jdbc:ignite:cfg://config/default-config.xm> CREATE TABLE person (id INT PRIMARY KEY, name VARCHAR, age INT);
0: jdbc:ignite:cfg://config/default-config.xm> INSERT INTO person (id, name, age) VALUES (1, 'Vassisualy', 45);
0: jdbc:ignite:cfg://config/default-config.xm> ALTER TABLE person DROP COLUMN age;
0: jdbc:ignite:cfg://config/default-config.xm> SELECT * FROM person;
+--------------------------------+--------------------------------+
|               ID               |              NAME              |
+--------------------------------+--------------------------------+
| 1                              | Vassisualy                     |
+--------------------------------+--------------------------------+
1 row selected (0,059 seconds)

Do you have steps to reproduce?

Regards,
--
Ilya Kasnacheev


чт, 3 сент. 2020 г. в 20:46, Shravya Nethula <[hidden email]>:
Hi,

I have tried "ALTER TABLE PUBLIC.person DROP COLUMN (age)" query as well.
I am getting the same error message.

Regards,

Shravya Nethula,

BigData Developer,


Hyderabad.


From: Denis Mekhanikov <[hidden email]>
Sent: Thursday, September 3, 2020 2:17 PM
To: user <[hidden email]>
Subject: Re: Issue in Alter Table - Drop Column functionality
 
I mean, DROP COLUMN, not DROP TABLE.

Denis

чт, 3 сент. 2020 г. в 11:46, Denis Mekhanikov <[hidden email]>:
Does the DROP TABLE statement work if you specify the schema name explicitly?
For example, if the table is in the PUBLIC schema, try running the following query: ALTER TABLE PUBLIC.person DROP COLUMN (age)

Denis

ср, 2 сент. 2020 г. в 20:27, Shravya Nethula <[hidden email]>:
Hi,

When I am trying the following query from GridGain, it works as expected.
ALTER TABLE person DROP COLUMN (age)

But when I try to execute the same query as a thick client with following Java code, its throwing IgniteSQLException.
Java Code:
String sql = "ALTER TABLE person DROP COLUMN (age)";
FieldsQueryCursor cursor = cache.query(new SqlFieldsQuery(sql));

Output:
javax.cache.CacheException
    at org.apache.ignite.internal.processors.cache.IgniteCacheProxyImpl.query(IgniteCacheProxyImpl.java:817)
    at org.apache.ignite.internal.processors.cache.IgniteCacheProxyImpl.query(IgniteCacheProxyImpl.java:750)
    at org.apache.ignite.internal.processors.cache.GatewayProtectedCacheProxy.query(GatewayProtectedCacheProxy.java:424)
    at net.aline.cloudedh.base.database.IgniteTable._alterTable(IgniteTable.java:138)
    at net.aline.cloudedh.base.database.BigTable.alter(BigTable.java:697)
    at net.aline.cloudedh.base.framework.DACEngine.alterTable(DACEngine.java:1015)
    at net.aline.cloudedh.base.framework.DACOperationsTest.main(DACOperationsTest.java:89)
Caused by: class org.apache.ignite.internal.processors.query.IgniteSQLException: null
    at org.apache.ignite.internal.processors.query.h2.CommandProcessor.runCommandH2(CommandProcessor.java:888)
    at org.apache.ignite.internal.processors.query.h2.CommandProcessor.runCommand(CommandProcessor.java:418)
    at org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.executeCommand(IgniteH2Indexing.java:1048)
    at org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.querySqlFields(IgniteH2Indexing.java:1130)
    at org.apache.ignite.internal.processors.query.GridQueryProcessor$3.applyx(GridQueryProcessor.java:2406)
    at org.apache.ignite.internal.processors.query.GridQueryProcessor$3.applyx(GridQueryProcessor.java:2402)
    at org.apache.ignite.internal.util.lang.IgniteOutClosureX.apply(IgniteOutClosureX.java:36)
    at org.apache.ignite.internal.processors.query.GridQueryProcessor.executeQuery(GridQueryProcessor.java:2919)
    at org.apache.ignite.internal.processors.query.GridQueryProcessor.lambda$querySqlFields$1(GridQueryProcessor.java:2422)
    at org.apache.ignite.internal.processors.query.GridQueryProcessor.executeQuerySafe(GridQueryProcessor.java:2460)
    at org.apache.ignite.internal.processors.query.GridQueryProcessor.querySqlFields(GridQueryProcessor.java:2396)
    at org.apache.ignite.internal.processors.query.GridQueryProcessor.querySqlFields(GridQueryProcessor.java:2323)
    at org.apache.ignite.internal.processors.cache.IgniteCacheProxyImpl.query(IgniteCacheProxyImpl.java:802)
    ... 6 more
Caused by: java.lang.NullPointerException
    at org.apache.ignite.internal.processors.query.h2.CommandProcessor.runCommandH2(CommandProcessor.java:834)
    ... 18 more


On the other hand, ALTER TABLE ADD COLUMN functionality is working in both GridGain and also through thick client Java code.
Why is it so? Is there any parameter or configurations that are missing?
Please kindly let me know if you need any more details regarding the failure scenario.




Regards,

Shravya Nethula,

BigData Developer,


Hyderabad.

ilya.kasnacheev ilya.kasnacheev
Reply | Threaded
Open this post in threaded view
|

Re: Issue in Alter Table - Drop Column functionality

Hello!

Sqlline can use both thin JDBC and thick JDBC driver. In this case it uses thick JDBC driver.

Do you have a reproducer project to demonstrate the issue? I can't get it to show up:

public class ClientNode {
public static void main(String[] args) {
IgniteConfiguration igniteCfg = new IgniteConfiguration();
igniteCfg.setClientMode(true);

Ignite ignite = Ignition.start(igniteCfg);
IgniteCache foo = ignite.getOrCreateCache("foo");

foo.query(new SqlFieldsQuery("CREATE TABLE person (id INT PRIMARY KEY, name VARCHAR, age INT);"));
foo.query(new SqlFieldsQuery("INSERT INTO person (id, name, age) VALUES (1, 'Vassisualy', 45);"));
foo.query(new SqlFieldsQuery("ALTER TABLE person DROP COLUMN (age);"));

System.err.println(foo.query(new SqlFieldsQuery("SELECT * FROM person;")).getAll());
}
}

gives

[[1, Vassisualy]]

Regards,
--
Ilya Kasnacheev


пн, 21 сент. 2020 г. в 10:27, Shravya Nethula <[hidden email]>:
Hi Ilya,

sqlLine is also a thin client if I am not wrong!
From thin clients like GridGain and sqlLine, its working as expected.

It is not working from thick client.
When I try to execute the same query as a thick client with following Java code, its throwing IgniteSQLException.
Java Code:
String sql = "ALTER TABLE person DROP COLUMN (age)";
FieldsQueryCursor cursor = cache.query(new SqlFieldsQuery(sql));


Regards,

Shravya Nethula,

BigData Developer,


Hyderabad.


From: Ilya Kasnacheev <[hidden email]>
Sent: Tuesday, September 15, 2020 3:07 PM
To: [hidden email] <[hidden email]>
Subject: Re: Issue in Alter Table - Drop Column functionality
 
Hello!

I have just validated it on 2.8.1. Seems to work!

~/Downloads/apache-ignite-2.8.1-bin% bin/sqlline.sh 
sqlline version 1.3.0
sqlline> !connect jdbc:ignite:cfg://config/default-config.xml
[12:33:33]    __________  ________________  
[12:33:33]   /  _/ ___/ |/ /  _/_  __/ __/  
[12:33:33]  _/ // (7 7    // /  / / / _/    
[12:33:33] /___/\___/_/|_/___/ /_/ /___/   
[12:33:33]  
[12:33:33] ver. 2.8.1#20200521-sha1:86422096
[12:33:33] 2020 Copyright(C) Apache Software Foundation
[12:33:33]  
[12:33:38] Topology snapshot [ver=2, locNode=16da55e5, servers=1, clients=1, state=ACTIVE, CPUs=4, offheap=3.1GB, heap=6.8GB]
[12:33:38]   ^-- Baseline [id=0, size=1, online=1, offline=0]
Error: Transactions are not supported. (state=,code=0)
0: jdbc:ignite:cfg://config/default-config.xm> CREATE TABLE person (id INT PRIMARY KEY, name VARCHAR, age INT);
0: jdbc:ignite:cfg://config/default-config.xm> INSERT INTO person (id, name, age) VALUES (1, 'Vassisualy', 45);
0: jdbc:ignite:cfg://config/default-config.xm> ALTER TABLE person DROP COLUMN age;
0: jdbc:ignite:cfg://config/default-config.xm> SELECT * FROM person;
+--------------------------------+--------------------------------+
|               ID               |              NAME              |
+--------------------------------+--------------------------------+
| 1                              | Vassisualy                     |
+--------------------------------+--------------------------------+
1 row selected (0,059 seconds)

Do you have steps to reproduce?

Regards,
--
Ilya Kasnacheev


чт, 3 сент. 2020 г. в 20:46, Shravya Nethula <[hidden email]>:
Hi,

I have tried "ALTER TABLE PUBLIC.person DROP COLUMN (age)" query as well.
I am getting the same error message.

Regards,

Shravya Nethula,

BigData Developer,


Hyderabad.


From: Denis Mekhanikov <[hidden email]>
Sent: Thursday, September 3, 2020 2:17 PM
To: user <[hidden email]>
Subject: Re: Issue in Alter Table - Drop Column functionality
 
I mean, DROP COLUMN, not DROP TABLE.

Denis

чт, 3 сент. 2020 г. в 11:46, Denis Mekhanikov <[hidden email]>:
Does the DROP TABLE statement work if you specify the schema name explicitly?
For example, if the table is in the PUBLIC schema, try running the following query: ALTER TABLE PUBLIC.person DROP COLUMN (age)

Denis

ср, 2 сент. 2020 г. в 20:27, Shravya Nethula <[hidden email]>:
Hi,

When I am trying the following query from GridGain, it works as expected.
ALTER TABLE person DROP COLUMN (age)

But when I try to execute the same query as a thick client with following Java code, its throwing IgniteSQLException.
Java Code:
String sql = "ALTER TABLE person DROP COLUMN (age)";
FieldsQueryCursor cursor = cache.query(new SqlFieldsQuery(sql));

Output:
javax.cache.CacheException
    at org.apache.ignite.internal.processors.cache.IgniteCacheProxyImpl.query(IgniteCacheProxyImpl.java:817)
    at org.apache.ignite.internal.processors.cache.IgniteCacheProxyImpl.query(IgniteCacheProxyImpl.java:750)
    at org.apache.ignite.internal.processors.cache.GatewayProtectedCacheProxy.query(GatewayProtectedCacheProxy.java:424)
    at net.aline.cloudedh.base.database.IgniteTable._alterTable(IgniteTable.java:138)
    at net.aline.cloudedh.base.database.BigTable.alter(BigTable.java:697)
    at net.aline.cloudedh.base.framework.DACEngine.alterTable(DACEngine.java:1015)
    at net.aline.cloudedh.base.framework.DACOperationsTest.main(DACOperationsTest.java:89)
Caused by: class org.apache.ignite.internal.processors.query.IgniteSQLException: null
    at org.apache.ignite.internal.processors.query.h2.CommandProcessor.runCommandH2(CommandProcessor.java:888)
    at org.apache.ignite.internal.processors.query.h2.CommandProcessor.runCommand(CommandProcessor.java:418)
    at org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.executeCommand(IgniteH2Indexing.java:1048)
    at org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.querySqlFields(IgniteH2Indexing.java:1130)
    at org.apache.ignite.internal.processors.query.GridQueryProcessor$3.applyx(GridQueryProcessor.java:2406)
    at org.apache.ignite.internal.processors.query.GridQueryProcessor$3.applyx(GridQueryProcessor.java:2402)
    at org.apache.ignite.internal.util.lang.IgniteOutClosureX.apply(IgniteOutClosureX.java:36)
    at org.apache.ignite.internal.processors.query.GridQueryProcessor.executeQuery(GridQueryProcessor.java:2919)
    at org.apache.ignite.internal.processors.query.GridQueryProcessor.lambda$querySqlFields$1(GridQueryProcessor.java:2422)
    at org.apache.ignite.internal.processors.query.GridQueryProcessor.executeQuerySafe(GridQueryProcessor.java:2460)
    at org.apache.ignite.internal.processors.query.GridQueryProcessor.querySqlFields(GridQueryProcessor.java:2396)
    at org.apache.ignite.internal.processors.query.GridQueryProcessor.querySqlFields(GridQueryProcessor.java:2323)
    at org.apache.ignite.internal.processors.cache.IgniteCacheProxyImpl.query(IgniteCacheProxyImpl.java:802)
    ... 6 more
Caused by: java.lang.NullPointerException
    at org.apache.ignite.internal.processors.query.h2.CommandProcessor.runCommandH2(CommandProcessor.java:834)
    ... 18 more


On the other hand, ALTER TABLE ADD COLUMN functionality is working in both GridGain and also through thick client Java code.
Why is it so? Is there any parameter or configurations that are missing?
Please kindly let me know if you need any more details regarding the failure scenario.




Regards,

Shravya Nethula,

BigData Developer,


Hyderabad.