Broadcom API Portal 5.3 - User's change are not saved or end in error. Cannot update or create new applications, it throws 500 error, Portal-data log show SQL's error "this is incompatible with sql_mode=only_full_group_by:"

Products

• CA API Developer Portal

Issue/Introduction

Symptoms/behaviour:

• APIs and Keys are not deployed. 
• Applications fail to be published
• Failed to generate API Key Secret
• Not be able to update or create a new application. It throws a 500 Error

Examining the Portal data log (portal_portal-data),  there is an error message contains "nonaggregated column '<column name>' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by". 

Environment

API Portal 5.x

Cause

Database configured not according Portal's documented pre-requisites. 

Resolution

Follow the documentation under the section Database requirements

Portal DB requires the following SQL Mode:

• STRICT_TRANS_TABLES
• NO_ZERO_IN_DATE
• NO_ZERO_DATE
• ERROR_FOR_DIVISION_BY_ZERO
• NO_ENGINE_SUBSTITUTION

Additional Information

Document link that contains the sql mode required settings: 

https://techdocs.broadcom.com/us/en/ca-enterprise-software/layer7-api-management/api-developer-portal/5-3/install-configure-and-upgrade/install-portal-on-docker-swarm/upgrade-portal-database-to-mysql-8.html