Current behaviour
Currently 3scale creates some tables without primary keys, for example features_plan table.

Expected behaviour
Some customers have the requirement to use Group Replication in mysql which is not compatible with tables that doesn't have primary keys. The request is to add primary keys to all tables.

RELEASE NOTES:

Due to a bug in the API, the same feature could be added multiple times to the same plan. This only happens when the API endpoint is called multiple times with the same feature and plan as parameters. When this happens, this migration could fail due to duplicate entries in the features_plans table. These are the errors raised by the DB engine in this case:

MySQL:

Mysql2::Error: Duplicate entry '8-23' for key 'PRIMARY': ALTER TABLE features_plans ADD PRIMARY KEY (plan_id, feature_id)

Postgres:

PG::UniqueViolation: ERROR: could not create unique index "features_plans_pk"
DETAIL: Key (plan_id, feature_id)=(8, 3) is duplicated.
: ALTER TABLE features_plans ADD CONSTRAINT features_plans_pk PRIMARY KEY (plan_id, feature_id)

Oracle:

OCIError: ORA-02437: cannot validate (RAILS.SYS_C0010658) - primary key violated: ALTER TABLE FEATURES_PLANS ADD PRIMARY KEY (PLAN_ID, FEATURE_ID)

 The duplicated rows must be removed from the table before retrying to run the migration. The next query returns a list of duplicated rows (Valid for all DB engines):

SELECT plan_id, feature_id, count(*)
FROM features_plans
GROUP BY plan_id, feature_id
HAVING count(*) > 1

When duplicated rows are found, we must keep one row for each of the results returned by the query above, and delete it's duplicates. The process to delete the duplicated rows is different depending on the DB engine and version. For instance, one can use "ROW_NUM" method for Oracle or MySQL 8+, but only the "Intermediate table" method is available for MySQL 5.7. The next links summarize all possible situations:

MySQL:
https://www.mysqltutorial.org/mysql-delete-duplicate-rows/

PostgreSQL:
https://www.postgresqltutorial.com/postgresql-tutorial/how-to-delete-duplicate-rows-in-postgresql/

Oracle:
https://www.oracletutorial.com/advanced-oracle-sql/how-to-delete-duplicate-records-in-oracle/

Additionally, in Oracle the next error can happen when retrying the migration after failing because of duplicates:

No indexes found on features_plans with the options provided.

This can be solved by executing the next SQL query and trying the migration again:

CREATE INDEX FEATURES_PLANS_PLAN_ID_IDX ON FEATURES_PLANS (PLAN_ID,FEATURE_ID);

In the worst scenario when removing duplicates isn't possible or too complicated, this migration can be safely skipped unless the client wants to use "Group Replication" feature in MySQL.