Uploaded image for project: 'Sqoop (Retired)'
  1. Sqoop (Retired)
  2. SQOOP-2949

SQL Syntax error when split-by column is of character type and min or max value has single quote inside it

    XMLWordPrintableJSON

Details

    • Bug
    • Status: Resolved
    • Major
    • Resolution: Fixed
    • 1.4.6
    • 3.0.0
    • None
    • None
    • Sqoop 1.4.6
      Run on Hadoop 2.6.0
      On Ubuntu

    Description

      Did a sqoop import from mysql table "emp", with split-by column "ename", which is a varchar(100) type.

      Used below command:
      sqoop import --connect jdbc:mysql://localhost/testdb --username root --password ***** --table emp --m 2 --target-dir /sqoopTest/5 --split-by ename;

      Ename has following records:

      ename
      gireesh
      aavesh
      shiva'
      jamir
      balu
      santosh
      sameer

      Min value is "aavesh" and max value is "shiva'" (please note the single quote inside max value).

      When run, it tried to execute below query in mapper 2 and failed:
      SELECT `ename`, `eid`, `deptid` FROM `emp` AS `emp` WHERE ( `ename` >= 'jd聯聭聪G耀' ) AND ( `ename` <= 'shiva'' )

      Stack trace:

      2016-06-05 16:54:06,749 ERROR [main] org.apache.sqoop.mapreduce.db.DBRecordReader: Top level exception:
      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 ''shiva'' )' at line 1
      at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
      at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
      at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
      at java.lang.reflect.Constructor.newInstance(Constructor.java:422)
      at com.mysql.jdbc.Util.handleNewInstance(Util.java:404)
      at com.mysql.jdbc.Util.getInstance(Util.java:387)
      at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:942)
      at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3966)
      at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3902)
      at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2526)
      at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2673)
      at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2549)
      at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1861)
      at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1962)
      at org.apache.sqoop.mapreduce.db.DBRecordReader.executeQuery(DBRecordReader.java:111)
      at org.apache.sqoop.mapreduce.db.DBRecordReader.nextKeyValue(DBRecordReader.java:235)
      at org.apache.hadoop.mapred.MapTask$NewTrackingRecordReader.nextKeyValue(MapTask.java:553)
      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:784)
      at org.apache.hadoop.mapred.MapTask.run(MapTask.java:341)
      at org.apache.hadoop.mapred.YarnChild$2.run(YarnChild.java:163)
      at java.security.AccessController.doPrivileged(Native Method)
      at javax.security.auth.Subject.doAs(Subject.java:422)
      at org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1628)
      at org.apache.hadoop.mapred.YarnChild.main(YarnChild.java:158)

      Attachments

        There are no Sub-Tasks for this issue.

        Activity

          People

            gireeshp Gireesh Puthumana
            gireeshp Gireesh Puthumana
            Votes:
            0 Vote for this issue
            Watchers:
            7 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved: