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

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

BerniHacker — Tue, 04 Jan 2022 17:14:32 GMT

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

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

BerniHacker — Wed, 05 Jan 2022 07:43:48 GMT

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

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

BerniHacker — Wed, 05 Jan 2022 18:00:33 GMT

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

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

BerniHacker — Wed, 12 Jan 2022 09:20:55 GMT

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

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

BerniHacker — Wed, 12 Jan 2022 09:23:38 GMT

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>

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

BerniHacker — Thu, 13 Jan 2022 14:50:45 GMT

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