Pentaho

 View Only

 Pentaho PDI joblog bug?

Quincy van der Ree's profile image
Quincy van der Ree posted 12-02-2022 07:12
We're currently running into an issue with renaming columns within the Job log table that's specified within each job. Currently we have the following setup, which works:
image.png
However, in another environment we have I found that some of the columns specified here have been renamed, as you can see down below. 
image.png
Here you see that LOGDATE has been replaced with ENDDATETIME and REPLAYDATE has been replaced with STARTDATETIME. These are also the actual column names within the Job log table. 

The problem however is that we can't change the column names from the first mentioned instance. We would like to rename the columns so that they're aligned with the second instance, but when I rename the columns and recreate the table via the SQL button I get an error when running the Pentaho job:
2022/10/26 15:07:45 - Spoon - Starting job...
2022/10/26 15:07:45 - sj_dsa_synergy_kwartier_postgres - Start of job execution
2022/10/26 15:07:45 - sj_dsa_synergy_kwartier_postgres - ERROR (version 8.3.0.0-371, build 8.3.0.0-371 from 2019-06-11 11.09.08 by buildguy) : A serious error occurred during job execution:
2022/10/26 15:07:45 - sj_dsa_synergy_kwartier_postgres - Unable to begin processing by logging start in logtable RTICT_MONITORING_PENTAHO
2022/10/26 15:07:45 - sj_dsa_synergy_kwartier_postgres -
2022/10/26 15:07:45 - sj_dsa_synergy_kwartier_postgres - Unable to obtain last logdate from table "DPL_SP_META"."RTICT_MONITORING_PENTAHO"
2022/10/26 15:07:45 - sj_dsa_synergy_kwartier_postgres - ERROR: column "LOGDATE" does not exist
  Hint: Perhaps you meant to reference the column "RTICT_MONITORING_PENTAHO.ENDDATE" or the column "RTICT_MONITORING_PENTAHO.DEPDATE".
  Position: 170
For some reason when I've recreated the table with the renamed columns via Pentaho it still thinks it needs the 'old' column. As you can see in the logging here it is giving the error that the column LOGDATE does not exist. This is correct, because I've renamed this column to ENDDATETIME and recreated the table. In the table it therefore does not have this field, but I'd expect that when Pentaho recreates the table itself it would recognise the metadata. 
The weirdest thing to me is that somehow it has worked in the past, since we have another instance that does work with renamed columns and works perfectly. Unfortunately I now have no opportunity to align both instances to one another. 
Our Pentaho version is:
image.png
Any help is much appreciated. 
Quincy van der Ree's profile image
Quincy van der Ree posted 01-11-2023 08:16
anyone?
Carlos Lopez's profile image
Carlos Lopez posted 01-12-2023 13:53
Quincy: are you upgrading from an earlier version of PDI to 8.3 and attempting to use the same PDI tables from the earlier version?
Quincy van der Ree's profile image
Quincy van der Ree posted 01-13-2023 02:55
Hi @Carlos Lopez,

I'm not upgrading from an earlier version. It's the same version of Pentaho (even on the same server). It may have been that the old Pentaho jobs were build in an older version, but now everything is running on 8.3.

But now we're trying to rebuild the same tables with custom names, like we did earlier, and it's no longer working.
Carl Messner's profile image
Carl Messner posted 01-13-2023 05:02
Greetings,
First all, i don't understand the reason why you are trying to rename columns of tables that are fully managed internally by Pentaho.
Second, in my opinion, you should avoid of touching anything of that tables even add primary or foreign keys to the log tables.
We have jobs runing on both Pentaho 6.2 and Pentaho 9.3 versions and they are using the same tables for logs without any issue.

Best regards
Quincy van der Ree's profile image
Quincy van der Ree posted 01-13-2023 05:50
Hi @Carl Messner,

Personally, I wouldn't have touched the standard logging either, but someone has changed this in an older environment that is still in use. Because those log tables are used for different types of reporting, changing them is not an option.

It's the reason I'm looking into the option of changing the log table. It would be preferable if the log tables are equal to each other regarding their columns and lay-out. Apparently it was possible to change these tables, it's still is actually, because you are able to change names and set check marks for the columns you want. ​
Carl Messner's profile image
Carl Messner posted 01-13-2023 07:01
Hi Quincy,
Perhaps a possible solution could be the following:
Stop the scheduled execution of all jobs on all your servers.
Delete the log tables of the database where they are hosted (be sure to make a backup before).
Clear the database connection cache.
Choose a job that has a correct definition of the tables and their columns, create the tables again from Pentaho to start clean.
Make sure the tables were created as you specified.
Check the jobs one by one to make sure that the tables and log names are consistent across all of them.
Reactivate the scheduler.
I know it's not nice to delete existing log data, but if there are inconsistencies (and possibly there are) they will no longer be reliable.

Best regards

Related Content