Attached is the verbose log.
I'm using 5.1.21 of the mysql connector. I've also tried with the latest
(5.1.39) and that didn't work.
For jdbc drivers in the lib directory there are:
mariadb-connector-java.jar
postgresql-jdbc.jar
hsqldb-1.8.0.10.jar
On Thu, Aug 4, 2016 at 8:36 PM, Attila Szabo <[email protected]> wrote:
> Hi Geren,
>
> For further investigation:
> Could you please send us the whole verbose logfile for further
> investigation?
> On the top could you please check which version of the MySQL JDBC driver
> is present in the lib directory of Sqoop?
> We would also appreciate if you could tell which other JDBC drivers
> located there!
>
> Many thanks,
> M.
>
> On Thu, Aug 4, 2016 at 1:44 PM, Geren White <[email protected]> wrote:
>
>> Hello all,
>>
>> I'm having trouble importing data from a mysql database (version 5.26)
>> with sqoop (version 1.4.6).
>>
>> I have a number of imports that are happening in AWS EMR and all of them
>> are working that go against tables. I have one import that goes against a
>> view that is not working. It works on our dev database but for some reason
>> not on our prod server.
>>
>> Here's the command I'm running:
>> sqoop import --connect jdbc:mysql://$databaseHost/inventory
>> --connection-manager org.apache.sqoop.manager.MySQLManager --username
>> $user_name
>> --password-file=s3://$s3Bucket/dibs/settings/sqoop_db_credentials.properties
>> --as-avrodatafile --target-dir /user/hadoop/inventory/category_view
>> --delete-target-dir --table category_view --split-by id --verbose
>>
>> I've tried with and without specifying the jdbc driver and without
>> specifying the connection-manager. I've also tried overriding the default
>> SQL it is using but nothing seems to help. I've also seen a number of other
>> users with this issue that all point to the table_definition_cache but
>> playing with those values has not fixed the issue.
>>
>> From the below stacktrace you can see that it is using a
>> MariaDbServerPreparedStatement. This seems weird to me and would expect
>> it to be using the Mysql specific PreparedStatement class. Is this the
>> normal behavior?
>>
>> Here's the stacktrace:
>>
>> 2016-08-04 19:49:40,589 INFO org.apache.sqoop.manager.SqlManager (main):
>> Executing SQL statement: SELECT t.* FROM `category_view` AS t LIMIT 1
>> 2016-08-04 19:49:40,601 ERROR org.apache.sqoop.manager.SqlManager (main):
>> Error executing statement: java.sql.BatchUpdateException: Prepared statement
>> needs to be re-prepared
>> java.sql.BatchUpdateException: Prepared statement needs to be re-prepared
>> at
>> org.mariadb.jdbc.MariaDbServerPreparedStatement.execute(MariaDbServerPreparedStatement.java:374)
>> at
>> org.mariadb.jdbc.MariaDbServerPreparedStatement.executeQuery(MariaDbServerPreparedStatement.java:341)
>> at org.apache.sqoop.manager.SqlManager.execute(SqlManager.java:758)
>> at org.apache.sqoop.manager.SqlManager.execute(SqlManager.java:767)
>> at
>> org.apache.sqoop.manager.SqlManager.getColumnInfoForRawQuery(SqlManager.java:270)
>> at
>> org.apache.sqoop.manager.SqlManager.getColumnTypesForRawQuery(SqlManager.java:241)
>> at
>> org.apache.sqoop.manager.SqlManager.getColumnTypes(SqlManager.java:227)
>> at
>> org.apache.sqoop.manager.ConnManager.getColumnTypes(ConnManager.java:295)
>> at
>> org.apache.sqoop.orm.ClassWriter.getColumnTypes(ClassWriter.java:1833)
>> at org.apache.sqoop.orm.ClassWriter.generate(ClassWriter.java:1645)
>> at org.apache.sqoop.tool.CodeGenTool.generateORM(CodeGenTool.java:107)
>> at org.apache.sqoop.tool.ImportTool.importTable(ImportTool.java:478)
>> at org.apache.sqoop.tool.ImportTool.run(ImportTool.java:605)
>> at org.apache.sqoop.Sqoop.run(Sqoop.java:143)
>> at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:70)
>> at org.apache.sqoop.Sqoop.runSqoop(Sqoop.java:179)
>> at org.apache.sqoop.Sqoop.runTool(Sqoop.java:218)
>> at org.apache.sqoop.Sqoop.runTool(Sqoop.java:227)
>> at org.apache.sqoop.Sqoop.main(Sqoop.java:236)
>> Caused by: java.sql.SQLException: Prepared statement needs to be re-prepared
>> at
>> org.mariadb.jdbc.internal.util.ExceptionMapper.get(ExceptionMapper.java:136)
>> at
>> org.mariadb.jdbc.internal.util.ExceptionMapper.throwException(ExceptionMapper.java:69)
>> at
>> org.mariadb.jdbc.MariaDbServerPreparedStatement.executeQueryEpilog(MariaDbServerPreparedStatement.java:336)
>> at
>> org.mariadb.jdbc.MariaDbServerPreparedStatement.executeInternal(MariaDbServerPreparedStatement.java:291)
>> at
>> org.mariadb.jdbc.MariaDbServerPreparedStatement.execute(MariaDbServerPreparedStatement.java:369)
>> ... 18 more
>> Caused by: org.mariadb.jdbc.internal.util.dao.QueryException: Prepared
>> statement needs to be re-prepared
>> at
>> org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.getResult(AbstractQueryProtocol.java:479)
>> at
>> org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.executePreparedQuery(AbstractQueryProtocol.java:592)
>> at
>> org.mariadb.jdbc.MariaDbServerPreparedStatement.executeInternal(MariaDbServerPreparedStatement.java:279)
>> ... 19 more
>> 2016-08-04 19:49:40,606 ERROR org.apache.sqoop.tool.ImportTool (main):
>> Encountered IOException running import job: java.io.IOException: No columns
>> to generate for ClassWriter
>> at org.apache.sqoop.orm.ClassWriter.generate(ClassWriter.java:1651)
>> at org.apache.sqoop.tool.CodeGenTool.generateORM(CodeGenTool.java:107)
>> at org.apache.sqoop.tool.ImportTool.importTable(ImportTool.java:478)
>> at org.apache.sqoop.tool.ImportTool.run(ImportTool.java:605)
>> at org.apache.sqoop.Sqoop.run(Sqoop.java:143)
>> at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:70)
>> at org.apache.sqoop.Sqoop.runSqoop(Sqoop.java:179)
>> at org.apache.sqoop.Sqoop.runTool(Sqoop.java:218)
>> at org.apache.sqoop.Sqoop.runTool(Sqoop.java:227)
>> at org.apache.sqoop.Sqoop.main(Sqoop.java:236)
>>
>>
>> Any thoughts/recommendations would be greatly appreciated.
>>
>> Thanks,
>> --
>> *Geren White | Senior Java Developer*
>> *(e)* [email protected]
>>
>
>
>
> --
> Best regards,
>
> Attila Szabo
> Sotware Engineer
>
> <http://www.cloudera.com>
>
--
*Geren White | Senior Java Developer*
*(e)* [email protected]
2016-08-04 19:24:49,662 INFO com.amazon.ws.emr.hadoop.fs.EmrFileSystem (main):
Consistency disabled, using com.amazon.ws.emr.hadoop.fs.s3n.S3NativeFileSystem
as filesystem implementation
2016-08-04 19:24:49,935 INFO amazon.emr.metrics.MetricsSaver (main):
MetricsConfigRecord disabledInCluster: false instanceEngineCycleSec: 60
clusterEngineCycleSec: 60 disableClusterEngine: true maxMemoryMb: 3072
maxInstanceCount: 500 lastModified: 1470335157593
2016-08-04 19:24:49,935 INFO amazon.emr.metrics.MetricsSaver (main): Created
MetricsSaver j-1BGTC5V6YC7PC:i-eca15175:RunJar:22266 period:60
/mnt/var/em/raw/i-eca15175_20160804_RunJar_22266_raw.bin
2016-08-04 19:24:50,551 INFO com.amazon.ws.emr.hadoop.fs.s3n.S3NativeFileSystem
(main): Opening
's3://1stdibs-analytics-emr-prod/scripts/sqoop_imports/category_view_sqoop.sh'
for reading
2016-08-04 19:24:50,575 INFO amazon.emr.metrics.MetricsSaver (main): Thread 1
created MetricsLockFreeSaver 1
2016-08-04 19:24:51,508 INFO org.apache.sqoop.Sqoop (main): Running Sqoop
version: 1.4.6
2016-08-04 19:24:51,524 DEBUG org.apache.sqoop.tool.BaseSqoopTool (main):
Enabled debug logging.
2016-08-04 19:24:51,530 DEBUG org.apache.sqoop.util.password.FilePasswordLoader
(main): Fetching password from specified path:
s3://1stdibs-analytics-emr-prod/dibs/settings/sqoop_db_credentials.properties
2016-08-04 19:24:53,558 INFO com.amazon.ws.emr.hadoop.fs.EmrFileSystem (main):
Consistency disabled, using com.amazon.ws.emr.hadoop.fs.s3n.S3NativeFileSystem
as filesystem implementation
2016-08-04 19:24:53,769 INFO amazon.emr.metrics.MetricsSaver (main):
MetricsConfigRecord disabledInCluster: false instanceEngineCycleSec: 60
clusterEngineCycleSec: 60 disableClusterEngine: true maxMemoryMb: 3072
maxInstanceCount: 500 lastModified: 1470335157593
2016-08-04 19:24:53,769 INFO amazon.emr.metrics.MetricsSaver (main): Created
MetricsSaver j-1BGTC5V6YC7PC:i-eca15175:Sqoop:22348 period:60
/mnt/var/em/raw/i-eca15175_20160804_Sqoop_22348_raw.bin
2016-08-04 19:24:54,258 INFO com.amazon.ws.emr.hadoop.fs.s3n.S3NativeFileSystem
(main): Opening
's3://1stdibs-analytics-emr-prod/dibs/settings/sqoop_db_credentials.properties'
for reading
2016-08-04 19:24:54,281 INFO amazon.emr.metrics.MetricsSaver (main): Thread 1
created MetricsLockFreeSaver 1
2016-08-04 19:24:54,302 DEBUG org.apache.sqoop.ConnFactory (main): Loaded
manager factory: org.apache.sqoop.manager.oracle.OraOopManagerFactory
2016-08-04 19:24:54,313 DEBUG org.apache.sqoop.ConnFactory (main): Loaded
manager factory: com.cloudera.sqoop.manager.DefaultManagerFactory
2016-08-04 19:24:54,324 INFO org.apache.sqoop.manager.MySQLManager (main):
Preparing to use a MySQL streaming resultset.
2016-08-04 19:24:54,324 INFO org.apache.sqoop.tool.CodeGenTool (main):
Beginning code generation
2016-08-04 19:24:54,371 DEBUG org.apache.sqoop.manager.SqlManager (main):
Execute getColumnInfoRawQuery : SELECT t.* FROM `category_view` AS t LIMIT 1
2016-08-04 19:24:54,388 DEBUG org.apache.sqoop.manager.SqlManager (main): No
connection paramenters specified. Using regular API for making connection.
2016-08-04 19:24:54,462 DEBUG org.apache.sqoop.manager.SqlManager (main): Using
fetchSize for next query: -2147483648
2016-08-04 19:24:54,462 INFO org.apache.sqoop.manager.SqlManager (main):
Executing SQL statement: SELECT t.* FROM `category_view` AS t LIMIT 1
2016-08-04 19:24:54,472 ERROR org.apache.sqoop.manager.SqlManager (main): Error
executing statement: java.sql.BatchUpdateException: Prepared statement needs to
be re-prepared
java.sql.BatchUpdateException: Prepared statement needs to be re-prepared
at
org.mariadb.jdbc.MariaDbServerPreparedStatement.execute(MariaDbServerPreparedStatement.java:374)
at
org.mariadb.jdbc.MariaDbServerPreparedStatement.executeQuery(MariaDbServerPreparedStatement.java:341)
at org.apache.sqoop.manager.SqlManager.execute(SqlManager.java:758)
at org.apache.sqoop.manager.SqlManager.execute(SqlManager.java:767)
at
org.apache.sqoop.manager.SqlManager.getColumnInfoForRawQuery(SqlManager.java:270)
at
org.apache.sqoop.manager.SqlManager.getColumnTypesForRawQuery(SqlManager.java:241)
at
org.apache.sqoop.manager.SqlManager.getColumnTypes(SqlManager.java:227)
at
org.apache.sqoop.manager.ConnManager.getColumnTypes(ConnManager.java:295)
at
org.apache.sqoop.orm.ClassWriter.getColumnTypes(ClassWriter.java:1833)
at org.apache.sqoop.orm.ClassWriter.generate(ClassWriter.java:1645)
at org.apache.sqoop.tool.CodeGenTool.generateORM(CodeGenTool.java:107)
at org.apache.sqoop.tool.ImportTool.importTable(ImportTool.java:478)
at org.apache.sqoop.tool.ImportTool.run(ImportTool.java:605)
at org.apache.sqoop.Sqoop.run(Sqoop.java:143)
at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:70)
at org.apache.sqoop.Sqoop.runSqoop(Sqoop.java:179)
at org.apache.sqoop.Sqoop.runTool(Sqoop.java:218)
at org.apache.sqoop.Sqoop.runTool(Sqoop.java:227)
at org.apache.sqoop.Sqoop.main(Sqoop.java:236)
Caused by: java.sql.SQLException: Prepared statement needs to be re-prepared
at
org.mariadb.jdbc.internal.util.ExceptionMapper.get(ExceptionMapper.java:136)
at
org.mariadb.jdbc.internal.util.ExceptionMapper.throwException(ExceptionMapper.java:69)
at
org.mariadb.jdbc.MariaDbServerPreparedStatement.executeQueryEpilog(MariaDbServerPreparedStatement.java:336)
at
org.mariadb.jdbc.MariaDbServerPreparedStatement.executeInternal(MariaDbServerPreparedStatement.java:291)
at
org.mariadb.jdbc.MariaDbServerPreparedStatement.execute(MariaDbServerPreparedStatement.java:369)
... 18 more
Caused by: org.mariadb.jdbc.internal.util.dao.QueryException: Prepared
statement needs to be re-prepared
at
org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.getResult(AbstractQueryProtocol.java:479)
at
org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.executePreparedQuery(AbstractQueryProtocol.java:592)
at
org.mariadb.jdbc.MariaDbServerPreparedStatement.executeInternal(MariaDbServerPreparedStatement.java:279)
... 19 more
2016-08-04 19:24:54,476 ERROR org.apache.sqoop.tool.ImportTool (main):
Encountered IOException running import job: java.io.IOException: No columns to
generate for ClassWriter
at org.apache.sqoop.orm.ClassWriter.generate(ClassWriter.java:1651)
at org.apache.sqoop.tool.CodeGenTool.generateORM(CodeGenTool.java:107)
at org.apache.sqoop.tool.ImportTool.importTable(ImportTool.java:478)
at org.apache.sqoop.tool.ImportTool.run(ImportTool.java:605)
at org.apache.sqoop.Sqoop.run(Sqoop.java:143)
at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:70)
at org.apache.sqoop.Sqoop.runSqoop(Sqoop.java:179)
at org.apache.sqoop.Sqoop.runTool(Sqoop.java:218)
at org.apache.sqoop.Sqoop.runTool(Sqoop.java:227)
at org.apache.sqoop.Sqoop.main(Sqoop.java:236)
