Apache-Nifi Cannot create JDBC driver of class 'com.mysql.jdbc.Driver'

Question

Output from $NIFI_HOME/logs/nifi-app.log:

2016-10-26 12:45:13,304 ERROR [Timer-Driven Process Thread-6] o.apache.nifi.processors.standard.PutSQL
org.apache.nifi.processor.exception.ProcessException: org.apache.commons.dbcp.SQLNestedException: Cannot create JDBC driver of class 'com.mysql.jdbc.Driver' for connect URL 'jdbc:sqlserver://*BLOCKED*/*BLOCKED*'
        at org.apache.nifi.dbcp.DBCPConnectionPool.getConnection(DBCPConnectionPool.java:234) ~[na:na]
        at sun.reflect.GeneratedMethodAccessor393.invoke(Unknown Source) ~[na:na]
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_101]
        at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_101]
        at org.apache.nifi.controller.service.StandardControllerServiceProvider$1.invoke(StandardControllerServiceProvider.java:177) ~[nifi-framework-core-1.0.0.jar:1.0.0]
        at com.sun.proxy.$Proxy81.getConnection(Unknown Source) ~[na:na]
        at org.apache.nifi.processors.standard.PutSQL.onTrigger(PutSQL.java:218) ~[na:na]
        at org.apache.nifi.processor.AbstractProcessor.onTrigger(AbstractProcessor.java:27) ~[nifi-api-1.0.0.jar:1.0.0]
        at org.apache.nifi.controller.StandardProcessorNode.onTrigger(StandardProcessorNode.java:1064) ~[nifi-framework-core-1.0.0.jar:1.0.0]
        at org.apache.nifi.controller.tasks.ContinuallyRunProcessorTask.call(ContinuallyRunProcessorTask.java:136) [nifi-framework-core-1.0.0.jar:1.0.0]
        at org.apache.nifi.controller.tasks.ContinuallyRunProcessorTask.call(ContinuallyRunProcessorTask.java:47) [nifi-framework-core-1.0.0.jar:1.0.0]
        at org.apache.nifi.controller.scheduling.TimerDrivenSchedulingAgent$1.run(TimerDrivenSchedulingAgent.java:132) [nifi-framework-core-1.0.0.jar:1.0.0]
        at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511) [na:1.8.0_101]
        at java.util.concurrent.FutureTask.runAndReset(FutureTask.java:308) [na:1.8.0_101]
        at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(ScheduledThreadPoolExecutor.java:180) [na:1.8.0_101]
        at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:294) [na:1.8.0_101]
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) [na:1.8.0_101]
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) [na:1.8.0_101]
        at java.lang.Thread.run(Thread.java:745) [na:1.8.0_101]
Caused by: org.apache.commons.dbcp.SQLNestedException: Cannot create JDBC driver of class 'com.mysql.jdbc.Driver' for connect URL 'jdbc:sqlserver://*BLOCKED*:3306/*BLOCKED*'
        at org.apache.commons.dbcp.BasicDataSource.createConnectionFactory(BasicDataSource.java:1452) ~[na:na]
        at org.apache.commons.dbcp.BasicDataSource.createDataSource(BasicDataSource.java:1371) ~[na:na]
        at org.apache.commons.dbcp.BasicDataSource.getConnection(BasicDataSource.java:1044) ~[na:na]
        at org.apache.nifi.dbcp.DBCPConnectionPool.getConnection(DBCPConnectionPool.java:231) ~[na:na]
        ... 18 common frames omitted
Caused by: java.sql.SQLException: No suitable driver
        at java.sql.DriverManager.getDriver(DriverManager.java:315) ~[na:1.8.0_101]
        at org.apache.commons.dbcp.BasicDataSource.createConnectionFactory(BasicDataSource.java:1437) ~[na:na]

Fusiform

Is that the only error listed? I would've thought there was another "Caused by" below that. If not, then it doesn't look like it's finding your MySQL driver JAR. – Porfirioporgy

Is the JDBC URL correct? I understand you've redacted the host and database name, and 3306 is the default port for MySQL, but the url references sqlserver. Is that Microsoft SQL Server? I think it should be jdbc:mysql://host:port/database. – Accusal

@Accusal it's caused by no suitable driver – Fusiform

Can you test the connection by using the same URL with the code here? https://mcmap.net/q/17808/-connect-java-to-a-mysql-database – Accusal

Answer 1

By the exception message you get

java.sql.SQLException: No suitable driver

it's clear that what you're missing out is the drivers. The DBCPConnectionPool has a configuration property named Database Driver Location(s). You need to fill it with a location to your MySQL drivers(jars). This could do.

What happens is, that the controller service is loading this jar in run-time, and after the classes have been loaded to memory, it looks for com.mysql.jdbc.Driver. That way, you can provide any driver jar and connect to any jdbc supported db.

Yager

Answer 2

It appears this question was already answered on HCC, the full thread is here, and the conclusion is:

1. start with the "jdbc:hive2" prefix

jdbc:hive2://host.name.net:10000/;principal=hive/[email protected]

2. Add following property to hive-site.xml that is passed under HiveConnectionPool "Hive Configuration Resources" property.

hadoop.security.authentication kerberos

Andreasandree