Wikimedia Developer Support

Database structure change for use in MySql Cluster

Hello!

Hi!
I’m using Wiki 1.31 with Percona XtraDB Cluster (master x master, version 5.7) with 3 nodes. Second the developer: “Percona XtraDB Cluster cannot properly propagate certain write operations to tables that do not have primary keys defined”.

I have checked and some tables does not have primary keys:
-mw_interwiki
-mw_oldimage
-mw_page_props
-mw_protected_titles
-mw_querycache
-mw_querycachetwo
-mw_site_identifiers
-mw_user_newtalk

Too, the table mw_searchindex still using the engine MyIsam (all other tables are InnoDB);

I think clustering will be increasingly used… and this changes will continue to be compatible with local DB use.

Do this changes on tables structures maybe considered an enhancement for a future release ?

Best regards.

This is a known issue and you can see more information at https://phabricator.wikimedia.org/T17441 - Some of them might not have a PK, but they do have (at least) UNIQUE indexes.

https://phabricator.wikimedia.org/T146571
https://phabricator.wikimedia.org/T146568
https://phabricator.wikimedia.org/T146585
https://phabricator.wikimedia.org/T146586
https://phabricator.wikimedia.org/T146591

It would look like a couple of those tables need tasks filing for them though. I’ll get those done

https://phabricator.wikimedia.org/T198811 filed for the remaining 4 tables that didn’t have a task already

https://phabricator.wikimedia.org/T107875 for InnoDB in search_index

About the search_index, if you want to use cluster, I’d suggest you to use Extension:CirrusSearch with ElasticSearch, which has better performance and supports cluster natively. With that extension, the search_index table is no longer used.