alter a table NiFi processor error

[#1]

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

[#2]

Have you checked this link:

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

[#3]

@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. 

[#4 · Accepted solution]

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]