Details
-
Bug
-
Status: Open
-
Major
-
Resolution: Unresolved
-
1.4.3
-
None
-
None
-
None
-
Sqoop 1.4.3, MySQL 5.5.27
Description
When Sqoop Export upsert query tries to update data in MySQL table by the 2 default mappers it failed with following MySQL deadlock exception.
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