Issue Task Closed

#101425TYPO3 and MariaDB Galera Cluster (PRIMARY KEY)

Ralph Brugger 07/24/23, 08:01 updated 01/30/24, 08:31

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