Hi Tony, This looks like it may be the behavior described in NIFI-8320. Matt has already said he is looking at the problem I described. I'm curious to see what he finds.
Mit freundlichen Grüßen / best regards Kay-Uwe Moosheimer > Am 26.03.2021 um 14:46 schrieb Anton Kovaľ <akte...@gmail.com>: > > Hi Uwe, > I was digging at something similar in PutDatabaseRecord 1.13.2 and I found > out that problem is the order of the fields in JSON. I wrote test > (TestPutDatabaseRecord) that shows: > > @Test > void testInsertJsonChangeOrderFields() throws InitializationException, > ProcessException, SQLException, IOException { > recreateTable(createPersons) > > final jsonReader = new JsonTreeReader() > runner.addControllerService("jsonParser", jsonReader) > runner.enableControllerService(jsonReader) > > final input = ''' > [ > { > "id": 1, > "code": 101, > "name": "rec1" > }, > { > "id": 2, > "name": "rec2", > "code": 102 > } > ] > ''' > > runner.setProperty(PutDatabaseRecord.RECORD_READER_FACTORY, > 'jsonParser') > runner.setProperty(PutDatabaseRecord.STATEMENT_TYPE, > PutDatabaseRecord.INSERT_TYPE) > runner.setProperty(PutDatabaseRecord.TABLE_NAME, 'PERSONS') > > runner.enqueue(input) > runner.run() > > runner.assertTransferCount(PutDatabaseRecord.REL_SUCCESS, 1) > final Connection conn = dbcp.getConnection() > final Statement stmt = conn.createStatement() > final ResultSet rs = stmt.executeQuery('SELECT * FROM PERSONS') > assertTrue(rs.next()) > assertEquals(1, rs.getInt(1)) > assertEquals('rec1', rs.getString(2)) > assertEquals(101, rs.getInt(3)) > assertTrue(rs.next()) > assertEquals(2, rs.getInt(1)) > assertEquals('rec2', rs.getString(2)) > assertEquals(102, rs.getInt(3)) > > stmt.close() > conn.close() > } > > Routing to failure.: java.lang.NumberFormatException: For input string: "rec1" > > Tony Koval > >> On 2021/03/25 17:59:07, "u...@moosheimer.com" <u...@moosheimer.com> wrote: >> Matt, >> >> Thank you for taking care of this problem! >> >> I have added the "active=true" in the JSON. >> Unfortunately exactly the same error. >> >> I can't add the primary key (unless I read the sequence myself which I >> want to avoid). >> The attribute "ts" must be set only by the database, because it must be >> the exact time. >> >> With this I have unfortunately no more possibility. Or have I overlooked >> something? >> >> Regards, >> Uwe >> >>> Am 25.03.21 um 17:07 schrieb Matt Burgess: >>> Uwe, >>> >>> I think it's related to [1], which tries to use the field's datatype >>> rather than the column's datatype if they're compatible but not >>> identical. I'm guessing if your record doesn't have the field, we're >>> mishandling the datatype and associated INSERT statement. I will >>> reproduce, write up the Jira, and fix it shortly. A workaround should >>> be to fill the records with all fields using the DB's default values. >>> >>> Regards, >>> Matt >>> >>> [1] https://issues.apache.org/jira/browse/NIFI-8223 >>> >>> On Thu, Mar 25, 2021 at 11:20 AM u...@moosheimer.com <u...@moosheimer.com> >>> wrote: >>>> Hi Dev-Team, >>>> >>>> We still have a problem with PutDatabaseRecord on NiFi 1.13.2, which >>>> used to run on NiFi 1.12.1. >>>> >>>> We have a JSON that has both more attributes than exist in the table and >>>> missing attributes that are defined in the table with default value. >>>> The table has a bool value that is set "default true" and is not passed >>>> in the JSON. >>>> >>>> Processor settings are: >>>> Record Reader - JsonTreeReader >>>> Database Type - PostgreSQL (tried also with Generic) >>>> Statement Type - INSERT >>>> Translate Field Names - false >>>> Quote Column Identifier - false >>>> Quate Table identifier - false >>>> Unmatched Field Behavior - Ignore Unmatched Fields >>>> Unmatched Column Behavior - Ignore Unmatched Columns >>>> >>>> As error message we get: >>>> >>>> 2021-03-25 15:00:24,509 ERROR [Timer-Driven Process Thread-7] >>>> o.a.n.p.standard.PutDatabaseRecord >>>> PutDatabaseRecord[id=a1ef9918-0177-1000-ffff-ffffba128239] Failed to put >>>> Records to database for >>>> StandardFlowFileRecord[uuid=47bacb24-718b-42e0-97a9-588ab628a4af,claim=StandardContentClaim >>>> [resourceClaim=StandardResourceClaim[id=1616614332963-2, >>>> container=default, section=2], offset=97327973, >>>> length=877],offset=0,name=3699054725979545,size=877]. Routing to >>>> failure.: org.postgresql.util.PSQLException: Cannot cast to boolean: >>>> "hKaT0ytUPfgwNcjhDDtKRin42743t" >>>> org.postgresql.util.PSQLException: Cannot cast to boolean: >>>> "hKaT0ytUPfgwNcjhDDtKRin42743t" >>>> at >>>> org.postgresql.jdbc.BooleanTypeUtil.cannotCoerceException(BooleanTypeUtil.java:99) >>>> at >>>> org.postgresql.jdbc.BooleanTypeUtil.fromString(BooleanTypeUtil.java:67) >>>> at >>>> org.postgresql.jdbc.BooleanTypeUtil.castToBoolean(BooleanTypeUtil.java:43) >>>> at >>>> org.postgresql.jdbc.PgPreparedStatement.setObject(PgPreparedStatement.java:655) >>>> at >>>> org.postgresql.jdbc.PgPreparedStatement.setObject(PgPreparedStatement.java:935) >>>> at >>>> org.apache.commons.dbcp2.DelegatingPreparedStatement.setObject(DelegatingPreparedStatement.java:529) >>>> at >>>> org.apache.commons.dbcp2.DelegatingPreparedStatement.setObject(DelegatingPreparedStatement.java:529) >>>> at jdk.internal.reflect.GeneratedMethodAccessor687.invoke(Unknown >>>> Source) >>>> at >>>> java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) >>>> at java.base/java.lang.reflect.Method.invoke(Method.java:566) >>>> at >>>> org.apache.nifi.controller.service.StandardControllerServiceInvocationHandler.invoke(StandardControllerServiceInvocationHandler.java:254) >>>> at >>>> org.apache.nifi.controller.service.StandardControllerServiceInvocationHandler.access$100(StandardControllerServiceInvocationHandler.java:38) >>>> at >>>> org.apache.nifi.controller.service.StandardControllerServiceInvocationHandler$ProxiedReturnObjectInvocationHandler.invoke(StandardControllerServiceInvocationHandler.java:240) >>>> at com.sun.proxy.$Proxy357.setObject(Unknown Source) >>>> at >>>> org.apache.nifi.processors.standard.PutDatabaseRecord.executeDML(PutDatabaseRecord.java:736) >>>> at >>>> org.apache.nifi.processors.standard.PutDatabaseRecord.putToDatabase(PutDatabaseRecord.java:841) >>>> 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.base/java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:515) >>>> at >>>> java.base/java.util.concurrent.FutureTask.runAndReset(FutureTask.java:305) >>>> at >>>> java.base/java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:305) >>>> at >>>> java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128) >>>> at >>>> java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628) >>>> at java.base/java.lang.Thread.run(Thread.java:834) >>>> >>>> >>>> Our test environment is: >>>> NiFi 1.13.2 Cluster with 3 nodes >>>> Postgres 13.2 >>>> openjdk version "11.0.10" >>>> Ubuntu 20.04.1 LTS >>>> >>>> Is this a known issue or an individual fate that makes us despair? >>>> >>>> >>