View Issue Details

Jump to NotesJump to History
IDProjectCategoryView StatusDate SubmittedLast Update
1793RackTablesdefaultpublic2017-10-03 09:592017-10-09 18:41
ReporterJolrael Assigned Toinfrastation  
PrioritynormalSeverityminorReproducibilityalways
Status closedResolutionfixed 
PlatformPercona XtraDB Cluster 
Product Version0.20.14 
Target Version0.21.1Fixed in Version0.21.1 
Summary1793: Tables without primary/unique keys give issue on Percona XtraDB Cluster
DescriptionWe run a database cluster of Percona XtraDB Cluster with Galera (version 5.7.17-29.20-3.xenial). When trying to load a database dump of Racktables from another server, we get an error:

ERROR 1105 (HY000) at line 43: Percona-XtraDB-Cluster prohibits use of DML command on a table (racktables_db.Atom) without an explicit primary key with pxc_strict_mode = ENFORCING or MASTER

This has to do with tables without a primary key, which is not very well supported in a clustered environment.

In order to be able to use this type of clustered database, the desired solution would be to add at least one unique (primary) key to the tables that current don't have one. As far as I can tell, the following tables don't seem to have any unique key at this moment:

Atom
Location
ObjectHistory
Rack
RackObject
Row

I guess simply adding an (tableName_)id column with autoincrement to these tables would probably fix this.
Steps To Reproduce- Setup a Percona XtraDB Galera cluster
- Load a database dump of racktables with at least some data in for instance the 'Atom' table
TagsNo tags attached.

Activities

infrastation

infrastation

2017-10-03 16:08

administrator   ~0003725

Thank you for reporting this. Location, Rack, RackObject and Row are views, they are based on the Object table, which already has a primary key. I am not sure why this makes a problem in Percona. The Atom table in fact could benefit from a multiple-column primary key without an additional column, I will consider this change for the next release. For the ObjectHistory table I can add a new column to serve as a primary key as you suggest.
Jolrael

Jolrael

2017-10-03 16:38

reporter   ~0003727

Ah good point; I just ran a query to find out which table didn't have any unique keys, but I didn't filter out the views :-P
infrastation

infrastation

2017-10-09 18:41

administrator   ~0003739

RackTables 0.21.1 will have unique keys in the Atom and ObjectHistory tables, if you encounter another issue specific to Percona please open a new issue. Thanks for the report.

Issue History

Date Modified Username Field Change
2017-10-03 09:59 Jolrael New Issue
2017-10-03 16:08 infrastation Note Added: 0003725
2017-10-03 16:38 Jolrael Note Added: 0003727
2017-10-03 16:55 infrastation Status new => acknowledged
2017-10-03 16:55 infrastation Target Version => 0.21.1
2017-10-09 18:41 infrastation Assigned To => infrastation
2017-10-09 18:41 infrastation Status acknowledged => closed
2017-10-09 18:41 infrastation Resolution open => fixed
2017-10-09 18:41 infrastation Fixed in Version => 0.21.1
2017-10-09 18:41 infrastation Note Added: 0003739