Skip to main content
AbhishekSingh
Gold ⭐️
Jul 18, 2022 7:52 AM UTC
Solved

alter a table NiFi processor error

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_typesScreenshot from 2022-07-17 23-38-17.pngScreenshot from 2022-07-17 23-37-20.pngScreenshot from 2022-07-17 23-37-01.pngScreenshot from 2022-07-17 23-36-31.pngScreenshot from 2022-07-17 23-24-00.pngScreenshot from 2022-07-17 23-12-46.png

 

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

AbhishekSingh
Gold ⭐️
Jul 19, 2022 5:48 PM UTC

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]

3 replies

rafy
Gold ⭐️
Jul 18, 2022 5:21 PM UTC
AbhishekSingh
Gold ⭐️
Jul 19, 2022 5:32 PM UTC

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

Translate
AbhishekSingh
AbhishekSinghAuthorAnswer
Gold ⭐️
Jul 19, 2022 5:48 PM UTC

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]

Translate