Data of relations in some cases not migrated

[GregorSondermeier]

Bug report

Required System information

• Node.js version: 14.20.1
• NPM version: 6.14.17
• Source Strapi version: 3.6.10
• Target Strapi version: 4.3.9
• Source Database: SQL (MariaDB and SQLite)
• Target Database: SQL (MariaDB and SQLite)
• Operating system: Ubuntu 20.04
• Which script are you running: v3-sql-v4-sql

Describe the bug

In some cases, many-to-many relations are not properly migrated and data is missing.

It looks like it has something to do with which attribute is the dominant one, or with the alphabetical order of the referenced content-types, or with the name of the table that stores the relations.

Steps to reproduce the behavior

1. Run npx create-strapi-app@v3 my-project --quickstart to generate an empty Strapi v3 project
2. As described in the v3 Quick Start Guide, create the restaurant and categories content-types, but with one difference:
   Instead of adding the many-to-many relation into the category, add it to the restaurant
   
3. Create a restaurant and some categories and link them via the relation field
   
4. Notice that the name of the relation table is categories_restaurants__restaurants_categories
   
5. Run codemods to migrate code to v4 syntax
6. Remove package-lock.json, remove node_modules folder, change sqlite3 dependency to latest better-sqlite3 dependency for SQLite + Strapi v4 compatibility (source)
7. Run npm i
8. Configure new sqlite database path for Strapi v4 in .env
9. Run npm run build && npm run develop to start Strapi v4 and let it generate the empty tables, kill process afterwards
10. Execute the v3-sql-v4-sql script to migrate the database from v3 to v4
11. Run npm run develop to start Strapi v4
12. Notice that the many-to-many relations between restaurants and categories are missing:
    

Expected behavior

The restaurant_categories_links table contains the migrated many-to-many-relations.

Additional info

With the "How to reproduce" guide in mind this might look like an artificial issue. But we're currently migrating a v3 project which has been in production for 1.5 years to v4. That project contains some many-to-many relations of all sorts and changing how these relations are defined while ensuring data integrity is something I'm a little afraid of :) By pure chance I found out that some data is missing. I hope there isn't more :(

If you try to reproduce this issue, pay attention to step 2:

When I create the many-to-many relation in the category as described in the Quick Start Guide, the many-to-many relation gets properly migrated. Only when I create it the other way round the migration does not happen.

I suspect it has something to do with either the alphabetical order of the content-type name, or with the dominant property of the attribute.

Although the dominant property would be weird, because the doc mentions that that one is used for NoSQL databases only.

When it's like this the migration will work:

// category.settings.json
{
...
  "attributes": {
    ...
    "restaurants": {
      "collection": "restaurant",
      "via": "categories",
      "dominant": true
    }
  }
}

// restaurant.settings.json
{
  ...
  "attributes": {
    ...
    "categories": {
      "via": "restaurants",
      "collection": "category"
    }
  }
}

But when it's like this the migration will not work:

// category.settings.json
{
...
  "attributes": {
    ...
    "restaurants": {
      "via": "categories",
      "collection": "restaurant"
    }
  }
}

// restaurant.settings.json
{
  ...
  "attributes": {
    ...
    "categories": {
      "collection": "category",
      "via": "restaurants",
      "dominant": true
    }
  }
}

[bglidwell]

can confirm this issue as well

[GregorSondermeier]

Or is this a codemods issue?

[ondrejhadrava]

I have same issue

[derrickmehaffy]

Fairly certain this is codemods related and is likely related to strapi/codemods#44

[GregorSondermeier]

If I understand issue strapi/codemods#44 correctly, then it doesn't seem to be related to the previously missing component migrations fixed by @derrickmehaffy's PR strapi/codemods#46.

Because the issue I have reported here is not related to components, it is related to collection types directly.

[GregorSondermeier]

For me personally I switched the mappedBy/inversedBy properties of the many-to-many relation (luckily in our project we only had one) and then did the v3 -> v4 migration process again, which properly migrated the data.

[derrickmehaffy]

Interesting can anyone else shed some light on reproducing the issue as I couldn't reproduce this (it may not be codemods related so I'll move this back to the data migrations repo)

[darkcherrycreative]

Interesting can anyone else shed some light on reproducing the issue as I couldn't reproduce this (it may not be codemods related so I'll move this back to the data migrations repo)

Can confirm i've had the same issue, all manyToMany relations are lost when the migration script is run. All other relations work fine, but manyToMany are lost with the exact symptoms described by the OP

Edit:

This is fixed by switching around "inversedBy" and "mappedBy" in the relevant scheme files after running the codemods. After swapping these around, generate a fresh db file by doing "yarn develop", then run the migration scrips as normal. I found i also had to manually create the "up_users" and "up_users_role_links" tables but the data is then migrated properly.

[derrickmehaffy]

Interesting can anyone else shed some light on reproducing the issue as I couldn't reproduce this (it may not be codemods related so I'll move this back to the data migrations repo)

Can confirm i've had the same issue, all manyToMany relations are lost when the migration script is run. All other relations work fine, but manyToMany are lost with the exact symptoms described by the OP

Edit:

This is fixed by switching around "inversedBy" and "mappedBy" in the relevant scheme files after running the codemods. After swapping these around, generate a fresh db file by doing "yarn develop", then run the migration scrips as normal. I found i also had to manually create the "up_users" and "up_users_role_links" tables but the data is then migrated properly.

For users-permissions, that one is an issue with codemods, we have already patched it but pending a new release of codemods (hopefully tomorrow).

I'll try again to reproduce this issue with data migrations.

[GregorSondermeier]

Ok, apparently this is not reduced to many-to-many relations, but also to other kinds of relations.

In our project we have a collectionType TeachingUnit, which looks roughly like this:

v3

{
  "kind": "collectionType",
  "collectionName": "teaching_units",
  "info": {
    "name": "teachingUnit",
    "description": ""
  },
  "attributes": {
    "defaultPeriods": {
      "collection": "period"
    },
    "defaultSubjects": {
      "collection": "tag"
    },
  }
}

v3 table teaching_units__default_periods:

v3 table teaching_units__default_subjects

v4

{
  "kind": "collectionType",
  "collectionName": "teaching_units",
  "info": {
    "singularName": "teaching-unit",
    "pluralName": "teaching-units",
    "displayName": "Teaching-unit",
    "name": "teaching-unit"
  },
  "attributes": {
    "defaultPeriods": {
      "type": "relation",
      "relation": "oneToMany",
      "target": "api::period.period"
    },
    "defaultSubjects": {
      "type": "relation",
      "relation": "oneToMany",
      "target": "api::tag.tag"
    },
  }
}

v4 table teaching_units_default_periods_links (empty):

v4 table teaching_units_default_subjects_links (empty):

I'm pretty sure it has something to do with the alphabetical order of the related collection types, or the field names.

Unfortunately I do not have the time to go into more detail right now.

[Andrii23LV]

@GregorSondermeier Hi, I was wondering if you really solved the problem with replacing "inversedBy" with "mappedBy" or you found another way to move all the data? If the first option, then the procedure looks like this -> replacing "inversedBy" with "mappedBy" -> yarn develop -> scripts for database migration? Thank You in advance.

[GregorSondermeier]

@Andrii23LV Sorry I can't remember, too long ago