[NIFI-8320] Fetching wrong schema from PostgreSQL DB

• Type: Bug
• Status: Resolved
• Priority: Critical
• Resolution: Fixed
• Affects Version/s: 1.13.0, 1.13.2
• Fix Version/s: 1.14.0
• Component/s: Core Framework
• Environment: Nifi Docker, PostgreSQL 11.10
• Assignee: Matt Burgess
• Reporter: Augusto Dias
• Created: 14/Mar/21 11:51
• Updated: 07/Jul/21 17:05
• Resolved: 30/Apr/21 15:42

Description

I believe Nifi is incorrectly assuming the table schema.
Nifi is using the column type from the column just before (n-1) to validate the value with the column type.
In this case, the column is varchar and the column just before is smallint.
Nifi accuses error because it can't cast "1x/dia" to short.
By changing the type of column n-1 the problem solves.

Do you think these two issues are related? Thanks.

------- Nifi version -----------
Nifi 1.13.0
02/10/2021 19:15:44 BRT
Tagged nifi-1.13.0-RC4
From 3bc6a12 on branch UNKNOWN

------ JDBC PostgreSQL Version ----
42.2.19

------------------- Nifi Interface Bulletin ----------------
18:54:55 BRT ERROR
PutDatabaseRecord[id=2d23c61d-bfe8-351a-d7e5-84332341d827] Failed to put Records to database for StandardFlowFileRecord[uuid=2cbcd514-b0a5-4de7-b19d-79fa4891d39b,claim=StandardContentClaim [resourceClaim=StandardResourceClaim[id=1615382414416-4, container=default, section=4], offset=18303, length=980],offset=0,name=2cbcd514-b0a5-4de7-b19d-79fa4891d39b,size=718]. Routing to failure.: For input string: "1x/dia"

----------------- Nifi logs/nifi-app.log ------------------------

2021-03-10 18:54:55,175 ERROR [Timer-Driven Process Thread-7] o.a.n.p.standard.PutDatabaseRecord PutDatabaseRecord[id=2d23c61d-bfe8-351a-d7e5-84332341d827] Failed to put Records to database for StandardFlowFileRecord[uuid=2cbcd514-b0a5-4de7-b19d-79fa4891d39b,claim=StandardContentClaim [resourceClaim=StandardResourceClaim[id=1615382414416-4, container=default, section=4], offset=18303, length=980],offset=0,name=2cbcd514-b0a5-4de7-b19d-79fa4891d39b,size=718]. Routing to failure.: java.lang.NumberFormatException: For input string: "1x/dia"
java.lang.NumberFormatException: For input string: "1x/dia"
at java.lang.NumberFormatException.forInputString(NumberFormatException.java:65)
at java.lang.Integer.parseInt(Integer.java:580)
at java.lang.Short.parseShort(Short.java:118)
at java.lang.Short.parseShort(Short.java:144)
at org.apache.nifi.serialization.record.util.DataTypeUtils.toShort(DataTypeUtils.java:1614)
at org.apache.nifi.serialization.record.util.DataTypeUtils.convertType(DataTypeUtils.java:203)
at org.apache.nifi.serialization.record.util.DataTypeUtils.convertType(DataTypeUtils.java:152)
at org.apache.nifi.serialization.record.util.DataTypeUtils.convertType(DataTypeUtils.java:148)
at org.apache.nifi.processors.standard.PutDatabaseRecord.executeDML(PutDatabaseRecord.java:707)
at org.apache.nifi.processors.standard.PutDatabaseRecord.putToDatabase(PutDatabaseRecord.java:838)
at org.apache.nifi.processors.standard.PutDatabaseRecord.onTrigger(PutDatabaseRecord.java:487)
at org.apache.nifi.processor.AbstractProcessor.onTrigger(AbstractProcessor.java:27)
at org.apache.nifi.controller.StandardProcessorNode.onTrigger(StandardProcessorNode.java:1173)
at org.apache.nifi.controller.tasks.ConnectableTask.invoke(ConnectableTask.java:214)
at org.apache.nifi.controller.scheduling.TimerDrivenSchedulingAgent$1.run(TimerDrivenSchedulingAgent.java:117)
at org.apache.nifi.engine.FlowEngine$2.run(FlowEngine.java:110)
at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
at java.util.concurrent.FutureTask.runAndReset(FutureTask.java:308)
at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(ScheduledThreadPoolExecutor.java:180)
at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:294)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at java.lang.Thread.run(Thread.java:748)

----------- Create Table

CREATE TABLE frequencia (
fkhospital int2 NOT NULL DEFAULT '1'::smallint,
fkfrequencia varchar(250) NOT NULL,
nome varchar(250) NOT NULL,
frequenciadia float4 NULL,
frequenciahora float4 NULL,
CONSTRAINT frequencia_pk PRIMARY KEY (fkhospital, fkfrequencia)
);

 

----------- Sample Input

[

{ "nome" : "2x/dia", "fkhospital" : 1, "fkfrequencia" : "2x/dia" }

,

{ "nome" : "1x/dia", "fkhospital" : 1, "fkfrequencia" : "1x/dia" }

,

{ "nome" : "Terça", "fkhospital" : 1, "fkfrequencia" : "Terça" }

,

{ "nome" : "ACM", "fkhospital" : 1, "fkfrequencia" : "ACM" }

]

Issue Links

• fixes: NIFI-8750 PutDatabaseRecord Insert Varchar Type Data Issue
• is duplicated by: NIFI-8446 PutDabaseRecord is not mapping properly the columns
• is duplicated by: NIFI-8493 PutDatabaseRecord incorrect type resolution for auto increment columns
• links to: GitHub Pull Request #5024