Skip to main content
BerniHacker
BerniHacker
Gold ⭐️
January 4, 2022
Solved

Not able to connect to MySQL database when using Apache NiFi in Linux Ubuntu

Hi!

 

I am trying to connect to a MySQL RDS instance in AWS with Apache NiFi on Linux Ubuntu.

 

The MySQL database is behind a SSH tunnel which is working in a dedicated terminal. The MySQL database is accessible through another Linux terminal.

 

$ ssh -i /home/bernardo/.ssh/sshbernardo3.pem -N -L 33061:<db_name>.<id>.eu-west-1.rds.amazonaws.com:3306 <user>@<ip_address> -p <port>

 

I have uploaded and unzipped the JDBC driver from https://downloads.mysql.com/archives/c-j/

 

The file java.sql.Driver is located in the directory /usr/share/java/META-INF/services in Linux

 

I have installed the jdbc connector in Linux and set up the user to use JDBC

$ sudo apt-get install libmysql-java

$ cd /home/bernardo

$ gedit .bashrc

 

Added the following lines at the end of the file:

 

# Setting up the user to use JDBC

CLASSPATH=$CLASSPATH:/usr/share/java/mysql-connector-java-8.0.26.jar

export CLASSPATH

 

$ source .bashrc

 

In NiFi I have used a DBCPConnectionPool1.15.2 controller service which is configured as follows:

 

• Database Connection URL: jbdc:mysql://127.0.0.1:33061/<db_name>
• Database Driver Class Name: java.sql.Driver
• Database Driver Location: /usr/share/java/META-INF/services
• Database User: <user>
• Password: <password>

 

When enabling the controller service, I get the following error:

 

StandardControllerServiceNode[service=DBCPConnectionPool[id=2440f624-017e-1000-9467-8bb12dda6ca6], name=<controller_service_name>, active=true] Failed to invoke @OnEnabled method due to java.lang.NoSuchMethodException: java.sql.Driver.<init>()
causes: java.lang.InstantiationException: java.sql.Driver
causes: org.apache.nifi.processor.exception.ProcessException: Creating driver instance is failed: Creating driver instance is failed

 

Any hint?

 

This is the SW I have installed:

 

Ubuntu 18.04.6
openjdk version "11.0.13" 2021-10-19
javac 11.0.13
nifi-1.15.2

 

Thanks,

 

Bernardo

BerniHacker
BerniHackerAnswer
Gold ⭐️
January 13, 2022

I managed to fix the issue. It was related to the version of the jdbc driver.

 

I did the following:

 

In Linux browser go to https://dev.mysql.com/downloads/connector/j/

 

Select

 

• Ubuntu Linux

• 18.04

 

Download

 

In a CLI terminal go to the Downloads directory

 

$ cd /home/bernardo/Downloads

Unpack the deb file

 

$ sudo dpkg -i mysql-connector-java_8.0.26-1ubuntu18.04_all.deb

Check where is the connector location in the file system

 

$ dpkg -L mysql-connector-java | fgrep jar

/usr/share/java/mysql-connector-java-8.0.26.jar

Clean /usr/share/java from the previous jdbc driver

 

$ sudo rm mysql-connector-java-5.1.45.jar
$ sudo rm -r com
$ sudo rm -r META-INF/

Install the connector: unpack the .jar file

 

$ sudo jar xvf /usr/share/java/mysql-connector-java-8.0.26.jar

To find the driver class name, open /usr/share/java/META-INF/services/java.sql.Driver

 

$ cat /usr/share/java/META-INF/services/java.sql.Driver
com.mysql.cj.jdbc.Driver

The driver class name is: com.mysql.jdbc.Driver

 

Restart Apache Ni-Fi

 

Modify the configuration of the controller service with:

 

• Database Driver Class Name: com.mysql.cj.jdbc.Driver

• Database Driver Location: /usr/share/java/mysql-connector-java-8.0.26.jar

View in thread

5 replies

BerniHacker
BerniHackerAuthor
Gold ⭐️
January 5, 2022

My Linux is running on VirtualBox 6.1 on top of Windows 10 Pro.

Translate
BerniHacker
BerniHackerAuthor
Gold ⭐️
January 5, 2022

The file mysql-connector-java-8.0.26.jar is in /usr/share/java

 

Also:

 

$ cat /usr/share/java/META-INF/services/java.sql.Driver

com.mysql.cj.jdbc.Driver

 

SO, I have made the following changes to the DBCPConnectionPool1.15.2 controller service configuration:

 

• Database Driver Class Name: com.mysql.cj.jdbc.Driver
• Database Driver Location: /usr/share/java/mysql-connector-java-8.0.26.jar

 

The other parameters are not changed:

 

• Database Connection URL: jbdc:mysql://127.0.0.1:33061/<db_name>

• Database User: <user>
• Password: <password>

 

$ netstat -nplt

Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name

tcp 0 0 127.0.0.1:33061 0.0.0.0:* LISTEN 4873/ssh
tcp6 0 0 ::1:33061 :::* LISTEN 4873/ssh

 

Still I get an error:

 

causes: org.apache.nifi.processor.exception.ProcessException: No suitable driver for the given Database Connection URL

Translate
BerniHacker
BerniHackerAuthor
Gold ⭐️
January 12, 2022

I thought it could be an issue related to the driver version so I re-installed the jdbc driver. I have cleaned /home/bernardo/Download and /usr/share/java from the old sql related files. Then I have done the installation again.

 

Install the jdbc connector

 

$ sudo apt-get install libmysql-java

 

The .jar file is in /usr/share/java/mysql-connector-java-5.1.45.jar Unpack the .jar file

 

$ sudo jar xvf /usr/share/java/mysql-connector-java-5.1.45.jar

 

To find the driver class name, open /usr/share/java/META-INF/services/java.sql.Driver

 

$ cat /usr/share/java/META-INF/services/java.sql.Driver

com.mysql.jdbc.Driver

com.mysql.fabric.jdbc.FabricMySQLDriver

 

The driver class name is: com.mysql.jdbc.Driver

 

This is the updated configuration in the DBCPConnectionPool1.15.2 controller service in Apache Ni-Fi:

 

• Database Driver Class Name: com.mysql.jdbc.Driver

• Database Driver Location: /usr/share/java/mysql-connector-java-5.1.45.jar

• Database Connection URL: jbdc:mysql://127.0.0.1:33061/<db_name>

• Database User: <db_user>

• Password: <db_password>

 

I have obtained the same result:

 

causes: org.apache.nifi.processor.exception.ProcessException: No suitable driver for the given Database Connection URL

Then I checked if the problem was in the SSH tunnel.

 

$ netstat -nplt
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name
tcp 0 0 127.0.0.1:33061 0.0.0.0:* LISTEN 4497/ssh
tcp 0 0 127.0.0.1:3306 0.0.0.0:* LISTEN -
tcp6 0 0 ::1:33061 :::* LISTEN 4497/ssh

$ cat /proc/4497/status

Name: ssh
State: S (sleeping)

 

The SSH process is in sleeping state.

 

In a separate terminal I accessed the remote MySQL database:

 

$ mysql -u <db_user> -p -h 127.0.0.1 -P 33061

 

Finally, I tried to connect to my local mysql database in my local Linux instance by using the following Ni-Fi settings:

 

• Database Driver Class Name: com.mysql.jdbc.Driver

• Database Driver Location: /usr/share/java/mysql-connector-java-5.1.45.jar

• Database Connection URL: jbdc:mysql://127.0.0.1:3306/<local_db_name>

• Database User: <local_db_user>

• Password: <local_db_password>

 

I have obtained the same result:

 

causes: org.apache.nifi.processor.exception.ProcessException: No suitable driver for the given Database Connection URL

Still, in a separate Linux terminal I am able to access the local mysql database

:

$ mysql -u <local_db_user> -p -h 127.0.0.1 -P 3306

 

 

Translate
BerniHacker
BerniHackerAuthor
Gold ⭐️
January 12, 2022

Hi have corrected a typo in Ni-Fi controller service configuration:

 

• Database Connection URL: jdbc:mysql://127.0.0.1:33061/<local_db_name>

 

This way I solved the controller service problem and I have been able to enable it.

 

However, when I run the processor QueryDatabaseTable, I get the following error: Cannot create PoolableConnectionFactory - Communications link failure

 

QueryDatabaseTable[id=017e1003-c2d8-14cf-4e34-feee76411595] Unable to execute SQL select query SELECT * FROM periodic_measurements_test due to java.sql.SQLException: Cannot create PoolableConnectionFactory (Communications link failure

The last packet successfully received from the server was 1 milliseconds ago. The last packet sent successfully to the server was 1 milliseconds ago.): javax.net.ssl.SSLHandshakeException: No appropriate protocol (protocol is disabled or cipher suites are inappropriate) ↳ causes: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

The last packet successfully received from the server was 1 milliseconds ago. The last packet sent successfully to the server was 1 milliseconds ago. ↳ causes: java.sql.SQLException: Cannot create PoolableConnectionFactory (Communications link failure

The last packet successfully received from the server was 1 milliseconds ago. The last packet sent successfully to the server was 1 milliseconds ago.) ↳ causes: org.apache.nifi.processor.exception.ProcessException: java.sql.SQLException: Cannot create PoolableConnectionFactory (Communications link failure

The last packet successfully received from the server was 1 milliseconds ago. The last packet sent successfully to the server was 1 milliseconds ago.)

The processor configuration is default, except for:

 

• Database Connection Pooling Service: < DBCPConnectionPool controller service name >

• Database Type: MySQL

• Table Name: periodic_measurements_test

 

The problem might be due to the SSH tunnel but I cannot understand where the problem is.

 

netstat -nplt

Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name
tcp 0 0 127.0.0.1:33061 0.0.0.0:* LISTEN 29483/ssh
tcp 0 0 127.0.0.1:3306 0.0.0.0:* LISTEN -
tcp 0 0 127.0.0.1:8443 0.0.0.0:* LISTEN -

 

And in Linux terminal I can still access the MySQL database thorough CLI:

 

$ mysql -u <db_user> -p -h 127.0.0.1 -P 33061

 

The controller service configuration is still:

 

• Database Connection URL: jdbc:mysql://127.0.0.1:33061/<db_name>

• Database Driver Class Name: com.mysql.jdbc.Driver

• Database Driver Location: /usr/share/java/mysql-connector-java-5.1.45.jar

• Database User: <db_user>

• Password: <db_password>

Translate
Terms & ConditionsAccessibility statement