sqoop throws SQLException in nextKeyValue

sim6 — Fri, 16 Sep 2022 10:28:43 GMT

Why do I get this error for standard sqoop import from mysql?

Error: java.io.IOException: SQLException in nextKeyValue
	at org.apache.sqoop.mapreduce.db.DBRecordReader.nextKeyValue(DBRecordReader.java:277)
	at org.apache.hadoop.mapred.MapTask$NewTrackingRecordReader.nextKeyValue(MapTask.java:556)
	at org.apache.hadoop.mapreduce.task.MapContextImpl.nextKeyValue(MapContextImpl.java:80)
	at org.apache.hadoop.mapreduce.lib.map.WrappedMapper$Context.nextKeyValue(WrappedMapper.java:91)
	at org.apache.hadoop.mapreduce.Mapper.run(Mapper.java:144)
	at org.apache.sqoop.mapreduce.AutoProgressMapper.run(AutoProgressMapper.java:64)
	at org.apache.hadoop.mapred.MapTask.runNewMapper(MapTask.java:787)
	at org.apache.hadoop.mapred.MapTask.run(MapTask.java:341)
	at org.apache.hadoop.mapred.YarnChild$2.run(YarnChild.java:164)
	at java.security.AccessController.doPrivileged(Native Method)
	at javax.security.auth.Subject.doAs(Subject.java:415)
	at org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1693)
	at org.apache.hadoop.mapred.YarnChild.main(YarnChild.java:158)
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'delete, edited, qty, created_date, sold, revenue, sold_date, image, special_pric' at line 1
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
	at java.lang.reflect.Constructor.newInstance(Constructor.java:526)
	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:1052)
	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.executeQuery(PreparedStatement.java:2283)
	at org.apache.sqoop.mapreduce.db.DBRecordReader.executeQuery(DBRecordReader.java:111)
	at org.apache.sqoop.mapreduce.db.DBRecordReader.nextKeyValue(DBRecordReader.java:235)
	... 12 more16/07/06 18:31:20 INFO mapreduce.Job: Task Id : attempt_1463739226103_5482_m_000000_0, Status : FAILED
Error: java.io.IOException: SQLException in nextKeyValue
	at org.apache.sqoop.mapreduce.db.DBRecordReader.nextKeyValue(DBRecordReader.java:277)
	at org.apache.hadoop.mapred.MapTask$NewTrackingRecordReader.nextKeyValue(MapTask.java:556)
	at org.apache.hadoop.mapreduce.task.MapContextImpl.nextKeyValue(MapContextImpl.java:80)
	at org.apache.hadoop.mapreduce.lib.map.WrappedMapper$Context.nextKeyValue(WrappedMapper.java:91)
	at org.apache.hadoop.mapreduce.Mapper.run(Mapper.java:144)
	at org.apache.sqoop.mapreduce.AutoProgressMapper.run(AutoProgressMapper.java:64)
	at org.apache.hadoop.mapred.MapTask.runNewMapper(MapTask.java:787)
	at org.apache.hadoop.mapred.MapTask.run(MapTask.java:341)
	at org.apache.hadoop.mapred.YarnChild$2.run(YarnChild.java:164)
	at java.security.AccessController.doPrivileged(Native Method)
	at javax.security.auth.Subject.doAs(Subject.java:415)
	at org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1693)
	at org.apache.hadoop.mapred.YarnChild.main(YarnChild.java:158)
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'delete, edited, qty, created_date, sold, revenue, sold_date, image, special_pric' at line 1
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
	at java.lang.reflect.Constructor.newInstance(Constructor.java:526)
	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:1052)
	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.executeQuery(PreparedStatement.java:2283)
	at org.apache.sqoop.mapreduce.db.DBRecordReader.executeQuery(DBRecordReader.java:111)
	at org.apache.sqoop.mapreduce.db.DBRecordReader.nextKeyValue(DBRecordReader.java:235)
	... 12 more

Datatypes in MySQL table are varchar, int, decimal ,timestamp and date.

I don't see anything problematic here.

My sqoop import looks like this:

sqoop import --driver com.mysql.jdbc.Driver --connect jdbc:mysql://IP/erp --username root --password 'PASSWORD' --table table_name

My table schema looks like this:

CREATE TABLE `retailSKU` (
  `id` int(12) NOT NULL,
  `sku` varchar(255) NOT NULL,
  `new_price` decimal(12,4) NOT NULL,
  `price` decimal(12,4) NOT NULL,
  `cost` decimal(12,4) NOT NULL,
  `new_status` int(1) NOT NULL,
  `status` int(1) NOT NULL,
  `liquidation` int(1) NOT NULL,
  `delete` int(1) NOT NULL,
  `edited` int(1) NOT NULL,
  `qty` int(12) NOT NULL,
  `created_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `sold` int(12) NOT NULL,
  `revenue` decimal(12,4) NOT NULL,
  `sold_date` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `image` varchar(255) NOT NULL,
  `special_price` decimal(12,4) DEFAULT '0.0000',
  `new_special_price` decimal(12,4) NOT NULL,
  `name` varchar(255) NOT NULL,
  `fab` varchar(255) DEFAULT NULL,
  `meter` varchar(255) DEFAULT NULL,
  `production_date` date DEFAULT NULL,
  `fab_qty` int(10) DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `id` (`id`,`sku`,`name`,`created_date`,`production_date`,`sold_date`,`qty`,`revenue`,`status`,`sold`,`special_price`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

What could be causing the problem here?

Note: Free form query import works just fine for the table though

Re: sqoop throws SQLException in nextKeyValue

Harsh J — Wed, 06 Jul 2016 22:08:08 GMT

The `delete` named column in your retailSKU table is actually a reserved
word, and that's the central point of the issue. When Sqoop's building out
the query, its currently not escaping the column names with backticks (``)
which is necessary when not intending to mean using a reserved word (
https://dev.mysql.com/doc/refman/5.7/en/keywords.html).

Would it be possible for you to alter the column name on the source
retailSKU table, or pass a custom query instead which uses the right escape
syntax, via --query:
http://archive.cloudera.com/cdh5/cdh/5/sqoop/SqoopUserGuide.html#_free_form_query_imports
?

Edit: Just noticed you've mentioned that free-form works just fine.

question sqoop throws SQLException in nextKeyValue in Support Questions

sqoop throws SQLException in nextKeyValue

Re: sqoop throws SQLException in nextKeyValue