Feature #29513

Redmine usability on Percona XtraDB cluster

Added by Richard Pijnenburg over 4 years ago. Updated over 3 years ago.

Status:NewStart date:
Priority:NormalDue date:
Assignee:-% Done:

0%

Category:Database
Target version:-
Resolution:

Description

Hi,

At my work we run Percona XtraDB. To make Redmine work on it, it will require several changes to the database layout.

1. Tables that require a 'PRIMARY KEY':

changeset_parents
changesets_issues
custom_fields_projects
custom_fields_roles
custom_fields_trackers
groups_users
plugin_schema_info
projects_trackers
queries_roles
roles_managed_roles
schema_migrations

2. Tables that need to be converted from MyISAM to InnoDB:

plugin_schema_info

I'm guessing that these changes will have to be implemented on the new install side as well a DB migration step has to be created?

History

#1 Updated by Pavel Rosick√Ĺ over 4 years ago

interesting, have you managed to run redmine on galera with these changes?

here's a documentation about why are primary keys needed
http://galeracluster.com/documentation-webpages/limitations.html#tables-without-primary-keys

not sure if it should be part of the migration step. Btw. Rails 4 used to have a problem with efficient preloading these tables without a primary key, but it was fixed in Rails 5. I don't know about any other use case except replication where this could be useful. Maybe a rake task would be a better choice?

2/ plugin_schema_info table isn't available in the standard redmine installation, right?

#2 Updated by Richard Pijnenburg over 4 years ago

Hi Pavel,

I haven't made the modifications yet to test it.
I wanted to make sure this was made visible to create some idea's.

I'm not sure about the table 'plugin_schema_info' where it came from but I can find out ( It could be part of a plugin we have installed )

A rake task could work yeah

#3 Updated by Robert E2 over 3 years ago

We got the same issue of missing primary keys running MySQL8 with group replication.

Back at the time when we migrated, we thought adding a UNIQUE-KEY constraint will solve the issue:

mysql> show create table changeset_parents
    -> ;
+-------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table             | Create Table                                                                                                                                                                                                                                            |
+-------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| changeset_parents | CREATE TABLE `changeset_parents` (
  `changeset_id` int(11) NOT NULL,
  `parent_id` int(11) NOT NULL,
  KEY `changeset_parents_changeset_ids` (`changeset_id`),
  KEY `changeset_parents_parent_ids` (`parent_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0,00 sec)

mysql> ALTER TABLE changeset_parents ADD CONSTRAINT UNIQUE(changeset_id, parent_id);
ERROR 1062 (23000): Duplicate entry '89630-89615' for key 'changeset_id'
mysql> SELECT * FROM changeset_parents GROUP BY changeset_id, parent_id HAVING COUNT(*) > 1;
+--------------+-----------+
| changeset_id | parent_id |
+--------------+-----------+
|        89630 |     89615 |
+--------------+-----------+
1 row in set (0,05 sec)

mysql> DELETE FROM changeset_parents WHERE changeset_id=89630 AND parent_id=89615;
Query OK, 2 rows affected (0,07 sec)

mysql> INSERT INTO changeset_parents VALUES (89630, 89615);
Query OK, 1 row affected (0,00 sec)

mysql> ALTER TABLE changeset_parents ADD CONSTRAINT UNIQUE(changeset_id, parent_id);
Query OK, 0 rows affected (0,40 sec)
Records: 0  Duplicates: 0  Warnings: 0

Unfortunately not every repository obeys to this rule, so we now got stuck with one Git repository which is not updated within Redmine.

Also available in: Atom PDF