#101425TYPO3 and MariaDB Galera Cluster (PRIMARY KEY)
Description
In the Known Limitations of the MariaDB Galera Cluster you can read:
https://mariadb.com/kb/en/mariadb-galera-cluster-known-limitations/
All tables should have a primary key (multi-column primary keys are supported). DELETE operations are unsupported on tables without a primary key. Also, rows in tables without a primary key may appear in a different order on different nodes.
In our project we have a lot mm-tables that don’t have a primary key:
TYPO3:
sys_category_record_mm
EXTENSIONS:
tx_rsmevents_event_category_mm
…
For TYPO3 it’s normal to NOT have a primary key on MM-Tables:
https://docs.typo3.org/m/typo3/reference-tca/11.5/en-us/ColumnsConfig/CommonProperties/Mm.html#tca-property-mm
If you have a look at the LogWriter-Docu there is a hint about Galera:
https://docs.typo3.org/m/typo3/reference-coreapi/11.5/en-us/ApiOverview/Logging/Writers/Index.html
If you are using a MariaDB Galera Cluster you should definitely add a primary key field to the database definition, since it is required by Galera (this can be a normal uid autoincrement field as known from other tables): MariaDB Galera Cluster - Known Limitations.
So what for the mm-tables?
We could add:
a) an uid field with auto_increment and PRIMARY KEY
CREATE TABLE anytable_MM (
uid int(11) NOT NULL auto_increment,
..
PRIMARY KEY (uid),
);
b) or add an multi-column primary key on uid_local uid_foreign – I’m not sure if this is possible, or there can de duplicates.
CREATE TABLE anytable_MM ( uid_local int(11) DEFAULT '0' NOT NULL, uid_foreign int(11) DEFAULT '0' NOT NULL, ... PRIMARY KEY (uid_local,uid_foreign), );
I think the combination of uid_local and uid_foreign could or should be unique.
But I’m not sure, so solution a) should be the better solution.
Ralph
Reviews 5
Related issues 7
- Feature #95061Auto creation of MM tables from TCA
- Task #101528Fix MM TCA of blog_example
- Task #101537Have correct blog_example related_posts TCA
- Task #101554Get rid of MM_hasUidField
- Bug #101720sys_category_mm does not have a unique key
- Task #101731Adjust auto-created column length for MM tables
- Bug #103604MM table generation sets wrong PRIMARY KEY for columns with multiple=1