Hello, and thank you for this quick and extensive answer! You are absolutely right. The MySQL server is updated continuously whereas the SQL server is a batch oriented service updated once every 24 hours which confirms your explanation.
My mistake was to assume that the timestamp defined by the dynamic property initial.maxvalue.<max_value_column> would be set in the GenerateTableFetch processor and that the flowfile would be terminated after the state is set. It does make sense to generate a new flowfile based on the source table when the time stamp changed in the source table. If I forget to remove the dynamic property, the use case would break since the state would be incorrectly set the next time independently of the source table. This is one of the things I like most about Apache NiFi, you have thought of everything, but sometimes new users need some time to realize how robust the implementation actually is. Thank you! From: Isha Lamboo <[email protected]> Sent: den 14 januari 2022 17:30 To: [email protected] Subject: RE: Inconsistent behaviour in GenerateTableFetch 1.15.0 Hi Hendrik, The behaviour you describe sounds like the first GenerateTableFetch processor doesn’t find any records with a higher max_value than the initial value. In that case, it will not emit any flowfile (unless you have Output Empty FlowFile on Zero Results set to true). Since it hasn’t found any records, it also hasn’t increased the max_value in the state. In the second case, it does find records new than your initial value and generates a select statement up to the highest value it sees (which appears to be close to realtime from your description). The state should then be the new observed max value. Both are expected behavior for the respective cases. I don’t believe the processor *ever* sets the state to now(), only to the highest observed value. The most likely explanations are that: * The MySQL server table received very recent records that you didn’t expect * The SQL server’s time value is not getting converted the way you expect and that’s why it’s not finding any new records. Maybe a microseconds vs milliseconds thing? You could verify this by manually executing a select with the specified starting values and checking the results. Regards, Isha Van: Hendrik Ruijter <[email protected]<mailto:[email protected]>> Verzonden: vrijdag 14 januari 2022 16:43 Aan: [email protected]<mailto:[email protected]> Onderwerp: Inconsistent behaviour in GenerateTableFetch 1.15.0 Hello, in GenerateTableFetch 1.15.0, I use the dynamic property initial.maxvalue.<max_value_column> when I move timestamp state to a new cluster from old GenerateTableFetch 1.13.2 processors. First use case: SQL Server initial.maxvalue.tm 1326581088 State is set correctly to 1326581088 and the flowfile is terminated by GenerateTableFetch 1.15.0. Second use case: MySQL Server initial.maxvalue.time 2022-01-14 06:13:57.0 State is set correctly to Now() UTC (not to the dynamic property 2022-01-14 06:13:57.0) and the flowfile contains a correct select statement with a where clause to execute. Is the expected behavior to terminate the flowfile when the state is set as in the first use case? Or, is the expected behavior to set the state and execute the generated select statement as in second use case? Sorry, I am unable to find the answer in the documentation and my Java source code knowledge is very limited.
