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

[#1]

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

[#2]

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

[#3]

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

[#4]

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

[#5]

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>

[#6 · Accepted solution]

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