We are integrating Kafka plugin detected two incorrect behaviors:
The first (and most important) issue is a problem arising from the way we insert into the "tm_job_transaction" table.
The "scheduled_date" field doesn't allow nulls, but the INSERT is returning a null in that field. After reviewing the database logs (RDS for MySQL), this is the SQL statement the plugin is generating.
As you can see, the description table is this. scheduled_date is not null
But the plugin generate this SQL sentence
2025-10-09T08:12:39.717781Z1625671 QueryINSERT INTO tm_job_transaction (job_type_id, context_json, scheduled_date, status_id) VALUES (132, '{"topic":"sbna.payment_execution.ach_payment_exception_created.json.eb.01evts0039v1bed","partition":0,"offset":603,"key":null,"value":"{\\"PARTACCOUNTNUM\\":\\"031700013000500346\\",\\"detail-type\\":\\"gravity.achexceptioncreate\\",\\"detail\\":{\\"headers\\":{\\"timestamp\\":\\"2025-10-09T08:02:39.794+0000\\",\\"processType\\":\\"AchExceptioncreated\\",\\"country\\":\\"US\\",\\"channel\\":\\"Digital channels\\",\\"Schema Version\\":1},\\"payload\\":{\\"PAYMENTREFID\\":\\"03171633765BBBCWBD-2025-10-09 00:00:00-1\\",\\"PARTACCOUNTNUM\\":\\"031700013000500346\\",\\"NUMORD\\":1,\\"FECRECOP\\":\\"2025-10-09 00:00:00\\",\\"CODACLA\\":\\"QU8\\",\\"ESTAOP\\":\\"WAIT\\",\\"NUMPERA\\":\\"42641009230 \\",\\"ERRORCODE\\":\\"SV0109\\",\\"TRANSTIP\\":\\"61077\\",\\"CODBAN\\":\\"6017\\",\\"CODSRC\\":\\"PEP\\",\\"NUMSECBL\\":\\"00000000000\\",\\"REFORIG\\":\\"00000000000\\",\\"FECLATF\\":\\"2025-10-09\\",\\"CHKACCST\\":1,\\"ESTADCON\\":0,\\"TIMSTMP\\":\\"2025-10-09 10:02:35.481429000\\",\\"ZCENTROP\\":\\" \\",\\"SUCTERM\\":\\" \\",\\"USERALT\\":\\"JIBIMCO \\",\\"CDRETURN\\":\\"R24\\",\\"CODREC1\\":\\"006\\",\\"INDTCONC\\":\\"02\\",\\"ENTITY\\":\\"SBNA \\",\\"TYPEACCO\\":\\"OTHER TYPE \\",\\"STATACCO\\":\\"OTHER STAT\\",\\"BLOCKCODE\\":\\" \\",\\"BEGBLOCK\\":\\"0001-01-01 00:00:00\\",\\"ENDBLOCK\\":\\"0001-01-01 00:00:00\\",\\"HOLDAMOU\\":0.0,\\"AVAILAMO\\":0.0,\\"LEDGEAMO\\":0.0,\\"CURREAMO\\":0.0}}}"}', null, 1)
BBDD engine version 8.0.mysql_aurora.3.10.0
The second issue is the format messange. As you can see in the JSON message is not a typical JSON.
Discussion posts and replies are publicly visible
1. That NULL insert in schedule_date should work due to the default CURRENT_TIMESTAMP. In case you faces any error I would suggest rise this to your DBA team to check if this is cause by the strict mode in your database configuration. They can provide you the help you need
2. "value" property is being considered as string not as JSON itself. Please verify that this value is correctly generated from the source. If so, I would suggest apply a workaround as create a rule to remove following the commas in the "value" string