[jira] [Updated] (HIVE-22566) Drop table involved in materialized view leaves the table in inconsistent state
[
https://issues.apache.org/jira/browse/HIVE-22566?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Pablo Junge updated HIVE-22566:
---
Description:
If you try dropping a table which is part of the definition of a created
materialized view, the table is not dropped, which is the desired state as it
is part of the materialized view.
However, there was a "drop" call to the table, so it tried to drop it but did
not succeed, leaving it in an inconsistent state.
Repro:
---
1) Create tables:
{code:java}
CREATE TABLE emps ( empid INT, deptno INT, name VARCHAR(256), salary FLOAT,
hire_date TIMESTAMP)STORED AS ORCTBLPROPERTIES ('transactional'='true');
CREATE TABLE depts ( deptno INT, deptname VARCHAR(256), locationid
INT)STORED AS ORCTBLPROPERTIES ('transactional'='true');
{code}
2) Create the VM:
{code:java}
CREATE MATERIALIZED VIEW mv1ASSELECT empid, deptname, hire_dateFROM emps JOIN
depts ON (emps.deptno = depts.deptno)WHERE hire_date >= '2016-01-01';
{code}
3) Following is in backend database at this point:
{code:java}
mysql> select TBL_ID, DB_ID, SD_ID, TBL_NAME, TBL_TYPE from TBLS where DB_ID=16;
++---+---+--+---+
| TBL_ID | DB_ID | SD_ID | TBL_NAME | TBL_TYPE |
++---+---+--+---+
| 81 | 16 | 81 | emps | MANAGED_TABLE |
| 83 | 16 | 83 | depts | MANAGED_TABLE |
| 84 | 16 | 84 | mv1 | MATERIALIZED_VIEW |
++---+---+--+---+
3 rows in set (0.00 sec)
{code}
4) Let's drop the 'emps' table:
{code:java}
0: jdbc:hive2://c1122-node2.squadron.support.> drop table emps;
INFO : Compiling
command(queryId=hive_20191202200025_c13079d0-8695-4485-8a18-14804b8b014b): drop
table emps
INFO : Semantic Analysis Completed (retrial = false)
INFO : Returning Hive schema: Schema(fieldSchemas:null, properties:null)
INFO : Completed compiling
command(queryId=hive_20191202200025_c13079d0-8695-4485-8a18-14804b8b014b); Time
taken: 0.05 seconds
INFO : Executing
command(queryId=hive_20191202200025_c13079d0-8695-4485-8a18-14804b8b014b): drop
table emps
INFO : Starting task [Stage-0:DDL] in serial mode
INFO : Completed executing
command(queryId=hive_20191202200025_c13079d0-8695-4485-8a18-14804b8b014b); Time
taken: 10.281 seconds
INFO : OK
No rows affected (16.949 seconds)
{code}
No issue displayed
5) List tables:
{code:java}
0: jdbc:hive2://c1122-node2.squadron.support.> show tables;
INFO : Compiling
command(queryId=hive_20191202200125_ca12565b-1d4d-4433-a602-ecf685863413): show
tables
INFO : Semantic Analysis Completed (retrial = false)
INFO : Returning Hive schema: Schema(fieldSchemas:[FieldSchema(name:tab_name,
type:string, comment:from deserializer)], properties:null)
INFO : Completed compiling
command(queryId=hive_20191202200125_ca12565b-1d4d-4433-a602-ecf685863413); Time
taken: 0.041 seconds
INFO : Executing
command(queryId=hive_20191202200125_ca12565b-1d4d-4433-a602-ecf685863413): show
tables
INFO : Starting task [Stage-0:DDL] in serial mode
INFO : Completed executing
command(queryId=hive_20191202200125_ca12565b-1d4d-4433-a602-ecf685863413); Time
taken: 0.016 seconds
INFO : OK
+---+
| tab_name |
+---+
| depts |
| emps |
+---+
2 rows selected (0.08 seconds)
{code}
6) Now, from the backend-db point of view:
{code:java}
mysql> select TBL_ID, DB_ID, SD_ID, TBL_NAME, TBL_TYPE from TBLS where DB_ID=16;
++---+---+--+---+
| TBL_ID | DB_ID | SD_ID | TBL_NAME | TBL_TYPE |
++---+---+--+---+
| 81 | 16 | NULL | emps | MANAGED_TABLE |
| 83 | 16 | 83 | depts | MANAGED_TABLE |
| 84 | 16 | 84 | mv1 | MATERIALIZED_VIEW |
++---+---+--+---+
3 rows in set (0.00 sec)
{code}
The table is left with NULL in SD_ID, making it not available.
7) From Metastore.log
{code:java}
2019-12-02T20:00:25,545 INFO [pool-6-thread-195]: metastore.HiveMetaStore
(HiveMetaStore.java:logInfo(907)) - 196: source:172.25.34.150 drop_table :
tbl=hive.mvs.emps
2019-12-02T20:00:25,545 INFO [pool-6-thread-195]: HiveMetaStore.audit
(HiveMetaStore.java:logAuditEvent(349)) - ugi=hive ip=172.25.34.150
cmd=source:172.25.34.150 drop_table : tbl=hive.mvs.emps
2019-12-02T20:00:25,580 INFO [pool-6-thread-195]:
metastore.ObjectStore$RetryingExecutor (ObjectStore.java:run(9966)) -
Attempting to acquire the DB log notification lock: 0 out of 10 retries
javax.jdo.JDODataStoreException: Error executing SQL query "select
"NEXT_EVENT_ID" from "NOTIFICATION_SEQUENCE" for update".
at
org.datanucleus.api.jdo.NucleusJDOHelper.getJDOExceptionForNucleusException(NucleusJDOHelper.java:543)
~[datanucleus-api-jdo-4.2.4.jar:?]
at
[jira] [Updated] (HIVE-22566) Drop table involved in materialized view leaves the table in inconsistent state
[
https://issues.apache.org/jira/browse/HIVE-22566?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Pablo Junge updated HIVE-22566:
---
Description:
If you try dropping a table which is part of the definition of a created
materialized view, the table is not dropped, which is the desired state as it
is part of the materialized view.
However, there was a "drop" call to the table, so it tried to drop it but did
not succeed, leaving it in an inconsistent state.
Repro:
---
1) Create tables:
{code:java}
CREATE TABLE emps ( empid INT, deptno INT, name VARCHAR(256), salary FLOAT,
hire_date TIMESTAMP)STORED AS ORC TBLPROPERTIES ('transactional'='true');
CREATE TABLE depts ( deptno INT, deptname VARCHAR(256), locationid
INT)STORED AS ORC TBLPROPERTIES ('transactional'='true');
{code}
2) Create the VM:
{code:java}
CREATE MATERIALIZED VIEW mv1 AS SELECT empid, deptname, hire_dateFROM emps JOIN
depts ON (emps.deptno = depts.deptno) WHERE hire_date >= '2016-01-01';
{code}
3) Following is in backend database at this point:
{code:java}
mysql> select TBL_ID, DB_ID, SD_ID, TBL_NAME, TBL_TYPE from TBLS where DB_ID=16;
++---+---+--+---+
| TBL_ID | DB_ID | SD_ID | TBL_NAME | TBL_TYPE |
++---+---+--+---+
| 81 | 16 | 81 | emps | MANAGED_TABLE |
| 83 | 16 | 83 | depts | MANAGED_TABLE |
| 84 | 16 | 84 | mv1 | MATERIALIZED_VIEW |
++---+---+--+---+
3 rows in set (0.00 sec)
{code}
4) Let's drop the 'emps' table:
{code:java}
0: jdbc:hive2://c1122-node2.squadron.support.> drop table emps;
INFO : Compiling
command(queryId=hive_20191202200025_c13079d0-8695-4485-8a18-14804b8b014b): drop
table emps
INFO : Semantic Analysis Completed (retrial = false)
INFO : Returning Hive schema: Schema(fieldSchemas:null, properties:null)
INFO : Completed compiling
command(queryId=hive_20191202200025_c13079d0-8695-4485-8a18-14804b8b014b); Time
taken: 0.05 seconds
INFO : Executing
command(queryId=hive_20191202200025_c13079d0-8695-4485-8a18-14804b8b014b): drop
table emps
INFO : Starting task [Stage-0:DDL] in serial mode
INFO : Completed executing
command(queryId=hive_20191202200025_c13079d0-8695-4485-8a18-14804b8b014b); Time
taken: 10.281 seconds
INFO : OK
No rows affected (16.949 seconds)
{code}
No issue displayed
5) List tables:
{code:java}
0: jdbc:hive2://c1122-node2.squadron.support.> show tables;
INFO : Compiling
command(queryId=hive_20191202200125_ca12565b-1d4d-4433-a602-ecf685863413): show
tables
INFO : Semantic Analysis Completed (retrial = false)
INFO : Returning Hive schema: Schema(fieldSchemas:[FieldSchema(name:tab_name,
type:string, comment:from deserializer)], properties:null)
INFO : Completed compiling
command(queryId=hive_20191202200125_ca12565b-1d4d-4433-a602-ecf685863413); Time
taken: 0.041 seconds
INFO : Executing
command(queryId=hive_20191202200125_ca12565b-1d4d-4433-a602-ecf685863413): show
tables
INFO : Starting task [Stage-0:DDL] in serial mode
INFO : Completed executing
command(queryId=hive_20191202200125_ca12565b-1d4d-4433-a602-ecf685863413); Time
taken: 0.016 seconds
INFO : OK
+---+
| tab_name |
+---+
| depts |
| emps |
+---+
2 rows selected (0.08 seconds)
{code}
6) Now, from the backend-db point of view:
{code:java}
mysql> select TBL_ID, DB_ID, SD_ID, TBL_NAME, TBL_TYPE from TBLS where DB_ID=16;
++---+---+--+---+
| TBL_ID | DB_ID | SD_ID | TBL_NAME | TBL_TYPE |
++---+---+--+---+
| 81 | 16 | NULL | emps | MANAGED_TABLE |
| 83 | 16 | 83 | depts | MANAGED_TABLE |
| 84 | 16 | 84 | mv1 | MATERIALIZED_VIEW |
++---+---+--+---+
3 rows in set (0.00 sec)
{code}
The table is left with NULL in SD_ID, making it not available.
7) From Metastore.log
{code:java}
2019-12-02T20:00:25,545 INFO [pool-6-thread-195]: metastore.HiveMetaStore
(HiveMetaStore.java:logInfo(907)) - 196: source:172.25.34.150 drop_table :
tbl=hive.mvs.emps
2019-12-02T20:00:25,545 INFO [pool-6-thread-195]: HiveMetaStore.audit
(HiveMetaStore.java:logAuditEvent(349)) - ugi=hive ip=172.25.34.150
cmd=source:172.25.34.150 drop_table : tbl=hive.mvs.emps
2019-12-02T20:00:25,580 INFO [pool-6-thread-195]:
metastore.ObjectStore$RetryingExecutor (ObjectStore.java:run(9966)) -
Attempting to acquire the DB log notification lock: 0 out of 10 retries
javax.jdo.JDODataStoreException: Error executing SQL query "select
"NEXT_EVENT_ID" from "NOTIFICATION_SEQUENCE" for update".
at
org.datanucleus.api.jdo.NucleusJDOHelper.getJDOExceptionForNucleusException(NucleusJDOHelper.java:543)
~[datanucleus-api-jdo-4.2.4.jar:?]
[jira] [Updated] (HIVE-22566) Drop table involved in materialized view leaves the table in inconsistent state
[
https://issues.apache.org/jira/browse/HIVE-22566?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Pablo Junge updated HIVE-22566:
---
Description:
If you try dropping a table which is part of the definition of a created
materialized view, the table is not dropped, which is the desired state as it
is part of the materialized view.
However, there was a "drop" call to the table, so it tried to drop it but did
not succeed, leaving it in an inconsistent state.
Repro:
---
1) Create tables:
{code:java}
CREATE TABLE emps ( empid INT, deptno INT, name VARCHAR(256), salary FLOAT,
hire_date TIMESTAMP)STORED AS ORC TBLPROPERTIES ('transactional'='true');
CREATE TABLE depts ( deptno INT, deptname VARCHAR(256), locationid
INT)STORED AS ORC TBLPROPERTIES ('transactional'='true');
{code}
2) Create the VM:
{code:java}
CREATE MATERIALIZED VIEW mv1ASSELECT empid, deptname, hire_dateFROM emps JOIN
depts ON (emps.deptno = depts.deptno)WHERE hire_date >= '2016-01-01';
{code}
3) Following is in backend database at this point:
{code:java}
mysql> select TBL_ID, DB_ID, SD_ID, TBL_NAME, TBL_TYPE from TBLS where DB_ID=16;
++---+---+--+---+
| TBL_ID | DB_ID | SD_ID | TBL_NAME | TBL_TYPE |
++---+---+--+---+
| 81 | 16 | 81 | emps | MANAGED_TABLE |
| 83 | 16 | 83 | depts | MANAGED_TABLE |
| 84 | 16 | 84 | mv1 | MATERIALIZED_VIEW |
++---+---+--+---+
3 rows in set (0.00 sec)
{code}
4) Let's drop the 'emps' table:
{code:java}
0: jdbc:hive2://c1122-node2.squadron.support.> drop table emps;
INFO : Compiling
command(queryId=hive_20191202200025_c13079d0-8695-4485-8a18-14804b8b014b): drop
table emps
INFO : Semantic Analysis Completed (retrial = false)
INFO : Returning Hive schema: Schema(fieldSchemas:null, properties:null)
INFO : Completed compiling
command(queryId=hive_20191202200025_c13079d0-8695-4485-8a18-14804b8b014b); Time
taken: 0.05 seconds
INFO : Executing
command(queryId=hive_20191202200025_c13079d0-8695-4485-8a18-14804b8b014b): drop
table emps
INFO : Starting task [Stage-0:DDL] in serial mode
INFO : Completed executing
command(queryId=hive_20191202200025_c13079d0-8695-4485-8a18-14804b8b014b); Time
taken: 10.281 seconds
INFO : OK
No rows affected (16.949 seconds)
{code}
No issue displayed
5) List tables:
{code:java}
0: jdbc:hive2://c1122-node2.squadron.support.> show tables;
INFO : Compiling
command(queryId=hive_20191202200125_ca12565b-1d4d-4433-a602-ecf685863413): show
tables
INFO : Semantic Analysis Completed (retrial = false)
INFO : Returning Hive schema: Schema(fieldSchemas:[FieldSchema(name:tab_name,
type:string, comment:from deserializer)], properties:null)
INFO : Completed compiling
command(queryId=hive_20191202200125_ca12565b-1d4d-4433-a602-ecf685863413); Time
taken: 0.041 seconds
INFO : Executing
command(queryId=hive_20191202200125_ca12565b-1d4d-4433-a602-ecf685863413): show
tables
INFO : Starting task [Stage-0:DDL] in serial mode
INFO : Completed executing
command(queryId=hive_20191202200125_ca12565b-1d4d-4433-a602-ecf685863413); Time
taken: 0.016 seconds
INFO : OK
+---+
| tab_name |
+---+
| depts |
| emps |
+---+
2 rows selected (0.08 seconds)
{code}
6) Now, from the backend-db point of view:
{code:java}
mysql> select TBL_ID, DB_ID, SD_ID, TBL_NAME, TBL_TYPE from TBLS where DB_ID=16;
++---+---+--+---+
| TBL_ID | DB_ID | SD_ID | TBL_NAME | TBL_TYPE |
++---+---+--+---+
| 81 | 16 | NULL | emps | MANAGED_TABLE |
| 83 | 16 | 83 | depts | MANAGED_TABLE |
| 84 | 16 | 84 | mv1 | MATERIALIZED_VIEW |
++---+---+--+---+
3 rows in set (0.00 sec)
{code}
The table is left with NULL in SD_ID, making it not available.
7) From Metastore.log
{code:java}
2019-12-02T20:00:25,545 INFO [pool-6-thread-195]: metastore.HiveMetaStore
(HiveMetaStore.java:logInfo(907)) - 196: source:172.25.34.150 drop_table :
tbl=hive.mvs.emps
2019-12-02T20:00:25,545 INFO [pool-6-thread-195]: HiveMetaStore.audit
(HiveMetaStore.java:logAuditEvent(349)) - ugi=hive ip=172.25.34.150
cmd=source:172.25.34.150 drop_table : tbl=hive.mvs.emps
2019-12-02T20:00:25,580 INFO [pool-6-thread-195]:
metastore.ObjectStore$RetryingExecutor (ObjectStore.java:run(9966)) -
Attempting to acquire the DB log notification lock: 0 out of 10 retries
javax.jdo.JDODataStoreException: Error executing SQL query "select
"NEXT_EVENT_ID" from "NOTIFICATION_SEQUENCE" for update".
at
org.datanucleus.api.jdo.NucleusJDOHelper.getJDOExceptionForNucleusException(NucleusJDOHelper.java:543)
~[datanucleus-api-jdo-4.2.4.jar:?]
a
[jira] [Updated] (HIVE-22566) Drop table involved in materialized view leaves the table in inconsistent state
[
https://issues.apache.org/jira/browse/HIVE-22566?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Pablo Junge updated HIVE-22566:
---
Description:
If you try dropping a table which is part of the definition of a created
materialized view, the table is not dropped, which is the desired state as it
is part of the materialized view.
However, there was a "drop" call to the table, so it tried to drop it but did
not succeed, leaving it in an inconsistent state.
Repro:
---
1) Create tables:
{code:java}
CREATE TABLE emps ( empid INT, deptno INT, name VARCHAR(256), salary FLOAT,
hire_date TIMESTAMP)STORED AS ORC TBLPROPERTIES ('transactional'='true');
CREATE TABLE depts ( deptno INT, deptname VARCHAR(256), locationid
INT)STORED AS ORC TBLPROPERTIES ('transactional'='true');
{code}
2) Create the VM:
{code:java}
CREATE MATERIALIZED VIEW mv1 AS SELECT empid, deptname, hire_date FROM emps
JOIN depts ON (emps.deptno = depts.deptno) WHERE hire_date >= '2016-01-01';
{code}
3) Following is in backend database at this point:
{code:java}
mysql> select TBL_ID, DB_ID, SD_ID, TBL_NAME, TBL_TYPE from TBLS where DB_ID=16;
++---+---+--+---+
| TBL_ID | DB_ID | SD_ID | TBL_NAME | TBL_TYPE |
++---+---+--+---+
| 81 | 16 | 81 | emps | MANAGED_TABLE |
| 83 | 16 | 83 | depts | MANAGED_TABLE |
| 84 | 16 | 84 | mv1 | MATERIALIZED_VIEW |
++---+---+--+---+
3 rows in set (0.00 sec)
{code}
4) Let's drop the 'emps' table:
{code:java}
0: jdbc:hive2://c1122-node2.squadron.support.> drop table emps;
INFO : Compiling
command(queryId=hive_20191202200025_c13079d0-8695-4485-8a18-14804b8b014b): drop
table emps
INFO : Semantic Analysis Completed (retrial = false)
INFO : Returning Hive schema: Schema(fieldSchemas:null, properties:null)
INFO : Completed compiling
command(queryId=hive_20191202200025_c13079d0-8695-4485-8a18-14804b8b014b); Time
taken: 0.05 seconds
INFO : Executing
command(queryId=hive_20191202200025_c13079d0-8695-4485-8a18-14804b8b014b): drop
table emps
INFO : Starting task [Stage-0:DDL] in serial mode
INFO : Completed executing
command(queryId=hive_20191202200025_c13079d0-8695-4485-8a18-14804b8b014b); Time
taken: 10.281 seconds
INFO : OK
No rows affected (16.949 seconds)
{code}
No issue displayed
5) List tables:
{code:java}
0: jdbc:hive2://c1122-node2.squadron.support.> show tables;
INFO : Compiling
command(queryId=hive_20191202200125_ca12565b-1d4d-4433-a602-ecf685863413): show
tables
INFO : Semantic Analysis Completed (retrial = false)
INFO : Returning Hive schema: Schema(fieldSchemas:[FieldSchema(name:tab_name,
type:string, comment:from deserializer)], properties:null)
INFO : Completed compiling
command(queryId=hive_20191202200125_ca12565b-1d4d-4433-a602-ecf685863413); Time
taken: 0.041 seconds
INFO : Executing
command(queryId=hive_20191202200125_ca12565b-1d4d-4433-a602-ecf685863413): show
tables
INFO : Starting task [Stage-0:DDL] in serial mode
INFO : Completed executing
command(queryId=hive_20191202200125_ca12565b-1d4d-4433-a602-ecf685863413); Time
taken: 0.016 seconds
INFO : OK
+---+
| tab_name |
+---+
| depts |
| emps |
+---+
2 rows selected (0.08 seconds)
{code}
6) Now, from the backend-db point of view:
{code:java}
mysql> select TBL_ID, DB_ID, SD_ID, TBL_NAME, TBL_TYPE from TBLS where DB_ID=16;
++---+---+--+---+
| TBL_ID | DB_ID | SD_ID | TBL_NAME | TBL_TYPE |
++---+---+--+---+
| 81 | 16 | NULL | emps | MANAGED_TABLE |
| 83 | 16 | 83 | depts | MANAGED_TABLE |
| 84 | 16 | 84 | mv1 | MATERIALIZED_VIEW |
++---+---+--+---+
3 rows in set (0.00 sec)
{code}
The table is left with NULL in SD_ID, making it not available.
7) From Metastore.log
{code:java}
2019-12-02T20:00:25,545 INFO [pool-6-thread-195]: metastore.HiveMetaStore
(HiveMetaStore.java:logInfo(907)) - 196: source:172.25.34.150 drop_table :
tbl=hive.mvs.emps
2019-12-02T20:00:25,545 INFO [pool-6-thread-195]: HiveMetaStore.audit
(HiveMetaStore.java:logAuditEvent(349)) - ugi=hive ip=172.25.34.150
cmd=source:172.25.34.150 drop_table : tbl=hive.mvs.emps
2019-12-02T20:00:25,580 INFO [pool-6-thread-195]:
metastore.ObjectStore$RetryingExecutor (ObjectStore.java:run(9966)) -
Attempting to acquire the DB log notification lock: 0 out of 10 retries
javax.jdo.JDODataStoreException: Error executing SQL query "select
"NEXT_EVENT_ID" from "NOTIFICATION_SEQUENCE" for update".
at
org.datanucleus.api.jdo.NucleusJDOHelper.getJDOExceptionForNucleusException(NucleusJDOHelper.java:543)
~[datanucleus-api-jdo-4.2.4.jar:?]
[jira] [Updated] (HIVE-22566) Drop table involved in materialized view leaves the table in inconsistent state
[
https://issues.apache.org/jira/browse/HIVE-22566?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Pablo Junge updated HIVE-22566:
---
Attachment: HIVE-22566.patch
Status: Patch Available (was: Open)
> Drop table involved in materialized view leaves the table in inconsistent
> state
> ---
>
> Key: HIVE-22566
> URL: https://issues.apache.org/jira/browse/HIVE-22566
> Project: Hive
> Issue Type: Bug
> Components: HiveServer2
>Affects Versions: 3.1.0
>Reporter: Pablo Junge
>Assignee: Pablo Junge
>Priority: Minor
> Attachments: HIVE-22566.patch
>
>
> If you try dropping a table which is part of the definition of a created
> materialized view, the table is not dropped, which is the desired state as it
> is part of the materialized view.
> However, there was a "drop" call to the table, so it tried to drop it but did
> not succeed, leaving it in an inconsistent state.
>
> Repro:
> ---
> 1) Create tables:
>
> {code:java}
> CREATE TABLE emps ( empid INT, deptno INT, name VARCHAR(256), salary
> FLOAT, hire_date TIMESTAMP)STORED AS ORC TBLPROPERTIES
> ('transactional'='true');
> CREATE TABLE depts ( deptno INT, deptname VARCHAR(256), locationid
> INT)STORED AS ORC TBLPROPERTIES ('transactional'='true');
> {code}
>
> 2) Create the VM:
>
> {code:java}
> CREATE MATERIALIZED VIEW mv1 AS SELECT empid, deptname, hire_date FROM emps
> JOIN depts ON (emps.deptno = depts.deptno) WHERE hire_date >= '2016-01-01';
> {code}
>
> 3) Following is in backend database at this point:
>
> {code:java}
> mysql> select TBL_ID, DB_ID, SD_ID, TBL_NAME, TBL_TYPE from TBLS where
> DB_ID=16;
> ++---+---+--+---+
> | TBL_ID | DB_ID | SD_ID | TBL_NAME | TBL_TYPE |
> ++---+---+--+---+
> | 81 | 16 | 81 | emps | MANAGED_TABLE |
> | 83 | 16 | 83 | depts | MANAGED_TABLE |
> | 84 | 16 | 84 | mv1 | MATERIALIZED_VIEW |
> ++---+---+--+---+
> 3 rows in set (0.00 sec)
> {code}
>
> 4) Let's drop the 'emps' table:
>
> {code:java}
> 0: jdbc:hive2://c1122-node2.squadron.support.> drop table emps;
> INFO : Compiling
> command(queryId=hive_20191202200025_c13079d0-8695-4485-8a18-14804b8b014b):
> drop table emps
> INFO : Semantic Analysis Completed (retrial = false)
> INFO : Returning Hive schema: Schema(fieldSchemas:null, properties:null)
> INFO : Completed compiling
> command(queryId=hive_20191202200025_c13079d0-8695-4485-8a18-14804b8b014b);
> Time taken: 0.05 seconds
> INFO : Executing
> command(queryId=hive_20191202200025_c13079d0-8695-4485-8a18-14804b8b014b):
> drop table emps
> INFO : Starting task [Stage-0:DDL] in serial mode
> INFO : Completed executing
> command(queryId=hive_20191202200025_c13079d0-8695-4485-8a18-14804b8b014b);
> Time taken: 10.281 seconds
> INFO : OK
> No rows affected (16.949 seconds)
> {code}
> No issue displayed
>
> 5) List tables:
>
> {code:java}
> 0: jdbc:hive2://c1122-node2.squadron.support.> show tables;
> INFO : Compiling
> command(queryId=hive_20191202200125_ca12565b-1d4d-4433-a602-ecf685863413):
> show tables
> INFO : Semantic Analysis Completed (retrial = false)
> INFO : Returning Hive schema:
> Schema(fieldSchemas:[FieldSchema(name:tab_name, type:string, comment:from
> deserializer)], properties:null)
> INFO : Completed compiling
> command(queryId=hive_20191202200125_ca12565b-1d4d-4433-a602-ecf685863413);
> Time taken: 0.041 seconds
> INFO : Executing
> command(queryId=hive_20191202200125_ca12565b-1d4d-4433-a602-ecf685863413):
> show tables
> INFO : Starting task [Stage-0:DDL] in serial mode
> INFO : Completed executing
> command(queryId=hive_20191202200125_ca12565b-1d4d-4433-a602-ecf685863413);
> Time taken: 0.016 seconds
> INFO : OK
> +---+
> | tab_name |
> +---+
> | depts |
> | emps |
> +---+
> 2 rows selected (0.08 seconds)
> {code}
>
> 6) Now, from the backend-db point of view:
>
> {code:java}
> mysql> select TBL_ID, DB_ID, SD_ID, TBL_NAME, TBL_TYPE from TBLS where
> DB_ID=16;
> ++---+---+--+---+
> | TBL_ID | DB_ID | SD_ID | TBL_NAME | TBL_TYPE |
> ++---+---+--+---+
> | 81 | 16 | NULL | emps | MANAGED_TABLE |
> | 83 | 16 | 83 | depts | MANAGED_TABLE |
> | 84 | 16 | 84 | mv1 | MATERIALIZED_VIEW |
> ++---+---+--+---+
> 3 rows in set (0.00 sec)
> {code}
> The table is left with NULL in SD_ID, making it not available.
>
> 7) From Metastore.log
>
> {code:java}
> 2019-12-02T20:00:25,545 INFO [pool-6-thread-19
[jira] [Updated] (HIVE-22566) Drop table involved in materialized view leaves the table in inconsistent state
[
https://issues.apache.org/jira/browse/HIVE-22566?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Pablo Junge updated HIVE-22566:
---
Status: Open (was: Patch Available)
> Drop table involved in materialized view leaves the table in inconsistent
> state
> ---
>
> Key: HIVE-22566
> URL: https://issues.apache.org/jira/browse/HIVE-22566
> Project: Hive
> Issue Type: Bug
> Components: HiveServer2
>Affects Versions: 3.1.0
>Reporter: Pablo Junge
>Assignee: Pablo Junge
>Priority: Minor
> Attachments: HIVE-22566.patch
>
>
> If you try dropping a table which is part of the definition of a created
> materialized view, the table is not dropped, which is the desired state as it
> is part of the materialized view.
> However, there was a "drop" call to the table, so it tried to drop it but did
> not succeed, leaving it in an inconsistent state.
>
> Repro:
> ---
> 1) Create tables:
>
> {code:java}
> CREATE TABLE emps ( empid INT, deptno INT, name VARCHAR(256), salary
> FLOAT, hire_date TIMESTAMP)STORED AS ORC TBLPROPERTIES
> ('transactional'='true');
> CREATE TABLE depts ( deptno INT, deptname VARCHAR(256), locationid
> INT)STORED AS ORC TBLPROPERTIES ('transactional'='true');
> {code}
>
> 2) Create the VM:
>
> {code:java}
> CREATE MATERIALIZED VIEW mv1 AS SELECT empid, deptname, hire_date FROM emps
> JOIN depts ON (emps.deptno = depts.deptno) WHERE hire_date >= '2016-01-01';
> {code}
>
> 3) Following is in backend database at this point:
>
> {code:java}
> mysql> select TBL_ID, DB_ID, SD_ID, TBL_NAME, TBL_TYPE from TBLS where
> DB_ID=16;
> ++---+---+--+---+
> | TBL_ID | DB_ID | SD_ID | TBL_NAME | TBL_TYPE |
> ++---+---+--+---+
> | 81 | 16 | 81 | emps | MANAGED_TABLE |
> | 83 | 16 | 83 | depts | MANAGED_TABLE |
> | 84 | 16 | 84 | mv1 | MATERIALIZED_VIEW |
> ++---+---+--+---+
> 3 rows in set (0.00 sec)
> {code}
>
> 4) Let's drop the 'emps' table:
>
> {code:java}
> 0: jdbc:hive2://c1122-node2.squadron.support.> drop table emps;
> INFO : Compiling
> command(queryId=hive_20191202200025_c13079d0-8695-4485-8a18-14804b8b014b):
> drop table emps
> INFO : Semantic Analysis Completed (retrial = false)
> INFO : Returning Hive schema: Schema(fieldSchemas:null, properties:null)
> INFO : Completed compiling
> command(queryId=hive_20191202200025_c13079d0-8695-4485-8a18-14804b8b014b);
> Time taken: 0.05 seconds
> INFO : Executing
> command(queryId=hive_20191202200025_c13079d0-8695-4485-8a18-14804b8b014b):
> drop table emps
> INFO : Starting task [Stage-0:DDL] in serial mode
> INFO : Completed executing
> command(queryId=hive_20191202200025_c13079d0-8695-4485-8a18-14804b8b014b);
> Time taken: 10.281 seconds
> INFO : OK
> No rows affected (16.949 seconds)
> {code}
> No issue displayed
>
> 5) List tables:
>
> {code:java}
> 0: jdbc:hive2://c1122-node2.squadron.support.> show tables;
> INFO : Compiling
> command(queryId=hive_20191202200125_ca12565b-1d4d-4433-a602-ecf685863413):
> show tables
> INFO : Semantic Analysis Completed (retrial = false)
> INFO : Returning Hive schema:
> Schema(fieldSchemas:[FieldSchema(name:tab_name, type:string, comment:from
> deserializer)], properties:null)
> INFO : Completed compiling
> command(queryId=hive_20191202200125_ca12565b-1d4d-4433-a602-ecf685863413);
> Time taken: 0.041 seconds
> INFO : Executing
> command(queryId=hive_20191202200125_ca12565b-1d4d-4433-a602-ecf685863413):
> show tables
> INFO : Starting task [Stage-0:DDL] in serial mode
> INFO : Completed executing
> command(queryId=hive_20191202200125_ca12565b-1d4d-4433-a602-ecf685863413);
> Time taken: 0.016 seconds
> INFO : OK
> +---+
> | tab_name |
> +---+
> | depts |
> | emps |
> +---+
> 2 rows selected (0.08 seconds)
> {code}
>
> 6) Now, from the backend-db point of view:
>
> {code:java}
> mysql> select TBL_ID, DB_ID, SD_ID, TBL_NAME, TBL_TYPE from TBLS where
> DB_ID=16;
> ++---+---+--+---+
> | TBL_ID | DB_ID | SD_ID | TBL_NAME | TBL_TYPE |
> ++---+---+--+---+
> | 81 | 16 | NULL | emps | MANAGED_TABLE |
> | 83 | 16 | 83 | depts | MANAGED_TABLE |
> | 84 | 16 | 84 | mv1 | MATERIALIZED_VIEW |
> ++---+---+--+---+
> 3 rows in set (0.00 sec)
> {code}
> The table is left with NULL in SD_ID, making it not available.
>
> 7) From Metastore.log
>
> {code:java}
> 2019-12-02T20:00:25,545 INFO [pool-6-thread-195]: metastore.HiveMetaStore
> (HiveM
[jira] [Updated] (HIVE-22566) Drop table involved in materialized view leaves the table in inconsistent state
[
https://issues.apache.org/jira/browse/HIVE-22566?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Pablo Junge updated HIVE-22566:
---
Attachment: (was: HIVE-22566.patch)
> Drop table involved in materialized view leaves the table in inconsistent
> state
> ---
>
> Key: HIVE-22566
> URL: https://issues.apache.org/jira/browse/HIVE-22566
> Project: Hive
> Issue Type: Bug
> Components: HiveServer2
>Affects Versions: 3.1.0
>Reporter: Pablo Junge
>Assignee: Pablo Junge
>Priority: Minor
>
> If you try dropping a table which is part of the definition of a created
> materialized view, the table is not dropped, which is the desired state as it
> is part of the materialized view.
> However, there was a "drop" call to the table, so it tried to drop it but did
> not succeed, leaving it in an inconsistent state.
>
> Repro:
> ---
> 1) Create tables:
>
> {code:java}
> CREATE TABLE emps ( empid INT, deptno INT, name VARCHAR(256), salary
> FLOAT, hire_date TIMESTAMP)STORED AS ORC TBLPROPERTIES
> ('transactional'='true');
> CREATE TABLE depts ( deptno INT, deptname VARCHAR(256), locationid
> INT)STORED AS ORC TBLPROPERTIES ('transactional'='true');
> {code}
>
> 2) Create the VM:
>
> {code:java}
> CREATE MATERIALIZED VIEW mv1 AS SELECT empid, deptname, hire_date FROM emps
> JOIN depts ON (emps.deptno = depts.deptno) WHERE hire_date >= '2016-01-01';
> {code}
>
> 3) Following is in backend database at this point:
>
> {code:java}
> mysql> select TBL_ID, DB_ID, SD_ID, TBL_NAME, TBL_TYPE from TBLS where
> DB_ID=16;
> ++---+---+--+---+
> | TBL_ID | DB_ID | SD_ID | TBL_NAME | TBL_TYPE |
> ++---+---+--+---+
> | 81 | 16 | 81 | emps | MANAGED_TABLE |
> | 83 | 16 | 83 | depts | MANAGED_TABLE |
> | 84 | 16 | 84 | mv1 | MATERIALIZED_VIEW |
> ++---+---+--+---+
> 3 rows in set (0.00 sec)
> {code}
>
> 4) Let's drop the 'emps' table:
>
> {code:java}
> 0: jdbc:hive2://c1122-node2.squadron.support.> drop table emps;
> INFO : Compiling
> command(queryId=hive_20191202200025_c13079d0-8695-4485-8a18-14804b8b014b):
> drop table emps
> INFO : Semantic Analysis Completed (retrial = false)
> INFO : Returning Hive schema: Schema(fieldSchemas:null, properties:null)
> INFO : Completed compiling
> command(queryId=hive_20191202200025_c13079d0-8695-4485-8a18-14804b8b014b);
> Time taken: 0.05 seconds
> INFO : Executing
> command(queryId=hive_20191202200025_c13079d0-8695-4485-8a18-14804b8b014b):
> drop table emps
> INFO : Starting task [Stage-0:DDL] in serial mode
> INFO : Completed executing
> command(queryId=hive_20191202200025_c13079d0-8695-4485-8a18-14804b8b014b);
> Time taken: 10.281 seconds
> INFO : OK
> No rows affected (16.949 seconds)
> {code}
> No issue displayed
>
> 5) List tables:
>
> {code:java}
> 0: jdbc:hive2://c1122-node2.squadron.support.> show tables;
> INFO : Compiling
> command(queryId=hive_20191202200125_ca12565b-1d4d-4433-a602-ecf685863413):
> show tables
> INFO : Semantic Analysis Completed (retrial = false)
> INFO : Returning Hive schema:
> Schema(fieldSchemas:[FieldSchema(name:tab_name, type:string, comment:from
> deserializer)], properties:null)
> INFO : Completed compiling
> command(queryId=hive_20191202200125_ca12565b-1d4d-4433-a602-ecf685863413);
> Time taken: 0.041 seconds
> INFO : Executing
> command(queryId=hive_20191202200125_ca12565b-1d4d-4433-a602-ecf685863413):
> show tables
> INFO : Starting task [Stage-0:DDL] in serial mode
> INFO : Completed executing
> command(queryId=hive_20191202200125_ca12565b-1d4d-4433-a602-ecf685863413);
> Time taken: 0.016 seconds
> INFO : OK
> +---+
> | tab_name |
> +---+
> | depts |
> | emps |
> +---+
> 2 rows selected (0.08 seconds)
> {code}
>
> 6) Now, from the backend-db point of view:
>
> {code:java}
> mysql> select TBL_ID, DB_ID, SD_ID, TBL_NAME, TBL_TYPE from TBLS where
> DB_ID=16;
> ++---+---+--+---+
> | TBL_ID | DB_ID | SD_ID | TBL_NAME | TBL_TYPE |
> ++---+---+--+---+
> | 81 | 16 | NULL | emps | MANAGED_TABLE |
> | 83 | 16 | 83 | depts | MANAGED_TABLE |
> | 84 | 16 | 84 | mv1 | MATERIALIZED_VIEW |
> ++---+---+--+---+
> 3 rows in set (0.00 sec)
> {code}
> The table is left with NULL in SD_ID, making it not available.
>
> 7) From Metastore.log
>
> {code:java}
> 2019-12-02T20:00:25,545 INFO [pool-6-thread-195]: metastore.HiveMetaStore
> (HiveMetaStore.java:logInfo(907)) - 196: sour
[jira] [Updated] (HIVE-22566) Drop table involved in materialized view leaves the table in inconsistent state
[
https://issues.apache.org/jira/browse/HIVE-22566?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Pablo Junge updated HIVE-22566:
---
Attachment: HIVE-22566.patch
Status: Patch Available (was: Open)
> Drop table involved in materialized view leaves the table in inconsistent
> state
> ---
>
> Key: HIVE-22566
> URL: https://issues.apache.org/jira/browse/HIVE-22566
> Project: Hive
> Issue Type: Bug
> Components: HiveServer2
>Affects Versions: 3.1.0
>Reporter: Pablo Junge
>Assignee: Pablo Junge
>Priority: Minor
> Attachments: HIVE-22566.patch
>
>
> If you try dropping a table which is part of the definition of a created
> materialized view, the table is not dropped, which is the desired state as it
> is part of the materialized view.
> However, there was a "drop" call to the table, so it tried to drop it but did
> not succeed, leaving it in an inconsistent state.
>
> Repro:
> ---
> 1) Create tables:
>
> {code:java}
> CREATE TABLE emps ( empid INT, deptno INT, name VARCHAR(256), salary
> FLOAT, hire_date TIMESTAMP)STORED AS ORC TBLPROPERTIES
> ('transactional'='true');
> CREATE TABLE depts ( deptno INT, deptname VARCHAR(256), locationid
> INT)STORED AS ORC TBLPROPERTIES ('transactional'='true');
> {code}
>
> 2) Create the VM:
>
> {code:java}
> CREATE MATERIALIZED VIEW mv1 AS SELECT empid, deptname, hire_date FROM emps
> JOIN depts ON (emps.deptno = depts.deptno) WHERE hire_date >= '2016-01-01';
> {code}
>
> 3) Following is in backend database at this point:
>
> {code:java}
> mysql> select TBL_ID, DB_ID, SD_ID, TBL_NAME, TBL_TYPE from TBLS where
> DB_ID=16;
> ++---+---+--+---+
> | TBL_ID | DB_ID | SD_ID | TBL_NAME | TBL_TYPE |
> ++---+---+--+---+
> | 81 | 16 | 81 | emps | MANAGED_TABLE |
> | 83 | 16 | 83 | depts | MANAGED_TABLE |
> | 84 | 16 | 84 | mv1 | MATERIALIZED_VIEW |
> ++---+---+--+---+
> 3 rows in set (0.00 sec)
> {code}
>
> 4) Let's drop the 'emps' table:
>
> {code:java}
> 0: jdbc:hive2://c1122-node2.squadron.support.> drop table emps;
> INFO : Compiling
> command(queryId=hive_20191202200025_c13079d0-8695-4485-8a18-14804b8b014b):
> drop table emps
> INFO : Semantic Analysis Completed (retrial = false)
> INFO : Returning Hive schema: Schema(fieldSchemas:null, properties:null)
> INFO : Completed compiling
> command(queryId=hive_20191202200025_c13079d0-8695-4485-8a18-14804b8b014b);
> Time taken: 0.05 seconds
> INFO : Executing
> command(queryId=hive_20191202200025_c13079d0-8695-4485-8a18-14804b8b014b):
> drop table emps
> INFO : Starting task [Stage-0:DDL] in serial mode
> INFO : Completed executing
> command(queryId=hive_20191202200025_c13079d0-8695-4485-8a18-14804b8b014b);
> Time taken: 10.281 seconds
> INFO : OK
> No rows affected (16.949 seconds)
> {code}
> No issue displayed
>
> 5) List tables:
>
> {code:java}
> 0: jdbc:hive2://c1122-node2.squadron.support.> show tables;
> INFO : Compiling
> command(queryId=hive_20191202200125_ca12565b-1d4d-4433-a602-ecf685863413):
> show tables
> INFO : Semantic Analysis Completed (retrial = false)
> INFO : Returning Hive schema:
> Schema(fieldSchemas:[FieldSchema(name:tab_name, type:string, comment:from
> deserializer)], properties:null)
> INFO : Completed compiling
> command(queryId=hive_20191202200125_ca12565b-1d4d-4433-a602-ecf685863413);
> Time taken: 0.041 seconds
> INFO : Executing
> command(queryId=hive_20191202200125_ca12565b-1d4d-4433-a602-ecf685863413):
> show tables
> INFO : Starting task [Stage-0:DDL] in serial mode
> INFO : Completed executing
> command(queryId=hive_20191202200125_ca12565b-1d4d-4433-a602-ecf685863413);
> Time taken: 0.016 seconds
> INFO : OK
> +---+
> | tab_name |
> +---+
> | depts |
> | emps |
> +---+
> 2 rows selected (0.08 seconds)
> {code}
>
> 6) Now, from the backend-db point of view:
>
> {code:java}
> mysql> select TBL_ID, DB_ID, SD_ID, TBL_NAME, TBL_TYPE from TBLS where
> DB_ID=16;
> ++---+---+--+---+
> | TBL_ID | DB_ID | SD_ID | TBL_NAME | TBL_TYPE |
> ++---+---+--+---+
> | 81 | 16 | NULL | emps | MANAGED_TABLE |
> | 83 | 16 | 83 | depts | MANAGED_TABLE |
> | 84 | 16 | 84 | mv1 | MATERIALIZED_VIEW |
> ++---+---+--+---+
> 3 rows in set (0.00 sec)
> {code}
> The table is left with NULL in SD_ID, making it not available.
>
> 7) From Metastore.log
>
> {code:java}
> 2019-12-02T20:00:25,545 INFO [pool-6-thread-19
[jira] [Updated] (HIVE-22566) Drop table involved in materialized view leaves the table in inconsistent state
[
https://issues.apache.org/jira/browse/HIVE-22566?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Pablo Junge updated HIVE-22566:
---
Attachment: (was: HIVE-22566.patch)
> Drop table involved in materialized view leaves the table in inconsistent
> state
> ---
>
> Key: HIVE-22566
> URL: https://issues.apache.org/jira/browse/HIVE-22566
> Project: Hive
> Issue Type: Bug
> Components: HiveServer2
>Affects Versions: 3.1.0
>Reporter: Pablo Junge
>Assignee: Pablo Junge
>Priority: Minor
>
> If you try dropping a table which is part of the definition of a created
> materialized view, the table is not dropped, which is the desired state as it
> is part of the materialized view.
> However, there was a "drop" call to the table, so it tried to drop it but did
> not succeed, leaving it in an inconsistent state.
>
> Repro:
> ---
> 1) Create tables:
>
> {code:java}
> CREATE TABLE emps ( empid INT, deptno INT, name VARCHAR(256), salary
> FLOAT, hire_date TIMESTAMP)STORED AS ORC TBLPROPERTIES
> ('transactional'='true');
> CREATE TABLE depts ( deptno INT, deptname VARCHAR(256), locationid
> INT)STORED AS ORC TBLPROPERTIES ('transactional'='true');
> {code}
>
> 2) Create the VM:
>
> {code:java}
> CREATE MATERIALIZED VIEW mv1 AS SELECT empid, deptname, hire_date FROM emps
> JOIN depts ON (emps.deptno = depts.deptno) WHERE hire_date >= '2016-01-01';
> {code}
>
> 3) Following is in backend database at this point:
>
> {code:java}
> mysql> select TBL_ID, DB_ID, SD_ID, TBL_NAME, TBL_TYPE from TBLS where
> DB_ID=16;
> ++---+---+--+---+
> | TBL_ID | DB_ID | SD_ID | TBL_NAME | TBL_TYPE |
> ++---+---+--+---+
> | 81 | 16 | 81 | emps | MANAGED_TABLE |
> | 83 | 16 | 83 | depts | MANAGED_TABLE |
> | 84 | 16 | 84 | mv1 | MATERIALIZED_VIEW |
> ++---+---+--+---+
> 3 rows in set (0.00 sec)
> {code}
>
> 4) Let's drop the 'emps' table:
>
> {code:java}
> 0: jdbc:hive2://c1122-node2.squadron.support.> drop table emps;
> INFO : Compiling
> command(queryId=hive_20191202200025_c13079d0-8695-4485-8a18-14804b8b014b):
> drop table emps
> INFO : Semantic Analysis Completed (retrial = false)
> INFO : Returning Hive schema: Schema(fieldSchemas:null, properties:null)
> INFO : Completed compiling
> command(queryId=hive_20191202200025_c13079d0-8695-4485-8a18-14804b8b014b);
> Time taken: 0.05 seconds
> INFO : Executing
> command(queryId=hive_20191202200025_c13079d0-8695-4485-8a18-14804b8b014b):
> drop table emps
> INFO : Starting task [Stage-0:DDL] in serial mode
> INFO : Completed executing
> command(queryId=hive_20191202200025_c13079d0-8695-4485-8a18-14804b8b014b);
> Time taken: 10.281 seconds
> INFO : OK
> No rows affected (16.949 seconds)
> {code}
> No issue displayed
>
> 5) List tables:
>
> {code:java}
> 0: jdbc:hive2://c1122-node2.squadron.support.> show tables;
> INFO : Compiling
> command(queryId=hive_20191202200125_ca12565b-1d4d-4433-a602-ecf685863413):
> show tables
> INFO : Semantic Analysis Completed (retrial = false)
> INFO : Returning Hive schema:
> Schema(fieldSchemas:[FieldSchema(name:tab_name, type:string, comment:from
> deserializer)], properties:null)
> INFO : Completed compiling
> command(queryId=hive_20191202200125_ca12565b-1d4d-4433-a602-ecf685863413);
> Time taken: 0.041 seconds
> INFO : Executing
> command(queryId=hive_20191202200125_ca12565b-1d4d-4433-a602-ecf685863413):
> show tables
> INFO : Starting task [Stage-0:DDL] in serial mode
> INFO : Completed executing
> command(queryId=hive_20191202200125_ca12565b-1d4d-4433-a602-ecf685863413);
> Time taken: 0.016 seconds
> INFO : OK
> +---+
> | tab_name |
> +---+
> | depts |
> | emps |
> +---+
> 2 rows selected (0.08 seconds)
> {code}
>
> 6) Now, from the backend-db point of view:
>
> {code:java}
> mysql> select TBL_ID, DB_ID, SD_ID, TBL_NAME, TBL_TYPE from TBLS where
> DB_ID=16;
> ++---+---+--+---+
> | TBL_ID | DB_ID | SD_ID | TBL_NAME | TBL_TYPE |
> ++---+---+--+---+
> | 81 | 16 | NULL | emps | MANAGED_TABLE |
> | 83 | 16 | 83 | depts | MANAGED_TABLE |
> | 84 | 16 | 84 | mv1 | MATERIALIZED_VIEW |
> ++---+---+--+---+
> 3 rows in set (0.00 sec)
> {code}
> The table is left with NULL in SD_ID, making it not available.
>
> 7) From Metastore.log
>
> {code:java}
> 2019-12-02T20:00:25,545 INFO [pool-6-thread-195]: metastore.HiveMetaStore
> (HiveMetaStore.java:logInfo(907)) - 196: sour
[jira] [Updated] (HIVE-22566) Drop table involved in materialized view leaves the table in inconsistent state
[
https://issues.apache.org/jira/browse/HIVE-22566?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Pablo Junge updated HIVE-22566:
---
Status: Open (was: Patch Available)
> Drop table involved in materialized view leaves the table in inconsistent
> state
> ---
>
> Key: HIVE-22566
> URL: https://issues.apache.org/jira/browse/HIVE-22566
> Project: Hive
> Issue Type: Bug
> Components: HiveServer2
>Affects Versions: 3.1.0
>Reporter: Pablo Junge
>Assignee: Pablo Junge
>Priority: Minor
>
> If you try dropping a table which is part of the definition of a created
> materialized view, the table is not dropped, which is the desired state as it
> is part of the materialized view.
> However, there was a "drop" call to the table, so it tried to drop it but did
> not succeed, leaving it in an inconsistent state.
>
> Repro:
> ---
> 1) Create tables:
>
> {code:java}
> CREATE TABLE emps ( empid INT, deptno INT, name VARCHAR(256), salary
> FLOAT, hire_date TIMESTAMP)STORED AS ORC TBLPROPERTIES
> ('transactional'='true');
> CREATE TABLE depts ( deptno INT, deptname VARCHAR(256), locationid
> INT)STORED AS ORC TBLPROPERTIES ('transactional'='true');
> {code}
>
> 2) Create the VM:
>
> {code:java}
> CREATE MATERIALIZED VIEW mv1 AS SELECT empid, deptname, hire_date FROM emps
> JOIN depts ON (emps.deptno = depts.deptno) WHERE hire_date >= '2016-01-01';
> {code}
>
> 3) Following is in backend database at this point:
>
> {code:java}
> mysql> select TBL_ID, DB_ID, SD_ID, TBL_NAME, TBL_TYPE from TBLS where
> DB_ID=16;
> ++---+---+--+---+
> | TBL_ID | DB_ID | SD_ID | TBL_NAME | TBL_TYPE |
> ++---+---+--+---+
> | 81 | 16 | 81 | emps | MANAGED_TABLE |
> | 83 | 16 | 83 | depts | MANAGED_TABLE |
> | 84 | 16 | 84 | mv1 | MATERIALIZED_VIEW |
> ++---+---+--+---+
> 3 rows in set (0.00 sec)
> {code}
>
> 4) Let's drop the 'emps' table:
>
> {code:java}
> 0: jdbc:hive2://c1122-node2.squadron.support.> drop table emps;
> INFO : Compiling
> command(queryId=hive_20191202200025_c13079d0-8695-4485-8a18-14804b8b014b):
> drop table emps
> INFO : Semantic Analysis Completed (retrial = false)
> INFO : Returning Hive schema: Schema(fieldSchemas:null, properties:null)
> INFO : Completed compiling
> command(queryId=hive_20191202200025_c13079d0-8695-4485-8a18-14804b8b014b);
> Time taken: 0.05 seconds
> INFO : Executing
> command(queryId=hive_20191202200025_c13079d0-8695-4485-8a18-14804b8b014b):
> drop table emps
> INFO : Starting task [Stage-0:DDL] in serial mode
> INFO : Completed executing
> command(queryId=hive_20191202200025_c13079d0-8695-4485-8a18-14804b8b014b);
> Time taken: 10.281 seconds
> INFO : OK
> No rows affected (16.949 seconds)
> {code}
> No issue displayed
>
> 5) List tables:
>
> {code:java}
> 0: jdbc:hive2://c1122-node2.squadron.support.> show tables;
> INFO : Compiling
> command(queryId=hive_20191202200125_ca12565b-1d4d-4433-a602-ecf685863413):
> show tables
> INFO : Semantic Analysis Completed (retrial = false)
> INFO : Returning Hive schema:
> Schema(fieldSchemas:[FieldSchema(name:tab_name, type:string, comment:from
> deserializer)], properties:null)
> INFO : Completed compiling
> command(queryId=hive_20191202200125_ca12565b-1d4d-4433-a602-ecf685863413);
> Time taken: 0.041 seconds
> INFO : Executing
> command(queryId=hive_20191202200125_ca12565b-1d4d-4433-a602-ecf685863413):
> show tables
> INFO : Starting task [Stage-0:DDL] in serial mode
> INFO : Completed executing
> command(queryId=hive_20191202200125_ca12565b-1d4d-4433-a602-ecf685863413);
> Time taken: 0.016 seconds
> INFO : OK
> +---+
> | tab_name |
> +---+
> | depts |
> | emps |
> +---+
> 2 rows selected (0.08 seconds)
> {code}
>
> 6) Now, from the backend-db point of view:
>
> {code:java}
> mysql> select TBL_ID, DB_ID, SD_ID, TBL_NAME, TBL_TYPE from TBLS where
> DB_ID=16;
> ++---+---+--+---+
> | TBL_ID | DB_ID | SD_ID | TBL_NAME | TBL_TYPE |
> ++---+---+--+---+
> | 81 | 16 | NULL | emps | MANAGED_TABLE |
> | 83 | 16 | 83 | depts | MANAGED_TABLE |
> | 84 | 16 | 84 | mv1 | MATERIALIZED_VIEW |
> ++---+---+--+---+
> 3 rows in set (0.00 sec)
> {code}
> The table is left with NULL in SD_ID, making it not available.
>
> 7) From Metastore.log
>
> {code:java}
> 2019-12-02T20:00:25,545 INFO [pool-6-thread-195]: metastore.HiveMetaStore
> (HiveMetaStore.java:logInfo(907)) - 196: source:
[jira] [Updated] (HIVE-22566) Drop table involved in materialized view leaves the table in inconsistent state
[
https://issues.apache.org/jira/browse/HIVE-22566?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Pablo Junge updated HIVE-22566:
---
Attachment: HIVE-22566.patch
Fix Version/s: 4.0.0
Status: Patch Available (was: Open)
> Drop table involved in materialized view leaves the table in inconsistent
> state
> ---
>
> Key: HIVE-22566
> URL: https://issues.apache.org/jira/browse/HIVE-22566
> Project: Hive
> Issue Type: Bug
> Components: HiveServer2
>Affects Versions: 3.1.0
>Reporter: Pablo Junge
>Assignee: Pablo Junge
>Priority: Minor
> Fix For: 4.0.0
>
> Attachments: HIVE-22566.patch
>
>
> If you try dropping a table which is part of the definition of a created
> materialized view, the table is not dropped, which is the desired state as it
> is part of the materialized view.
> However, there was a "drop" call to the table, so it tried to drop it but did
> not succeed, leaving it in an inconsistent state.
>
> Repro:
> ---
> 1) Create tables:
>
> {code:java}
> CREATE TABLE emps ( empid INT, deptno INT, name VARCHAR(256), salary
> FLOAT, hire_date TIMESTAMP)STORED AS ORC TBLPROPERTIES
> ('transactional'='true');
> CREATE TABLE depts ( deptno INT, deptname VARCHAR(256), locationid
> INT)STORED AS ORC TBLPROPERTIES ('transactional'='true');
> {code}
>
> 2) Create the VM:
>
> {code:java}
> CREATE MATERIALIZED VIEW mv1 AS SELECT empid, deptname, hire_date FROM emps
> JOIN depts ON (emps.deptno = depts.deptno) WHERE hire_date >= '2016-01-01';
> {code}
>
> 3) Following is in backend database at this point:
>
> {code:java}
> mysql> select TBL_ID, DB_ID, SD_ID, TBL_NAME, TBL_TYPE from TBLS where
> DB_ID=16;
> ++---+---+--+---+
> | TBL_ID | DB_ID | SD_ID | TBL_NAME | TBL_TYPE |
> ++---+---+--+---+
> | 81 | 16 | 81 | emps | MANAGED_TABLE |
> | 83 | 16 | 83 | depts | MANAGED_TABLE |
> | 84 | 16 | 84 | mv1 | MATERIALIZED_VIEW |
> ++---+---+--+---+
> 3 rows in set (0.00 sec)
> {code}
>
> 4) Let's drop the 'emps' table:
>
> {code:java}
> 0: jdbc:hive2://c1122-node2.squadron.support.> drop table emps;
> INFO : Compiling
> command(queryId=hive_20191202200025_c13079d0-8695-4485-8a18-14804b8b014b):
> drop table emps
> INFO : Semantic Analysis Completed (retrial = false)
> INFO : Returning Hive schema: Schema(fieldSchemas:null, properties:null)
> INFO : Completed compiling
> command(queryId=hive_20191202200025_c13079d0-8695-4485-8a18-14804b8b014b);
> Time taken: 0.05 seconds
> INFO : Executing
> command(queryId=hive_20191202200025_c13079d0-8695-4485-8a18-14804b8b014b):
> drop table emps
> INFO : Starting task [Stage-0:DDL] in serial mode
> INFO : Completed executing
> command(queryId=hive_20191202200025_c13079d0-8695-4485-8a18-14804b8b014b);
> Time taken: 10.281 seconds
> INFO : OK
> No rows affected (16.949 seconds)
> {code}
> No issue displayed
>
> 5) List tables:
>
> {code:java}
> 0: jdbc:hive2://c1122-node2.squadron.support.> show tables;
> INFO : Compiling
> command(queryId=hive_20191202200125_ca12565b-1d4d-4433-a602-ecf685863413):
> show tables
> INFO : Semantic Analysis Completed (retrial = false)
> INFO : Returning Hive schema:
> Schema(fieldSchemas:[FieldSchema(name:tab_name, type:string, comment:from
> deserializer)], properties:null)
> INFO : Completed compiling
> command(queryId=hive_20191202200125_ca12565b-1d4d-4433-a602-ecf685863413);
> Time taken: 0.041 seconds
> INFO : Executing
> command(queryId=hive_20191202200125_ca12565b-1d4d-4433-a602-ecf685863413):
> show tables
> INFO : Starting task [Stage-0:DDL] in serial mode
> INFO : Completed executing
> command(queryId=hive_20191202200125_ca12565b-1d4d-4433-a602-ecf685863413);
> Time taken: 0.016 seconds
> INFO : OK
> +---+
> | tab_name |
> +---+
> | depts |
> | emps |
> +---+
> 2 rows selected (0.08 seconds)
> {code}
>
> 6) Now, from the backend-db point of view:
>
> {code:java}
> mysql> select TBL_ID, DB_ID, SD_ID, TBL_NAME, TBL_TYPE from TBLS where
> DB_ID=16;
> ++---+---+--+---+
> | TBL_ID | DB_ID | SD_ID | TBL_NAME | TBL_TYPE |
> ++---+---+--+---+
> | 81 | 16 | NULL | emps | MANAGED_TABLE |
> | 83 | 16 | 83 | depts | MANAGED_TABLE |
> | 84 | 16 | 84 | mv1 | MATERIALIZED_VIEW |
> ++---+---+--+---+
> 3 rows in set (0.00 sec)
> {code}
> The table is left with NULL in SD_ID, making it not available.
>
> 7) From Metastore.log
>
>
[jira] [Updated] (HIVE-22566) Drop table involved in materialized view leaves the table in inconsistent state
[
https://issues.apache.org/jira/browse/HIVE-22566?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Pablo Junge updated HIVE-22566:
---
Status: Open (was: Patch Available)
> Drop table involved in materialized view leaves the table in inconsistent
> state
> ---
>
> Key: HIVE-22566
> URL: https://issues.apache.org/jira/browse/HIVE-22566
> Project: Hive
> Issue Type: Bug
> Components: HiveServer2
>Affects Versions: 3.1.0
>Reporter: Pablo Junge
>Assignee: Pablo Junge
>Priority: Minor
> Fix For: 4.0.0
>
> Attachments: HIVE-22566.patch
>
>
> If you try dropping a table which is part of the definition of a created
> materialized view, the table is not dropped, which is the desired state as it
> is part of the materialized view.
> However, there was a "drop" call to the table, so it tried to drop it but did
> not succeed, leaving it in an inconsistent state.
>
> Repro:
> ---
> 1) Create tables:
>
> {code:java}
> CREATE TABLE emps ( empid INT, deptno INT, name VARCHAR(256), salary
> FLOAT, hire_date TIMESTAMP)STORED AS ORC TBLPROPERTIES
> ('transactional'='true');
> CREATE TABLE depts ( deptno INT, deptname VARCHAR(256), locationid
> INT)STORED AS ORC TBLPROPERTIES ('transactional'='true');
> {code}
>
> 2) Create the VM:
>
> {code:java}
> CREATE MATERIALIZED VIEW mv1 AS SELECT empid, deptname, hire_date FROM emps
> JOIN depts ON (emps.deptno = depts.deptno) WHERE hire_date >= '2016-01-01';
> {code}
>
> 3) Following is in backend database at this point:
>
> {code:java}
> mysql> select TBL_ID, DB_ID, SD_ID, TBL_NAME, TBL_TYPE from TBLS where
> DB_ID=16;
> ++---+---+--+---+
> | TBL_ID | DB_ID | SD_ID | TBL_NAME | TBL_TYPE |
> ++---+---+--+---+
> | 81 | 16 | 81 | emps | MANAGED_TABLE |
> | 83 | 16 | 83 | depts | MANAGED_TABLE |
> | 84 | 16 | 84 | mv1 | MATERIALIZED_VIEW |
> ++---+---+--+---+
> 3 rows in set (0.00 sec)
> {code}
>
> 4) Let's drop the 'emps' table:
>
> {code:java}
> 0: jdbc:hive2://c1122-node2.squadron.support.> drop table emps;
> INFO : Compiling
> command(queryId=hive_20191202200025_c13079d0-8695-4485-8a18-14804b8b014b):
> drop table emps
> INFO : Semantic Analysis Completed (retrial = false)
> INFO : Returning Hive schema: Schema(fieldSchemas:null, properties:null)
> INFO : Completed compiling
> command(queryId=hive_20191202200025_c13079d0-8695-4485-8a18-14804b8b014b);
> Time taken: 0.05 seconds
> INFO : Executing
> command(queryId=hive_20191202200025_c13079d0-8695-4485-8a18-14804b8b014b):
> drop table emps
> INFO : Starting task [Stage-0:DDL] in serial mode
> INFO : Completed executing
> command(queryId=hive_20191202200025_c13079d0-8695-4485-8a18-14804b8b014b);
> Time taken: 10.281 seconds
> INFO : OK
> No rows affected (16.949 seconds)
> {code}
> No issue displayed
>
> 5) List tables:
>
> {code:java}
> 0: jdbc:hive2://c1122-node2.squadron.support.> show tables;
> INFO : Compiling
> command(queryId=hive_20191202200125_ca12565b-1d4d-4433-a602-ecf685863413):
> show tables
> INFO : Semantic Analysis Completed (retrial = false)
> INFO : Returning Hive schema:
> Schema(fieldSchemas:[FieldSchema(name:tab_name, type:string, comment:from
> deserializer)], properties:null)
> INFO : Completed compiling
> command(queryId=hive_20191202200125_ca12565b-1d4d-4433-a602-ecf685863413);
> Time taken: 0.041 seconds
> INFO : Executing
> command(queryId=hive_20191202200125_ca12565b-1d4d-4433-a602-ecf685863413):
> show tables
> INFO : Starting task [Stage-0:DDL] in serial mode
> INFO : Completed executing
> command(queryId=hive_20191202200125_ca12565b-1d4d-4433-a602-ecf685863413);
> Time taken: 0.016 seconds
> INFO : OK
> +---+
> | tab_name |
> +---+
> | depts |
> | emps |
> +---+
> 2 rows selected (0.08 seconds)
> {code}
>
> 6) Now, from the backend-db point of view:
>
> {code:java}
> mysql> select TBL_ID, DB_ID, SD_ID, TBL_NAME, TBL_TYPE from TBLS where
> DB_ID=16;
> ++---+---+--+---+
> | TBL_ID | DB_ID | SD_ID | TBL_NAME | TBL_TYPE |
> ++---+---+--+---+
> | 81 | 16 | NULL | emps | MANAGED_TABLE |
> | 83 | 16 | 83 | depts | MANAGED_TABLE |
> | 84 | 16 | 84 | mv1 | MATERIALIZED_VIEW |
> ++---+---+--+---+
> 3 rows in set (0.00 sec)
> {code}
> The table is left with NULL in SD_ID, making it not available.
>
> 7) From Metastore.log
>
> {code:java}
> 2019-12-02T20:00:25,545 INFO [pool-6-thread-195]: me
[jira] [Updated] (HIVE-22566) Drop table involved in materialized view leaves the table in inconsistent state
[
https://issues.apache.org/jira/browse/HIVE-22566?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Pablo Junge updated HIVE-22566:
---
Attachment: HIVE-22566.02.patch
Status: Patch Available (was: Open)
> Drop table involved in materialized view leaves the table in inconsistent
> state
> ---
>
> Key: HIVE-22566
> URL: https://issues.apache.org/jira/browse/HIVE-22566
> Project: Hive
> Issue Type: Bug
> Components: HiveServer2
>Affects Versions: 3.1.0
>Reporter: Pablo Junge
>Assignee: Pablo Junge
>Priority: Minor
> Fix For: 4.0.0
>
> Attachments: HIVE-22566.02.patch, HIVE-22566.patch
>
>
> If you try dropping a table which is part of the definition of a created
> materialized view, the table is not dropped, which is the desired state as it
> is part of the materialized view.
> However, there was a "drop" call to the table, so it tried to drop it but did
> not succeed, leaving it in an inconsistent state.
>
> Repro:
> ---
> 1) Create tables:
>
> {code:java}
> CREATE TABLE emps ( empid INT, deptno INT, name VARCHAR(256), salary
> FLOAT, hire_date TIMESTAMP)STORED AS ORC TBLPROPERTIES
> ('transactional'='true');
> CREATE TABLE depts ( deptno INT, deptname VARCHAR(256), locationid
> INT)STORED AS ORC TBLPROPERTIES ('transactional'='true');
> {code}
>
> 2) Create the VM:
>
> {code:java}
> CREATE MATERIALIZED VIEW mv1 AS SELECT empid, deptname, hire_date FROM emps
> JOIN depts ON (emps.deptno = depts.deptno) WHERE hire_date >= '2016-01-01';
> {code}
>
> 3) Following is in backend database at this point:
>
> {code:java}
> mysql> select TBL_ID, DB_ID, SD_ID, TBL_NAME, TBL_TYPE from TBLS where
> DB_ID=16;
> ++---+---+--+---+
> | TBL_ID | DB_ID | SD_ID | TBL_NAME | TBL_TYPE |
> ++---+---+--+---+
> | 81 | 16 | 81 | emps | MANAGED_TABLE |
> | 83 | 16 | 83 | depts | MANAGED_TABLE |
> | 84 | 16 | 84 | mv1 | MATERIALIZED_VIEW |
> ++---+---+--+---+
> 3 rows in set (0.00 sec)
> {code}
>
> 4) Let's drop the 'emps' table:
>
> {code:java}
> 0: jdbc:hive2://c1122-node2.squadron.support.> drop table emps;
> INFO : Compiling
> command(queryId=hive_20191202200025_c13079d0-8695-4485-8a18-14804b8b014b):
> drop table emps
> INFO : Semantic Analysis Completed (retrial = false)
> INFO : Returning Hive schema: Schema(fieldSchemas:null, properties:null)
> INFO : Completed compiling
> command(queryId=hive_20191202200025_c13079d0-8695-4485-8a18-14804b8b014b);
> Time taken: 0.05 seconds
> INFO : Executing
> command(queryId=hive_20191202200025_c13079d0-8695-4485-8a18-14804b8b014b):
> drop table emps
> INFO : Starting task [Stage-0:DDL] in serial mode
> INFO : Completed executing
> command(queryId=hive_20191202200025_c13079d0-8695-4485-8a18-14804b8b014b);
> Time taken: 10.281 seconds
> INFO : OK
> No rows affected (16.949 seconds)
> {code}
> No issue displayed
>
> 5) List tables:
>
> {code:java}
> 0: jdbc:hive2://c1122-node2.squadron.support.> show tables;
> INFO : Compiling
> command(queryId=hive_20191202200125_ca12565b-1d4d-4433-a602-ecf685863413):
> show tables
> INFO : Semantic Analysis Completed (retrial = false)
> INFO : Returning Hive schema:
> Schema(fieldSchemas:[FieldSchema(name:tab_name, type:string, comment:from
> deserializer)], properties:null)
> INFO : Completed compiling
> command(queryId=hive_20191202200125_ca12565b-1d4d-4433-a602-ecf685863413);
> Time taken: 0.041 seconds
> INFO : Executing
> command(queryId=hive_20191202200125_ca12565b-1d4d-4433-a602-ecf685863413):
> show tables
> INFO : Starting task [Stage-0:DDL] in serial mode
> INFO : Completed executing
> command(queryId=hive_20191202200125_ca12565b-1d4d-4433-a602-ecf685863413);
> Time taken: 0.016 seconds
> INFO : OK
> +---+
> | tab_name |
> +---+
> | depts |
> | emps |
> +---+
> 2 rows selected (0.08 seconds)
> {code}
>
> 6) Now, from the backend-db point of view:
>
> {code:java}
> mysql> select TBL_ID, DB_ID, SD_ID, TBL_NAME, TBL_TYPE from TBLS where
> DB_ID=16;
> ++---+---+--+---+
> | TBL_ID | DB_ID | SD_ID | TBL_NAME | TBL_TYPE |
> ++---+---+--+---+
> | 81 | 16 | NULL | emps | MANAGED_TABLE |
> | 83 | 16 | 83 | depts | MANAGED_TABLE |
> | 84 | 16 | 84 | mv1 | MATERIALIZED_VIEW |
> ++---+---+--+---+
> 3 rows in set (0.00 sec)
> {code}
> The table is left with NULL in SD_ID, making it not available.
>
> 7) From Metastore.log
>
> {code:
[jira] [Updated] (HIVE-22566) Drop table involved in materialized view leaves the table in inconsistent state
[
https://issues.apache.org/jira/browse/HIVE-22566?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Pablo Junge updated HIVE-22566:
---
Status: Open (was: Patch Available)
> Drop table involved in materialized view leaves the table in inconsistent
> state
> ---
>
> Key: HIVE-22566
> URL: https://issues.apache.org/jira/browse/HIVE-22566
> Project: Hive
> Issue Type: Bug
> Components: HiveServer2
>Affects Versions: 3.1.0
>Reporter: Pablo Junge
>Assignee: Pablo Junge
>Priority: Minor
> Fix For: 4.0.0
>
> Attachments: HIVE-22566.02.patch, HIVE-22566.patch
>
>
> If you try dropping a table which is part of the definition of a created
> materialized view, the table is not dropped, which is the desired state as it
> is part of the materialized view.
> However, there was a "drop" call to the table, so it tried to drop it but did
> not succeed, leaving it in an inconsistent state.
>
> Repro:
> ---
> 1) Create tables:
>
> {code:java}
> CREATE TABLE emps ( empid INT, deptno INT, name VARCHAR(256), salary
> FLOAT, hire_date TIMESTAMP)STORED AS ORC TBLPROPERTIES
> ('transactional'='true');
> CREATE TABLE depts ( deptno INT, deptname VARCHAR(256), locationid
> INT)STORED AS ORC TBLPROPERTIES ('transactional'='true');
> {code}
>
> 2) Create the VM:
>
> {code:java}
> CREATE MATERIALIZED VIEW mv1 AS SELECT empid, deptname, hire_date FROM emps
> JOIN depts ON (emps.deptno = depts.deptno) WHERE hire_date >= '2016-01-01';
> {code}
>
> 3) Following is in backend database at this point:
>
> {code:java}
> mysql> select TBL_ID, DB_ID, SD_ID, TBL_NAME, TBL_TYPE from TBLS where
> DB_ID=16;
> ++---+---+--+---+
> | TBL_ID | DB_ID | SD_ID | TBL_NAME | TBL_TYPE |
> ++---+---+--+---+
> | 81 | 16 | 81 | emps | MANAGED_TABLE |
> | 83 | 16 | 83 | depts | MANAGED_TABLE |
> | 84 | 16 | 84 | mv1 | MATERIALIZED_VIEW |
> ++---+---+--+---+
> 3 rows in set (0.00 sec)
> {code}
>
> 4) Let's drop the 'emps' table:
>
> {code:java}
> 0: jdbc:hive2://c1122-node2.squadron.support.> drop table emps;
> INFO : Compiling
> command(queryId=hive_20191202200025_c13079d0-8695-4485-8a18-14804b8b014b):
> drop table emps
> INFO : Semantic Analysis Completed (retrial = false)
> INFO : Returning Hive schema: Schema(fieldSchemas:null, properties:null)
> INFO : Completed compiling
> command(queryId=hive_20191202200025_c13079d0-8695-4485-8a18-14804b8b014b);
> Time taken: 0.05 seconds
> INFO : Executing
> command(queryId=hive_20191202200025_c13079d0-8695-4485-8a18-14804b8b014b):
> drop table emps
> INFO : Starting task [Stage-0:DDL] in serial mode
> INFO : Completed executing
> command(queryId=hive_20191202200025_c13079d0-8695-4485-8a18-14804b8b014b);
> Time taken: 10.281 seconds
> INFO : OK
> No rows affected (16.949 seconds)
> {code}
> No issue displayed
>
> 5) List tables:
>
> {code:java}
> 0: jdbc:hive2://c1122-node2.squadron.support.> show tables;
> INFO : Compiling
> command(queryId=hive_20191202200125_ca12565b-1d4d-4433-a602-ecf685863413):
> show tables
> INFO : Semantic Analysis Completed (retrial = false)
> INFO : Returning Hive schema:
> Schema(fieldSchemas:[FieldSchema(name:tab_name, type:string, comment:from
> deserializer)], properties:null)
> INFO : Completed compiling
> command(queryId=hive_20191202200125_ca12565b-1d4d-4433-a602-ecf685863413);
> Time taken: 0.041 seconds
> INFO : Executing
> command(queryId=hive_20191202200125_ca12565b-1d4d-4433-a602-ecf685863413):
> show tables
> INFO : Starting task [Stage-0:DDL] in serial mode
> INFO : Completed executing
> command(queryId=hive_20191202200125_ca12565b-1d4d-4433-a602-ecf685863413);
> Time taken: 0.016 seconds
> INFO : OK
> +---+
> | tab_name |
> +---+
> | depts |
> | emps |
> +---+
> 2 rows selected (0.08 seconds)
> {code}
>
> 6) Now, from the backend-db point of view:
>
> {code:java}
> mysql> select TBL_ID, DB_ID, SD_ID, TBL_NAME, TBL_TYPE from TBLS where
> DB_ID=16;
> ++---+---+--+---+
> | TBL_ID | DB_ID | SD_ID | TBL_NAME | TBL_TYPE |
> ++---+---+--+---+
> | 81 | 16 | NULL | emps | MANAGED_TABLE |
> | 83 | 16 | 83 | depts | MANAGED_TABLE |
> | 84 | 16 | 84 | mv1 | MATERIALIZED_VIEW |
> ++---+---+--+---+
> 3 rows in set (0.00 sec)
> {code}
> The table is left with NULL in SD_ID, making it not available.
>
> 7) From Metastore.log
>
> {code:java}
> 2019-12-02T20:00:25,545 INFO [p
[jira] [Updated] (HIVE-22566) Drop table involved in materialized view leaves the table in inconsistent state
[
https://issues.apache.org/jira/browse/HIVE-22566?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Pablo Junge updated HIVE-22566:
---
Attachment: HIVE-22566.03.patch
Status: Patch Available (was: Open)
> Drop table involved in materialized view leaves the table in inconsistent
> state
> ---
>
> Key: HIVE-22566
> URL: https://issues.apache.org/jira/browse/HIVE-22566
> Project: Hive
> Issue Type: Bug
> Components: HiveServer2
>Affects Versions: 3.1.0
>Reporter: Pablo Junge
>Assignee: Pablo Junge
>Priority: Minor
> Fix For: 4.0.0
>
> Attachments: HIVE-22566.02.patch, HIVE-22566.03.patch,
> HIVE-22566.patch
>
>
> If you try dropping a table which is part of the definition of a created
> materialized view, the table is not dropped, which is the desired state as it
> is part of the materialized view.
> However, there was a "drop" call to the table, so it tried to drop it but did
> not succeed, leaving it in an inconsistent state.
>
> Repro:
> ---
> 1) Create tables:
>
> {code:java}
> CREATE TABLE emps ( empid INT, deptno INT, name VARCHAR(256), salary
> FLOAT, hire_date TIMESTAMP)STORED AS ORC TBLPROPERTIES
> ('transactional'='true');
> CREATE TABLE depts ( deptno INT, deptname VARCHAR(256), locationid
> INT)STORED AS ORC TBLPROPERTIES ('transactional'='true');
> {code}
>
> 2) Create the VM:
>
> {code:java}
> CREATE MATERIALIZED VIEW mv1 AS SELECT empid, deptname, hire_date FROM emps
> JOIN depts ON (emps.deptno = depts.deptno) WHERE hire_date >= '2016-01-01';
> {code}
>
> 3) Following is in backend database at this point:
>
> {code:java}
> mysql> select TBL_ID, DB_ID, SD_ID, TBL_NAME, TBL_TYPE from TBLS where
> DB_ID=16;
> ++---+---+--+---+
> | TBL_ID | DB_ID | SD_ID | TBL_NAME | TBL_TYPE |
> ++---+---+--+---+
> | 81 | 16 | 81 | emps | MANAGED_TABLE |
> | 83 | 16 | 83 | depts | MANAGED_TABLE |
> | 84 | 16 | 84 | mv1 | MATERIALIZED_VIEW |
> ++---+---+--+---+
> 3 rows in set (0.00 sec)
> {code}
>
> 4) Let's drop the 'emps' table:
>
> {code:java}
> 0: jdbc:hive2://c1122-node2.squadron.support.> drop table emps;
> INFO : Compiling
> command(queryId=hive_20191202200025_c13079d0-8695-4485-8a18-14804b8b014b):
> drop table emps
> INFO : Semantic Analysis Completed (retrial = false)
> INFO : Returning Hive schema: Schema(fieldSchemas:null, properties:null)
> INFO : Completed compiling
> command(queryId=hive_20191202200025_c13079d0-8695-4485-8a18-14804b8b014b);
> Time taken: 0.05 seconds
> INFO : Executing
> command(queryId=hive_20191202200025_c13079d0-8695-4485-8a18-14804b8b014b):
> drop table emps
> INFO : Starting task [Stage-0:DDL] in serial mode
> INFO : Completed executing
> command(queryId=hive_20191202200025_c13079d0-8695-4485-8a18-14804b8b014b);
> Time taken: 10.281 seconds
> INFO : OK
> No rows affected (16.949 seconds)
> {code}
> No issue displayed
>
> 5) List tables:
>
> {code:java}
> 0: jdbc:hive2://c1122-node2.squadron.support.> show tables;
> INFO : Compiling
> command(queryId=hive_20191202200125_ca12565b-1d4d-4433-a602-ecf685863413):
> show tables
> INFO : Semantic Analysis Completed (retrial = false)
> INFO : Returning Hive schema:
> Schema(fieldSchemas:[FieldSchema(name:tab_name, type:string, comment:from
> deserializer)], properties:null)
> INFO : Completed compiling
> command(queryId=hive_20191202200125_ca12565b-1d4d-4433-a602-ecf685863413);
> Time taken: 0.041 seconds
> INFO : Executing
> command(queryId=hive_20191202200125_ca12565b-1d4d-4433-a602-ecf685863413):
> show tables
> INFO : Starting task [Stage-0:DDL] in serial mode
> INFO : Completed executing
> command(queryId=hive_20191202200125_ca12565b-1d4d-4433-a602-ecf685863413);
> Time taken: 0.016 seconds
> INFO : OK
> +---+
> | tab_name |
> +---+
> | depts |
> | emps |
> +---+
> 2 rows selected (0.08 seconds)
> {code}
>
> 6) Now, from the backend-db point of view:
>
> {code:java}
> mysql> select TBL_ID, DB_ID, SD_ID, TBL_NAME, TBL_TYPE from TBLS where
> DB_ID=16;
> ++---+---+--+---+
> | TBL_ID | DB_ID | SD_ID | TBL_NAME | TBL_TYPE |
> ++---+---+--+---+
> | 81 | 16 | NULL | emps | MANAGED_TABLE |
> | 83 | 16 | 83 | depts | MANAGED_TABLE |
> | 84 | 16 | 84 | mv1 | MATERIALIZED_VIEW |
> ++---+---+--+---+
> 3 rows in set (0.00 sec)
> {code}
> The table is left with NULL in SD_ID, making it not available.
>
> 7) From Me
[jira] [Updated] (HIVE-22566) Drop table involved in materialized view leaves the table in inconsistent state
[
https://issues.apache.org/jira/browse/HIVE-22566?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Pablo Junge updated HIVE-22566:
---
Status: Open (was: Patch Available)
> Drop table involved in materialized view leaves the table in inconsistent
> state
> ---
>
> Key: HIVE-22566
> URL: https://issues.apache.org/jira/browse/HIVE-22566
> Project: Hive
> Issue Type: Bug
> Components: HiveServer2
>Affects Versions: 3.1.0
>Reporter: Pablo Junge
>Assignee: Pablo Junge
>Priority: Minor
> Fix For: 4.0.0
>
> Attachments: HIVE-22566.02.patch, HIVE-22566.03.patch,
> HIVE-22566.patch
>
>
> If you try dropping a table which is part of the definition of a created
> materialized view, the table is not dropped, which is the desired state as it
> is part of the materialized view.
> However, there was a "drop" call to the table, so it tried to drop it but did
> not succeed, leaving it in an inconsistent state.
>
> Repro:
> ---
> 1) Create tables:
>
> {code:java}
> CREATE TABLE emps ( empid INT, deptno INT, name VARCHAR(256), salary
> FLOAT, hire_date TIMESTAMP)STORED AS ORC TBLPROPERTIES
> ('transactional'='true');
> CREATE TABLE depts ( deptno INT, deptname VARCHAR(256), locationid
> INT)STORED AS ORC TBLPROPERTIES ('transactional'='true');
> {code}
>
> 2) Create the VM:
>
> {code:java}
> CREATE MATERIALIZED VIEW mv1 AS SELECT empid, deptname, hire_date FROM emps
> JOIN depts ON (emps.deptno = depts.deptno) WHERE hire_date >= '2016-01-01';
> {code}
>
> 3) Following is in backend database at this point:
>
> {code:java}
> mysql> select TBL_ID, DB_ID, SD_ID, TBL_NAME, TBL_TYPE from TBLS where
> DB_ID=16;
> ++---+---+--+---+
> | TBL_ID | DB_ID | SD_ID | TBL_NAME | TBL_TYPE |
> ++---+---+--+---+
> | 81 | 16 | 81 | emps | MANAGED_TABLE |
> | 83 | 16 | 83 | depts | MANAGED_TABLE |
> | 84 | 16 | 84 | mv1 | MATERIALIZED_VIEW |
> ++---+---+--+---+
> 3 rows in set (0.00 sec)
> {code}
>
> 4) Let's drop the 'emps' table:
>
> {code:java}
> 0: jdbc:hive2://c1122-node2.squadron.support.> drop table emps;
> INFO : Compiling
> command(queryId=hive_20191202200025_c13079d0-8695-4485-8a18-14804b8b014b):
> drop table emps
> INFO : Semantic Analysis Completed (retrial = false)
> INFO : Returning Hive schema: Schema(fieldSchemas:null, properties:null)
> INFO : Completed compiling
> command(queryId=hive_20191202200025_c13079d0-8695-4485-8a18-14804b8b014b);
> Time taken: 0.05 seconds
> INFO : Executing
> command(queryId=hive_20191202200025_c13079d0-8695-4485-8a18-14804b8b014b):
> drop table emps
> INFO : Starting task [Stage-0:DDL] in serial mode
> INFO : Completed executing
> command(queryId=hive_20191202200025_c13079d0-8695-4485-8a18-14804b8b014b);
> Time taken: 10.281 seconds
> INFO : OK
> No rows affected (16.949 seconds)
> {code}
> No issue displayed
>
> 5) List tables:
>
> {code:java}
> 0: jdbc:hive2://c1122-node2.squadron.support.> show tables;
> INFO : Compiling
> command(queryId=hive_20191202200125_ca12565b-1d4d-4433-a602-ecf685863413):
> show tables
> INFO : Semantic Analysis Completed (retrial = false)
> INFO : Returning Hive schema:
> Schema(fieldSchemas:[FieldSchema(name:tab_name, type:string, comment:from
> deserializer)], properties:null)
> INFO : Completed compiling
> command(queryId=hive_20191202200125_ca12565b-1d4d-4433-a602-ecf685863413);
> Time taken: 0.041 seconds
> INFO : Executing
> command(queryId=hive_20191202200125_ca12565b-1d4d-4433-a602-ecf685863413):
> show tables
> INFO : Starting task [Stage-0:DDL] in serial mode
> INFO : Completed executing
> command(queryId=hive_20191202200125_ca12565b-1d4d-4433-a602-ecf685863413);
> Time taken: 0.016 seconds
> INFO : OK
> +---+
> | tab_name |
> +---+
> | depts |
> | emps |
> +---+
> 2 rows selected (0.08 seconds)
> {code}
>
> 6) Now, from the backend-db point of view:
>
> {code:java}
> mysql> select TBL_ID, DB_ID, SD_ID, TBL_NAME, TBL_TYPE from TBLS where
> DB_ID=16;
> ++---+---+--+---+
> | TBL_ID | DB_ID | SD_ID | TBL_NAME | TBL_TYPE |
> ++---+---+--+---+
> | 81 | 16 | NULL | emps | MANAGED_TABLE |
> | 83 | 16 | 83 | depts | MANAGED_TABLE |
> | 84 | 16 | 84 | mv1 | MATERIALIZED_VIEW |
> ++---+---+--+---+
> 3 rows in set (0.00 sec)
> {code}
> The table is left with NULL in SD_ID, making it not available.
>
> 7) From Metastore.log
>
> {code:java}
> 2019-12-
[jira] [Updated] (HIVE-22566) Drop table involved in materialized view leaves the table in inconsistent state
[
https://issues.apache.org/jira/browse/HIVE-22566?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Pablo Junge updated HIVE-22566:
---
Attachment: HIVE-22566.04.patch
Status: Patch Available (was: Open)
> Drop table involved in materialized view leaves the table in inconsistent
> state
> ---
>
> Key: HIVE-22566
> URL: https://issues.apache.org/jira/browse/HIVE-22566
> Project: Hive
> Issue Type: Bug
> Components: HiveServer2
>Affects Versions: 3.1.0
>Reporter: Pablo Junge
>Assignee: Pablo Junge
>Priority: Minor
> Fix For: 4.0.0
>
> Attachments: HIVE-22566.02.patch, HIVE-22566.03.patch,
> HIVE-22566.04.patch, HIVE-22566.patch
>
>
> If you try dropping a table which is part of the definition of a created
> materialized view, the table is not dropped, which is the desired state as it
> is part of the materialized view.
> However, there was a "drop" call to the table, so it tried to drop it but did
> not succeed, leaving it in an inconsistent state.
>
> Repro:
> ---
> 1) Create tables:
>
> {code:java}
> CREATE TABLE emps ( empid INT, deptno INT, name VARCHAR(256), salary
> FLOAT, hire_date TIMESTAMP)STORED AS ORC TBLPROPERTIES
> ('transactional'='true');
> CREATE TABLE depts ( deptno INT, deptname VARCHAR(256), locationid
> INT)STORED AS ORC TBLPROPERTIES ('transactional'='true');
> {code}
>
> 2) Create the VM:
>
> {code:java}
> CREATE MATERIALIZED VIEW mv1 AS SELECT empid, deptname, hire_date FROM emps
> JOIN depts ON (emps.deptno = depts.deptno) WHERE hire_date >= '2016-01-01';
> {code}
>
> 3) Following is in backend database at this point:
>
> {code:java}
> mysql> select TBL_ID, DB_ID, SD_ID, TBL_NAME, TBL_TYPE from TBLS where
> DB_ID=16;
> ++---+---+--+---+
> | TBL_ID | DB_ID | SD_ID | TBL_NAME | TBL_TYPE |
> ++---+---+--+---+
> | 81 | 16 | 81 | emps | MANAGED_TABLE |
> | 83 | 16 | 83 | depts | MANAGED_TABLE |
> | 84 | 16 | 84 | mv1 | MATERIALIZED_VIEW |
> ++---+---+--+---+
> 3 rows in set (0.00 sec)
> {code}
>
> 4) Let's drop the 'emps' table:
>
> {code:java}
> 0: jdbc:hive2://c1122-node2.squadron.support.> drop table emps;
> INFO : Compiling
> command(queryId=hive_20191202200025_c13079d0-8695-4485-8a18-14804b8b014b):
> drop table emps
> INFO : Semantic Analysis Completed (retrial = false)
> INFO : Returning Hive schema: Schema(fieldSchemas:null, properties:null)
> INFO : Completed compiling
> command(queryId=hive_20191202200025_c13079d0-8695-4485-8a18-14804b8b014b);
> Time taken: 0.05 seconds
> INFO : Executing
> command(queryId=hive_20191202200025_c13079d0-8695-4485-8a18-14804b8b014b):
> drop table emps
> INFO : Starting task [Stage-0:DDL] in serial mode
> INFO : Completed executing
> command(queryId=hive_20191202200025_c13079d0-8695-4485-8a18-14804b8b014b);
> Time taken: 10.281 seconds
> INFO : OK
> No rows affected (16.949 seconds)
> {code}
> No issue displayed
>
> 5) List tables:
>
> {code:java}
> 0: jdbc:hive2://c1122-node2.squadron.support.> show tables;
> INFO : Compiling
> command(queryId=hive_20191202200125_ca12565b-1d4d-4433-a602-ecf685863413):
> show tables
> INFO : Semantic Analysis Completed (retrial = false)
> INFO : Returning Hive schema:
> Schema(fieldSchemas:[FieldSchema(name:tab_name, type:string, comment:from
> deserializer)], properties:null)
> INFO : Completed compiling
> command(queryId=hive_20191202200125_ca12565b-1d4d-4433-a602-ecf685863413);
> Time taken: 0.041 seconds
> INFO : Executing
> command(queryId=hive_20191202200125_ca12565b-1d4d-4433-a602-ecf685863413):
> show tables
> INFO : Starting task [Stage-0:DDL] in serial mode
> INFO : Completed executing
> command(queryId=hive_20191202200125_ca12565b-1d4d-4433-a602-ecf685863413);
> Time taken: 0.016 seconds
> INFO : OK
> +---+
> | tab_name |
> +---+
> | depts |
> | emps |
> +---+
> 2 rows selected (0.08 seconds)
> {code}
>
> 6) Now, from the backend-db point of view:
>
> {code:java}
> mysql> select TBL_ID, DB_ID, SD_ID, TBL_NAME, TBL_TYPE from TBLS where
> DB_ID=16;
> ++---+---+--+---+
> | TBL_ID | DB_ID | SD_ID | TBL_NAME | TBL_TYPE |
> ++---+---+--+---+
> | 81 | 16 | NULL | emps | MANAGED_TABLE |
> | 83 | 16 | 83 | depts | MANAGED_TABLE |
> | 84 | 16 | 84 | mv1 | MATERIALIZED_VIEW |
> ++---+---+--+---+
> 3 rows in set (0.00 sec)
> {code}
> The table is left with NULL in SD_ID, making it not availa
[jira] [Updated] (HIVE-22566) Drop table involved in materialized view leaves the table in inconsistent state
[
https://issues.apache.org/jira/browse/HIVE-22566?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Pablo Junge updated HIVE-22566:
---
Attachment: (was: HIVE-22566.04.patch)
> Drop table involved in materialized view leaves the table in inconsistent
> state
> ---
>
> Key: HIVE-22566
> URL: https://issues.apache.org/jira/browse/HIVE-22566
> Project: Hive
> Issue Type: Bug
> Components: HiveServer2
>Affects Versions: 3.1.0
>Reporter: Pablo Junge
>Assignee: Pablo Junge
>Priority: Minor
> Fix For: 4.0.0
>
> Attachments: HIVE-22566.02.patch, HIVE-22566.03.patch,
> HIVE-22566.patch
>
>
> If you try dropping a table which is part of the definition of a created
> materialized view, the table is not dropped, which is the desired state as it
> is part of the materialized view.
> However, there was a "drop" call to the table, so it tried to drop it but did
> not succeed, leaving it in an inconsistent state.
>
> Repro:
> ---
> 1) Create tables:
>
> {code:java}
> CREATE TABLE emps ( empid INT, deptno INT, name VARCHAR(256), salary
> FLOAT, hire_date TIMESTAMP)STORED AS ORC TBLPROPERTIES
> ('transactional'='true');
> CREATE TABLE depts ( deptno INT, deptname VARCHAR(256), locationid
> INT)STORED AS ORC TBLPROPERTIES ('transactional'='true');
> {code}
>
> 2) Create the VM:
>
> {code:java}
> CREATE MATERIALIZED VIEW mv1 AS SELECT empid, deptname, hire_date FROM emps
> JOIN depts ON (emps.deptno = depts.deptno) WHERE hire_date >= '2016-01-01';
> {code}
>
> 3) Following is in backend database at this point:
>
> {code:java}
> mysql> select TBL_ID, DB_ID, SD_ID, TBL_NAME, TBL_TYPE from TBLS where
> DB_ID=16;
> ++---+---+--+---+
> | TBL_ID | DB_ID | SD_ID | TBL_NAME | TBL_TYPE |
> ++---+---+--+---+
> | 81 | 16 | 81 | emps | MANAGED_TABLE |
> | 83 | 16 | 83 | depts | MANAGED_TABLE |
> | 84 | 16 | 84 | mv1 | MATERIALIZED_VIEW |
> ++---+---+--+---+
> 3 rows in set (0.00 sec)
> {code}
>
> 4) Let's drop the 'emps' table:
>
> {code:java}
> 0: jdbc:hive2://c1122-node2.squadron.support.> drop table emps;
> INFO : Compiling
> command(queryId=hive_20191202200025_c13079d0-8695-4485-8a18-14804b8b014b):
> drop table emps
> INFO : Semantic Analysis Completed (retrial = false)
> INFO : Returning Hive schema: Schema(fieldSchemas:null, properties:null)
> INFO : Completed compiling
> command(queryId=hive_20191202200025_c13079d0-8695-4485-8a18-14804b8b014b);
> Time taken: 0.05 seconds
> INFO : Executing
> command(queryId=hive_20191202200025_c13079d0-8695-4485-8a18-14804b8b014b):
> drop table emps
> INFO : Starting task [Stage-0:DDL] in serial mode
> INFO : Completed executing
> command(queryId=hive_20191202200025_c13079d0-8695-4485-8a18-14804b8b014b);
> Time taken: 10.281 seconds
> INFO : OK
> No rows affected (16.949 seconds)
> {code}
> No issue displayed
>
> 5) List tables:
>
> {code:java}
> 0: jdbc:hive2://c1122-node2.squadron.support.> show tables;
> INFO : Compiling
> command(queryId=hive_20191202200125_ca12565b-1d4d-4433-a602-ecf685863413):
> show tables
> INFO : Semantic Analysis Completed (retrial = false)
> INFO : Returning Hive schema:
> Schema(fieldSchemas:[FieldSchema(name:tab_name, type:string, comment:from
> deserializer)], properties:null)
> INFO : Completed compiling
> command(queryId=hive_20191202200125_ca12565b-1d4d-4433-a602-ecf685863413);
> Time taken: 0.041 seconds
> INFO : Executing
> command(queryId=hive_20191202200125_ca12565b-1d4d-4433-a602-ecf685863413):
> show tables
> INFO : Starting task [Stage-0:DDL] in serial mode
> INFO : Completed executing
> command(queryId=hive_20191202200125_ca12565b-1d4d-4433-a602-ecf685863413);
> Time taken: 0.016 seconds
> INFO : OK
> +---+
> | tab_name |
> +---+
> | depts |
> | emps |
> +---+
> 2 rows selected (0.08 seconds)
> {code}
>
> 6) Now, from the backend-db point of view:
>
> {code:java}
> mysql> select TBL_ID, DB_ID, SD_ID, TBL_NAME, TBL_TYPE from TBLS where
> DB_ID=16;
> ++---+---+--+---+
> | TBL_ID | DB_ID | SD_ID | TBL_NAME | TBL_TYPE |
> ++---+---+--+---+
> | 81 | 16 | NULL | emps | MANAGED_TABLE |
> | 83 | 16 | 83 | depts | MANAGED_TABLE |
> | 84 | 16 | 84 | mv1 | MATERIALIZED_VIEW |
> ++---+---+--+---+
> 3 rows in set (0.00 sec)
> {code}
> The table is left with NULL in SD_ID, making it not available.
>
> 7) From Metastore.log
>
> {code:java}
> 20
[jira] [Updated] (HIVE-22566) Drop table involved in materialized view leaves the table in inconsistent state
[
https://issues.apache.org/jira/browse/HIVE-22566?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Pablo Junge updated HIVE-22566:
---
Status: Open (was: Patch Available)
> Drop table involved in materialized view leaves the table in inconsistent
> state
> ---
>
> Key: HIVE-22566
> URL: https://issues.apache.org/jira/browse/HIVE-22566
> Project: Hive
> Issue Type: Bug
> Components: HiveServer2
>Affects Versions: 3.1.0
>Reporter: Pablo Junge
>Assignee: Pablo Junge
>Priority: Minor
> Fix For: 4.0.0
>
> Attachments: HIVE-22566.02.patch, HIVE-22566.03.patch,
> HIVE-22566.04.patch, HIVE-22566.patch
>
>
> If you try dropping a table which is part of the definition of a created
> materialized view, the table is not dropped, which is the desired state as it
> is part of the materialized view.
> However, there was a "drop" call to the table, so it tried to drop it but did
> not succeed, leaving it in an inconsistent state.
>
> Repro:
> ---
> 1) Create tables:
>
> {code:java}
> CREATE TABLE emps ( empid INT, deptno INT, name VARCHAR(256), salary
> FLOAT, hire_date TIMESTAMP)STORED AS ORC TBLPROPERTIES
> ('transactional'='true');
> CREATE TABLE depts ( deptno INT, deptname VARCHAR(256), locationid
> INT)STORED AS ORC TBLPROPERTIES ('transactional'='true');
> {code}
>
> 2) Create the VM:
>
> {code:java}
> CREATE MATERIALIZED VIEW mv1 AS SELECT empid, deptname, hire_date FROM emps
> JOIN depts ON (emps.deptno = depts.deptno) WHERE hire_date >= '2016-01-01';
> {code}
>
> 3) Following is in backend database at this point:
>
> {code:java}
> mysql> select TBL_ID, DB_ID, SD_ID, TBL_NAME, TBL_TYPE from TBLS where
> DB_ID=16;
> ++---+---+--+---+
> | TBL_ID | DB_ID | SD_ID | TBL_NAME | TBL_TYPE |
> ++---+---+--+---+
> | 81 | 16 | 81 | emps | MANAGED_TABLE |
> | 83 | 16 | 83 | depts | MANAGED_TABLE |
> | 84 | 16 | 84 | mv1 | MATERIALIZED_VIEW |
> ++---+---+--+---+
> 3 rows in set (0.00 sec)
> {code}
>
> 4) Let's drop the 'emps' table:
>
> {code:java}
> 0: jdbc:hive2://c1122-node2.squadron.support.> drop table emps;
> INFO : Compiling
> command(queryId=hive_20191202200025_c13079d0-8695-4485-8a18-14804b8b014b):
> drop table emps
> INFO : Semantic Analysis Completed (retrial = false)
> INFO : Returning Hive schema: Schema(fieldSchemas:null, properties:null)
> INFO : Completed compiling
> command(queryId=hive_20191202200025_c13079d0-8695-4485-8a18-14804b8b014b);
> Time taken: 0.05 seconds
> INFO : Executing
> command(queryId=hive_20191202200025_c13079d0-8695-4485-8a18-14804b8b014b):
> drop table emps
> INFO : Starting task [Stage-0:DDL] in serial mode
> INFO : Completed executing
> command(queryId=hive_20191202200025_c13079d0-8695-4485-8a18-14804b8b014b);
> Time taken: 10.281 seconds
> INFO : OK
> No rows affected (16.949 seconds)
> {code}
> No issue displayed
>
> 5) List tables:
>
> {code:java}
> 0: jdbc:hive2://c1122-node2.squadron.support.> show tables;
> INFO : Compiling
> command(queryId=hive_20191202200125_ca12565b-1d4d-4433-a602-ecf685863413):
> show tables
> INFO : Semantic Analysis Completed (retrial = false)
> INFO : Returning Hive schema:
> Schema(fieldSchemas:[FieldSchema(name:tab_name, type:string, comment:from
> deserializer)], properties:null)
> INFO : Completed compiling
> command(queryId=hive_20191202200125_ca12565b-1d4d-4433-a602-ecf685863413);
> Time taken: 0.041 seconds
> INFO : Executing
> command(queryId=hive_20191202200125_ca12565b-1d4d-4433-a602-ecf685863413):
> show tables
> INFO : Starting task [Stage-0:DDL] in serial mode
> INFO : Completed executing
> command(queryId=hive_20191202200125_ca12565b-1d4d-4433-a602-ecf685863413);
> Time taken: 0.016 seconds
> INFO : OK
> +---+
> | tab_name |
> +---+
> | depts |
> | emps |
> +---+
> 2 rows selected (0.08 seconds)
> {code}
>
> 6) Now, from the backend-db point of view:
>
> {code:java}
> mysql> select TBL_ID, DB_ID, SD_ID, TBL_NAME, TBL_TYPE from TBLS where
> DB_ID=16;
> ++---+---+--+---+
> | TBL_ID | DB_ID | SD_ID | TBL_NAME | TBL_TYPE |
> ++---+---+--+---+
> | 81 | 16 | NULL | emps | MANAGED_TABLE |
> | 83 | 16 | 83 | depts | MANAGED_TABLE |
> | 84 | 16 | 84 | mv1 | MATERIALIZED_VIEW |
> ++---+---+--+---+
> 3 rows in set (0.00 sec)
> {code}
> The table is left with NULL in SD_ID, making it not available.
>
> 7) From Metastore.log
>
> {
[jira] [Updated] (HIVE-22566) Drop table involved in materialized view leaves the table in inconsistent state
[
https://issues.apache.org/jira/browse/HIVE-22566?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Pablo Junge updated HIVE-22566:
---
Attachment: HIVE-22566.04.patch
Status: Patch Available (was: Open)
Resubmitting
> Drop table involved in materialized view leaves the table in inconsistent
> state
> ---
>
> Key: HIVE-22566
> URL: https://issues.apache.org/jira/browse/HIVE-22566
> Project: Hive
> Issue Type: Bug
> Components: HiveServer2
>Affects Versions: 3.1.0
>Reporter: Pablo Junge
>Assignee: Pablo Junge
>Priority: Minor
> Fix For: 4.0.0
>
> Attachments: HIVE-22566.02.patch, HIVE-22566.03.patch,
> HIVE-22566.04.patch, HIVE-22566.patch
>
>
> If you try dropping a table which is part of the definition of a created
> materialized view, the table is not dropped, which is the desired state as it
> is part of the materialized view.
> However, there was a "drop" call to the table, so it tried to drop it but did
> not succeed, leaving it in an inconsistent state.
>
> Repro:
> ---
> 1) Create tables:
>
> {code:java}
> CREATE TABLE emps ( empid INT, deptno INT, name VARCHAR(256), salary
> FLOAT, hire_date TIMESTAMP)STORED AS ORC TBLPROPERTIES
> ('transactional'='true');
> CREATE TABLE depts ( deptno INT, deptname VARCHAR(256), locationid
> INT)STORED AS ORC TBLPROPERTIES ('transactional'='true');
> {code}
>
> 2) Create the VM:
>
> {code:java}
> CREATE MATERIALIZED VIEW mv1 AS SELECT empid, deptname, hire_date FROM emps
> JOIN depts ON (emps.deptno = depts.deptno) WHERE hire_date >= '2016-01-01';
> {code}
>
> 3) Following is in backend database at this point:
>
> {code:java}
> mysql> select TBL_ID, DB_ID, SD_ID, TBL_NAME, TBL_TYPE from TBLS where
> DB_ID=16;
> ++---+---+--+---+
> | TBL_ID | DB_ID | SD_ID | TBL_NAME | TBL_TYPE |
> ++---+---+--+---+
> | 81 | 16 | 81 | emps | MANAGED_TABLE |
> | 83 | 16 | 83 | depts | MANAGED_TABLE |
> | 84 | 16 | 84 | mv1 | MATERIALIZED_VIEW |
> ++---+---+--+---+
> 3 rows in set (0.00 sec)
> {code}
>
> 4) Let's drop the 'emps' table:
>
> {code:java}
> 0: jdbc:hive2://c1122-node2.squadron.support.> drop table emps;
> INFO : Compiling
> command(queryId=hive_20191202200025_c13079d0-8695-4485-8a18-14804b8b014b):
> drop table emps
> INFO : Semantic Analysis Completed (retrial = false)
> INFO : Returning Hive schema: Schema(fieldSchemas:null, properties:null)
> INFO : Completed compiling
> command(queryId=hive_20191202200025_c13079d0-8695-4485-8a18-14804b8b014b);
> Time taken: 0.05 seconds
> INFO : Executing
> command(queryId=hive_20191202200025_c13079d0-8695-4485-8a18-14804b8b014b):
> drop table emps
> INFO : Starting task [Stage-0:DDL] in serial mode
> INFO : Completed executing
> command(queryId=hive_20191202200025_c13079d0-8695-4485-8a18-14804b8b014b);
> Time taken: 10.281 seconds
> INFO : OK
> No rows affected (16.949 seconds)
> {code}
> No issue displayed
>
> 5) List tables:
>
> {code:java}
> 0: jdbc:hive2://c1122-node2.squadron.support.> show tables;
> INFO : Compiling
> command(queryId=hive_20191202200125_ca12565b-1d4d-4433-a602-ecf685863413):
> show tables
> INFO : Semantic Analysis Completed (retrial = false)
> INFO : Returning Hive schema:
> Schema(fieldSchemas:[FieldSchema(name:tab_name, type:string, comment:from
> deserializer)], properties:null)
> INFO : Completed compiling
> command(queryId=hive_20191202200125_ca12565b-1d4d-4433-a602-ecf685863413);
> Time taken: 0.041 seconds
> INFO : Executing
> command(queryId=hive_20191202200125_ca12565b-1d4d-4433-a602-ecf685863413):
> show tables
> INFO : Starting task [Stage-0:DDL] in serial mode
> INFO : Completed executing
> command(queryId=hive_20191202200125_ca12565b-1d4d-4433-a602-ecf685863413);
> Time taken: 0.016 seconds
> INFO : OK
> +---+
> | tab_name |
> +---+
> | depts |
> | emps |
> +---+
> 2 rows selected (0.08 seconds)
> {code}
>
> 6) Now, from the backend-db point of view:
>
> {code:java}
> mysql> select TBL_ID, DB_ID, SD_ID, TBL_NAME, TBL_TYPE from TBLS where
> DB_ID=16;
> ++---+---+--+---+
> | TBL_ID | DB_ID | SD_ID | TBL_NAME | TBL_TYPE |
> ++---+---+--+---+
> | 81 | 16 | NULL | emps | MANAGED_TABLE |
> | 83 | 16 | 83 | depts | MANAGED_TABLE |
> | 84 | 16 | 84 | mv1 | MATERIALIZED_VIEW |
> ++---+---+--+---+
> 3 rows in set (0.00 sec)
> {code}
> The table is left with NULL in SD_ID, making
[jira] [Updated] (HIVE-22566) Drop table involved in materialized view leaves the table in inconsistent state
[
https://issues.apache.org/jira/browse/HIVE-22566?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Pablo Junge updated HIVE-22566:
---
Attachment: HIVE-22566.05.patch
Status: Patch Available (was: Open)
> Drop table involved in materialized view leaves the table in inconsistent
> state
> ---
>
> Key: HIVE-22566
> URL: https://issues.apache.org/jira/browse/HIVE-22566
> Project: Hive
> Issue Type: Bug
> Components: HiveServer2
>Affects Versions: 3.1.0
>Reporter: Pablo Junge
>Assignee: Pablo Junge
>Priority: Minor
> Fix For: 4.0.0
>
> Attachments: HIVE-22566.02.patch, HIVE-22566.03.patch,
> HIVE-22566.04.patch, HIVE-22566.05.patch, HIVE-22566.patch
>
>
> If you try dropping a table which is part of the definition of a created
> materialized view, the table is not dropped, which is the desired state as it
> is part of the materialized view.
> However, there was a "drop" call to the table, so it tried to drop it but did
> not succeed, leaving it in an inconsistent state.
>
> Repro:
> ---
> 1) Create tables:
>
> {code:java}
> CREATE TABLE emps ( empid INT, deptno INT, name VARCHAR(256), salary
> FLOAT, hire_date TIMESTAMP)STORED AS ORC TBLPROPERTIES
> ('transactional'='true');
> CREATE TABLE depts ( deptno INT, deptname VARCHAR(256), locationid
> INT)STORED AS ORC TBLPROPERTIES ('transactional'='true');
> {code}
>
> 2) Create the VM:
>
> {code:java}
> CREATE MATERIALIZED VIEW mv1 AS SELECT empid, deptname, hire_date FROM emps
> JOIN depts ON (emps.deptno = depts.deptno) WHERE hire_date >= '2016-01-01';
> {code}
>
> 3) Following is in backend database at this point:
>
> {code:java}
> mysql> select TBL_ID, DB_ID, SD_ID, TBL_NAME, TBL_TYPE from TBLS where
> DB_ID=16;
> ++---+---+--+---+
> | TBL_ID | DB_ID | SD_ID | TBL_NAME | TBL_TYPE |
> ++---+---+--+---+
> | 81 | 16 | 81 | emps | MANAGED_TABLE |
> | 83 | 16 | 83 | depts | MANAGED_TABLE |
> | 84 | 16 | 84 | mv1 | MATERIALIZED_VIEW |
> ++---+---+--+---+
> 3 rows in set (0.00 sec)
> {code}
>
> 4) Let's drop the 'emps' table:
>
> {code:java}
> 0: jdbc:hive2://c1122-node2.squadron.support.> drop table emps;
> INFO : Compiling
> command(queryId=hive_20191202200025_c13079d0-8695-4485-8a18-14804b8b014b):
> drop table emps
> INFO : Semantic Analysis Completed (retrial = false)
> INFO : Returning Hive schema: Schema(fieldSchemas:null, properties:null)
> INFO : Completed compiling
> command(queryId=hive_20191202200025_c13079d0-8695-4485-8a18-14804b8b014b);
> Time taken: 0.05 seconds
> INFO : Executing
> command(queryId=hive_20191202200025_c13079d0-8695-4485-8a18-14804b8b014b):
> drop table emps
> INFO : Starting task [Stage-0:DDL] in serial mode
> INFO : Completed executing
> command(queryId=hive_20191202200025_c13079d0-8695-4485-8a18-14804b8b014b);
> Time taken: 10.281 seconds
> INFO : OK
> No rows affected (16.949 seconds)
> {code}
> No issue displayed
>
> 5) List tables:
>
> {code:java}
> 0: jdbc:hive2://c1122-node2.squadron.support.> show tables;
> INFO : Compiling
> command(queryId=hive_20191202200125_ca12565b-1d4d-4433-a602-ecf685863413):
> show tables
> INFO : Semantic Analysis Completed (retrial = false)
> INFO : Returning Hive schema:
> Schema(fieldSchemas:[FieldSchema(name:tab_name, type:string, comment:from
> deserializer)], properties:null)
> INFO : Completed compiling
> command(queryId=hive_20191202200125_ca12565b-1d4d-4433-a602-ecf685863413);
> Time taken: 0.041 seconds
> INFO : Executing
> command(queryId=hive_20191202200125_ca12565b-1d4d-4433-a602-ecf685863413):
> show tables
> INFO : Starting task [Stage-0:DDL] in serial mode
> INFO : Completed executing
> command(queryId=hive_20191202200125_ca12565b-1d4d-4433-a602-ecf685863413);
> Time taken: 0.016 seconds
> INFO : OK
> +---+
> | tab_name |
> +---+
> | depts |
> | emps |
> +---+
> 2 rows selected (0.08 seconds)
> {code}
>
> 6) Now, from the backend-db point of view:
>
> {code:java}
> mysql> select TBL_ID, DB_ID, SD_ID, TBL_NAME, TBL_TYPE from TBLS where
> DB_ID=16;
> ++---+---+--+---+
> | TBL_ID | DB_ID | SD_ID | TBL_NAME | TBL_TYPE |
> ++---+---+--+---+
> | 81 | 16 | NULL | emps | MANAGED_TABLE |
> | 83 | 16 | 83 | depts | MANAGED_TABLE |
> | 84 | 16 | 84 | mv1 | MATERIALIZED_VIEW |
> ++---+---+--+---+
> 3 rows in set (0.00 sec)
> {code}
> The table is left with NULL in SD_ID,
[jira] [Updated] (HIVE-22566) Drop table involved in materialized view leaves the table in inconsistent state
[
https://issues.apache.org/jira/browse/HIVE-22566?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Pablo Junge updated HIVE-22566:
---
Status: Open (was: Patch Available)
> Drop table involved in materialized view leaves the table in inconsistent
> state
> ---
>
> Key: HIVE-22566
> URL: https://issues.apache.org/jira/browse/HIVE-22566
> Project: Hive
> Issue Type: Bug
> Components: HiveServer2
>Affects Versions: 3.1.0
>Reporter: Pablo Junge
>Assignee: Pablo Junge
>Priority: Minor
> Fix For: 4.0.0
>
> Attachments: HIVE-22566.02.patch, HIVE-22566.03.patch,
> HIVE-22566.04.patch, HIVE-22566.05.patch, HIVE-22566.patch
>
>
> If you try dropping a table which is part of the definition of a created
> materialized view, the table is not dropped, which is the desired state as it
> is part of the materialized view.
> However, there was a "drop" call to the table, so it tried to drop it but did
> not succeed, leaving it in an inconsistent state.
>
> Repro:
> ---
> 1) Create tables:
>
> {code:java}
> CREATE TABLE emps ( empid INT, deptno INT, name VARCHAR(256), salary
> FLOAT, hire_date TIMESTAMP)STORED AS ORC TBLPROPERTIES
> ('transactional'='true');
> CREATE TABLE depts ( deptno INT, deptname VARCHAR(256), locationid
> INT)STORED AS ORC TBLPROPERTIES ('transactional'='true');
> {code}
>
> 2) Create the VM:
>
> {code:java}
> CREATE MATERIALIZED VIEW mv1 AS SELECT empid, deptname, hire_date FROM emps
> JOIN depts ON (emps.deptno = depts.deptno) WHERE hire_date >= '2016-01-01';
> {code}
>
> 3) Following is in backend database at this point:
>
> {code:java}
> mysql> select TBL_ID, DB_ID, SD_ID, TBL_NAME, TBL_TYPE from TBLS where
> DB_ID=16;
> ++---+---+--+---+
> | TBL_ID | DB_ID | SD_ID | TBL_NAME | TBL_TYPE |
> ++---+---+--+---+
> | 81 | 16 | 81 | emps | MANAGED_TABLE |
> | 83 | 16 | 83 | depts | MANAGED_TABLE |
> | 84 | 16 | 84 | mv1 | MATERIALIZED_VIEW |
> ++---+---+--+---+
> 3 rows in set (0.00 sec)
> {code}
>
> 4) Let's drop the 'emps' table:
>
> {code:java}
> 0: jdbc:hive2://c1122-node2.squadron.support.> drop table emps;
> INFO : Compiling
> command(queryId=hive_20191202200025_c13079d0-8695-4485-8a18-14804b8b014b):
> drop table emps
> INFO : Semantic Analysis Completed (retrial = false)
> INFO : Returning Hive schema: Schema(fieldSchemas:null, properties:null)
> INFO : Completed compiling
> command(queryId=hive_20191202200025_c13079d0-8695-4485-8a18-14804b8b014b);
> Time taken: 0.05 seconds
> INFO : Executing
> command(queryId=hive_20191202200025_c13079d0-8695-4485-8a18-14804b8b014b):
> drop table emps
> INFO : Starting task [Stage-0:DDL] in serial mode
> INFO : Completed executing
> command(queryId=hive_20191202200025_c13079d0-8695-4485-8a18-14804b8b014b);
> Time taken: 10.281 seconds
> INFO : OK
> No rows affected (16.949 seconds)
> {code}
> No issue displayed
>
> 5) List tables:
>
> {code:java}
> 0: jdbc:hive2://c1122-node2.squadron.support.> show tables;
> INFO : Compiling
> command(queryId=hive_20191202200125_ca12565b-1d4d-4433-a602-ecf685863413):
> show tables
> INFO : Semantic Analysis Completed (retrial = false)
> INFO : Returning Hive schema:
> Schema(fieldSchemas:[FieldSchema(name:tab_name, type:string, comment:from
> deserializer)], properties:null)
> INFO : Completed compiling
> command(queryId=hive_20191202200125_ca12565b-1d4d-4433-a602-ecf685863413);
> Time taken: 0.041 seconds
> INFO : Executing
> command(queryId=hive_20191202200125_ca12565b-1d4d-4433-a602-ecf685863413):
> show tables
> INFO : Starting task [Stage-0:DDL] in serial mode
> INFO : Completed executing
> command(queryId=hive_20191202200125_ca12565b-1d4d-4433-a602-ecf685863413);
> Time taken: 0.016 seconds
> INFO : OK
> +---+
> | tab_name |
> +---+
> | depts |
> | emps |
> +---+
> 2 rows selected (0.08 seconds)
> {code}
>
> 6) Now, from the backend-db point of view:
>
> {code:java}
> mysql> select TBL_ID, DB_ID, SD_ID, TBL_NAME, TBL_TYPE from TBLS where
> DB_ID=16;
> ++---+---+--+---+
> | TBL_ID | DB_ID | SD_ID | TBL_NAME | TBL_TYPE |
> ++---+---+--+---+
> | 81 | 16 | NULL | emps | MANAGED_TABLE |
> | 83 | 16 | 83 | depts | MANAGED_TABLE |
> | 84 | 16 | 84 | mv1 | MATERIALIZED_VIEW |
> ++---+---+--+---+
> 3 rows in set (0.00 sec)
> {code}
> The table is left with NULL in SD_ID, making it not available.
>
> 7) From
[jira] [Updated] (HIVE-22566) Drop table involved in materialized view leaves the table in inconsistent state
[
https://issues.apache.org/jira/browse/HIVE-22566?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Pablo Junge updated HIVE-22566:
---
Attachment: (was: HIVE-22566.03.patch)
> Drop table involved in materialized view leaves the table in inconsistent
> state
> ---
>
> Key: HIVE-22566
> URL: https://issues.apache.org/jira/browse/HIVE-22566
> Project: Hive
> Issue Type: Bug
> Components: HiveServer2
>Affects Versions: 3.1.0
>Reporter: Pablo Junge
>Assignee: Pablo Junge
>Priority: Minor
> Fix For: 4.0.0
>
> Attachments: HIVE-22566.02.patch, HIVE-22566.05.patch,
> HIVE-22566.patch
>
>
> If you try dropping a table which is part of the definition of a created
> materialized view, the table is not dropped, which is the desired state as it
> is part of the materialized view.
> However, there was a "drop" call to the table, so it tried to drop it but did
> not succeed, leaving it in an inconsistent state.
>
> Repro:
> ---
> 1) Create tables:
>
> {code:java}
> CREATE TABLE emps ( empid INT, deptno INT, name VARCHAR(256), salary
> FLOAT, hire_date TIMESTAMP)STORED AS ORC TBLPROPERTIES
> ('transactional'='true');
> CREATE TABLE depts ( deptno INT, deptname VARCHAR(256), locationid
> INT)STORED AS ORC TBLPROPERTIES ('transactional'='true');
> {code}
>
> 2) Create the VM:
>
> {code:java}
> CREATE MATERIALIZED VIEW mv1 AS SELECT empid, deptname, hire_date FROM emps
> JOIN depts ON (emps.deptno = depts.deptno) WHERE hire_date >= '2016-01-01';
> {code}
>
> 3) Following is in backend database at this point:
>
> {code:java}
> mysql> select TBL_ID, DB_ID, SD_ID, TBL_NAME, TBL_TYPE from TBLS where
> DB_ID=16;
> ++---+---+--+---+
> | TBL_ID | DB_ID | SD_ID | TBL_NAME | TBL_TYPE |
> ++---+---+--+---+
> | 81 | 16 | 81 | emps | MANAGED_TABLE |
> | 83 | 16 | 83 | depts | MANAGED_TABLE |
> | 84 | 16 | 84 | mv1 | MATERIALIZED_VIEW |
> ++---+---+--+---+
> 3 rows in set (0.00 sec)
> {code}
>
> 4) Let's drop the 'emps' table:
>
> {code:java}
> 0: jdbc:hive2://c1122-node2.squadron.support.> drop table emps;
> INFO : Compiling
> command(queryId=hive_20191202200025_c13079d0-8695-4485-8a18-14804b8b014b):
> drop table emps
> INFO : Semantic Analysis Completed (retrial = false)
> INFO : Returning Hive schema: Schema(fieldSchemas:null, properties:null)
> INFO : Completed compiling
> command(queryId=hive_20191202200025_c13079d0-8695-4485-8a18-14804b8b014b);
> Time taken: 0.05 seconds
> INFO : Executing
> command(queryId=hive_20191202200025_c13079d0-8695-4485-8a18-14804b8b014b):
> drop table emps
> INFO : Starting task [Stage-0:DDL] in serial mode
> INFO : Completed executing
> command(queryId=hive_20191202200025_c13079d0-8695-4485-8a18-14804b8b014b);
> Time taken: 10.281 seconds
> INFO : OK
> No rows affected (16.949 seconds)
> {code}
> No issue displayed
>
> 5) List tables:
>
> {code:java}
> 0: jdbc:hive2://c1122-node2.squadron.support.> show tables;
> INFO : Compiling
> command(queryId=hive_20191202200125_ca12565b-1d4d-4433-a602-ecf685863413):
> show tables
> INFO : Semantic Analysis Completed (retrial = false)
> INFO : Returning Hive schema:
> Schema(fieldSchemas:[FieldSchema(name:tab_name, type:string, comment:from
> deserializer)], properties:null)
> INFO : Completed compiling
> command(queryId=hive_20191202200125_ca12565b-1d4d-4433-a602-ecf685863413);
> Time taken: 0.041 seconds
> INFO : Executing
> command(queryId=hive_20191202200125_ca12565b-1d4d-4433-a602-ecf685863413):
> show tables
> INFO : Starting task [Stage-0:DDL] in serial mode
> INFO : Completed executing
> command(queryId=hive_20191202200125_ca12565b-1d4d-4433-a602-ecf685863413);
> Time taken: 0.016 seconds
> INFO : OK
> +---+
> | tab_name |
> +---+
> | depts |
> | emps |
> +---+
> 2 rows selected (0.08 seconds)
> {code}
>
> 6) Now, from the backend-db point of view:
>
> {code:java}
> mysql> select TBL_ID, DB_ID, SD_ID, TBL_NAME, TBL_TYPE from TBLS where
> DB_ID=16;
> ++---+---+--+---+
> | TBL_ID | DB_ID | SD_ID | TBL_NAME | TBL_TYPE |
> ++---+---+--+---+
> | 81 | 16 | NULL | emps | MANAGED_TABLE |
> | 83 | 16 | 83 | depts | MANAGED_TABLE |
> | 84 | 16 | 84 | mv1 | MATERIALIZED_VIEW |
> ++---+---+--+---+
> 3 rows in set (0.00 sec)
> {code}
> The table is left with NULL in SD_ID, making it not available.
>
> 7) From Metastore.log
>
> {code:java}
> 20
[jira] [Updated] (HIVE-22566) Drop table involved in materialized view leaves the table in inconsistent state
[
https://issues.apache.org/jira/browse/HIVE-22566?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Pablo Junge updated HIVE-22566:
---
Attachment: (was: HIVE-22566.04.patch)
> Drop table involved in materialized view leaves the table in inconsistent
> state
> ---
>
> Key: HIVE-22566
> URL: https://issues.apache.org/jira/browse/HIVE-22566
> Project: Hive
> Issue Type: Bug
> Components: HiveServer2
>Affects Versions: 3.1.0
>Reporter: Pablo Junge
>Assignee: Pablo Junge
>Priority: Minor
> Fix For: 4.0.0
>
> Attachments: HIVE-22566.02.patch, HIVE-22566.05.patch,
> HIVE-22566.patch
>
>
> If you try dropping a table which is part of the definition of a created
> materialized view, the table is not dropped, which is the desired state as it
> is part of the materialized view.
> However, there was a "drop" call to the table, so it tried to drop it but did
> not succeed, leaving it in an inconsistent state.
>
> Repro:
> ---
> 1) Create tables:
>
> {code:java}
> CREATE TABLE emps ( empid INT, deptno INT, name VARCHAR(256), salary
> FLOAT, hire_date TIMESTAMP)STORED AS ORC TBLPROPERTIES
> ('transactional'='true');
> CREATE TABLE depts ( deptno INT, deptname VARCHAR(256), locationid
> INT)STORED AS ORC TBLPROPERTIES ('transactional'='true');
> {code}
>
> 2) Create the VM:
>
> {code:java}
> CREATE MATERIALIZED VIEW mv1 AS SELECT empid, deptname, hire_date FROM emps
> JOIN depts ON (emps.deptno = depts.deptno) WHERE hire_date >= '2016-01-01';
> {code}
>
> 3) Following is in backend database at this point:
>
> {code:java}
> mysql> select TBL_ID, DB_ID, SD_ID, TBL_NAME, TBL_TYPE from TBLS where
> DB_ID=16;
> ++---+---+--+---+
> | TBL_ID | DB_ID | SD_ID | TBL_NAME | TBL_TYPE |
> ++---+---+--+---+
> | 81 | 16 | 81 | emps | MANAGED_TABLE |
> | 83 | 16 | 83 | depts | MANAGED_TABLE |
> | 84 | 16 | 84 | mv1 | MATERIALIZED_VIEW |
> ++---+---+--+---+
> 3 rows in set (0.00 sec)
> {code}
>
> 4) Let's drop the 'emps' table:
>
> {code:java}
> 0: jdbc:hive2://c1122-node2.squadron.support.> drop table emps;
> INFO : Compiling
> command(queryId=hive_20191202200025_c13079d0-8695-4485-8a18-14804b8b014b):
> drop table emps
> INFO : Semantic Analysis Completed (retrial = false)
> INFO : Returning Hive schema: Schema(fieldSchemas:null, properties:null)
> INFO : Completed compiling
> command(queryId=hive_20191202200025_c13079d0-8695-4485-8a18-14804b8b014b);
> Time taken: 0.05 seconds
> INFO : Executing
> command(queryId=hive_20191202200025_c13079d0-8695-4485-8a18-14804b8b014b):
> drop table emps
> INFO : Starting task [Stage-0:DDL] in serial mode
> INFO : Completed executing
> command(queryId=hive_20191202200025_c13079d0-8695-4485-8a18-14804b8b014b);
> Time taken: 10.281 seconds
> INFO : OK
> No rows affected (16.949 seconds)
> {code}
> No issue displayed
>
> 5) List tables:
>
> {code:java}
> 0: jdbc:hive2://c1122-node2.squadron.support.> show tables;
> INFO : Compiling
> command(queryId=hive_20191202200125_ca12565b-1d4d-4433-a602-ecf685863413):
> show tables
> INFO : Semantic Analysis Completed (retrial = false)
> INFO : Returning Hive schema:
> Schema(fieldSchemas:[FieldSchema(name:tab_name, type:string, comment:from
> deserializer)], properties:null)
> INFO : Completed compiling
> command(queryId=hive_20191202200125_ca12565b-1d4d-4433-a602-ecf685863413);
> Time taken: 0.041 seconds
> INFO : Executing
> command(queryId=hive_20191202200125_ca12565b-1d4d-4433-a602-ecf685863413):
> show tables
> INFO : Starting task [Stage-0:DDL] in serial mode
> INFO : Completed executing
> command(queryId=hive_20191202200125_ca12565b-1d4d-4433-a602-ecf685863413);
> Time taken: 0.016 seconds
> INFO : OK
> +---+
> | tab_name |
> +---+
> | depts |
> | emps |
> +---+
> 2 rows selected (0.08 seconds)
> {code}
>
> 6) Now, from the backend-db point of view:
>
> {code:java}
> mysql> select TBL_ID, DB_ID, SD_ID, TBL_NAME, TBL_TYPE from TBLS where
> DB_ID=16;
> ++---+---+--+---+
> | TBL_ID | DB_ID | SD_ID | TBL_NAME | TBL_TYPE |
> ++---+---+--+---+
> | 81 | 16 | NULL | emps | MANAGED_TABLE |
> | 83 | 16 | 83 | depts | MANAGED_TABLE |
> | 84 | 16 | 84 | mv1 | MATERIALIZED_VIEW |
> ++---+---+--+---+
> 3 rows in set (0.00 sec)
> {code}
> The table is left with NULL in SD_ID, making it not available.
>
> 7) From Metastore.log
>
> {code:java}
> 20
[jira] [Updated] (HIVE-22566) Drop table involved in materialized view leaves the table in inconsistent state
[
https://issues.apache.org/jira/browse/HIVE-22566?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Pablo Junge updated HIVE-22566:
---
Resolution: Fixed
Status: Resolved (was: Patch Available)
> Drop table involved in materialized view leaves the table in inconsistent
> state
> ---
>
> Key: HIVE-22566
> URL: https://issues.apache.org/jira/browse/HIVE-22566
> Project: Hive
> Issue Type: Bug
> Components: HiveServer2
>Affects Versions: 3.1.0
>Reporter: Pablo Junge
>Assignee: Pablo Junge
>Priority: Minor
> Fix For: 4.0.0
>
> Attachments: HIVE-22566.02.patch, HIVE-22566.05.patch,
> HIVE-22566.patch
>
>
> If you try dropping a table which is part of the definition of a created
> materialized view, the table is not dropped, which is the desired state as it
> is part of the materialized view.
> However, there was a "drop" call to the table, so it tried to drop it but did
> not succeed, leaving it in an inconsistent state.
>
> Repro:
> ---
> 1) Create tables:
>
> {code:java}
> CREATE TABLE emps ( empid INT, deptno INT, name VARCHAR(256), salary
> FLOAT, hire_date TIMESTAMP)STORED AS ORC TBLPROPERTIES
> ('transactional'='true');
> CREATE TABLE depts ( deptno INT, deptname VARCHAR(256), locationid
> INT)STORED AS ORC TBLPROPERTIES ('transactional'='true');
> {code}
>
> 2) Create the VM:
>
> {code:java}
> CREATE MATERIALIZED VIEW mv1 AS SELECT empid, deptname, hire_date FROM emps
> JOIN depts ON (emps.deptno = depts.deptno) WHERE hire_date >= '2016-01-01';
> {code}
>
> 3) Following is in backend database at this point:
>
> {code:java}
> mysql> select TBL_ID, DB_ID, SD_ID, TBL_NAME, TBL_TYPE from TBLS where
> DB_ID=16;
> ++---+---+--+---+
> | TBL_ID | DB_ID | SD_ID | TBL_NAME | TBL_TYPE |
> ++---+---+--+---+
> | 81 | 16 | 81 | emps | MANAGED_TABLE |
> | 83 | 16 | 83 | depts | MANAGED_TABLE |
> | 84 | 16 | 84 | mv1 | MATERIALIZED_VIEW |
> ++---+---+--+---+
> 3 rows in set (0.00 sec)
> {code}
>
> 4) Let's drop the 'emps' table:
>
> {code:java}
> 0: jdbc:hive2://c1122-node2.squadron.support.> drop table emps;
> INFO : Compiling
> command(queryId=hive_20191202200025_c13079d0-8695-4485-8a18-14804b8b014b):
> drop table emps
> INFO : Semantic Analysis Completed (retrial = false)
> INFO : Returning Hive schema: Schema(fieldSchemas:null, properties:null)
> INFO : Completed compiling
> command(queryId=hive_20191202200025_c13079d0-8695-4485-8a18-14804b8b014b);
> Time taken: 0.05 seconds
> INFO : Executing
> command(queryId=hive_20191202200025_c13079d0-8695-4485-8a18-14804b8b014b):
> drop table emps
> INFO : Starting task [Stage-0:DDL] in serial mode
> INFO : Completed executing
> command(queryId=hive_20191202200025_c13079d0-8695-4485-8a18-14804b8b014b);
> Time taken: 10.281 seconds
> INFO : OK
> No rows affected (16.949 seconds)
> {code}
> No issue displayed
>
> 5) List tables:
>
> {code:java}
> 0: jdbc:hive2://c1122-node2.squadron.support.> show tables;
> INFO : Compiling
> command(queryId=hive_20191202200125_ca12565b-1d4d-4433-a602-ecf685863413):
> show tables
> INFO : Semantic Analysis Completed (retrial = false)
> INFO : Returning Hive schema:
> Schema(fieldSchemas:[FieldSchema(name:tab_name, type:string, comment:from
> deserializer)], properties:null)
> INFO : Completed compiling
> command(queryId=hive_20191202200125_ca12565b-1d4d-4433-a602-ecf685863413);
> Time taken: 0.041 seconds
> INFO : Executing
> command(queryId=hive_20191202200125_ca12565b-1d4d-4433-a602-ecf685863413):
> show tables
> INFO : Starting task [Stage-0:DDL] in serial mode
> INFO : Completed executing
> command(queryId=hive_20191202200125_ca12565b-1d4d-4433-a602-ecf685863413);
> Time taken: 0.016 seconds
> INFO : OK
> +---+
> | tab_name |
> +---+
> | depts |
> | emps |
> +---+
> 2 rows selected (0.08 seconds)
> {code}
>
> 6) Now, from the backend-db point of view:
>
> {code:java}
> mysql> select TBL_ID, DB_ID, SD_ID, TBL_NAME, TBL_TYPE from TBLS where
> DB_ID=16;
> ++---+---+--+---+
> | TBL_ID | DB_ID | SD_ID | TBL_NAME | TBL_TYPE |
> ++---+---+--+---+
> | 81 | 16 | NULL | emps | MANAGED_TABLE |
> | 83 | 16 | 83 | depts | MANAGED_TABLE |
> | 84 | 16 | 84 | mv1 | MATERIALIZED_VIEW |
> ++---+---+--+---+
> 3 rows in set (0.00 sec)
> {code}
> The table is left with NULL in SD_ID, making it not available.
>
> 7) From Metastore.lo
