Missing Primary Key screws up DB Replication

[roha4000]

Hi,

We are using a Galera cluster as a MySQL-DB backend. Within the db_structure.xml there are three tables without a Primary Key defined which leads to replication failures within the db cluster. This should also affect normal Master - Slave replication scenarios.

tables missing a Key:

group_user
group_admin
properties

As far as I can say those tables are a good candidate for an auto_increment field 'id', aren't they?

best regards
Roland Hager

[BernhardPosselt]

@blizzz @icewind1991

[BernhardPosselt]

Adding it will cause a ton of breakage. Not sure how this fix should be done.

[roha4000]

I actually added an auto_increment field "id" and had no breakage using MySQL. Other databases, which are adding automatically primary keys when the are missing might be more affected. BUT: Shouldn't a primary key be obligatory?

btw. the properties table is really under frequent access. We already have more than 300k entries in our test environment and not using an index results in a lot of full table scans. Setting an index reduced the number of accessed rows from 201.009 to 54.412 and the execution time from 0.16s to 0.07s when "explaining" on of the worse selects. The index is at about 12% of the size of the whole table.

mysql> create index userid_index ON oc_properties (userid);

[BernhardPosselt]

see #2191

[BernhardPosselt]

Ok, seems to be a very serious issue since there is not a single! field with primary key. I'll be asking on the mailing list if this is fixable in any way that doesnt make upgrading impossible.

[tanghus]

seems to be a very serious issue since there is not a single! field with primary key.

Most tables have primary keys from the autoincrement field, so afaik it is only the three tables mentioned above.

[BernhardPosselt]

tables that dont have a primary key (and neither auto increment):

• appconfig
• file_map
• files_trash
• files_trashsize
• files_versions
• group_admin
• group_user
• groups
• permissions
• preferences
• properties
• users
• category_to_object

by using sqliteman

[tanghus]

• appconfig

Could make appconfig_appid_key_index unique and primary

• filemap

Could set file_map_lp_index as primary

groups

has groups_pKey as primary

permissions

Could use id_user_index

preferences

Could use pref_userid_appid_key_index

users

Defines a primary key on users_pKey

• category_to_object

Defines a primary key on category_object_index.