Pentaho

 View Only
Expand all | Collapse all

Error connecting to Mysql 8 with caching_sha2_password enabled

This thread has been viewed 38 times
  • 1.  Error connecting to Mysql 8 with caching_sha2_password enabled

    Posted 05-06-2022 11:39
    Hi,
    I have some jobs scheduled on a Pentaho Server 9.2 that read data from a Mysql 5 and the authentication "mysql_native_password".

    Now the database has been migrated to Mysql 8.0.29 with caching_sha2_password authentication.
    I've added the connector library MySQL-connector-java-8.0.29.jar in the lib directory on the server, but the transformations that read data from Mysql fail.
    This is the error:
    17:23:26,973 ERROR [KettleComponent] Error Start: Pentaho pentaho-platform-core 9.2.0.0-290
    17:23:26,975 ERROR [KettleComponent] 77eab14b-cd50-11ec-855d-0050569862a1:COMPONENT:context-152396886-1651850603485:j_load_titoli_sns_datamart.xactionKettle.ERROR_0008 - [it_383] An unexpected error occurred running the transformation org.pentaho.platform.plugin.action.kettle.KettleComponentException: Kettle.ERROR_0014 - [it_249] Error occurred during transformation execution
    org.pentaho.platform.plugin.action.kettle.KettleComponentException: Kettle.ERROR_0014 - [it_249] Error occurred during transformation execution
    	at org.pentaho.platform.plugin.action.kettle.KettleComponent.executeJob(KettleComponent.java:1030)
    	at org.pentaho.platform.plugin.action.kettle.KettleComponent.executeAction(KettleComponent.java:606)
    	at org.pentaho.platform.engine.services.solution.ComponentBase.execute(ComponentBase.java:467)
    	at org.pentaho.platform.engine.services.runtime.RuntimeContext.executeComponent(RuntimeContext.java:1116)
    	at org.pentaho.platform.engine.services.runtime.RuntimeContext.executeAction(RuntimeContext.java:1081)
    	at org.pentaho.platform.engine.services.runtime.RuntimeContext.performActions(RuntimeContext.java:995)
    	at org.pentaho.platform.engine.services.runtime.RuntimeContext.executeLoop(RuntimeContext.java:949)
    	at org.pentaho.platform.engine.services.runtime.RuntimeContext.executeSequence(RuntimeContext.java:838)
    	at org.pentaho.platform.engine.services.runtime.RuntimeContext.executeSequence(RuntimeContext.java:752)
    	at org.pentaho.platform.engine.services.solution.SolutionEngine.executeInternal(SolutionEngine.java:354)
    	at org.pentaho.platform.engine.services.solution.SolutionEngine.execute(SolutionEngine.java:284)
    	at org.pentaho.platform.engine.services.solution.SolutionEngine.execute(SolutionEngine.java:181)
    	at org.pentaho.platform.plugin.action.builtin.ActionSequenceAction.execute(ActionSequenceAction.java:89)
    	at org.pentaho.platform.scheduler2.action.ActionRunner.callImpl(ActionRunner.java:176)
    	at org.pentaho.platform.scheduler2.action.ActionRunner.call(ActionRunner.java:80)
    	at org.pentaho.platform.scheduler2.action.ActionRunner.call(ActionRunner.java:56)
    	at org.pentaho.platform.engine.security.SecurityHelper.runAsUser(SecurityHelper.java:177)
    	at org.pentaho.platform.engine.security.SecurityHelper.runAsUser(SecurityHelper.java:166)
    	at org.pentaho.platform.scheduler2.action.DefaultActionInvoker.invokeActionImpl(DefaultActionInvoker.java:166)
    	at org.pentaho.platform.scheduler2.action.DefaultActionInvoker.invokeAction(DefaultActionInvoker.java:110)
    	at org.pentaho.platform.scheduler2.quartz.ActionAdapterQuartzJob.invokeAction(ActionAdapterQuartzJob.java:178)
    	at org.pentaho.platform.scheduler2.quartz.ActionAdapterQuartzJob.execute(ActionAdapterQuartzJob.java:79)
    	at org.pentaho.platform.scheduler2.quartz.BlockingQuartzJob.execute(BlockingQuartzJob.java:61)
    	at org.quartz.core.JobRunShell.run(JobRunShell.java:199)
    	at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:546)
    Caused by: org.pentaho.platform.plugin.action.kettle.KettleComponentException: Kettle.ERROR_0014 - [it_249] Error occurred during transformation execution
    	at org.pentaho.platform.plugin.action.kettle.KettleComponent.executeJob(KettleComponent.java:1026)
    	... 24 more
    17:23:26,979 ERROR [KettleComponent] Error end:​

    The same jobs and the transformations work well if executed from Spoon (PDI) 9.2 on my laptop.
    On this laptop, I'm using the same connector: MySQL-connector-java-8.0.29.jar.

    Why do these jobs fail on the server?
    How can I debug this problem?

    Thank you very much

    Claudio

    ------------------------------
    Claudio Battaglino
    IT Support Manager
    cbc
    ------------------------------


  • 2.  RE: Error connecting to Mysql 8 with caching_sha2_password enabled

    Posted 05-08-2022 19:40
    Hi Claudio

    It's a bit hard to tell from this, and I'm not familiar with xactions either (thought they'd been deprecated actually).

    Make sure you haven't got the  mysql5 driver under tomcat/webapps/WEB-INF/lib or tomcat/lib , stop the server, clear the karaf cache ( under pentaho-server/pentaho-solutions/system/karaf/caches/default )  and restart the server.

    If that doesn't fix it, try using a generic database connection instead of the MySQL one (as that seems to keep trying to use  org.gjt.mm.mysql.Driver instead of com.mysql.cj.jdbc.Driver or  com.mysql.jdbc.Driver ) and specify the drivername and connection string (i.e. jdbc:mysql://{hosturl}:{hostport}/{databasename} )

    Failing that, see if you can run a transformation with just a mysql connection in it on the server and see what error that throws.

    Regards

    ------------------------------
    Andrew Cave
    Systems Engineer
    BizCubed Pty Ltd
    Australia
    ------------------------------



  • 3.  RE: Error connecting to Mysql 8 with caching_sha2_password enabled

    Posted 05-09-2022 03:59
    Thank you Andrew for your reply.
    I didn't know that xactions were deprecated.
    So which is the right way to schedule jobs on a Pentaho server 9.2?

    Clearing the cache doesn't solve the problem and I have only one MySQL-connector-java-8.0.29.jar. in the tomcat/lib directory.

    I've tried to use a generic database connection but it changes the format of the result of the select queries. That is the result of a Table input item seems to be different from the one returned using a Mysql connector.

    Installing the mysql8 client on the Pentaho server I can connect and use the remote Mysql8 server without problems.

    I've noticed two things:
     - Removing all mysql connector I have the same error.
     - Into the directory /pentaho-solutions/system/dialects  there is a mysql5 folder but not a mysql8 folder.

    So I begin to have the doubt that Pentaho 9.2 is compatible with Mysql8.
    In this page I can see this comment:  "Version 5.1.x is the most recent version supported."
    Is it possible that I can't query a Mysql8 database?

    Claudio b.








    ------------------------------
    Claudio Battaglino
    ------------------------------



  • 4.  RE: Error connecting to Mysql 8 with caching_sha2_password enabled

    Posted 05-10-2022 15:10
    Claudio,

    We specifically made changes in Pentaho 9.3 to depricate the old references to 'org.gjt.mm.mysql.Driver'.  See core/src/main/java/org/pentaho/di/core/database/MySQLDatabaseMeta.java as an example.

    This might be the problem you are seeing in 9.2.

    ------------------------------
    Stephen Donovan
    Digital Solutions Architect
    Hitachi Vantara
    ------------------------------



  • 5.  RE: Error connecting to Mysql 8 with caching_sha2_password enabled

    Posted 05-11-2022 00:01
    Hi Claudio

    I've been connecting to MySQL 8 databases for the past three years.  I just confirmed it works in 9.2 and 9.3

    I've tried to use a generic database connection but it changes the format of the result of the select queries. That is the result of a Table input item seems to be different from the one returned using a Mysql connector.

    If you use the Generic Database connector, with the MySQL driver specified, then it is using the same driver as the Mysql Database Connector.  It just doesn't have pre-filled data.

    If you are getting different data forms returned, that must be in the original connection.  Can you check to see if there are custom values under the 'Options' and 'Advanced' tabs (in the left panel of the database connection editor).   If you can give specifics of what the differences are, we'll be better able to determine the problem.

    The details of exactly what is done and what exactly is changed are vital.

    ------------------------------
    Andrew Cave
    Systems Engineer
    BizCubed Pty Ltd
    Australia
    ------------------------------



  • 6.  RE: Error connecting to Mysql 8 with caching_sha2_password enabled

    Posted 05-11-2022 03:08
    Hi Andrew,

                         "I've been connecting to MySQL 8 databases for the past three years.  I just confirmed it works in 9.2 and 9.3"

    this is good news, so there is hope :-)
    But are you using the Mysql driver or the generic driver?
    And the MySQL user uses the protocol "mysql_native_password" or the protocol "caching_sha2_password"?

    Thank you very much


    cld


    ------------------------------
    Claudio Battaglino
    ------------------------------



  • 7.  RE: Error connecting to Mysql 8 with caching_sha2_password enabled

    Posted 05-11-2022 21:18
    I'm using the MySQL driver.  I put the postgresql-42.2.5.jar and the mysql-connector-java-5.1.41-bin.jar in the folder (or the current ones of each)

    caching_sha2_password is  used by default on MySQL8 (because it's more secure).  However, I do know that you can set the protocol to mysql_native_password in the my.cnf file   See here How to Run MySQL 8.0 with Native Password Authentication

    Regards

    ------------------------------
    Andrew Cave
    Systems Engineer
    BizCubed Pty Ltd
    Australia
    ------------------------------



  • 8.  RE: Error connecting to Mysql 8 with caching_sha2_password enabled

    Posted 05-12-2022 06:16
    Perhaps this also helps.
    Make sure that you have deployed a copy of the new mysql jdbc in all places required like data-integration (pdi) and the server, and removed the old copies:

    ...\pentaho\design-tools\data-integration\lib
    ...\pentaho\server\pentaho-server\tomcat\lib
    ...\pentaho\server\pentaho-server\tomcat\webapps\pentaho\WEB-INF\lib

    Best Regards

    ------------------------------
    Carl Messner
    Data Analyst
    MCSF
    ------------------------------



  • 9.  RE: Error connecting to Mysql 8 with caching_sha2_password enabled

    Posted 05-17-2022 10:03
    Really now I've seen that also using the driver: mysql-connector-java-5.1.49.jar
    The connection between the pentaho server and Mysql 8 works.
    That is I can create a datasource and test that the connection works.

    Only the schedulations of jobs that execute query on Mysql 8 fail.
    The execution of the same job from my pc on Windows, using Spoon and the same driver, works.

    cld


    ------------------------------
    Claudio Battaglino
    ------------------------------



  • 10.  RE: Error connecting to Mysql 8 with caching_sha2_password enabled

    Posted 05-17-2022 11:15
    Ok, I have solved.
    It was not a problem of a connector, I apologize to all you.

    The fact is that the on the new database Mysql 8 we changed the password of the user that executes query.
    I changed it on the file kettle.proporties on my pc, but not on the server.

    On the server there is this file:  /home/pentaho/.kettle/kettle.properties and it seems that the schedulation uses this file because now it works.
    I thought that kettle.properties was used only by pdi and not also by the server.

    So also using the connector mysql-connector-java-5.1.49.jar the connection with Mysql 8 works well.

    Thank you

    Claudio B.







    ------------------------------
    Claudio Battaglino
    ------------------------------