-
awood
- Multiple Poster
- Offline
-
- Posts: 2
- Joined: Tue Jul 16, 2019 6:51 pm
-
markc
- Contributor
- Offline
-
- Posts: 386
- Joined: Thu May 23, 2013 4:48 pm
-
awood
- Multiple Poster
- Offline
-
- Posts: 2
- Joined: Tue Jul 16, 2019 6:51 pm
-
michaeld
- Contributor
- Offline
-
- Posts: 17
- Joined: Mon Dec 01, 2014 11:38 am
Information and discussion related to the Kognitio on Hadoop product
unable to drop table
by awood » Tue Jul 16, 2019 8:06 pm
I am having an issue with a Kognitio on Hadoop cluster where I cannot drop a specific table.
code: drop table LUOY.T_NEW_MFR cascade;
The error is
HY000: [Kognitio][WX2 Driver][willbish03] CI0011: Insufficient memory
The has only 2,188 rows, however it does have dependencies (hence the cascade command in the drop statement) to two views which we discovered have a circular reference with each other (i.e. the views are referencing each other, one of which also joins to the table we are trying to drop). Neither view nor table is in RAM. The RAM report dashboard reports plenty of spare RAM capacity during execution of the drop statement.
I think this is the source of the problem are unable to fix the error because we cannot drop any of the problem objects in any order.
When the drop command is running there are no blocks and no apparent stuck jobs. The sessions tree reports the job as 'Compiling' until it errors after about 20 mins.
Even after a cluster restart (with and without a sys image) the issue persists.
Any suggestions?
code: drop table LUOY.T_NEW_MFR cascade;
The error is
HY000: [Kognitio][WX2 Driver][willbish03] CI0011: Insufficient memory
The has only 2,188 rows, however it does have dependencies (hence the cascade command in the drop statement) to two views which we discovered have a circular reference with each other (i.e. the views are referencing each other, one of which also joins to the table we are trying to drop). Neither view nor table is in RAM. The RAM report dashboard reports plenty of spare RAM capacity during execution of the drop statement.
I think this is the source of the problem are unable to fix the error because we cannot drop any of the problem objects in any order.
When the drop command is running there are no blocks and no apparent stuck jobs. The sessions tree reports the job as 'Compiling' until it errors after about 20 mins.
Even after a cluster restart (with and without a sys image) the issue persists.
Any suggestions?
Re: unable to drop table
by markc » Wed Jul 17, 2019 7:33 am
Yes, there is a known bug in 8.2.0 allowing circular dependencies to be created, which leads to this sort of problem where the compiler recurses through the dependencies until it runs out of resource. This is fixed by B45178 which will be backported to a future 8.2.0 patch, and is in the latest 8.2.3 release candidate currently in QA.
Where we've seen this before the circular dependency was caused by B depending on A, then A being modified with a CORV statement to depend on B. In that case, reverting the definition of A to the old definition with a CORV removes the circular dependency (which should not have been allowed in the first place), and lets your DROP and other queries which traverse the dependency tree complete normally.
Where we've seen this before the circular dependency was caused by B depending on A, then A being modified with a CORV statement to depend on B. In that case, reverting the definition of A to the old definition with a CORV removes the circular dependency (which should not have been allowed in the first place), and lets your DROP and other queries which traverse the dependency tree complete normally.
Re: unable to drop table
by awood » Mon Jul 22, 2019 8:04 pm
When attempting CORV on both LUOY.PRODUCT and SSE19.PRODUCT I get:
HY000 [Kognitio][WX2 Driver][prod02] CI4110: You are changing a view that has dependent views, but column RETAILER_DEPARTMENT has changed its name or datatype
Info:
You are changing a view that has dependent views, but a column has changed its name or type.
Action:
Either change the view definition so that it matches, or drop dependent views.
HY000 [Kognitio][WX2 Driver][prod02] CI4110: You are changing a view that has dependent views, but column RETAILER_DEPARTMENT has changed its name or datatype
Info:
You are changing a view that has dependent views, but a column has changed its name or type.
Action:
Either change the view definition so that it matches, or drop dependent views.
Re: unable to drop table
by michaeld » Tue Jul 23, 2019 11:21 pm
Hi,
To resolve the view dependency loop, you need to replace one of the views such that the names and types of all columns don't change from their current values. So assuming the mutually dependent views are LUOY.PRODUCT and SSE19.PRODUCT, you could:
1. run 'explain LUOY.PRODUCT' to see the current column names and types
2. Create an empty table with 'create ram only table rot1(< list of columns matching the above>)'
3. run 'create or replace view LUOY.PRODUCT as select * from rot1'
4. now you can drop both views
The above steps could also be run on SSE19.PRODUCT.
Regards,
Mike
To resolve the view dependency loop, you need to replace one of the views such that the names and types of all columns don't change from their current values. So assuming the mutually dependent views are LUOY.PRODUCT and SSE19.PRODUCT, you could:
1. run 'explain LUOY.PRODUCT' to see the current column names and types
2. Create an empty table with 'create ram only table rot1(< list of columns matching the above>)'
3. run 'create or replace view LUOY.PRODUCT as select * from rot1'
4. now you can drop both views
The above steps could also be run on SSE19.PRODUCT.
Regards,
Mike
4 posts
• Page 1 of 1
Who is online
Users browsing this forum: No registered users and 0 guests