[jira] [Commented] (HIVE-23756) drop table command fails with MySQLIntegrityConstraintViolationException:
[
https://issues.apache.org/jira/browse/HIVE-23756?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17325194#comment-17325194
]
Naveen Gangam commented on HIVE-23756:
--
+1 on this patch.
> drop table command fails with MySQLIntegrityConstraintViolationException:
> -
>
> Key: HIVE-23756
> URL: https://issues.apache.org/jira/browse/HIVE-23756
> Project: Hive
> Issue Type: Bug
>Reporter: Ganesha Shreedhara
>Assignee: Ganesha Shreedhara
>Priority: Major
> Attachments: HIVE-23756.1.patch
>
>
> Drop table command fails intermittently with the following exception.
> {code:java}
> Caused by: java.sql.BatchUpdateException: Cannot delete or update a parent
> row: a foreign key constraint fails ("metastore"."COLUMNS_V2", CONSTRAINT
> "COLUMNS_V2_FK1" FOREIGN KEY ("CD_ID") REFERENCES "CDS" ("CD_ID")) App > at
> com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:1815)at
> com.mysql.jdbc.PreparedStatement.executeBatch(PreparedStatement.java:1277)
> Appat
> org.datanucleus.store.rdbms.ParamLoggingPreparedStatement.executeBatch(ParamLoggingPreparedStatement.java:372)
> at
> org.datanucleus.store.rdbms.SQLController.processConnectionStatement(SQLController.java:628)
> at
> org.datanucleus.store.rdbms.SQLController.getStatementForUpdate(SQLController.java:207)
> at
> org.datanucleus.store.rdbms.SQLController.getStatementForUpdate(SQLController.java:179)
> at
> org.datanucleus.store.rdbms.scostore.JoinMapStore.clearInternal(JoinMapStore.java:901)
> ... 36 more
> Caused by:
> com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException:
> Cannot delete or update a parent row: a foreign key constraint fails
> ("metastore"."COLUMNS_V2", CONSTRAINT "COLUMNS_V2_FK1" FOREIGN KEY ("CD_ID")
> REFERENCES "CDS" ("CD_ID"))
> at sun.reflect.GeneratedConstructorAccessor121.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:377)
> at com.mysql.jdbc.Util.getInstance(Util.java:360)
> at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:971)
> at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3887)
> at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3823){code}
> Although HIVE-19994 resolves this issue, the FK constraint name of COLUMNS_V2
> table specified in package.jdo file is not same as the FK constraint name
> used while creating COLUMNS_V2 table ([Ref|#L60]]).
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Commented] (HIVE-23756) drop table command fails with MySQLIntegrityConstraintViolationException:
[
https://issues.apache.org/jira/browse/HIVE-23756?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17270318#comment-17270318
]
Kenrick Veit commented on HIVE-23756:
-
Is it an acceptable work around to change these two foreign key constraints in
the metastore to be on delete cascade?
> drop table command fails with MySQLIntegrityConstraintViolationException:
> -
>
> Key: HIVE-23756
> URL: https://issues.apache.org/jira/browse/HIVE-23756
> Project: Hive
> Issue Type: Bug
>Reporter: Ganesha Shreedhara
>Assignee: Ganesha Shreedhara
>Priority: Major
> Attachments: HIVE-23756.1.patch
>
>
> Drop table command fails intermittently with the following exception.
> {code:java}
> Caused by: java.sql.BatchUpdateException: Cannot delete or update a parent
> row: a foreign key constraint fails ("metastore"."COLUMNS_V2", CONSTRAINT
> "COLUMNS_V2_FK1" FOREIGN KEY ("CD_ID") REFERENCES "CDS" ("CD_ID")) App > at
> com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:1815)at
> com.mysql.jdbc.PreparedStatement.executeBatch(PreparedStatement.java:1277)
> Appat
> org.datanucleus.store.rdbms.ParamLoggingPreparedStatement.executeBatch(ParamLoggingPreparedStatement.java:372)
> at
> org.datanucleus.store.rdbms.SQLController.processConnectionStatement(SQLController.java:628)
> at
> org.datanucleus.store.rdbms.SQLController.getStatementForUpdate(SQLController.java:207)
> at
> org.datanucleus.store.rdbms.SQLController.getStatementForUpdate(SQLController.java:179)
> at
> org.datanucleus.store.rdbms.scostore.JoinMapStore.clearInternal(JoinMapStore.java:901)
> ... 36 more
> Caused by:
> com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException:
> Cannot delete or update a parent row: a foreign key constraint fails
> ("metastore"."COLUMNS_V2", CONSTRAINT "COLUMNS_V2_FK1" FOREIGN KEY ("CD_ID")
> REFERENCES "CDS" ("CD_ID"))
> at sun.reflect.GeneratedConstructorAccessor121.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:377)
> at com.mysql.jdbc.Util.getInstance(Util.java:360)
> at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:971)
> at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3887)
> at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3823){code}
> Although HIVE-19994 resolves this issue, the FK constraint name of COLUMNS_V2
> table specified in package.jdo file is not same as the FK constraint name
> used while creating COLUMNS_V2 table ([Ref|#L60]]).
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Commented] (HIVE-23756) drop table command fails with MySQLIntegrityConstraintViolationException:
[
https://issues.apache.org/jira/browse/HIVE-23756?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17143966#comment-17143966
]
Ganesha Shreedhara commented on HIVE-23756:
---
[~ngangam] I couldn't reproduce this locally. This issue was intermittent. But
we were getting the same issue even after back porting the fix available in
HIVE-19994. Also, it was reported in the same ticket that using FK constraint
name as `COLUMNS_V2_FK` was not working and user had to use the exact FK
constraint name which is `COLUMNS_V2_FK1`(Ref: [this
comment|https://issues.apache.org/jira/browse/HIVE-19994?focusedCommentId=16895036=com.atlassian.jira.plugin.system.issuetabpanels%3Acomment-tabpanel#comment-16895036]).
> drop table command fails with MySQLIntegrityConstraintViolationException:
> -
>
> Key: HIVE-23756
> URL: https://issues.apache.org/jira/browse/HIVE-23756
> Project: Hive
> Issue Type: Bug
>Reporter: Ganesha Shreedhara
>Assignee: Ganesha Shreedhara
>Priority: Major
> Attachments: HIVE-23756.1.patch
>
>
> Drop table command fails intermittently with the following exception.
> {code:java}
> Caused by: java.sql.BatchUpdateException: Cannot delete or update a parent
> row: a foreign key constraint fails ("metastore"."COLUMNS_V2", CONSTRAINT
> "COLUMNS_V2_FK1" FOREIGN KEY ("CD_ID") REFERENCES "CDS" ("CD_ID")) App > at
> com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:1815)at
> com.mysql.jdbc.PreparedStatement.executeBatch(PreparedStatement.java:1277)
> Appat
> org.datanucleus.store.rdbms.ParamLoggingPreparedStatement.executeBatch(ParamLoggingPreparedStatement.java:372)
> at
> org.datanucleus.store.rdbms.SQLController.processConnectionStatement(SQLController.java:628)
> at
> org.datanucleus.store.rdbms.SQLController.getStatementForUpdate(SQLController.java:207)
> at
> org.datanucleus.store.rdbms.SQLController.getStatementForUpdate(SQLController.java:179)
> at
> org.datanucleus.store.rdbms.scostore.JoinMapStore.clearInternal(JoinMapStore.java:901)
> ... 36 more
> Caused by:
> com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException:
> Cannot delete or update a parent row: a foreign key constraint fails
> ("metastore"."COLUMNS_V2", CONSTRAINT "COLUMNS_V2_FK1" FOREIGN KEY ("CD_ID")
> REFERENCES "CDS" ("CD_ID"))
> at sun.reflect.GeneratedConstructorAccessor121.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:377)
> at com.mysql.jdbc.Util.getInstance(Util.java:360)
> at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:971)
> at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3887)
> at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3823){code}
> Although HIVE-19994 resolves this issue, the FK constrain name of COLUMNS_V2
> table specified in package.jdo file is not same as the FK constraint name
> used while creating COLUMNS_V2 table
> ([Ref|[https://github.com/apache/hive/blob/master/standalone-metastore/metastore-server/src/main/sql/mysql/hive-schema-3.2.0.mysql.sql#L60]]).
>
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Commented] (HIVE-23756) drop table command fails with MySQLIntegrityConstraintViolationException:
[
https://issues.apache.org/jira/browse/HIVE-23756?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17143929#comment-17143929
]
Naveen Gangam commented on HIVE-23756:
--
[~ganeshas] Have you been able to reproduce this locally ? Have you confirmed
the fix resolves the issue? Appears like the fix should resolve the issue but
just curious why we haven't seen this sooner. Thanks
> drop table command fails with MySQLIntegrityConstraintViolationException:
> -
>
> Key: HIVE-23756
> URL: https://issues.apache.org/jira/browse/HIVE-23756
> Project: Hive
> Issue Type: Bug
>Reporter: Ganesha Shreedhara
>Assignee: Ganesha Shreedhara
>Priority: Major
> Attachments: HIVE-23756.1.patch
>
>
> Drop table command fails intermittently with the following exception.
> {code:java}
> Caused by: java.sql.BatchUpdateException: Cannot delete or update a parent
> row: a foreign key constraint fails ("metastore"."COLUMNS_V2", CONSTRAINT
> "COLUMNS_V2_FK1" FOREIGN KEY ("CD_ID") REFERENCES "CDS" ("CD_ID")) App > at
> com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:1815)at
> com.mysql.jdbc.PreparedStatement.executeBatch(PreparedStatement.java:1277)
> Appat
> org.datanucleus.store.rdbms.ParamLoggingPreparedStatement.executeBatch(ParamLoggingPreparedStatement.java:372)
> at
> org.datanucleus.store.rdbms.SQLController.processConnectionStatement(SQLController.java:628)
> at
> org.datanucleus.store.rdbms.SQLController.getStatementForUpdate(SQLController.java:207)
> at
> org.datanucleus.store.rdbms.SQLController.getStatementForUpdate(SQLController.java:179)
> at
> org.datanucleus.store.rdbms.scostore.JoinMapStore.clearInternal(JoinMapStore.java:901)
> ... 36 more
> Caused by:
> com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException:
> Cannot delete or update a parent row: a foreign key constraint fails
> ("metastore"."COLUMNS_V2", CONSTRAINT "COLUMNS_V2_FK1" FOREIGN KEY ("CD_ID")
> REFERENCES "CDS" ("CD_ID"))
> at sun.reflect.GeneratedConstructorAccessor121.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:377)
> at com.mysql.jdbc.Util.getInstance(Util.java:360)
> at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:971)
> at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3887)
> at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3823){code}
> Although HIVE-19994 resolves this issue, the FK constrain name of COLUMNS_V2
> table specified in package.jdo file is not same as the FK constraint name
> used while creating COLUMNS_V2 table
> ([Ref|[https://github.com/apache/hive/blob/master/standalone-metastore/metastore-server/src/main/sql/mysql/hive-schema-3.2.0.mysql.sql#L60]]).
>
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
