question Re: alter a table NiFi processor error in Support Questions

alter a table NiFi processor error

AbhishekSingh — Mon, 18 Jul 2022 07:52:56 GMT

I have configured the NiFi flow file where I am reading mysql binary-log file. for me insert, update and delete flow is working fine but when I alter a table then I am getting error.

sourceDBName: nrpsubscriptiondb2306 and tableName: product_types

destinationDBName: nrpreportdb and tableName: product_types

Alter table nifi error:

Record had no (or null) value for Field Containing SQL: query, FlowFile StandardFlowFileRecord[uuid=8ca029c9-70aa-4e53-aec3-d37c15c8cd07,claim=StandardContentClaim [resourceClaim=StandardResourceClaim

Re: alter a table NiFi processor error

rafy — Mon, 18 Jul 2022 17:21:01 GMT

Have you checked this link:

https://community.cloudera.com/t5/Support-Questions/Adding-columns-to-sql-in-nifi/m-p/224598

Re: alter a table NiFi processor error

AbhishekSingh — Tue, 19 Jul 2022 17:33:01 GMT

@rafy

Above link is saying that to use one more processor PutSQL before PutDatabaseRecord to execute a statement. We already have PutDatabaseRecord processor to execute statement on db then why we should add putSQL processor?

Also other than DDL if any other action(insert, update, delete) will be perform in source db, It will fail because putSql processor will expect sql statement to execute first and based on success next processor will be call. In this case provided link suggestion will not work.

Re: alter a table NiFi processor error

AbhishekSingh — Tue, 19 Jul 2022 17:48:40 GMT

Hi ,

I have modified Sql statement using replace text processor but still getting below error when I alter a table and add new column.

PutDatabaseRecord processor input json:

{
"type" : "ddl",
"timestamp" : 1658252209000,
"binlog_gtidset" : "e75d07af-eb37-11ec-9d1d-a86daa745b08:1-147",
"database" : "nrpsubscriptiondb2306",
"table_name" : null,
"table_id" : null,
"query" : "ALTER TABLE `nrpreportdb`.`user_org_status` ADD COLUMN `is_test` VARCHAR(255) NULL AFTER `code`"
}

Error log:

Record had no (or null) value for Field Containing SQL: query, FlowFile StandardFlowFileRecord[uuid=bc7d51b3-e71f-4eb2-834b-fecbcff572be,claim=StandardContentClaim [resourceClaim=StandardResourceClaim[id=1658226664320-3, container=default, section=3], offset=37727, length=277],offset=0,name=bc7d51b3-e71f-4eb2-834b-fecbcff572be,size=277]