Support Questions
Find answers, ask questions, and share your expertise

PutSQL 1.5.0 with MySQL 8.0

New Contributor
 
Hello,
 
I have Nifi 1.5.0 installed, when I tried to connect to MySQL 8.0 DB through the PutSQL processor, I got the following error:
 
2021-04-05 22:17:14,391 ERROR [Timer-Driven Process Thread-3] o.apache.nifi.processors.standard.PutSQL PutSQL[id=11721162-202c-1c85-dfe1-8dc8c9f00d93] Failed to process session due to org.apache.nifi.processor.exception.ProcessException: org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (Unknown system variable 'query_cache_size'): {}
org.apache.nifi.processor.exception.ProcessException: org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (Unknown system variable 'query_cache_size')
        at org.apache.nifi.dbcp.DBCPConnectionPool.getConnection(DBCPConnectionPool.java:275)
        at sun.reflect.GeneratedMethodAccessor554.invoke(Unknown Source)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:498)
        at org.apache.nifi.controller.service.StandardControllerServiceInvocationHandler.invoke(StandardControllerServiceInvocationHandler.java:89)
        at com.sun.proxy.$Proxy134.getConnection(Unknown Source)
        at org.apache.nifi.processors.standard.PutSQL.lambda$new$1(PutSQL.java:261)
        at org.apache.nifi.processor.util.pattern.Put.onTrigger(Put.java:96)
        at org.apache.nifi.processors.standard.PutSQL.lambda$onTrigger$20(PutSQL.java:574)
        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.PutSQL.onTrigger(PutSQL.java:574)
        at org.apache.nifi.controller.StandardProcessorNode.onTrigger(StandardProcessorNode.java:1122)
        at org.apache.nifi.controller.tasks.ContinuallyRunProcessorTask.call(ContinuallyRunProcessorTask.java:147)
        at org.apache.nifi.controller.tasks.ContinuallyRunProcessorTask.call(ContinuallyRunProcessorTask.java:47)
        at org.apache.nifi.controller.scheduling.TimerDrivenSchedulingAgent$1.run(TimerDrivenSchedulingAgent.java:128)
        at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
        at java.util.concurrent.FutureTask.runAndReset(FutureTask.java:308)
        at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(ScheduledThreadPoolExecutor.java:180)
        at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:294)
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
        at java.lang.Thread.run(Thread.java:745)
Caused by: org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (Unknown system variable 'query_cache_size')
        at org.apache.commons.dbcp.BasicDataSource.createPoolableConnectionFactory(BasicDataSource.java:1549)
        at org.apache.commons.dbcp.BasicDataSource.createDataSource(BasicDataSource.java:1388)
        at org.apache.commons.dbcp.BasicDataSource.getConnection(BasicDataSource.java:1044)
        at org.apache.nifi.dbcp.DBCPConnectionPool.getConnection(DBCPConnectionPool.java:272)
        ... 22 common frames omitted
Caused by: java.sql.SQLException: Unknown system variable 'query_cache_size'
        at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:545)
        at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:513)
        at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:115)
        at com.mysql.cj.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:1983)
        at com.mysql.cj.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:1936)
        at com.mysql.cj.jdbc.StatementImpl.executeQuery(StatementImpl.java:1422)
        at com.mysql.cj.jdbc.ConnectionImpl.loadServerVariables(ConnectionImpl.java:2831)
        at com.mysql.cj.jdbc.ConnectionImpl.initializePropsFromServer(ConnectionImpl.java:2381)
        at com.mysql.cj.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:1739)
        at com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:1596)
        at com.mysql.cj.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:633)
        at com.mysql.cj.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:347)
        at com.mysql.cj.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:219)
        at org.apache.commons.dbcp.DriverConnectionFactory.createConnection(DriverConnectionFactory.java:38)
        at org.apache.commons.dbcp.PoolableConnectionFactory.makeObject(PoolableConnectionFactory.java:582)
 
I tried to change the driver in the DBCPConnectionPool to overcome the 'query_cache' variables removal in MySQL 8.0:
  • mysql-connector-java-5.1.44.jar
  • mysql-connector-java-5.1.49.jar
  • mysql-connector-java-6.0.5.jar
  • mysql-connector-java-8.0.23.jar
But none of them worked for me!
 
Any suggestions?
 
Thanks,
Isam
0 REPLIES 0