Importing data messes up related tasks #1556

New Issue

hkockerbeck · 2023-06-17T08:40:36Z

hkockerbeck commented

2023-06-17 08:40:36 +00:00

Description

Previously, I've run Vikunja with SQLite as backend. But as the performance couldn't keep up any more, I've switched to MySQL/MariaDB as backend. To transfer the existing data from SQLite to MariaDB, I exported and re-imported it.

After the re-import, all relationships between tasks were messed up. Say, for example, I had a task A which had a task relation of "subtask" with task B. After the re-import, task A still had a task relation, but it pointed to task X instead.

If I had to guess, I'd say that the tasks get probably "renumbered" during the import, meaning they get assigned new internal ids. But the foreign keys for task relations probably don't get adjusted accordingly.

Vikunja Frontend Version

dev (build it myself to get it run in a subdirectory)

Vikunja API Version

0.20.4

Browser and version

No response

Can you reproduce the bug on the Vikunja demo site?

No

Screenshots

No response

### Description Previously, I've run Vikunja with SQLite as backend. But as the performance couldn't keep up any more, I've switched to MySQL/MariaDB as backend. To transfer the existing data from SQLite to MariaDB, I exported and re-imported it. After the re-import, all relationships between tasks were messed up. Say, for example, I had a task A which had a task relation of "subtask" with task B. After the re-import, task A still had a task relation, but it pointed to task X instead. If I had to guess, I'd say that the tasks get probably "renumbered" during the import, meaning they get assigned new internal ids. But the foreign keys for task relations probably don't get adjusted accordingly. ### Vikunja Frontend Version dev (build it myself to get it run in a subdirectory) ### Vikunja API Version 0.20.4 ### Browser and version _No response_ ### Can you reproduce the bug on the Vikunja demo site? No ### Screenshots _No response_

hkockerbeck added the

kind/bug

label 2023-06-17 08:40:36 +00:00

konrad commented

2023-06-18 09:29:49 +00:00

Can you check the task relations table between the sqlite version and the mysql version?

@xeruf did you notice the same when you migrated to postgres?

Can you check the task relations table between the sqlite version and the mysql version? @xeruf did you notice the same when you migrated to postgres?

hkockerbeck commented

2023-06-18 10:58:10 +00:00

Can you check the task relations table between the sqlite version and the mysql version?

I've created a little test: In the old sqlite db, I've created a "Task A", that refers to a "Task B" as its successor. The ids in the taskstable are

sqlite> select id, title from tasks where title like 'Task%';
21|Task A
22|Task B

The relationship seems to be correct in the task_relations table

sqlite> select id, task_id, other_task_id, relation_kind from task_relations where task_id in (21,22) or other_task_id in (21,22);
9|21|22|follows
10|22|21|precedes

Then I've exported this and re-imported it into a newly created test database in MariaDB. Now, "Task A" and "Task B" got new ids

select id, title from tasks where title like 'Task%';

id	title
12	Task A
13	Task B

In the new task_relations table, things get... interesting

select id, task_id, other_task_id, relation_kind from task_relations where task_id in (12,13) or other_task_id in (12,13);

id	task_id	other_task_id	relation_kind
3	4	12	follows
4	12	4	precedes
13	12	22	follows
14	22	12	precedes
15	13	21	precedes
16	21	13	follows

In the frontend, I see a relationship to a completely different task. I remember that different task having multiple relationships prior to the migration, but not to "Task A" or "Task B".

> Can you check the task relations table between the sqlite version and the mysql version? I've created a little test: In the old sqlite db, I've created a "Task A", that refers to a "Task B" as its successor. The ids in the `tasks`table are ``` sqlite> select id, title from tasks where title like 'Task%'; 21|Task A 22|Task B ``` The relationship seems to be correct in the `task_relations` table ``` sqlite> select id, task_id, other_task_id, relation_kind from task_relations where task_id in (21,22) or other_task_id in (21,22); 9|21|22|follows 10|22|21|precedes ``` Then I've exported this and re-imported it into a newly created test database in MariaDB. Now, "Task A" and "Task B" got new ids ``` select id, title from tasks where title like 'Task%'; id title 12 Task A 13 Task B ``` In the new `task_relations` table, things get... interesting ``` select id, task_id, other_task_id, relation_kind from task_relations where task_id in (12,13) or other_task_id in (12,13); id task_id other_task_id relation_kind 3 4 12 follows 4 12 4 precedes 13 12 22 follows 14 22 12 precedes 15 13 21 precedes 16 21 13 follows ``` In the frontend, I see a relationship to a completely different task. I remember that different task having multiple relationships prior to the migration, but not to "Task A" or "Task B".

konrad commented

2023-06-18 12:13:30 +00:00

That's interesting, because the restore process leaves all ids as they were in the old db. Can you check the values of the tasks.json and task_relations.json files in the export for both tasks?

That's interesting, because the restore process leaves all ids as they were in the old db. Can you check the values of the `tasks.json` and `task_relations.json` files in the export for both tasks?

xeruf commented

2023-08-31 13:59:34 +00:00

I don't think I had really used relations before migrating, so I can't help here