Handling UTF8/UTF8MB4 charset support when configuring MYSQL as the data source.

Description

Due to product level limitations we support only latin1 as the compatible charset for MYSQL.
But latin1 lacks support for Chinese characters. But the customers who requires to support chinese or non ASCII characters are forced to continue with other charsets like UTF8/UTF8MB4 and hence we need to provide instructions at the DOC level for such customers on how to alter certain tables/columns in the existing schema so that they can support other character sets like UTF8/UTF8MB4.

Suggested Improvement

A UUID column will only contain 64 bytes and hence we need to provide alter commands in the doc to reduce column length of the UUID columns of effected tables to 64 and the length of certain other columns also need to be reduced at doc [1] in the note below.

Below are the effected tables and the columns that need to be reduced in length and need to be provide Mysql sql script to do so.

The user either has to run the API Manager db scripts with charset set as latin 1 and then run the below alter commands to alter the column length and then change the charset to UTF8/UTF8MB4 or adjust these column lengths directly in the product database scripts and create the database with charset UTF8/UTF8MB4.

CREATE TABLE IF NOT EXISTS AM_API_ENDPOINTS (
API_UUID VARCHAR(256) NOT NULL,
ENDPOINT_UUID VARCHAR(256) NOT NULL,
REVISION_UUID VARCHAR(255) NOT NULL DEFAULT 'Current API',
ENDPOINT_NAME VARCHAR(255),
KEY_TYPE VARCHAR(100),
ENDPOINT_CONFIG LONGBLOB NOT NULL,
ORGANIZATION VARCHAR(100),
FOREIGN KEY (API_UUID) REFERENCES AM_API(API_UUID) ON DELETE CASCADE,
PRIMARY KEY (API_UUID, ENDPOINT_UUID, ENDPOINT_NAME, REVISION_UUID, ORGANIZATION)
)ENGINE INNODB;

UUID Columns : API_UUID, ENDPOINT_UUID, REVISION_UUID
SIZE :64

CREATE TABLE IF NOT EXISTS IDN_SCIM_GROUP (
ID INTEGER AUTO_INCREMENT,
TENANT_ID INTEGER NOT NULL,
ROLE_NAME VARCHAR(255) NOT NULL,
ATTR_NAME VARCHAR(1024) NOT NULL,
ATTR_VALUE VARCHAR(1024),
UNIQUE(TENANT_ID, ROLE_NAME, ATTR_NAME),
PRIMARY KEY (ID)
)ENGINE INNODB;

Column : ATTR_NAME
SIZE 255

CREATE TABLE IF NOT EXISTS IDN_OAUTH2_TOKEN_BINDING (
TOKEN_ID VARCHAR (255),
TOKEN_BINDING_TYPE VARCHAR (32),
TOKEN_BINDING_REF VARCHAR (32),
TOKEN_BINDING_VALUE VARCHAR (1024),
TENANT_ID INTEGER DEFAULT -1,
UNIQUE (TOKEN_ID,TOKEN_BINDING_TYPE,TOKEN_BINDING_VALUE),
FOREIGN KEY (TOKEN_ID) REFERENCES IDN_OAUTH2_ACCESS_TOKEN(TOKEN_ID) ON DELETE CASCADE
)ENGINE INNODB;

Column : TOKEN_BINDING_VALUE
SIZE : 255

CREATE TABLE IF NOT EXISTS IDN_INVALID_TOKENS (
UUID VARCHAR(255) NOT NULL,
TOKEN_IDENTIFIER VARCHAR(2048) NOT NULL,
CONSUMER_KEY VARCHAR(255) NOT NULL,
TIME_CREATED TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
EXPIRY_TIMESTAMP TIMESTAMP NOT NULL,
PRIMARY KEY (UUID)
)ENGINE=InnoDB;

Column: TOKEN_IDENTIFIER
Size : 512

CREATE INDEX IDX_IDN_SCIM_GROUP_TI_RN_AN ON IDN_SCIM_GROUP (TENANT_ID, ROLE_NAME, ATTR_NAME(500));

Column name : ATTR_NAME
size : 255

Version(s)

4.5.0

[1] - https://apim.docs.wso2.com/en/4.1.0/install-and-setup/setup/setting-up-databases/changing-default-databases/changing-to-mysql/

Start with the linked “Changing to MySQL” documentation page and review its current database setup instructions. Verify the listed table and column changes against the 4.5.0 schema guidance, then document the required MySQL commands and charset workflow. Done means customers can follow the page to use UTF8 or UTF8MB4 while preserving the required schema constraints.