PutDatabaseRecord processor error: "Record does not have a value for the PrimaryKey column 'HOST' "

[#1]

Hi,

I am getting error "Record does not have a value for the PrimaryKey column 'HOST' " when I update a data in source db table. This issue is happening when destination DB table's name: 'user' or 'users'. It is working fine when destination table's name is change to 'report_user'

(I mean when we change the table name 'user'or 'users' to something else which is not used as mysql internal  table)

 I am not sure why it is trying to insert or update on internal mysql table as I am giving the destination DBName and tableName.

Can anyone suggest how can we fix this issue without changing the destination table name?

Error Log:

PutDatabaseRecord[id=ff8ca68c-8252-3652-8f55-1044ad3f1bab] Failed to put Records to database for StandardFlowFileRecord[uuid=43e64992-a3d0-4a75-bb85-8d70c1f556d7,claim=StandardContentClaim [resourceClaim=StandardResourceClaim[id=1658747398462-4, container=default, section=4], offset=20549, length=1214],offset=0,name=43e64992-a3d0-4a75-bb85-8d70c1f556d7,size=1214]. Routing to failure.
org.apache.nifi.serialization.MalformedRecordException: Record does not have a value for the PrimaryKey column 'HOST'
at org.apache.nifi.processors.standard.PutDatabaseRecord.normalizeKeyColumnNamesAndCheckForValues(PutDatabaseRecord.java:1367)

PutDatabaseRecord processor flow file output:

[ {
"id" : 1,
"first_name" : "john",
"middle_name" : "s_test",
"last_name" : "asjndjnas",
"user_name" : "deleted_8AB-0034",
"is_terms_accepted" : "true",
"is_retail" : "true",
"tenant_id" : 1,
"email" : "sreejith.karonnan2@mailinator.com",
"alternate_email" : null,
"gender" : "M",
"dob" : "2008-01-01",
"sso_id" : null,
"phone_number" : null,
"status_id" : 2,
"time_zone_id" : 1,
"is_activated" : "true",
"activated_at" : "2021-01-07 16:29:25",
"is_deleted" : "false",
"import_id" : null,
"created_at" : "2021-01-07 16:29:25",
"updated_at" : "2021-11-16 18:38:42",
"created_by" : 123,
"updated_by" : 3,
"last_login_date" : null,
"agreement_acceptance_date" : "2021-03-22 20:44:23",
"user_ref_id" : "589RngS1",
"is_admin" : "false",
"is_distributor" : "true",
"is_self_register" : "false",
"ce_job_id" : null,
"region" : "US",
"recovery_status" : null,
"hlc_user_name" : null,
"allow_assignment" : false,
"has_ce" : false,
"product_launched_at" : null,
"is_perpetual" : false,
"learning_completed_at" : null,
"enrolled_at" : null,
"dispatch_confirmation" : false,
"subscription_end_at" : null,
"ecard_date" : null,
"offline_completion_date" : null,
"subscription_start_at" : null,
"product_completed_at" : null,
"online_completion_date" : null,
"is_legacy" : false,
"last_activity_at" : null,
"multi_package" : false,
"metadata" : null,
"self_registration_override" : false
} ]

Thanks,

[#2]

Seems like it's not using the schema name field when building the insert statement.   Are you specifying the database to use in your DBCP connection pool service?  

Although user/users are reserved words in mysql, so I would expect problems with that name.   Possibly creating the table wrapping the name in back ticks might work.

[#3 · Accepted solution]

@cnelson2 

Are you specifying the database to use in your DBCP connection pool service?  -> yes

I solve this issue. I have given Catalog Name : destinationDBName and Make empty schema Name value in putdatabase  record processor. Now I am able to perform insert , update and delete action source to destination 'user' table.

Thanks,