MySQL: JDBC_06: Failed to Initialize Connection Pool: Access Denied Error

MySQL: JDBC_06: Failed to Initialize Connection Pool: Access Denied Error

Problem

A pipeline which is using a JDBC stage to access a MySQL database generates an error int he following form:

START_ERROR: JDBC_06 - Failed to initialize connection pool: com.zaxxer.hikari.pool.HikariPool$PoolInitializationException: Failed to initialize pool: Access denied for user 'username'@'hostname' (using password: YES)

The pipeline is using a configured Connection in Control Hub, and when the Test Connection button is used in the Edit Connection window, the connection succeeds.  Only when it is used in an actual pipeline does it fail.

Cause

This behavior occurs when two conditions are met:

1. MySQL is configured to require SSL.  This condition can be met in one of three ways:
   1. The MySQL user is configured with the REQUIRE SSL option.
   2. The “requireSSL” property has been configured in the JDBC Additional Configuration Properties int he JDBC stage, with the value “true”
   3. “requireSSL=true” has been added to the JDBC connection string in the Connection configuration.
2. The “com.streamsets.pipeline.lib.jdbc.disableSSL“ property in sdc.properties is set to “true”.

The error message occurs because the JDBC stages check for the property “com.streamsets.pipeline.lib.jdbc.disableSSL=true“ and if it is found they attempt to disable SSL for the connection.  In the case of MySQL, this is done by adding “useSSL=false” to the connection properties when the connection pool is initialized.  As MySQL has been configured to requireSSL (by one of the methods under condition 1 above), MySQL will deny access.

This does not occur in the Test Connection case because SSL is not disabled by this property in the connection test.

Solution

The “com.streamsets.pipeline.lib.jdbc.disableSSL“ property should only be set to “true“ when there are pipelines running on the Data Collector which cannot use SSL.  If no pipelines being run on the Data Collector require a non-secure configuration, this property should be set to “false”, which will resolve the issue.

However, if there are pipelines running on the Data Collector which do require a non-SSL connection, this property should not be changed (as it will break those pipelines).  In this case, it is possible to override this property on a per-stage basis by adding the “useSSL=true“ property in the Additional JDBC Connection Properties of the JDBC stage.

To override the property, select the JDBC stage where the error is occurring and perform the following:

1. Choose "Configuration" in the stage properties (at the bottom left of the window)
2. Select the "JDBC" tab.
3. Select “Show Advanced Options” if the advanced options are not already shown.
4. Scroll down to the "Additional JDBC Configuration Properties" (at the bottom of the JDBC tab) and click the "+" button to add a new property.
5. In the "Name" field, enter "useSSL"
6. In the "Value" field, enter "true"