New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Task relationships are broken on MariaDB instances where auto-increment is != 1 #4137

Closed
psy-q opened this Issue Feb 6, 2019 · 3 comments

Comments

2 participants
@psy-q
Copy link
Contributor

psy-q commented Feb 6, 2019

Actual behaviour

Internal links between tasks either can't be assigned and throw an error "The exact same link already exists" or show up with a nonsense relationship (e.g. "blocks" will show up as "blocks" on the opposite side, instead of "is blocked by").

Only "relates to" actually works.

Expected behaviour

Internal links can be assigned and show their appropriate labels on each side.

Steps to reproduce

  • Install Kanboard and use a MySQL/MariaDB instance where auto-increment size is 2
  • Create a new task titled "Task 1"
  • Create a new task titled "Task 2"
  • Try to assign internal link "Task 1 is a milestone of Task 2"
  • Observe that you get an error "The exact same link already exists" even though none exists
  • Assign internal link "Task 1 blocks Task 2"
  • Observe that Task 2 is not showing "is blocked by Task 1", but paradoxically shows "Task 1 blocks Task 2" and "Task 2 blocks Task 1"

The table with the increments that Kanboard was probably not expecting looks like:

+----+-------------------+-------------+
| id | label             | opposite_id |
+----+-------------------+-------------+
|  1 | relates to        |           0 |
|  3 | blocks            |           3 |
|  5 | is blocked by     |           2 |
|  7 | duplicates        |           5 |
|  9 | is duplicated by  |           4 |
| 11 | is a child of     |           7 |
| 13 | is a parent of    |           6 |
| 15 | targets milestone |           9 |
| 17 | is a milestone of |           8 |
| 19 | fixes             |          11 |
| 21 | is fixed by       |          10 |
+----+-------------------+-------------+

Increment sizes in MySQL/MariaDB clusters are often 2, 4 or higher numbers depending on the cluster size, so expecting the IDs to always increment by 1 and hardcoding them will break Kanboard's relationships on those clusters.

Configuration

  • Application version: 1.2.8
  • PHP version: 7.0.33-0+deb9u1
  • PHP SAPI: apache2handler
  • OS version: Linux 4.9.0-8-amd64
  • Database driver: mysql
  • Database version: 10.1.37-MariaDB-0+deb9u1
  • Browser: Mozilla/5.0 (X11; Linux x86_64; rv:65.0) Gecko/20100101 Firefox/65.0
@psy-q

This comment has been minimized.

Copy link
Contributor Author

psy-q commented Feb 13, 2019

With the referenced PR the tables now have the correct IDs even with e.g. auto_increment_increment set to 4 and it should work fine on any cluster:

MariaDB [kanboard]> show variables like '%auto_increment%';
+------------------------------+-------+
| Variable_name                | Value |
+------------------------------+-------+
| auto_increment_increment     | 4     |
| auto_increment_offset        | 1     |
| wsrep_auto_increment_control | ON    |
+------------------------------+-------+
MariaDB [kanboard]> select * from links;
+----+-------------------+-------------+
| id | label             | opposite_id |
+----+-------------------+-------------+
|  1 | relates to        |           0 |
|  5 | blocks            |           9 |
|  9 | is blocked by     |           5 |
| 13 | duplicates        |          17 |
| 17 | is duplicated by  |          13 |
| 21 | is a parent of    |          25 |
| 25 | is a child of     |          21 |
| 29 | is a milestone of |          33 |
| 33 | targets milestone |          29 |
| 37 | is fixed by       |          41 |
| 41 | fixes             |          37 |
+----+-------------------+-------------+
@psy-q

This comment has been minimized.

Copy link
Contributor Author

psy-q commented Feb 14, 2019

creecros mentioned it should go into a new version_. I can revert the changes to version_46 and make a separate migration out of it, if you like?

It won't be possible to do in the mysql.sql dump of course. I don't know how a cluster reacts to loading that dump -- do the static IDs from the dump stay the same even though they are marked as auto_increment primary keys? I could test that next week.

@fguillot

This comment has been minimized.

Copy link
Collaborator

fguillot commented Feb 16, 2019

I tested quickly your PR on a Galera cluster and that seems to work. I didn't test the SQL dump yet.

Merged in this commit 314ca5b

@fguillot fguillot closed this in 46e3dd6 Feb 16, 2019

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment