Ask Your Question
1

JDBC Producer not connecting to desired MySQL database.

asked 2018-05-30 04:50:51 -0500

yash gravatar image

updated 2018-05-30 10:09:56 -0500

metadaddy gravatar image

The JDBC Producer is looking for the required table in the information_schema database even after providing the database name in the JDBC connection string, resulting in an error.

Connection String - jdbc:mysql://localhost:3306/testing

Table name - test_${record:value('/Table')}

Error Code: 1109
Message: Unknown table 'test_test' in information_schema

java.sql.SQLSyntaxErrorException: Unknown table 'test_test' in information_schema
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:118)
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:95)
    at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
    at com.mysql.cj.jdbc.StatementImpl.executeQuery(StatementImpl.java:1247)
    at com.mysql.cj.jdbc.DatabaseMetaData$7.forEach(DatabaseMetaData.java:3149)
    at com.mysql.cj.jdbc.DatabaseMetaData$7.forEach(DatabaseMetaData.java:3137)
    at com.mysql.cj.jdbc.IterateBlock.doForAll(IterateBlock.java:56)
    at com.mysql.cj.jdbc.DatabaseMetaData.getPrimaryKeys(DatabaseMetaData.java:3190)
    at com.streamsets.pipeline.lib.jdbc.JdbcUtil.getPrimaryKeys(JdbcUtil.java:270)
    at com.streamsets.pipeline.lib.jdbc.JdbcBaseRecordWriter.lookupPrimaryKeys(JdbcBaseRecordWriter.java:155)
    at com.streamsets.pipeline.lib.jdbc.JdbcBaseRecordWriter.<init>(JdbcBaseRecordWriter.java:136)
    at com.streamsets.pipeline.lib.jdbc.JdbcGenericRecordWriter.<init>(JdbcGenericRecordWriter.java:104)
    at com.streamsets.pipeline.lib.jdbc.JdbcRecordReaderWriterFactory.createJdbcRecordWriter(JdbcRecordReaderWriterFactory.java:100)
    at com.streamsets.pipeline.lib.jdbc.JdbcRecordReaderWriterFactory.createJdbcRecordWriter(JdbcRecordReaderWriterFactory.java:46)
    at com.streamsets.pipeline.stage.destination.jdbc.JdbcTarget$RecordWriterLoader.load(JdbcTarget.java:85)
    at com.streamsets.pipeline.stage.destination.jdbc.JdbcTarget$RecordWriterLoader.load(JdbcTarget.java:82)
    at com.google.common.cache.LocalCache$LoadingValueReference.loadFuture(LocalCache.java:3527)
    at com.google.common.cache.LocalCache$Segment.loadSync(LocalCache.java:2319)
    at com.google.common.cache.LocalCache$Segment.lockedGetOrLoad(LocalCache.java:2282)
    at com.google.common.cache.LocalCache$Segment.get(LocalCache.java:2197)
    at com.google.common.cache.LocalCache.get(LocalCache.java:3937)
    at com.google.common.cache.LocalCache.getOrLoad(LocalCache.java:3941)
    at com.google.common.cache.LocalCache$LocalLoadingCache.get(LocalCache.java:4824)
    at com.google.common.cache.LocalCache$LocalLoadingCache.getUnchecked(LocalCache.java:4830)
    at com.streamsets.pipeline.lib.jdbc.JdbcUtil.write(JdbcUtil.java:818)
    at com.streamsets.pipeline.stage.destination.jdbc.JdbcTarget.write(JdbcTarget.java:208)
    at com.streamsets.pipeline.api.base.configurablestage.DTarget.write(DTarget.java:34)
    at com.streamsets.datacollector.runner.StageRuntime.lambda$execute$2(StageRuntime.java:249)
    at com.streamsets.datacollector.runner.StageRuntime.execute(StageRuntime.java:195)
    at com.streamsets.datacollector.runner.StageRuntime.execute(StageRuntime.java:257)
    at com.streamsets.datacollector.runner.StagePipe.process(StagePipe.java:219)
    at com.streamsets.datacollector.execution.runner.common.ProductionPipelineRunner.processPipe(ProductionPipelineRunner.java:801)
    at com.streamsets.datacollector.execution.runner.common.ProductionPipelineRunner.lambda$executeRunner$3(ProductionPipelineRunner.java:846)
    at com.streamsets.datacollector.runner.PipeRunner.executeBatch(PipeRunner.java:136)
    at com.streamsets.datacollector.execution.runner.common.ProductionPipelineRunner.executeRunner(ProductionPipelineRunner.java:845)
    at com.streamsets.datacollector.execution.runner.common.ProductionPipelineRunner.runSourceLessBatch(ProductionPipelineRunner.java:823)
    at com.streamsets.datacollector.execution.runner.common.ProductionPipelineRunner.runPollSource(ProductionPipelineRunner.java:563)
    at com.streamsets.datacollector.execution.runner.common.ProductionPipelineRunner.run(ProductionPipelineRunner.java:383)
    at com.streamsets.datacollector.runner.Pipeline.run(Pipeline.java:512)
    at com.streamsets.datacollector.execution.runner.common.ProductionPipeline ...
(more)
edit retag flag offensive close merge delete

2 Answers

Sort by ยป oldest newest most voted
1

answered 2018-05-30 10:59:14 -0500

metadaddy gravatar image

updated 2018-06-05 13:35:13 -0500

There are a few things to check:

  • Do you have anything set in the Schema Name configuration for JDBC Producer? This should be blank for MySQL, since you're setting the database/schema name in the connect URL
  • Does the test_test table exist? The JDBC Producer will not create it for you.
  • Check that your MySQL driver matches the server. In particular, using the current version 8.0.x JDBC driver with a 5.x.x server seems to result in this problem. Download the older 5.1.x driver (currently 5.1.46) and it should work.
edit flag offensive delete link more

Comments

Yes Schema name configuration is blank test_test table exists Even after adding correct database name to the table name configuration, it shows the same error.

yash gravatar imageyash ( 2018-05-31 00:42:40 -0500 )edit

Someone else had the same problem and we tracked it down to using the version 8.0.x driver with a 5.7.x server. I updated my answer with the fix.

metadaddy gravatar imagemetadaddy ( 2018-06-05 13:36:00 -0500 )edit
0

answered 2018-06-01 08:49:53 -0500

Alex Woolford gravatar image

I tried to create a table in the information schema as the root user in MySQL. Here's what happened:

[root@deepthought ~]# mysql -u root -p
Enter password: 
Your MySQL connection id is 310
Server version: 5.7.22-log MySQL Community Server (GPL)

mysql> create table information_schema.test_test (id int);
ERROR 1044 (42000): Access denied for user 'root'@'%' to database 'information_schema'

Are you sure that the test_test table exists in the information_schema? That schema is for MySQL metadata and not for user-created tables.

edit flag offensive delete link more

Comments

I have created test_test in a different schema but it is looking for the table in information_schema

yash gravatar imageyash ( 2018-06-04 07:21:44 -0500 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2018-05-30 04:50:51 -0500

Seen: 60 times

Last updated: Jun 05