Ask Your Question

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(
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(
    at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(
    at com.mysql.cj.jdbc.StatementImpl.executeQuery(
    at com.mysql.cj.jdbc.DatabaseMetaData$7.forEach(
    at com.mysql.cj.jdbc.DatabaseMetaData$7.forEach(
    at com.mysql.cj.jdbc.IterateBlock.doForAll(
    at com.mysql.cj.jdbc.DatabaseMetaData.getPrimaryKeys(
    at com.streamsets.pipeline.lib.jdbc.JdbcUtil.getPrimaryKeys(
    at com.streamsets.pipeline.lib.jdbc.JdbcBaseRecordWriter.lookupPrimaryKeys(
    at com.streamsets.pipeline.lib.jdbc.JdbcBaseRecordWriter.<init>(
    at com.streamsets.pipeline.lib.jdbc.JdbcGenericRecordWriter.<init>(
    at com.streamsets.pipeline.lib.jdbc.JdbcRecordReaderWriterFactory.createJdbcRecordWriter(
    at com.streamsets.pipeline.lib.jdbc.JdbcRecordReaderWriterFactory.createJdbcRecordWriter(
    at com.streamsets.pipeline.stage.destination.jdbc.JdbcTarget$RecordWriterLoader.load(
    at com.streamsets.pipeline.stage.destination.jdbc.JdbcTarget$RecordWriterLoader.load(
    at com.streamsets.pipeline.lib.jdbc.JdbcUtil.write(
    at com.streamsets.pipeline.stage.destination.jdbc.JdbcTarget.write(
    at com.streamsets.pipeline.api.base.configurablestage.DTarget.write(
    at com.streamsets.datacollector.runner.StageRuntime.lambda$execute$2(
    at com.streamsets.datacollector.runner.StageRuntime.execute(
    at com.streamsets.datacollector.runner.StageRuntime.execute(
    at com.streamsets.datacollector.runner.StagePipe.process(
    at com.streamsets.datacollector.execution.runner.common.ProductionPipelineRunner.processPipe(
    at com.streamsets.datacollector.execution.runner.common.ProductionPipelineRunner.lambda$executeRunner$3(
    at com.streamsets.datacollector.runner.PipeRunner.executeBatch(
    at com.streamsets.datacollector.execution.runner.common.ProductionPipelineRunner.executeRunner(
    at com.streamsets.datacollector.execution.runner.common.ProductionPipelineRunner.runSourceLessBatch(
    at com.streamsets.datacollector.execution.runner.common.ProductionPipelineRunner.runPollSource(
    at com.streamsets.datacollector.execution.runner.common.ProductionPipeline ...
edit retag flag offensive close merge delete

5 Answers

Sort by ┬╗ oldest newest most voted

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


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

answered 2018-10-24 01:54:55 -0500

harley gravatar image

I have solved the question after found the currect Driver .

edit flag offensive delete link more

answered 2018-10-20 04:37:44 -0500

harley gravatar image

finally I solved the question after many attempts, because I found the correct jdbc Driver and write data into table successfully.

edit flag offensive delete link more

answered 2018-10-15 04:31:52 -0500

harley gravatar image

I met the seam question too,though I configurated and checked the connectionString,schema and tableName agin and agin and changed the driver from 8.x.x to 5.1.47,and my mysql version is 5.6.39,it still not work.At first, the log shows "java.sql.SQLSyntaxErrorException: Table 'amon.test3' doesn't exist" ,then I create table test3 in database amon and test agin ,it show "java.sql.SQLSyntaxErrorException: Table 'data.test3' doesn't exist",Amazing!it find the same table in another database it self, I did not set these databases in my configuration ever before,my aim database is data_xxxx and had created table in it, so I could not understand why the log says table doesn`t found in another database.could anyone else to resolve the question´╝čthanks!

edit flag offensive delete link more

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


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



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

Seen: 1,910 times

Last updated: Oct 15 '18