MySQL with group replication scripts need to be corrected

[maneeshaheshan]

Description

Hi Team,

When using MySQL InnoDB Cluster with Group Replication, the documentation [1] specifies that all tables must have a primary key, which is a mandatory requirement according to the official MySQL documentation [2].

Upon reviewing the latest MySQL database scripts for APIM 4.5.0, we observed that the following tables do not have primary keys defined:

• IDN_OAUTH2_TOKEN_BINDING
• AM_SCOPE_BINDING
• AM_API_REVISION_METADATA
• AM_WEBHOOKS_UNSUBSCRIPTION

Additionally, the documentation does not include ALTER TABLE commands for the IDN_OAUTH2_TOKEN_BINDING and AM_SCOPE_BINDING tables.

We also noticed that the currently documented ALTER TABLE commands result in the error ERROR 1075 (42000) and therefore require corrections. Each statement needs to be reviewed and updated to ensure missing elements are properly added and that the commands execute successfully in a Group Replication–enabled MySQL environment.

[1] https://apim.docs.wso2.com/en/4.5.0/install-and-setup/setup/setting-up-databases/changing-default-databases/changing-to-mysql/#:~:text=If%20you%20are%20using%20MySQL%20with%20group%20replication%2C%20it%20is%20mandatory%20to%20have%20primary%20keys%20for%20all%20the%20tables
[2] https://dev.mysql.com/doc/refman/8.0/en/group-replication-requirements.html

Suggested Fix

• Add Alter Table commands for IDN_OAUTH2_TOKEN_BINDING and AM_SCOPE_BINDING
• Correct existing ALTER commands in the documentation.
• Revisit all the tables in other databases (ex- Shared_DB) and correct if any.

Version(s)

4.5.0

[wso2-engineering-bot]

@wso2-engineering-bot create a PR for this issue following repo conventions