[
https://issues.apache.org/jira/browse/HIVE-20554?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16615386#comment-16615386
]
Krishnama Raju K commented on HIVE-20554:
-----------------------------------------
NOTE :
a. There is a Primary Key and Foreign Key relationship between these two
tables( PARTITIONS.PART_ID is PK and PART_COL_STATS.PART_ID is FK ).
b. This issue is ONLY for External Tables.
Detailed Reproducing steps and Possible Fix
==================================
Step 1. Create sample table (table003) and inserted few records to that tables
{noformat}
hive> select * from table003;
OK
a b 1
a b 2
a b 3
a b 4
a b 5
a b 6
a b 7
Time taken: 0.48 seconds, Fetched: 7 row(s)
hive>{noformat}
Step 2. Even after inserting data into table, PARTITIONS table is updated and
the PART_COL_STATS table is not updated ( Working as expected )
{code:java}
mysql>
mysql> select count * from TBLS, PARTITIONS where TBL_NAME = 'table003' and
PARTITIONS.TBL_ID = TBLS.TBL_ID ;
----------
count
----------
7
----------
1 row in set (0.00 sec)
mysql> select db_name, table_name, count from PART_COL_STATS group by db_name,
table_name;
Empty set (0.00 sec)
{code}
Step 3. Run analyze command for populating PART_COL_STATS
{noformat}
hive> analyze table table003 partition (data_dt) COMPUTE STATISTICS FOR COLUMNS;
Query ID = hive_20180912165312_39c4e8d6-f092-40fc-aeda-812c2c8079da
Total jobs = 1
Launching Job 1 out of 1
Tez session was closed. Reopening...
Session re-established.
Status: Running (Executing on YARN cluster with App id
application_1536713295508_0003)
--------------------------------------------------------------------------------
VERTICES STATUS TOTAL COMPLETED RUNNING PENDING FAILED KILLED
--------------------------------------------------------------------------------
Map 1 .......... SUCCEEDED 1 1 0 0 0 0
Reducer 2 ...... SUCCEEDED 1 1 0 0 0 0
--------------------------------------------------------------------------------
VERTICES: 02/02 ==========================>> 100% ELAPSED TIME: 6.24 s
--------------------------------------------------------------------------------
OK
Time taken: 12.387 seconds
hive>
mysql> select db_name, table_name, count from PART_COL_STATS group by db_name,
table_name;
---------------------------
db_name table_name count
---------------------------
default table003 14
---------------------------
1 row in set (0.00 sec)
{noformat}
Step 4. Rename the table and see PARTITIONS table getting updated but not
PART_COL_STATS
{noformat}
hive> alter table table003 rename to table004;
OK
Time taken: 0.363 seconds
hive>
mysql> select * from TBLS where lower(TBL_NAME) = 'table003';
Empty set (0.00 sec)
mysql> select * from TBLS where lower(TBL_NAME) = 'table004';
---------------------------------------------------------------------------------------------------------------------
TBL_ID CREATE_TIME DB_ID LAST_ACCESS_TIME OWNER RETENTION
SD_ID TBL_NAME TBL_TYPE VIEW_EXPANDED_TEXT
VIEW_ORIGINAL_TEXT
---------------------------------------------------------------------------------------------------------------------
26 1536769737 1 0 admin 0 76 table004
EXTERNAL_TABLE NULL NULL
---------------------------------------------------------------------------------------------------------------------
1 row in set (0.00 sec)
mysql> select count from TBLS, PARTITIONS where TBL_NAME = 'table004' and
PARTITIONS.TBL_ID = TBLS.TBL_ID ;
----------
count
----------
7
----------
1 row in set (0.00 sec)
mysql> select count from TBLS, PARTITIONS where TBL_NAME = 'table003' and
PARTITIONS.TBL_ID = TBLS.TBL_ID ;
----------
count
----------
0
----------
1 row in set (0.00 sec)
mysql> select db_name, table_name, count from PART_COL_STATS group by db_name,
table_name;
---------------------------
db_name table_name count
---------------------------
default table003 14
---------------------------
1 row in set (0.00 sec)
{noformat}
Step 5. Failure to drop the renamed table.
{noformat}
hive> drop table table004;
if I see hive.log then I am getting below error
Caused by: java.sql.BatchUpdateException: Cannot delete or update a parent row:
a foreign key constraint fails ("hive"."PART_COL_STATS", CONSTRAINT
"PART_COL_STATS_FK" FOREIGN KEY ("PART_ID") REFERENCES "PARTITIONS" ("PART_ID"))
at
com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:2024)
at com.mysql.jdbc.PreparedStatement.executeBatch(PreparedStatement.java:1449)
at com.jolbox.bonecp.StatementHandle.executeBatch(StatementHandle.java:424)
at
org.datanucleus.store.rdbms.ParamLoggingPreparedStatement.executeBatch(ParamLoggingPreparedStatement.java:366)
at
org.datanucleus.store.rdbms.SQLController.processConnectionStatement(SQLController.java:676)
at
org.datanucleus.store.rdbms.SQLController.processStatementsForConnection(SQLController.java:644)
at
org.datanucleus.store.rdbms.SQLController$1.transactionFlushed(SQLController.java:731)
NOTE:
a) If I rename table back to the previous name then I am able to drop the table
without any error.
b) If a new table is created with the previous name then I have to follow the
below method for solving the issue.{noformat}
WORKAROUND
=============
Step 1. Run analyze command to update PART_COL_STATS on the new table.
{noformat}
hive> analyze table table004 partition (data_dt) COMPUTE STATISTICS FOR COLUMNS;
Query ID = hive_20180912165929_12a13469-044e-4f93-98e1-14d603769e24
Total jobs = 1
Launching Job 1 out of 1
Status: Running (Executing on YARN cluster with App id
application_1536713295508_0004)
--------------------------------------------------------------------------------
VERTICES STATUS TOTAL COMPLETED RUNNING PENDING FAILED KILLED
--------------------------------------------------------------------------------
Map 1 .......... SUCCEEDED 1 1 0 0 0 0
Reducer 2 ...... SUCCEEDED 1 1 0 0 0 0
--------------------------------------------------------------------------------
VERTICES: 02/02 ==========================>> 100% ELAPSED TIME: 6.80 s
--------------------------------------------------------------------------------
OK
Time taken: 10.449 seconds
hive>{noformat}
Step 2. Trying to drop the table is still failing but, PART_ID in
PART_COL_STATS has two records,
{noformat}
mysql> select db_name, table_name, PART_ID,count * from PART_COL_STATS group by
db_name, table_name;
----------------------------------+
db_name table_name PART_ID count
----------------------------------+
default table003 51 14
default table004 51 14
----------------------------------+
2 rows in set (0.00 sec)
mysql>
mysql> select PARTITIONS.PART_ID,count * from TBLS, PARTITIONS where TBL_NAME =
'table004' and PARTITIONS.TBL_ID = TBLS.TBL_ID ;
-----------------+
PART_ID count
-----------------+
51 7
-----------------+
1 row in set (0.00 sec)
mysql>{noformat}
Step 3. Removing one entry( entry for an old table ) from PART_COL_STATS.
{noformat}
mysql> delete from PART_COL_STATS where table_name='table003';
Query OK, 14 rows affected (0.00 sec){noformat}
Step 4. Dropping the table works fine now.
{noformat}
hive> drop table table004;
OK
Time taken: 95.675 seconds{noformat}
workaround was suggested by Surya Adapa
> Unable to drop an external table after renaming it.
> ---------------------------------------------------
>
> Key: HIVE-20554
> URL: https://issues.apache.org/jira/browse/HIVE-20554
> Project: Hive
> Issue Type: Bug
> Components: Hive, HiveServer2
> Affects Versions: 1.2.0, 2.1.0
> Reporter: Krishnama Raju K
> Priority: Major
>
> Unable to drop an external partitioned table after renaming it. Getting the
> following exception,
>
> {noformat}
> java.sql.BatchUpdateException: Cannot delete or update a parent row: a
> foreign key constraint fails ("hive"."PART_COL_STATS", CONSTRAINT
> "PART_COL_STATS_FK" FOREIGN KEY ("PART_ID") REFERENCES "PARTITIONS"
> ("PART_ID"))
> at
> com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:2024)
>
> at com.mysql.jdbc.PreparedStatement.executeBatch(PreparedStatement.java:1449)
> at com.jolbox.bonecp.StatementHandle.executeBatch(StatementHandle.java:424)
> at
> org.datanucleus.store.rdbms.ParamLoggingPreparedStatement.executeBatch(ParamLoggingPreparedStatement.java:366)
>
> at
> org.datanucleus.store.rdbms.SQLController.processConnectionStatement(SQLController.java:676)
>
> at
> org.datanucleus.store.rdbms.SQLController.getStatementForUpdate(SQLController.java:204)
>
> at
> org.datanucleus.store.rdbms.SQLController.getStatementForUpdate(SQLController.java:176)
>
> at
> org.datanucleus.store.rdbms.scostore.JoinMapStore.clearInternal(JoinMapStore.java:900)
>
> at
> org.datanucleus.store.rdbms.scostore.JoinMapStore.clear(JoinMapStore.java:449)
>
> at org.datanucleus.store.types.wrappers.backed.Map.clear(Map.java:605)
> at
> org.datanucleus.store.rdbms.mapping.java.MapMapping.preDelete(MapMapping.java:252)
>
> at
> org.datanucleus.store.rdbms.request.DeleteRequest.execute(DeleteRequest.java:193)
>
> at
> org.datanucleus.store.rdbms.RDBMSPersistenceHandler.deleteObjectFromTable(RDBMSPersistenceHandler.java:499)
>
> at
> org.datanucleus.store.rdbms.RDBMSPersistenceHandler.deleteObject(RDBMSPersistenceHandler.java:470)
>
> at
> org.datanucleus.state.AbstractStateManager.internalDeletePersistent(AbstractStateManager.java:832)
>
> at
> org.datanucleus.state.StateManagerImpl.deletePersistent(StateManagerImpl.java:4244)
>
> at
> org.datanucleus.ExecutionContextImpl.deleteObjectInternal(ExecutionContextImpl.java:2395)
>
> at
> org.datanucleus.ExecutionContextImpl.deleteObjectWork(ExecutionContextImpl.java:2317)
>
> at
> org.datanucleus.ExecutionContextImpl.deleteObjects(ExecutionContextImpl.java:2209)
>
> at
> org.datanucleus.ExecutionContextThreadedImpl.deleteObjects(ExecutionContextThreadedImpl.java:259)
>
> at
> org.datanucleus.store.query.Query.performDeletePersistentAll(Query.java:2133)
> at
> org.datanucleus.store.query.AbstractJavaQuery.performDeletePersistentAll(AbstractJavaQuery.java:114)
>
> at org.datanucleus.store.query.Query.deletePersistentAll(Query.java:2085)
> at
> org.datanucleus.api.jdo.JDOQuery.deletePersistentInternal(JDOQuery.java:441)
> at org.datanucleus.api.jdo.JDOQuery.deletePersistentAll(JDOQuery.java:428)
> at
> org.apache.hadoop.hive.metastore.ObjectStore.dropPartitionsNoTxn(ObjectStore.java:2421)
>
> at
> org.apache.hadoop.hive.metastore.ObjectStore.dropPartitions(ObjectStore.java:1805)
>
> at sun.reflect.GeneratedMethodAccessor78.invoke(Unknown Source)
> at
> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
>
> at java.lang.reflect.Method.invoke(Method.java:498)
> at
> org.apache.hadoop.hive.metastore.RawStoreProxy.invoke(RawStoreProxy.java:103)
> at com.sun.proxy.$Proxy10.dropPartitions(Unknown Source)
> at
> org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.dropPartitionsAndGetLocations(HiveMetaStore.java:1838)
>
> at
> org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.drop_table_core(HiveMetaStore.java:1673)
>
> at
> org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.drop_table_with_environment_context(HiveMetaStore.java:1859)
>
> at sun.reflect.GeneratedMethodAccessor110.invoke(Unknown Source)
> at
> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
>
> at java.lang.reflect.Method.invoke(Method.java:498)
> at
> org.apache.hadoop.hive.metastore.RetryingHMSHandler.invokeInternal(RetryingHMSHandler.java:147)
>
> at
> org.apache.hadoop.hive.metastore.RetryingHMSHandler.invoke(RetryingHMSHandler.java:105)
>
> at com.sun.proxy.$Proxy12.drop_table_with_environment_context(Unknown Source)
> at
> org.apache.hadoop.hive.metastore.api.ThriftHiveMetastore$Processor$drop_table_with_environment_context.getResult(ThriftHiveMetastore.java:9523)
>
> at
> org.apache.hadoop.hive.metastore.api.ThriftHiveMetastore$Processor$drop_table_with_environment_context.getResult(ThriftHiveMetastore.java:9507)
>
> at org.apache.thrift.ProcessFunction.process(ProcessFunction.java:39)
> at org.apache.thrift.TBaseProcessor.process(TBaseProcessor.java:39)
> at
> org.apache.hadoop.hive.thrift.HadoopThriftAuthBridge$Server$TUGIAssumingProcessor$1.run(HadoopThriftAuthBridge.java:551)
>
> at
> org.apache.hadoop.hive.thrift.HadoopThriftAuthBridge$Server$TUGIAssumingProcessor$1.run(HadoopThriftAuthBridge.java:546)
>
> at java.security.AccessController.doPrivileged(Native Method)
> at javax.security.auth.Subject.doAs(Subject.java:422)
> at
> org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1866)
>
> at
> org.apache.hadoop.hive.thrift.HadoopThriftAuthBridge$Server$TUGIAssumingProcessor.process(HadoopThriftAuthBridge.java:546)
>
> at
> org.apache.thrift.server.TThreadPoolServer$WorkerProcess.run(TThreadPoolServer.java:286)
>
> at
> java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
>
> at
> java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
>
> at java.lang.Thread.run(Thread.java:745)
> Caused by:
> com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException:
> Cannot delete or update a parent row: a foreign key constraint fails
> ("hive"."PART_COL_STATS", CONSTRAINT "PART_COL_STATS_FK" FOREIGN KEY
> ("PART_ID") REFERENCES "PARTITIONS" ("PART_ID"))
> at sun.reflect.GeneratedConstructorAccessor344.newInstance(Unknown Source)
> at
> sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
>
> at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
> at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
> at com.mysql.jdbc.Util.getInstance(Util.java:386)
> at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1040)
> at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3597)
> at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3529)
> at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1990)
> at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2151)
> at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2625)
> at
> com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2119)
> at
> com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2415)
> at
> com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:1979)
>
> ... 54 more{noformat}
>
>
> Renaming was done using the following command,
> {noformat}
> hive -e "alter table <DB>.<OLD-Table-name> rename to <DB>.<New-Table-name>;
> {noformat}
>
>
>
> STEPS TO REPRODUCE
> ===================
> 1. Create an external table in hive.
> 2. . Add sample data to the table.
> 3. Rename the table using the above command.
> 4. Drop the renamed table.
>
>
>
>
>
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
