Percona XtraDB/MariaDB cluster compatible

Talk about new features for CMSMS and modules.
Post Reply
funkyhead
New Member
New Member
Posts: 4
Joined: Mon Oct 30, 2017 2:50 pm

Percona XtraDB/MariaDB cluster compatible

Post by funkyhead »

Hello,
An thanks for your good job on CMSMS.
In production cluster like Percona XtraDB/MariaDB, several CMSMS tables are not compatible with.
Primary Keys are needed and those clusters and in ENFORCING mode, sites are not working. In PERMISSIVE mode, it works but replications can be hazardous...
Ex :
WSREP: Percona-XtraDB-Cluster doesn't recommend use of DML command on a table (cmX.cms_content_props) without an explicit primary key with pxc_strict_mode = PERMISSIVE

So, it is possible to make CMSMS cluster compatible, by modifying code or tables structures in a next version ?

Thanks, for your attention.
Ed
User avatar
Jo Morg
Dev Team Member
Dev Team Member
Posts: 1827
Joined: Mon Jan 29, 2007 4:47 pm

Re: Percona XtraDB/MariaDB cluster compatible

Post by Jo Morg »

There are different aspects to CMSMS supporting pxc_strict_mode = ENFORCING in Percona-XtraDB-Clusters:
  • after some research I'm not entirely clear if adding the primary keys to the affected tables would be enough to make the CMSMS core compatible with those clusters, as there are other restrictions that may possibly conflict with the current CMSMS DB operations;
  • there are, even if rare, use cases for tables without primary keys;
  • whether it is recommended or not the use of DML commands on tables without an explicit primary key, setting pxc_strict_mode = PERMISSIVE or DISABLED does address the conflict and CMSMS works fine in those modes;
  • Even if the core made all its tables and operations compatible with the clusters, chances are that most 3rd party modules would not be compatible;
Having said that, and although not in our immediate plans to make all the tables have a primary key, it's an achievable goal for the core to implement it where pertinent, and possibly something to have in the 3.0 branch.
Thank you for your suggestion
"There are 10 types of people in this world, those who understand binary... and those who don't."
* by the way: English is NOT my native language (sorry for any mistakes...).
Code of Conduit | CMSMS Docs | Help Support CMSMS
My developer Page on the Forge
GeekMoot 2015 in Ghent, Belgium: I was there!
GeekMoot 2016 in Leicester, UK: I was there!
funkyhead
New Member
New Member
Posts: 4
Joined: Mon Oct 30, 2017 2:50 pm

Re: Percona XtraDB/MariaDB cluster compatible

Post by funkyhead »

Thanks for your reply,
WP sites work without any problem in the cluster, to make CMSMS works with it i've done those actions for all DBs at the moment :

ALTER TABLE cmX.cms_adminlog ADD COLUMN `id` int(10) UNSIGNED PRIMARY KEY AUTO_INCREMENT FIRST;
ALTER TABLE cmX.cms_content_props ADD COLUMN `id` int(10) UNSIGNED PRIMARY KEY AUTO_INCREMENT FIRST;
ALTER TABLE cmX.cms_module_search_index ADD COLUMN `id` int(10) UNSIGNED PRIMARY KEY AUTO_INCREMENT FIRST;
* I think the last one, make the internal "admin" search module not work (but i must verify).

All the "*_seq" tables in CMS DBs must be modified for a primary key too, but there are lots of tables in all DBs so i take time to make it right.

Thanks for your attention.
Last edited by funkyhead on Fri Feb 26, 2021 2:31 pm, edited 1 time in total.
funkyhead
New Member
New Member
Posts: 4
Joined: Mon Oct 30, 2017 2:50 pm

Re: Percona XtraDB/MariaDB cluster compatible

Post by funkyhead »

List of actions on DB to do for stop warnings on Percona XtraDB.
For new versions :
ALTER TABLE X.Y_adminlog ADD COLUMN `id` int(10) UNSIGNED PRIMARY KEY AUTO_INCREMENT FIRST;
ALTER TABLE X.Y_module_search_index ADD COLUMN `id` int(10) UNSIGNED PRIMARY KEY AUTO_INCREMENT FIRST;
ALTER TABLE X.Y_content_props ADD COLUMN `id` int(10) UNSIGNED PRIMARY KEY AUTO_INCREMENT FIRST;
ALTER TABLE X.Y_content_props ADD COLUMN `id` int(10) UNSIGNED PRIMARY KEY AUTO_INCREMENT FIRST;
ALTER TABLE X.Y_userprefs ADD PRIMARY KEY( `user_id`, `preference`);
ALTER TABLE X.Y_*_seq ADD PRIMARY KEY(`id`);
ALTER TABLE X.Y_siteprefs ADD INDEX(`sitepref_name`);
ALTER TABLE `X.Y_module_deps` ADD PRIMARY KEY( `child_module`, `minimum_version`);
ALTER TABLE X.Y_module_templates ADD PRIMARY KEY(`module_name`, `template_name`);
ALTER TABLE X.Y_userprefs ADD PRIMARY KEY( `user_id`, `preference`);
For old versions :
ALTER TABLE `X.Y_css_assoc` ADD PRIMARY KEY( `assoc_to_id`, `assoc_css_id`);
ALTER TABLE X1.Y_crossref ADD PRIMARY KEY(`child_id`, `parent_id`);


The last problem not solved at the moment is only on siteprefs table (with a PRIMARY KEY) :


*** WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 125186 page no 9 n bits 120 index PRIMARY of table `cms`.`cms_siteprefs` trx id 5350779 lock_mode X locks rec but not gap
2021-03-04T12:23:21.958431Z 5 [Note] WSREP: --------- CONFLICT DETECTED --------
2021-03-04T12:23:21.958463Z 5 [Note] WSREP: cluster conflict due to high priority abort for threads:

2021-03-04T12:23:21.958482Z 5 [Note] WSREP: Winning thread:
THD: 5, mode: applier, state: executing, conflict: no conflict, seqno: 1852645
SQL: (null)

2021-03-04T12:23:21.958498Z 5 [Note] WSREP: Victim thread:
THD: 215140, mode: local, state: committing, conflict: no conflict, seqno: -1
SQL: UPDATE cms_siteprefs SET sitepref_value = 1614860602 WHERE sitepref_name = 'CmsJobManager_mapi_pref_last_async_trigger'

Idem for :
SQL: UPDATE cms_siteprefs SET sitepref_value = 1614852940 WHERE sitepref_name = 'pseudocron_lastrun'
SQL: UPDATE cms_siteprefs SET sitepref_value = 1614857506 WHERE sitepref_name = 'CGExtensions_mapi_pref_cache_autoclean_last'


If someone can help me to solve this...
Thx in advance.
funkyhead
New Member
New Member
Posts: 4
Joined: Mon Oct 30, 2017 2:50 pm

Re: Percona XtraDB/MariaDB cluster compatible

Post by funkyhead »

I've forgotten to say i'm running on Percona XtraDB version 5.7.18-15-57.
Post Reply

Return to “Feature ideas”