Seems like a nice improvement. I would lean towards introducing the RetryableLookupFailureException. At the same time, I think we should add catch blocks for more specific subclasses of SQLException, such as SQLTransientException, which would also trigger a RetryableLookupFailureException instead of a LookupFailureException.
> On Oct 27, 2021, at 08:55, Bryan Bende <bbe...@gmail.com> wrote: > > I'd consider changing DatabaseRecordLookupService.. the way it is > currently implemented there is no way to tell the difference between a > retryable exception like IOException vs a non-retryable exception like > SQLException because the columns are wrong and will never work. We > could introduce a new RetryableLookupFailureException and have both > services catch IOException and throw the retryable exception, or both > services can let IOException be thrown and let the callers decide what > to do. > > On Wed, Oct 27, 2021 at 5:07 AM Edward Armes <edward.ar...@gmail.com> wrote: >> >> Hi Bilal, >> >> Thanks for confirming, it looks like my hunch was correct, and there is a >> discrepancy in the lookup service code itself. >> >> In DatabaseRecordLookupService we catch an IOException and return a lookup >> failure like is done for an SQLExeception. This isn't done in the >> SimpleDatabaseLookupService. I think in this case its worth adding the >> IOException catch to SimpleDatabaseLookupService to bringing it in line with >> DatabaseRecordLookupService. >> >> Edward >> >> On Wed, 27 Oct 2021, 09:11 Bilal Bektas, <bilal.bek...@obase.com> wrote: >>> >>> Hi Edward, >>> >>> >>> >>> Thank you for helping. >>> >>> >>> >>> You can find the information which you want: >>> >>> >>> >>> * LookupAttribute processor uses SimpleDatabaseLookupService, bundle of >>> which is “org.apache.nifi - nifi-lookup-services-nar” >>> >>> * SimpleDatabaseLookupService uses DBCPConnectionPool, bundle of which is >>> “org.apache.nifi - nifi-dbcp-service-nar”. There is no custom build service >>> or processor on NiFi. All are the default bundle. >>> >>> >>> * Teradata JDBC version: 16.20.00.13 >>> >>> * Oracle JDBC version: 12.2.0.1.0 >>> >>> >>> >>> >>> >>> In addition, I have done similar test on LookupAttribute (Oracle) >>> processor. The same situation happened; flow files were penalized and the >>> queue on upstream connection of LookupAttribute (Oracle) increased.. >>> >>> >>> >>> Thank you in advance, >>> >>> >>> >>> --Bilal >>> >>> >>> >>> >>> >>> From: Edward Armes <edward.ar...@gmail.com> >>> Sent: 26 Ekim 2021 Salı 23:40 >>> To: users@nifi.apache.org >>> Subject: Re: Penalty feature of Processor (Disable) >>> >>> >>> >>> Hi Bilal, >>> >>> >>> >>> Can you just confirm that your connection to Teradata is done using the >>> DatabaseRecordLookupService and is using one of the 2 DBCPConnectionPools >>> which is using the Teradata JDBC driver or are you using custom built >>> service? >>> >>> >>> >>> The reason for asking is that I want to do a quick check to make sure by >>> were not masking an issue in one of the underlying services that hasn't >>> been caught correctly for some reason >>> >>> >>> >>> Edward >>> >>> >>> >>> On Tue, 26 Oct 2021, 10:29 Bilal Bektas, <bilal.bek...@obase.com> wrote: >>> >>> Hi Community, >>> >>> >>> >>> Thank you for detailed solutions and analysis. >>> >>> >>> >>> @Dev Team, do you think to add a new feature (Rollback On Failure) for >>> LookupAttribute processor like PutHiveQL processor? >>> >>> >>> >>> Thank you for helping, >>> >>> >>> >>> --Bilal >>> >>> >>> >>> >>> >>> From: Edward Armes <edward.ar...@gmail.com> >>> Sent: 26 Ekim 2021 Salı 01:32 >>> To: users@nifi.apache.org >>> Subject: Re: Penalty feature of Processor (Disable) >>> >>> >>> >>> Having a quick look at the lookupAttribute code it looks like it takes a >>> Optional<> from the call to the configured service. So I wonder if its >>> worth adding the logic to service instead so that on erroring it can either >>> return a missing value or throw an exception that would trigger the >>> roleback. That would achieve the same goal without affecting other users of >>> the LookupAttribute processor, where such logic isn't needed or wanted >>> (e.g. SimpleLookupService). >>> >>> >>> >>> Edward >>> >>> >>> >>> On Mon, 25 Oct 2021, 21:54 Matt Burgess, <mattyb...@apache.org> wrote: >>> >>> The approach in #1 is already present in a few Put processors like >>> PutHive3QL, the property is named "Rollback on Failure" and takes a >>> boolean value. The docs explain that if set to false, the flowfile is >>> routed to failure, and if true will throw an exception and rollback >>> the session. Check RollbackOnFailure.java for more details. >>> >>> Regards, >>> Matt >>> >>> On Mon, Oct 25, 2021 at 4:46 PM Bryan Bende <bbe...@gmail.com> wrote: >>>> >>>> The try/catch for IOException in LookupAttribute is after already >>>> calling session.get(), so it is separate from loading a flow file. >>>> >>>> The SimpleDatabaseLookupService catches SQLException and throws >>>> LookupFailureException which is the indicator to route to failure, and >>>> it lets IOException be thrown so that callers can decide what to do. >>>> >>>> Typically IOException would be considered retryable so the current >>>> behavior seems reasonable, but in this case the user wants to decide >>>> not to retry which currently can't be done. >>>> >>>> Seems like two options... >>>> >>>> 1) Introduce a new property like "Communication Error Strategy" with >>>> choices of "Rollback" (current) or "Route to Failure" (needed for this >>>> case). >>>> >>>> 2) Introduce a new relationship like "Retry" and instead of throwing >>>> ProcessException when catching IOException, instead route to Retry. It >>>> is then up to the user to decide if they want to connect Retry back to >>>> self to get the current behavior, auto-terminate it, or connect it to >>>> the next processor like this case wants to do. >>>> >>>> >>>> On Mon, Oct 25, 2021 at 4:01 PM Edward Armes <edward.ar...@gmail.com> >>>> wrote: >>>>> >>>>> Hmm, it sounds like to me there might be 2 bugs here. >>>>> >>>>> One in the lookup attribute processor not isolating the loading of >>>>> attributes from a FlowFile which may legitimately cause an IOException >>>>> that would result in the FlowFile needing to be retired. The other in the >>>>> TeradataDB lookup service not returning suitable errors that indicate if >>>>> the issue is transient and a retry is needed or if it's a failure and >>>>> should be routed to the failure queue. >>>>> >>>>> Edward >>>>> >>>>> On Mon, 25 Oct 2021, 16:50 Bryan Bende, <bbe...@gmail.com> wrote: >>>>>> >>>>>> I'm not 100% sure on this, but I think the issue is that when >>>>>> LookupAttribute calls the LookupService, it catches IOException and >>>>>> throws a ProcessException, which rolls back the current session and puts >>>>>> the incoming flow files back in the preceding queue. The idea is that it >>>>>> would then retry the flow files until the comms issue is resolved, but >>>>>> in your case you don't want that. >>>>>> >>>>>> I think there would need to be an enhancement to LookupAttribute that >>>>>> adds a property to control the behavior on IOException so that the user >>>>>> can decide between rollback vs route to failure. >>>>>> >>>>>> On Mon, Oct 25, 2021 at 11:29 AM Etienne Jouvin >>>>>> <lapinoujou...@gmail.com> wrote: >>>>>>> >>>>>>> Hello all. >>>>>>> >>>>>>> You can decrease the penalty value on the processor. >>>>>>> Set to 0 for example. >>>>>>> >>>>>>> >>>>>>> >>>>>>> Le lun. 25 oct. 2021 à 16:22, Bilal Bektas <bilal.bek...@obase.com> a >>>>>>> écrit : >>>>>>>> >>>>>>>> Hi Community, >>>>>>>> >>>>>>>> >>>>>>>> >>>>>>>> We use LookupAttribute processor in order to get lookup value from >>>>>>>> Teradata or Oracle DB. Processors work as follows: >>>>>>>> >>>>>>>> >>>>>>>> >>>>>>>> LookupAttribute (Teradata) ---(failure & unmatched) ---> >>>>>>>> LookupAttribute (Oracle) >>>>>>>> >>>>>>>> >>>>>>>> >>>>>>>> This flows works well and LookupAttribute (Teradata) penalizes to flow >>>>>>>> files when Teradata DB is down. Therefore, the queue on upstream >>>>>>>> connection of LookupAttribute (Teradata) increases. But, we don't want >>>>>>>> to that LookupAttribute (Teradata) penalizes to flow files. We want to >>>>>>>> that LookupAttribute (Teradata) processor forwards flow files to >>>>>>>> failure downstream connection when all failure situation on >>>>>>>> LookupAttribute (Teradata). Thus, LookupAttribute (Oracle) can process >>>>>>>> flow files which cannot process on LookupAttribute (Teradata). >>>>>>>> >>>>>>>> >>>>>>>> >>>>>>>> Is it possible to disable penalty feature of processor or is there any >>>>>>>> solution which you can suggest for this situation. >>>>>>>> >>>>>>>> >>>>>>>> >>>>>>>> Thank you in advance, >>>>>>>> >>>>>>>> >>>>>>>> >>>>>>>> --Bilal >>>>>>>> >>>>>>>> obase >>>>>>>> TEL: +90216 527 30 00 >>>>>>>> FAX: +90216 527 31 11 >>>>>>>> >>>>>>>> Bu elektronik posta ve onunla iletilen bütün dosyalar sadece >>>>>>>> göndericisi tarafindan almasi amaclanan yetkili gercek ya da tüzel >>>>>>>> kisinin kullanimi icindir. Eger söz konusu yetkili alici degilseniz bu >>>>>>>> elektronik postanin icerigini aciklamaniz, kopyalamaniz, >>>>>>>> yönlendirmeniz ve kullanmaniz kesinlikle yasaktir ve bu elektronik >>>>>>>> postayi derhal silmeniz gerekmektedir. OBASE bu mesajin icerdigi >>>>>>>> bilgilerin doğruluğu veya eksiksiz oldugu konusunda herhangi bir >>>>>>>> garanti vermemektedir. Bu nedenle bu bilgilerin ne sekilde olursa >>>>>>>> olsun iceriginden, iletilmesinden, alinmasindan ve saklanmasindan >>>>>>>> sorumlu degildir. Bu mesajdaki görüsler yalnizca gönderen kisiye >>>>>>>> aittir ve OBASE görüslerini yansitmayabilir. >>>>>>>> >>>>>>>> Bu e-posta bilinen bütün bilgisayar virüslerine karsi taranmistir. >>>>>>>> >>>>>>>> This e-mail and any files transmitted with it are confidential and >>>>>>>> intended solely for the use of the individual or entity to whom they >>>>>>>> are addressed. If you are not the intended recipient you are hereby >>>>>>>> notified that any dissemination, forwarding, copying or use of any of >>>>>>>> the information is strictly prohibited, and the e-mail should >>>>>>>> immediately be deleted. OBASE makes no warranty as to the accuracy or >>>>>>>> completeness of any information contained in this message and hereby >>>>>>>> excludes any liability of any kind for the information contained >>>>>>>> therein or for the information transmission, recepxion, storage or use >>>>>>>> of such in any way whatsoever. The opinions expressed in this message >>>>>>>> belong to sender alone and may not necessarily reflect the opinions of >>>>>>>> OBASE. >>>>>>>> >>>>>>>> This e-mail has been scanned for all known computer viruses.
