question Re: how to configure and connect mysql with nifi and perform some basic sql queries. in Support Questions

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

adityahegde — Tue, 03 Jan 2017 18:19:46 GMT

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.

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

mburgess — Mon, 19 Aug 2019 11:33:58 GMT

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).

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

adityahegde — Thu, 05 Jan 2017 12:02:38 GMT

Hi Matt,

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

Regards,

Aditya Hegde

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

aishwarya020793 — Wed, 22 Feb 2017 17:39:43 GMT

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.

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

adityahegde — Thu, 23 Feb 2017 19:35:41 GMT

@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

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

keerthi4u83 — Thu, 19 Oct 2017 03:08:36 GMT

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.

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

Jim_Steinebrey — Fri, 27 Apr 2018 20:45:31 GMT

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.

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

zack_riesland — Fri, 12 Oct 2018 00:50:41 GMT

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...

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

abhinav_joshi — Wed, 29 Jul 2020 23:00:00 GMT

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 .