When the binlog (binary logging) is enabled in the MySQL server, SRM does not start

Products

• CA Spectrum
• DX NetOps

Issue/Introduction

I am trying to use the Debezium to pull data from the MySQL server, but when the binlog (binary logging) is enabled in the MySQL server, SRM does not start.

Debezium is a distributed platform that converts information from your existing databases into event streams, enabling applications to detect, and immediately respond to row-level changes in the databases.

https://debezium.io/documentation/reference/stable/connectors/mysql.html#:~:text=user%E2%80%99s%20MySQL%20password.-,Enabling%20the%20binlog,-You%20must%20enable

 

The issue occurs by adding the following entries in the my-spectrum.cnf file (the highlight section causes the issue):

#Enabling the binlog
server-id         = 2
log_bin           = mysql-bin
binlog_format     = ROW
binlog_row_image  = FULL
expire_logs_days  = 10

#Configuring session timeouts
interactive_timeout=60
wait_timeout=60

#Enabling query log events
binlog_rows_query_log_events=ON

 

In the stdout.log file:

Sep 09, 2022 07:25:53 PM (SRM/Startup/Container) (com.aprisma.spectrum.app.web.db.schemachange.SchemaChangeUpdater) - (ERROR) - Error occurred while applying schema change  Name: 1020 (Product version: 9.2.0) - Update and maintain all views in the reporting_api schema.
com.aprisma.spectrum.app.web.db.schemachange.FatalSchemaChangeException: com.aprisma.spectrum.app.web.db.schemachange.SchemaChangeException: Error occurred while running schema change script.
  Script: C:\win32app\Spectrum\Install-Tools\rpmgr\sql\srmdbapi\ddl\create_time_functions.sql
            at com.aprisma.spectrum.app.web.db.schemachange.BaseSchemaChange.applySchemaChange(BaseSchemaChange.java:194) ~[websrv.jar:?]
            at com.aprisma.spectrum.app.web.db.schemachange.SchemaChangeUpdater.applySchemaChange(SchemaChangeUpdater.java:96) ~[websrv.jar:?]

Caused by: com.aprisma.spectrum.app.web.db.schemachange.SchemaChangeException: Error occurred while running schema change script. 

  Stderr:
mysql: [Warning] Using a password on the command line interface can be insecure.

ERROR 1418 (HY000) at line 27 in file: 'C:\win32app\Spectrum\Install-Tools\rpmgr\sql\srmdbapi\ddl\create_time_functions.sql': This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

Sep 09, 2022 07:25:53 PM (SRM/Startup/Container) (org.springframework.web.context.support.XmlWebApplicationContext) - (WARN) - Exception encountered during context initialization - cancelling refresh attempt: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'SRM/Quartz-TimerFactory' defined in ServletContext resource [/WEB-INF/repmgr/config/container/scheduling.xml]: Cannot resolve reference to bean 'scheduledStaleBucketRemover' while setting bean property 'triggers' with key [1]; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'scheduledStaleBucketRemover' defined in ServletContext resource [/WEB-INF/repmgr/config/container/scheduling.xml]: Cannot resolve reference to bean 'staleBucketRemoverTask' while setting bean property 'jobDetail'; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'staleBucketRemoverTask' defined in ServletContext resource [/WEB-INF/repmgr/config/container/scheduling.xml]: Cannot resolve reference to bean 'staleBucketRemover' while setting bean property 'targetObject'; nested exception is org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'staleBucketRemover': Unsatisfied dependency expressed through field 'bucketActivityDAO'; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'jdbcBucketActivityLogDAO' defined in ServletContext resource [/WEB-INF/repmgr/config/container/data-access-beans.xml]: Cannot resolve reference to bean 'jdbcTemplate' while setting bean property 'jdbcTemplate'; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'reportManagerFoundation': Invocation of init method failed; nested exception is com.aprisma.spectrum.app.repmgr.exceptions.SRMException: Error occurred while applying schema changes to reporting database

Environment

Release : 22.2

Component : Spectrum Reporting

Cause

https://dev.mysql.com/doc/refman/8.0/en/stored-programs-logging.html

• When you attempt to execute a stored function, if binlog_format=STATEMENT is set, the DETERMINISTIC keyword must be specified in the function definition. If this is not the case, an error is generated and the function does not run, unless log_bin_trust_function_creators=1 is specified to override this check (see below). For recursive function calls, the DETERMINISTIC keyword is required on the outermost call only. If row-based or mixed binary logging is in use, the statement is accepted and replicated even if the function was defined without the DETERMINISTIC keyword.

Resolution

Please add this line in green: log_bin_trust_function_creators = 1

#Enabling the binlog
server-id         = 2
log_bin           = mysql-bin
binlog_format     = ROW
binlog_row_image  = FULL
expire_logs_days  = 10
log_bin_trust_function_creators = 1

#Configuring session timeouts
interactive_timeout=60
wait_timeout=60

#Enabling query log events
binlog_rows_query_log_events=ON