Uploaded image for project: 'Sqoop'
  1. Sqoop
  2. SQOOP-452

Mysql free form queries fail if alias is not used.

    Details

    • Type: Bug
    • Status: Resolved
    • Priority: Minor
    • Resolution: Fixed
    • Affects Version/s: 1.4.0-incubating
    • Fix Version/s: 1.4.2
    • Component/s: connectors/mysql
    • Labels:
      None

      Description

      Command I am running:
      sqoop import --connect jdbc:mysql://<mysqlhost>/mysqltestdb --username xxx --password xxxx --query 'select testtable.id, testtable2.fname from testtable join testtable2 on (testtable.id = testtable2.id ) where testtable.id < 3 AND $CONDITIONS' --split-by testtable.id --target-dir joinresults

      Sqoop fails with this error message

      2/03/02 12:12:08 INFO db.DataDrivenDBInputFormat: BoundingValsQuery: SELECT MIN(testtable.id), MAX(testtable.id) FROM (select testtable.id as id, testtable2.fname as fname from testtable join testtable2 on (testtable.id = testtable2.id ) where testtable.id < 3 AND  (1 = 1) ) AS t1
      12/03/02 12:12:08 INFO mapreduce.JobSubmitter: Cleaning up the staging area /user/prashant/.staging/job_1330641624236_0016
      12/03/02 12:12:09 ERROR security.UserGroupInformation: PriviledgedActionException as:prashant (auth:SIMPLE) cause:java.io.IOException: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'testtable.id' in 'field list'
      12/03/02 12:12:09 ERROR tool.ImportTool: Encountered IOException running import job: java.io.IOException: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'testtable.id' in 'field list'
      

      The job runs successfully if I modify the query:

      sqoop import --connect jdbc:mysql://<mysqlhost>/mysqltestdb --username xxxx --password xxxx --query 'select t1.id as id, t2.fname as fname from testtable as t1 join testtable2 as t2 on (t1.id = t2.id ) where t1.id < 3 AND $CONDITIONS' --split-by t1.id --target-dir joinresults

      It would be nice to have it documented at least.

        Activity

        Hide
        cheolsoo Cheolsoo Park added a comment -

        Hi Prashant, this issue is now fixed by SQOOP-468, by which the boundary query is changed

        from

        SELECT MIN(testtable.id), MAX(testtable.id) FROM ( ... ) AS t1
        

        to

        SELECT MIN(t1.id), MAX(t1.id) FROM ( ... ) AS t1
        

        As SQOOP-468 is submitted, I am going to close this jira, but please feel free to reopen it if you see further problems.

        Show
        cheolsoo Cheolsoo Park added a comment - Hi Prashant, this issue is now fixed by SQOOP-468 , by which the boundary query is changed from SELECT MIN(testtable.id), MAX(testtable.id) FROM ( ... ) AS t1 to SELECT MIN(t1.id), MAX(t1.id) FROM ( ... ) AS t1 As SQOOP-468 is submitted, I am going to close this jira, but please feel free to reopen it if you see further problems.
        Hide
        cheolsoo Cheolsoo Park added a comment -

        SQOOP-468 fixes the issue.

        Show
        cheolsoo Cheolsoo Park added a comment - SQOOP-468 fixes the issue.

          People

          • Assignee:
            cheolsoo Cheolsoo Park
            Reporter:
            prashant@cloudera.com Prashant Gokhale
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Development