Cloudera Community

Support Questions

Find answers, ask questions, and share your expertise
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Advanced Search
Solved Go to solution

sqoop throws SQLException in nextKeyValue

avatar
author-rank sim6
Expert Contributor

Created on ‎07-06-2016 07:09 AM - edited ‎09-16-2022 03:28 AM

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

20,836 Views
0 Kudos
1 ACCEPTED SOLUTION

avatar
author-rank Harsh J author-rank
Mentor

Created ‎07-06-2016 03:08 PM

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.

View solution in original post

20,827 Views
0 Kudos
1 REPLY 1

avatar
author-rank Harsh J author-rank
Mentor

Created ‎07-06-2016 03:08 PM

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.
20,828 Views
0 Kudos
Announcements
What's New @ Cloudera
Cloudera Data Engineering 1.23: Access Spark from Your Favor...
What's New @ Cloudera
HBase REST server scaling support is Generally Available
What's New @ Cloudera
New CLI option in the update-database command
What's New @ Cloudera
New Action menu item in the Cloudera Operational Database UI
What's New @ Cloudera
Get the list of supported instance types in the Cloudera Ope...
View More Announcements