[SQOOP-341] Sqoop doesn't handle unsigned ints at least with MySQL

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 1.4.0-incubating
• Assignee: Alex Newman
• Reporter: Alex Newman
• Created: 19/Sep/11 23:52
• Updated: 04/Oct/11 02:11
• Resolved: 04/Oct/11 01:12

Description

mysql> describe Inventory;
-----------------------------------------------------------+

Field

Type

Null

Key

Default

Extra

-----------------------------------------------------------+

ProductID	smallint(4) unsigned zerofill	YES		NULL
Quantity	int(10) unsigned	YES		NULL

-----------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> insert into Inventory Values (9999,9999999999);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select * from Inventory;
---------------------+

ProductID

Quantity

---------------------+

9999	4294967295

---------------------+

Get's the following error

ava.io.IOException: SQLException in nextKeyValue
at org.apache.sqoop.mapreduce.db.DBRecordReader.nextKeyValue(DBRecordReader.java:248)
at org.apache.hadoop.mapred.MapTask$NewTrackingRecordReader.nextKeyValue(MapTask.java:456)
at org.apache.hadoop.mapreduce.MapContext.nextKeyValue(MapContext.java:67)
at org.apache.hadoop.mapreduce.Mapper.run(Mapper.java:143)
at org.apache.sqoop.mapreduce.AutoProgressMapper.run(AutoProgressMapper.java:188)
at org.apache.hadoop.mapred.MapTask.runNewMapper(MapTask.java:647)
at org.apache.hadoop.mapred.MapTask.run(MapTask.java:323)
at org.apache.hadoop.mapred.LocalJobRunner$Job.run(LocalJobRunner.java:210)
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLDataException: '4.294967295E9' in column '2' is outside valid range for the datatype INTEGER.
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:1025)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:987)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:982)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:927)
at com.mysql.jdbc.ResultSetImpl.throwRangeException(ResultSetImpl.java:7970)
at com.mysql.jdbc.ResultSetImpl.parseIntAsDouble(ResultSetImpl.java:7199)
at com.mysql.jdbc.ResultSetImpl.getInt(ResultSetImpl.java:2702)
at org.apache.sqoop.lib.JdbcWritableBridge.readInteger(JdbcWritableBridge.java:51)
at Inventory.readFields(Inventory.java:75)
at org.apache.sqoop.mapreduce.db.DBRecordReader.nextKeyValue(DBRecordReader.java:244)
... 7 more

This seems to be caused by sqoop having no notion of unsigned values when importing from MySQL(or maybe in general)