Pentaho

 View Only

 PDI 9.0 - MySQL 8 / Native (JDBC) - Table Input preview and execution not returning database column names (works fine in PDI 8.2 with same driver and config)

  • Pentaho
  • Kettle
  • Pentaho
  • Pentaho Data Integration PDI
Iain Lattimer's profile image
Iain Lattimer posted 06-04-2020 11:11

Hi,

 

There appears to be an issue with the Table Loader in PDI 9.0 as follows. It is very easy to repeat and the same test/transformation works perfectly in PDI 8.2 with identical configuration. Any help appreciated.

 

The problem/example:

 

In a new transformation, I add just a Table Input node and carry out these steps:

 

  • Set the Connection
  • Enter 'select * from mytable'
  • Press Preview

 

The results are a table labelled with columns Field2 - FieldN. This is not desirable or as per PDI 8.2.

 

Additionally, if I use the 'Get SQL select statement' wizard and navigate to a table, the resulting autogenerated sql statement states 'select Field2, Field3, ... from mytable'. This cannot even execute.

 

I cannot get Table Input to select and return my true column names.

 

Configuration:

  • Database Type/Version
    • MySQL 8
  • Database Connection Type
    • Generic Database (Dialect MySQL)
    • Native (JDBC)
  • Driver
    • mysql-connector-java-8.0.13.jar
  • Driver Location
    • <PDI 8.2 Install>/lib
    • <PDI 9.0 Install>/lib

 

The Table Input node works perfectly in PDI 8.2 and returns the true column names. In PDI 9.0, it returns Field2, Field3 etc. Everything else about the setup is identical.

 

I have a large set of jobs and transformations that I'm regression testing with a move to PDI 9.0 and I'm stuck at this first runtime hurdle! No column names = broken transformations.

 

Thanks in advance for any help.

 

Iain

 

 

 

 

 


#PentahoDataIntegrationPDI
#Pentaho
#Kettle
Sergio Ribeiro's profile image
Sergio Ribeiro posted 06-04-2020 12:58

Hi @Iain Lattimer​,

 

Are you sure you're also using that driver in 8.2?

 

If you check MySQL section on JDBC drivers reference for both 8.2 and 9.0, you can see that «Version 5.1.x is the most recent version supported».

Try using the Driver URL stated to download most recent connector (5.1.49) and try again.

 

Regards,

 

Sérgio Ribeiro

Porto - Portugal

 

Iain Lattimer's profile image
Iain Lattimer posted 06-04-2020 13:09
Hi Sergio,Yes, 100%. As it is via the Generic database -> Native (JDBC) database connection, 8.0.x drivers have worked perfectly in Pentaho 8.x for me to date. You just set the Custom driver class name to 'com.mysql.cj.jdbc.Driver'. This is a valid database connection setup.I do appreciate that to use the specific, inbuilt MySQL connection type, the mysql 5.1.x driver is required.However, as this is a clean-build OS/machine I am absolutely certain that the only mysql driver is the 8.0.13 driver in the /lib folder of data-integration. In fact, I just tested 8.0.20 and it works fine with 8.2 also..just not with 9.0.In terms of using 5.1.48..this is not viable for connecting to and working with the latest MySQL 8 databases from my past experiments (I recall battling sha2_caching_password I think..it was 2 years ago at least).Cheers,Iain
Iain Lattimer's profile image
Iain Lattimer posted 06-04-2020 13:34

UPDATE 1

 

I have just tried using a Generic database -> Native (JDBC) connection with the 5.1.49 driver and changing the Custom driver class name to the old 'org.gjt.mm.mysql.Driver'.

 

Testing this in 9.0, it connects OK (no sha2_caching_password issues) , however, the Table Input still retrieves the columns as 'Field1, Field2' (see screenshot).

 

To validate that I am using the correct driver, I removed them all and it couldn't load the driver. I also had to change the driver class to match that of 5.1.* so I am 100% certain of the active driver version).

 

I will keep investigating and update accordingly.

 

 

 

 

Iain Lattimer's profile image
Iain Lattimer posted 06-04-2020 13:38

UPDATE 2

 

I now also tested using the default connection type of 'MySQL' -> Native (JDBC) instead of a Generic database specification.

 

The results are identical. Driver 5.1.49 with PDI 9.0 connecting to MySQL 8.0.20 appears not to retrieve field names.

 

Any help appreciated as having to revert to old builds with PDI 8.2 in the interim.

 

Thanks.

 

Iain

Related Content