The customer reported login failure issue in Admin portal when using external MySQL 8 with group replication.
According to debug logging of system-provider, an INSERT to user_sessions table fails.
[52218e11-c3a7-4674-be0a-bf201f973368] [3scale-admin.xxxxxxxxxxxxxxx] [xx.xx.xx.xx] (0.8ms) BEGIN [52218e11-c3a7-4674-be0a-bf201f973368] [3scale-admin.xxxxxxxxxxxxxxx] [xx.xx.xx.xx] UserSession Create (1.7ms) INSERT INTO `user_sessions` (`user_id`, `key`, `ip`, `user_agent`, `accessed_at`, `created_at`, `updated_at`) VALUES (9, '<SESSION_KEY>', 'yy.yy.yy.yy', 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/127.0.0.0 Safari/537.36 Edg/127.0.0.0', '2024-08-23 00:08:58', '2024-08-23 00:08:58', '2024-08-23 00:08:58') [52218e11-c3a7-4674-be0a-bf201f973368] [3scale-admin.xxxxxxxxxxxxxxx] [xx.xx.xx.xx] (0.4ms) ROLLBACK
At MySQL side, the following error happens at that time.
2024-08-23T05:43:36.361544Z 573786 [ERROR] [MY-011543] [Repl] Plugin group_replication reported: 'Table user_sessions has a foreign key with 'CASCADE', 'SET NULL' or 'SET DEFAULT' clause. This is not compatible with Group Replication.'
In MySQL 8 group replication, it appears that there is the limitation in 'Foreign Keys with Cascading Constraints' with multi-primary mode.
https://dev.mysql.com/doc/refman/8.0/en/group-replication-limitations.html
Foreign Keys with Cascading Constraints. Multi-primary mode groups (members all configured with group_replication_single_primary_mode=OFF) do not support tables with multi-level foreign key dependencies, specifically tables that have defined CASCADING foreign key constraints. This is because foreign key constraints that result in cascading operations executed by a multi-primary mode group can result in undetected conflicts and lead to inconsistent data across the members of the group. Therefore we recommend setting group_replication_enforce_update_everywhere_checks=ON on server instances used in multi-primary mode groups to avoid undetected conflicts. In single-primary mode this is not a problem as it does not allow concurrent writes to multiple members of the group and thus there is no risk of undetected conflicts. This limitation causes insert failure in user_sessions table.
In facts, some tables including user_sessions have a foreign key with cascading constraints.
https://github.com/3scale/porta/blob/master/db/schema.rb#L1467-L1475
When customer changed from multi-primary mode to single-primary mode in group replication plugin, login succeeded.
On the other hand, THREESCALE-9655 was filed to the support of GroupReplication in MySQL and primary key are added to all of tables.
But it seems that adding primary key is not enough to support MySQL group replication.
Can 3scale support Group Replicatin in MySQL ?
- is related to
-
THREESCALE-9655 Add primary keys to all tables
-
- Closed
-
