question Re: Apache Nifi Mysql Connectivity - Cannot create PoolableConnectionFactory in Support Questions

Apache Nifi Mysql Connectivity - Cannot create PoolableConnectionFactory

abhinav_joshi — Thu, 25 Jun 2020 03:44:00 GMT

Hi Team ,

I am having issues while connecting to mysql database using apache nifi. The error is as follows

Cannot create PoolableConnectionFactory

Please note that Apache Nifi and MySQL is installed on the same Linux server and hence there shouldn't be any problems with the 2 applications talking to each other.

The database connection pool settings is provided below

The error when I try extracting the records is provided below

This is something very urgent as we are doing a POC and if this succeeds our Projects will be based on this. Any help in this regard will be much appreciated.

@Matt @Shu

Re: Apache Nifi Mysql Connectivity - Cannot create PoolableConnectionFactory

abhinav_joshi — Thu, 25 Jun 2020 04:21:01 GMT

Hi All ,

I fixed this by changing the source URL to jdbc:mysql://127.0.0.1:3306/source .

I still need to understand the reason as to why it works this way when both the applications are installed on the same linux box . Also to note that in my case I have this linux installed on a VM on Google Cloud Platform

I will work on it and post the reasoning for reference.

Thanks

Re: Apache Nifi Mysql Connectivity - Cannot create PoolableConnectionFactory

stevenmatison — Thu, 25 Jun 2020 12:19:46 GMT

@abhinav_joshi its related to permissions for root user. First, you should avoid root user. Also recommend use FQDNs, not ips. It seems that user has perms on the localhost ip, but maybe not when connecting from the external IP. If you make a specific user, you would need to grant them access to a database from a specific host. For example:

CREATE DATABASE hive;
CREATE USER 'hive'@'hdp3.cloudera.com' IDENTIFIED BY 'hive';
GRANT ALL PRIVILEGES ON *.* TO 'hive'@'hdp3.cloudera.com' WITH GRANT OPTION;
FLUSH PRIVILEGES;

Re: Apache Nifi Mysql Connectivity - Cannot create PoolableConnectionFactory

abhinav_joshi — Wed, 08 Jul 2020 01:25:25 GMT

Thanks @stevenmatison for your response on this