1

I am trying to get file from local system and then put it in mysql database through PutDatabaseRecord but it is throwing the following error: Full error i am getting in PutDatabaseRecord. which is not letting me insert record in database

My processors in Nifi are

GetFile -> UpdateAttribute -> PutDatabaseRecord

PutDatabaseRecord:

Following is the Configuration of DBCPConnectionPool port and schema name are correct related to mysql database

Full stacktrace of Error from nifi-app.log file is:

2020-01-28 23:16:45,453 ERROR [Timer-Driven Process Thread-1] o.a.n.p.standard.PutDatabaseRecord PutDatabaseRecord[id=9a0b1689-016f-1000-90e6-0127b317779a] org.apache.nifi.processors.standard.PutDatabaseRecord$$Lambda$896/0x0000000802252c40@35125ca9 failed to process due to org.apache.nifi.processor.exception.ProcessException: java.sql.SQLException: Cannot create PoolableConnectionFactory (Could not create connection to database server.); rolling back session: org.apache.nifi.processor.exception.ProcessException: java.sql.SQLException: Cannot create PoolableConnectionFactory (Could not create connection to database server.)
org.apache.nifi.processor.exception.ProcessException: java.sql.SQLException: Cannot create PoolableConnectionFactory (Could not create connection to database server.)
    at org.apache.nifi.dbcp.DBCPConnectionPool.getConnection(DBCPConnectionPool.java:442)
    at org.apache.nifi.dbcp.DBCPService.getConnection(DBCPService.java:49)
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.base/java.lang.reflect.Method.invoke(Method.java:567)
    at org.apache.nifi.controller.service.StandardControllerServiceInvocationHandler.invoke(StandardControllerServiceInvocationHandler.java:87)
    at com.sun.proxy.$Proxy103.getConnection(Unknown Source)
    at org.apache.nifi.processors.standard.PutDatabaseRecord.lambda$new$0(PutDatabaseRecord.java:363)
    at org.apache.nifi.processor.util.pattern.Put.onTrigger(Put.java:97)
    at org.apache.nifi.processors.standard.PutDatabaseRecord.lambda$onTrigger$10(PutDatabaseRecord.java:753)
    at org.apache.nifi.processor.util.pattern.PartialFunctions.onTrigger(PartialFunctions.java:114)
    at org.apache.nifi.processor.util.pattern.RollbackOnFailure.onTrigger(RollbackOnFailure.java:184)
    at org.apache.nifi.processors.standard.PutDatabaseRecord.onTrigger(PutDatabaseRecord.java:753)
    at org.apache.nifi.controller.StandardProcessorNode.onTrigger(StandardProcessorNode.java:1176)
    at org.apache.nifi.controller.tasks.ConnectableTask.invoke(ConnectableTask.java:213)
    at org.apache.nifi.controller.scheduling.TimerDrivenSchedulingAgent$1.run(TimerDrivenSchedulingAgent.java:117)
    at org.apache.nifi.engine.FlowEngine$2.run(FlowEngine.java:110)
    at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:515)
    at java.base/java.util.concurrent.FutureTask.runAndReset(FutureTask.java:305)
    at java.base/java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:305)
    at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
    at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
    at java.base/java.lang.Thread.run(Thread.java:835)
Caused by: java.sql.SQLException: Cannot create PoolableConnectionFactory (Could not create connection to database server.)
    at org.apache.commons.dbcp2.BasicDataSource.createPoolableConnectionFactory(BasicDataSource.java:669)
    at org.apache.commons.dbcp2.BasicDataSource.createDataSource(BasicDataSource.java:544)
    at org.apache.commons.dbcp2.BasicDataSource.getConnection(BasicDataSource.java:753)
    at org.apache.nifi.dbcp.DBCPConnectionPool.getConnection(DBCPConnectionPool.java:438)
    ... 23 common frames omitted
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Could not create connection to database server.
    at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
    at java.base/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.base/java.lang.reflect.Constructor.newInstanceWithCaller(Constructor.java:500)
    at java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:481)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
    at com.mysql.jdbc.Util.getInstance(Util.java:408)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:918)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:897)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:886)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:860)
    at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2330)
    at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2083)
    at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:806)
    at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:47)
    at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
    at java.base/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.base/java.lang.reflect.Constructor.newInstanceWithCaller(Constructor.java:500)
    at java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:481)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
    at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:410)
    at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:328)
    at org.apache.commons.dbcp2.DriverConnectionFactory.createConnection(DriverConnectionFactory.java:55)
    at org.apache.commons.dbcp2.PoolableConnectionFactory.makeObject(PoolableConnectionFactory.java:355)
    at org.apache.commons.dbcp2.BasicDataSource.validateConnectionFactory(BasicDataSource.java:115)
    at org.apache.commons.dbcp2.BasicDataSource.createPoolableConnectionFactory(BasicDataSource.java:665)
    ... 26 common frames omitted
Caused by: java.lang.NullPointerException: null
    at com.mysql.jdbc.ConnectionImpl.getServerCharset(ConnectionImpl.java:2997)
    at com.mysql.jdbc.MysqlIO.sendConnectionAttributes(MysqlIO.java:1934)
    at com.mysql.jdbc.MysqlIO.proceedHandshakeWithPluggableAuthentication(MysqlIO.java:1863)
    at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1226)
    at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2253)
    at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2284)
    ... 41 common frames omitted
2020-01-28 23:16:45,478 ERROR [Timer-Driven Process Thread-1] o.a.n.p.standard.PutDatabaseRecord PutDatabaseRecord[id=9a0b1689-016f-1000-90e6-0127b317779a] Failed to process session due to org.apache.nifi.processor.exception.ProcessException: java.sql.SQLException: Cannot create PoolableConnectionFactory (Could not create connection to database server.): org.apache.nifi.processor.exception.ProcessException: java.sql.SQLException: Cannot create PoolableConnectionFactory (Could not create connection to database server.)
org.apache.nifi.processor.exception.ProcessException: java.sql.SQLException: Cannot create PoolableConnectionFactory (Could not create connection to database server.)
    at org.apache.nifi.dbcp.DBCPConnectionPool.getConnection(DBCPConnectionPool.java:442)
    at org.apache.nifi.dbcp.DBCPService.getConnection(DBCPService.java:49)
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.base/java.lang.reflect.Method.invoke(Method.java:567)
    at org.apache.nifi.controller.service.StandardControllerServiceInvocationHandler.invoke(StandardControllerServiceInvocationHandler.java:87)
    at com.sun.proxy.$Proxy103.getConnection(Unknown Source)
    at org.apache.nifi.processors.standard.PutDatabaseRecord.lambda$new$0(PutDatabaseRecord.java:363)
    at org.apache.nifi.processor.util.pattern.Put.onTrigger(Put.java:97)
    at org.apache.nifi.processors.standard.PutDatabaseRecord.lambda$onTrigger$10(PutDatabaseRecord.java:753)
    at org.apache.nifi.processor.util.pattern.PartialFunctions.onTrigger(PartialFunctions.java:114)
    at org.apache.nifi.processor.util.pattern.RollbackOnFailure.onTrigger(RollbackOnFailure.java:184)
    at org.apache.nifi.processors.standard.PutDatabaseRecord.onTrigger(PutDatabaseRecord.java:753)
    at org.apache.nifi.controller.StandardProcessorNode.onTrigger(StandardProcessorNode.java:1176)
    at org.apache.nifi.controller.tasks.ConnectableTask.invoke(ConnectableTask.java:213)
    at org.apache.nifi.controller.scheduling.TimerDrivenSchedulingAgent$1.run(TimerDrivenSchedulingAgent.java:117)
    at org.apache.nifi.engine.FlowEngine$2.run(FlowEngine.java:110)
    at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:515)
    at java.base/java.util.concurrent.FutureTask.runAndReset(FutureTask.java:305)
    at java.base/java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:305)
    at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
    at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
    at java.base/java.lang.Thread.run(Thread.java:835)
Caused by: java.sql.SQLException: Cannot create PoolableConnectionFactory (Could not create connection to database server.)
    at org.apache.commons.dbcp2.BasicDataSource.createPoolableConnectionFactory(BasicDataSource.java:669)
    at org.apache.commons.dbcp2.BasicDataSource.createDataSource(BasicDataSource.java:544)
    at org.apache.commons.dbcp2.BasicDataSource.getConnection(BasicDataSource.java:753)
    at org.apache.nifi.dbcp.DBCPConnectionPool.getConnection(DBCPConnectionPool.java:438)
    ... 23 common frames omitted
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Could not create connection to database server.
    at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
    at java.base/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.base/java.lang.reflect.Constructor.newInstanceWithCaller(Constructor.java:500)
    at java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:481)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
    at com.mysql.jdbc.Util.getInstance(Util.java:408)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:918)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:897)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:886)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:860)
    at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2330)
    at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2083)
    at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:806)
    at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:47)
    at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
    at java.base/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.base/java.lang.reflect.Constructor.newInstanceWithCaller(Constructor.java:500)
    at java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:481)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
    at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:410)
    at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:328)
    at org.apache.commons.dbcp2.DriverConnectionFactory.createConnection(DriverConnectionFactory.java:55)
    at org.apache.commons.dbcp2.PoolableConnectionFactory.makeObject(PoolableConnectionFactory.java:355)
    at org.apache.commons.dbcp2.BasicDataSource.validateConnectionFactory(BasicDataSource.java:115)
    at org.apache.commons.dbcp2.BasicDataSource.createPoolableConnectionFactory(BasicDataSource.java:665)
    ... 26 common frames omitted
Caused by: java.lang.NullPointerException: null
    at com.mysql.jdbc.ConnectionImpl.getServerCharset(ConnectionImpl.java:2997)
    at com.mysql.jdbc.MysqlIO.sendConnectionAttributes(MysqlIO.java:1934)
    at com.mysql.jdbc.MysqlIO.proceedHandshakeWithPluggableAuthentication(MysqlIO.java:1863)
    at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1226)
    at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2253)
    at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2284)
    ... 41 common frames omitted
2020-01-28 23:16:46,584 INFO [Flow Service Tasks Thread-1] o.a.nifi.controller.StandardFlowService Saved flow controller org.apache.nifi.controller.FlowController@19c7c3d1 // Another save pending = false
2020-01-28 23:16:48,373 INFO [Write-Ahead Local State Provider Maintenance] org.wali.MinimalLockingWriteAheadLog org.wali.MinimalLockingWriteAheadLog@446dcab3 checkpointed with 85 Records and 0 Swap Files in 961 milliseconds (Stop-the-world time = 16 milliseconds, Clear Edit Logs time = 706 millis), max Transaction ID 254
2020-01-28 23:16:48,570 INFO [NiFi Web Server-30] o.a.n.c.s.StandardProcessScheduler Stopping PutDatabaseRecord[id=9a0b1689-016f-1000-90e6-0127b317779a]
2020-01-28 23:16:48,570 INFO [NiFi Web Server-30] o.a.n.controller.StandardProcessorNode Stopping processor: PutDatabaseRecord[id=9a0b1689-016f-1000-90e6-0127b317779a]
2020-01-28 23:16:48,582 INFO [Timer-Driven Process Thread-2] o.a.n.c.s.TimerDrivenSchedulingAgent Stopped scheduling PutDatabaseRecord[id=9a0b1689-016f-1000-90e6-0127b317779a] to run
2020-01-28 23:16:49,645 INFO [Flow Service Tasks Thread-1] o.a.nifi.controller.StandardFlowService Saved flow controller org.apache.nifi.controller.FlowController@19c7c3d1 // Another save pending = false

The full flow of all 3 processors and you can see data is queued before entering into PutDatabaseRecord even after running that processor

Any help is highly appreciated. Thanks.

Community
  • 1
  • 1
Hashaam
  • 11
  • 4
  • Please provide the full stacktrace from the log file. There should be a real SQL/driver error. – daggett Jan 28 '20 at 03:18
  • please check the full stacktrace edited above. – Hashaam Jan 28 '20 at 18:27
  • now please please provide the connection pool parameters: driver, url, and jar name. also what is your mysql server version? – daggett Jan 28 '20 at 19:09
  • btw. there are answered questions about Null pointer exception in mysql jdbc driver: https://stackoverflow.com/questions/50081527/cant-connect-to-mysql-from-java-nullpointerexception-inside-mysql-driver-conne – daggett Jan 28 '20 at 19:13
  • I have attached an image showing the parameters url and jar name(image of PutDatabaseRecord) .. Mysql server version is 8.0 – Hashaam Jan 29 '20 at 10:45
  • first thing that you have to try: use the same jdbc driver version (8.x) as your server version. Right now server version - 8.x and jdbc vestion (jar) - 5.x – daggett Jan 29 '20 at 10:56
  • tried jdbc mysql-connector-java-8.0.11.jar but error remains same – Hashaam Jan 29 '20 at 19:36

1 Answers1

1

Please ensure the below options to resolve the above-mentioned issue

• Check whether provided port number (3307) is listening to MySQL server

• Check whether MySQL port number added to the firewall rule in the NiFi environment(since MySQL server is running on localhost, adding firewall rule is not necessary).

• Provided connection timeout(Max Wait Time) is not enough to connect MySQL server

Manoj
  • 390
  • 3
  • 12
  • I have checked and ensure the port number and max wait time but can't find how to check firewall rule? kindly elaborate please – Hashaam Jan 28 '20 at 18:29