how to configure and connect mysql with nifi and perform some basic sql queries.

[#1]

I am trying to connect to mysql database through NIFI as I need to access the database and perform some queries. Currently the whole setup is running in my local windows machine and I am getting error with connectivity.

[#2 · Accepted solution]

What error are you getting and what approach are you using (and what version of NiFi/HDF)? I recommend setting up a DBCPConnectionPool controller service. Your settings for the controller service will depend on your MySQL instance, here is an example of one I use to connect to a local MySQL running on Docker, using test_schema as the database:

The "Database Driver Location(s)" property is set to a URL pointing at my mysql-connector-java-5.1.38-bin.jar. As of NiFi 1.0.0 (via NIFI-2604), this property can include a comma-separated list of folders and/or JARs, and need not be URLs (i.e. you can use relative path names, although according to this post I don't think having the Windows drive letter at the front will work).

[#3]

Hi Matt,

Thanks for the solution. The dataflow has started working!! 🙂

Regards,

Aditya Hegde

[#4]

Hi @Aditya Hegde could you please tell me how did you perform the connection of mysql with nifi? I have to do the same. please provide the details of processor used by you.

[#5]

@aishwarya srivastava,

Find the attached files as a reference. If your nifi application and mysql server both are running in two different scopes, than look into port configuration. There should be proper connectivity.

processor-1-configuration-a.png

processor-1-configuration.png

[#6]

Thanks this was useful, i just wanted to add one more point. Since i was new to nifii, ever after creating the controller, i was not sure why the processor could not be started. Then i figured out that controller needs to be enabled, so that the processor shows the "Start" option.

,

This may be non trivial, but thought would help someone since I was struggling with the processor not being active even after creating the controller. You need to enable the controller service after creating them, so that you can start the processor.

[#7]

To learn why a process will not start, hover your cursor over the yellow triangle with exclamation mark and an error message will pop up to explain why it will not start. Then you will instantly know the problem.

[#8]

I'm following the instructions here, but I still can't seem to get it working.

I'm pointing to the same driver with the same driver class name.

However, my URL is a bit different.

I'm trying to connect to an AWS Aurora instance.

If I use a connection string like this:

jdbc:mysql://my-url.us-east-1.rds.amazonaws.com:1433;databaseName=my_db_name

I get the error below.

If I remove the port and db name, I get an error that says "Unable to execute SQL ... due to org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (Communications link failure"

Any ideas?

ExecuteSQL[id=df4d1531-3056-1f5a-9d32-fa30462c23ba] Unable to execute SQL select query <query> for StandardFlowFileRecord[uuid=7d70ed35-ae97-47e8-a860-0a2fa75fa2ef,claim=StandardContentClaim [resourceClaim=StandardResourceClaim[id=1538684794331-5, container=default, section=5], offset=939153, length=967],offset=0,name=properties.json,size=967] due to org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (Cannot load connection class because of underlying exception: 'java.lang.NumberFormatException: For input string: "1433;databaseName=my_db_name"'.); routing to failure: org.apache.nifi.processor.exception.ProcessException: org.apache.commons.dbcp.SQLNestedException...

[#9]

Did you get a solution to this . I am also getting a communication error . My Nifi Instance and Mysql are on the same linux server .