Ask Your Question
1

PostgreSQL jdbc driver no longer works

asked 2019-06-17 13:05:05 -0500

meash-nrel gravatar image

updated 2019-06-17 13:05:27 -0500

JDBC consumer states "Note: When connecting to a PostgreSQL database, you do not need to install a JDBC driver. Data Collector includes the JDBC driver required for PostgreSQL." I see the file as postgresql-42.2.2.jar in the jdbc internal libraries, and use it successfully to read and write via JDBC.

I subsequently installed a MySQL driver in to the Extras directory, as specified by the JDBC Consumer and External Libraries documentation. It took 2 restarts to recognize the new JDBC driver, not one (contrary to documentation), but I am now successfully using the MySQL driver in JDBC and MySQL BinLog libs.

However, afterwards, I am now getting errors with Postgres JDBC access that JDBC driver is not found.

It seems that installing a 3rd party JDBC library prevents the JDBC lib from utilizing the pre-provided JDBC driver for Postgres. This is contrary to the above snippet from the documentation that I do NOT have to install Postgres JDBC driver.

So it seems a bug. I am going to try to install a 3rd party Postgres JDBC driver to circumvent the one that is pre-installed to see if that repairs it.

edit retag flag offensive close merge delete

3 Answers

Sort by ยป oldest newest most voted
1

answered 2019-06-18 12:07:53 -0500

meash-nrel gravatar image

Ultimately metadaddy is correct in that the JDBC Driver Manager was clearly confused. However I didn't want to have to manually inform JDBC steps on every pipeline of the driver to use via the Legacy Drivers input. But it is good to know about the feature.

We ended up solving this in a very inelegant fashion. The bug was consistent and occurred on both our test and production stack, so we could play around on the test env to try to fix it manually.

We already had logging at DEBUG level so could see the driver loads. After loading the MySQL JDBC driver, after the first restart it was loading both drivers. After a second restart later, it solely saw the MySQL driver from that point forward even after further restarts.

Steps to fix:

  • Confirm bug: Postgres JDBC not working, and log shows only MySQL loaded.
  • Shut down SDC.
  • Move the included PostgreSQL driver jar out of internal lib dir streamsets-libs/streamsets-datacollector-jdbc-lib/lib/ to a holding place.
  • Restart SDC. Postgres JDBC still doesn't work (of course not), log still showing only MySQL driver loaded.
  • Shut down SDC again.
  • Move the PostgresSQL driver jar back into that internal lib dir.
  • Restart SDC.
  • Now JDBC lib working again. Verified Postgres JDBC now working, and shows MySQL and Postgres driver both loaded.
edit flag offensive delete link more
1

answered 2019-06-17 14:01:17 -0500

metadaddy gravatar image

updated 2019-06-17 14:04:53 -0500

Sometimes, when there are multiple JDBC drivers installed, the JDBC Driver Manager gets confused. The workaround is to specify the JDBC driver class name on the JDBC stage's Legacy Drivers tab. For the PostgreSQL driver, the class name is org.postgresql.Driver.

The MySQL JDBC driver class name varies with the MySQL version:

  • com.mysql.jdbc.Driver for MySQL 5.x
  • com.mysql.cj.jdbc.Driver for MySQL 8.x

This and other JDBC troubleshooting techniques are documented.

edit flag offensive delete link more
1

answered 2019-06-17 14:44:46 -0500

ak47 gravatar image

updated 2019-06-17 15:19:45 -0500

metadaddy gravatar image

If you ensure that Data Collector's log level is set to INFO, DEBUG or TRACE, then run the following command against the sdc log file:

grep "Driver class" * | grep YOUR_PIPELINE_NAME | awk '{print $1, $2, $(NF-2) }'

You should see what drivers are registered with Java's JDBC Driver Manager. If you do not see your driver then the following potential issues exist:

  1. Driver is not JDBC 3/4 compatible
  2. SDC does not have permissions to load those driver files ( file access)

As Pat mentioned, you could use the legacy driver tab to force usage of the driver by loading the class though "in general" that is not the preferred route since it will tie your pipeline to a specific driver rather than drivers provided by your admins/devops.

edit flag offensive delete link more

Comments

FYI the new MySQL driver is working fine. It was just that once it was installed, the internal Postgres JDBC driver stopped being identified.

meash-nrel gravatar imagemeash-nrel ( 2019-06-18 11:28:37 -0500 )edit

@meash-nrel Did you try specifying the driver class names in the Legacy Drivers tab? Did this work for you?

metadaddy gravatar imagemetadaddy ( 2019-06-18 11:35:08 -0500 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2019-06-17 13:05:05 -0500

Seen: 172 times

Last updated: Jun 18