[
https://issues.apache.org/jira/browse/SQOOP-1177?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13747248#comment-13747248
]
Jarek Jarcec Cecho commented on SQOOP-1177:
-------------------------------------------
Hi [~sarathrs],
I would suggest to try altering the default values of properties
{{sqoop.export.statements.per.transaction}} and
{{sqoop.export.records.per.statement}}. The defaults might be too big for your
use case. You can start with setting both to 1, which should avoid the
deadlock, but at the same time will provide worst performance:
{code}
sqoop import -Dsqoop.export.records.per.statement=1
-Dsqoop.export.statements.per.transaction=1 --connect ...
{code}
> Sqoop Export Upsert Query for MySQL fails with Deadlock exception
> ------------------------------------------------------------------
>
> Key: SQOOP-1177
> URL: https://issues.apache.org/jira/browse/SQOOP-1177
> Project: Sqoop
> Issue Type: Bug
> Affects Versions: 1.4.3
> Environment: Sqoop 1.4.3, MySQL 5.5.27
> Reporter: Sarath Rachakonda
>
> When Sqoop Export upsert query tries to update data in MySQL table by the 2
> default mappers it failed with following MySQL deadlock exception.
> {code}
> 013-08-17 21:29:23,753 DEBUG
> org.apache.sqoop.mapreduce.mysql.MySQLUpsertOutputFormat: Using upsert query:
> INSERT INTO `AGG_SOCIALMENTION`(datedm_id, externalindividual_type,
> topicdm_id, topicclassdm_id, companydm_id, locationdm_id, gender_type,
> media_id, mention_type_id, mentions_count, qualificationscore_avg,
> positive_sentiment_count, negative_sentiment_count, neutral_sentiment_count,
> agg_sm_id) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ON DUPLICATE
> KEY UPDATE datedm_id=VALUES(datedm_id),
> externalindividual_type=VALUES(externalindividual_type),
> topicdm_id=VALUES(topicdm_id), topicclassdm_id=VALUES(topicclassdm_id),
> companydm_id=VALUES(companydm_id), locationdm_id=VALUES(locationdm_id),
> gender_type=VALUES(gender_type), media_id=VALUES(media_id),
> mention_type_id=VALUES(mention_type_id),
> mentions_count=VALUES(mentions_count),
> qualificationscore_avg=VALUES(qualificationscore_avg),
> positive_sentiment_count=VALUES(positive_sentiment_count),
> negative_sentiment_count=VALUES(negative_sentiment_count),
> neutral_sentiment_count=VALUES(neutral_sentiment_count),
> agg_sm_id=VALUES(agg_sm_id)
> 2013-08-17 21:29:28,546 ERROR
> org.apache.sqoop.mapreduce.AsyncSqlOutputFormat: Got exception in update
> thread: java.sql.BatchUpdateException: Deadlock found when trying to get
> lock; try restarting transaction
> at
> com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:2013)
> at
> com.mysql.jdbc.PreparedStatement.executeBatch(PreparedStatement.java:1449)
> at
> org.apache.sqoop.mapreduce.AsyncSqlOutputFormat$AsyncSqlExecThread.run(AsyncSqlOutputFormat.java:231)
> Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException:
> Deadlock found when trying to get lock; try restarting transaction
> at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
> at
> sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
> at
> sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
> at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
> at com.mysql.jdbc.Util.handleNewInstance(Util.java:407)
> at com.mysql.jdbc.Util.getInstance(Util.java:382)
> at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1064)
> at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3603)
> at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3535)
> at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1989)
> at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2150)
> at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2626)
> 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:1976)
> ... 2 more
> 2013-08-17 21:29:28,546 DEBUG
> org.apache.sqoop.mapreduce.mysql.MySQLUpsertOutputFormat: Using upsert query:
> INSERT INTO `AGG_SOCIALMENTION`(datedm_id, externalindividual_type,
> topicdm_id, topicclassdm_id, companydm_id, locationdm_id, gender_type,
> media_id, mention_type_id, mentions_count, qualificationscore_avg,
> positive_sentiment_count, negative_sentiment_count, neutral_sentiment_count,
> agg_sm_id) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ON DUPLICATE
> KEY UPDATE datedm_id=VALUES(datedm_id),
> externalindividual_type=VALUES(externalindividual_type),
> topicdm_id=VALUES(topicdm_id), topicclassdm_id=VALUES(topicclassdm_id),
> companydm_id=VALUES(companydm_id), locationdm_id=VALUES(locationdm_id),
> gender_type=VALUES(gender_type), media_id=VALUES(media_id),
> mention_type_id=VALUES(mention_type_id),
> mentions_count=VALUES(mentions_count),
> qualificationscore_avg=VALUES(qualificationscore_avg),
> positive_sentiment_count=VALUES(positive_sentiment_count),
> negative_sentiment_count=VALUES(negative_sentiment_count),
> neutral_sentiment_count=VALUES(neutral_sentiment_count),
> agg_sm_id=VALUES(agg_sm_id)
> 2013-08-17 21:29:28,551 ERROR
> org.apache.sqoop.mapreduce.AsyncSqlRecordWriter: Top level exception:
> java.sql.BatchUpdateException: Deadlock found when trying to get lock; try
> restarting transaction
> at
> com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:2013)
> at
> com.mysql.jdbc.PreparedStatement.executeBatch(PreparedStatement.java:1449)
> at
> org.apache.sqoop.mapreduce.AsyncSqlOutputFormat$AsyncSqlExecThread.run(AsyncSqlOutputFormat.java:231)
> Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException:
> Deadlock found when trying to get lock; try restarting transaction
> at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
> at
> sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
> at
> sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
> at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
> at com.mysql.jdbc.Util.handleNewInstance(Util.java:407)
> at com.mysql.jdbc.Util.getInstance(Util.java:382)
> at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1064)
> at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3603)
> at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3535)
> at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1989)
> at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2150)
> at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2626)
> 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:1976)
> ... 2 more
> 2013-08-17 21:29:28,552 ERROR org.apache.sqoop.mapreduce.TextExportMapper:
> 2013-08-17 21:29:28,552 ERROR org.apache.sqoop.mapreduce.TextExportMapper:
> Exception raised during data export
> 2013-08-17 21:29:28,552 ERROR org.apache.sqoop.mapreduce.TextExportMapper:
> 2013-08-17 21:29:28,553 ERROR org.apache.sqoop.mapreduce.TextExportMapper:
> Exception:
> java.io.IOException: java.sql.BatchUpdateException: Deadlock found when
> trying to get lock; try restarting transaction
> at
> org.apache.sqoop.mapreduce.AsyncSqlRecordWriter.write(AsyncSqlRecordWriter.java:220)
> at
> org.apache.sqoop.mapreduce.AsyncSqlRecordWriter.write(AsyncSqlRecordWriter.java:46)
> at
> org.apache.hadoop.mapred.MapTask$NewDirectOutputCollector.write(MapTask.java:558)
> at
> org.apache.hadoop.mapreduce.task.TaskInputOutputContextImpl.write(TaskInputOutputContextImpl.java:85)
> at
> org.apache.hadoop.mapreduce.lib.map.WrappedMapper$Context.write(WrappedMapper.java:106)
> at
> org.apache.sqoop.mapreduce.TextExportMapper.map(TextExportMapper.java:84)
> at
> org.apache.sqoop.mapreduce.TextExportMapper.map(TextExportMapper.java:39)
> at org.apache.hadoop.mapreduce.Mapper.run(Mapper.java:140)
> at
> org.apache.sqoop.mapreduce.AutoProgressMapper.run(AutoProgressMapper.java:64)
> at org.apache.hadoop.mapred.MapTask.runNewMapper(MapTask.java:672)
> at org.apache.hadoop.mapred.MapTask.run(MapTask.java:330)
> at org.apache.hadoop.mapred.Child$4.run(Child.java:268)
> at java.security.AccessController.doPrivileged(Native Method)
> at javax.security.auth.Subject.doAs(Subject.java:396)
> at
> org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1408)
> at org.apache.hadoop.mapred.Child.main(Child.java:262)
> Caused by: java.sql.BatchUpdateException: Deadlock found when trying to get
> lock; try restarting transaction
> at
> com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:2013)
> at
> com.mysql.jdbc.PreparedStatement.executeBatch(PreparedStatement.java:1449)
> at
> org.apache.sqoop.mapreduce.AsyncSqlOutputFormat$AsyncSqlExecThread.run(AsyncSqlOutputFormat.java:231)
> Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException:
> Deadlock found when trying to get lock; try restarting transaction
> at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
> at
> sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
> at
> sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
> at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
> at com.mysql.jdbc.Util.handleNewInstance(Util.java:407)
> at com.mysql.jdbc.Util.getInstance(Util.java:382)
> at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1064)
> at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3603)
> at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3535)
> at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1989)
> at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2150)
> at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2626)
> 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:1976)
> ... 2 more
> 2013-08-17 21:29:28,553 ERROR org.apache.sqoop.mapreduce.TextExportMapper: On
> input: 20130715|4|285|10|18|37523|3|1|1|1|90.0|0|0|1|null
> {code}
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira
