Migrate strapi_permission (v3) into admin_permissions (v4) - column "fields" of relation "admin_permissions" does not exist (again) and "Cannot read properties of null (reading 'fields')"

[MO-Lewis]

Bug report

Required System information

• Node.js version: v16.17.0
• NPM version: v8.15.0
• Source Strapi version: v3.5.0
• Target Strapi version: v4.5.5
• Source Database: MySQL
• Target Database: MySQL
• Operating system: Windows 10 64-bit
• Which script are you running: v3-sql-v4-sql (using the MySQL .env files)

Describe the bug

This appears to be the same issue as this bug from last year: #10

This issue was never properly solved and was closed due to inactivity. As all of the code that I'm using has been provided by the Strapi team, I'm hoping that this issue will provide a reproducible example, so the team can provide an explanation for the issue, and potentially a fix. For any repos / npm packages I use, I aim to provide commit IDs and version numbers.

This also looks like it might be related to #19

After using the Strapi example repo to migrate the demo strapi application from V3 to V4 via codemods (and following the livestream: https://www.youtube.com/watch?v=NSvdQKVvV9k), I've cloned the migration-scripts repo and followed the data migration livestream (https://www.youtube.com/watch?v=kdxivJjjVhY).

After filling in the env files and specifying my targets, I run the migration script, but then get the following error:

npm run start

> strapi-migration-database@1.0.0 start
> node index.js

Migrating Core Store
Migrating 59/88 items from core_store to strapi_core_store_settings
core_store batch #1
core_store batch #2
Migrating Admin
Migrating 3 items from strapi_role to admin_roles
strapi_role batch #1
Migrating 1 items from strapi_administrator to admin_users
strapi_administrator batch #1
Migrating 2 items from strapi_users_roles to admin_users_roles_links
strapi_users_roles batch #1
Migrating 100 items from strapi_permission to admin_permissions
strapi_permission batch #1
node:internal/process/promises:279
            triggerUncaughtException(err, true /* fromPromise */);
            ^

[Error: insert into `admin_permissions` (`action`, `conditions`, `created_at`, `fields`, `id`, `properties`, `subject`, `updated_at`) select 'plugin::content-manager.explorer.create' as `action`, '[]' as `conditions`, 1595433593180 as `created_at`, '["name","restaurants"]' as `fields`, 1 as `id`, NULL as `properties`, 'api::category.category' as `subject`, 1595433593185 as `updated_at` union all select 'plugin::content-manager.explorer.create' as `action`, '[]' as `conditions`, 1595433593192 as `created_at`, '["author","review"]' as `fields`, 2 as `id`, NULL as `properties`, 'api::like.like' as `subject`, 1595433593197 as `updated_at` union all select 'plugin::content-manager.explorer.create' as `action`, '[]' as `conditions`, 1595433593217 as `created_at`, '["content","note","author","likes","restaurant"]' as `fields`, 4 as `id`, NULL as `properties`, 'api::review.review' as `subject`, 1595433593222 as `updated_at` union all select 'plugin::content-manager.explorer.read' as `action`, '[]' as `conditions`, 1595433593230 as `created_at`, '["name","restaurants"]' as `fields`, 5 as `id`, NULL as `properties`, 'api::category.category' as `subject`, 1595433593235 as `updated_at` union all select 'plugin::content-manager.explorer.read' as `action`, '[]' as `conditions`, 1595433593243 as `created_at`, '["author","review"]' as `fields`, 6 as `id`, NULL as `properties`, 'api::like.like' as `subject`, 1595433593247 as `updated_at` union all select 'plugin::content-manager.explorer.read' as `action`, '[]' as `conditions`, 1595433593268 as `created_at`, '["content","note","author","likes","restaurant"]' as `fields`, 8 as `id`, NULL as `properties`, 'api::review.review' as `subject`, 1595433593274 as `updated_at` union all select 'plugin::content-manager.explorer.update' as `action`, '[]' as `conditions`, 1595433593283 as `created_at`, '["name","restaurants"]' as `fields`, 9 as `id`, NULL as `properties`, 'api::category.category' as `subject`, 1595433593288 as `updated_at` union all select 'plugin::content-manager.explorer.update' as `action`, '[]' as `conditions`, 1595433593301 as `created_at`, '["author","review"]' as `fields`, 10 as `id`, NULL as `properties`, 'api::like.like' as `subject`, 1595433593307 as `updated_at` union all select 'plugin::content-manager.explorer.update' as `action`, '[]' as `conditions`, 1595433593335 as `created_at`, '["content","note","author","likes","restaurant"]' as `fields`, 12 as `id`, NULL as `properties`, 'api::review.review' as `subject`, 1595433593342 as `updated_at` union all select 'plugin::content-manager.explorer.delete' as `action`, '[]' as `conditions`, 1595433593352 as `created_at`, NULL as `fields`, 13 as `id`, NULL as `properties`, 'api::category.category' as `subject`, 1595433593359 as `updated_at` union all select 'plugin::content-manager.explorer.delete' as `action`, '[]' as `conditions`, 1595433593371 as `created_at`, NULL as `fields`, 14 as `id`, NULL as `properties`, 'api::like.like' as `subject`, 1595433593377 as `updated_at` union all select 'plugin::content-manager.explorer.delete' as `action`, '[]' as `conditions`, 1595433593386 as `created_at`, NULL as `fields`, 15 as `id`, NULL as `properties`, 'api::restaurant.restaurant' as `subject`, 1595433593391 as `updated_at` union all select 'plugin::content-manager.explorer.delete' as `action`, '[]' as `conditions`, 1595433593403 as `created_at`, NULL as `fields`, 16 as `id`, NULL as `properties`, 'api::review.review' as `subject`, 1595433593408 as `updated_at` union all select 'plugin::upload.read' as `action`, '[]' as `conditions`, 1595433593417 as `created_at`, NULL as `fields`, 17 as `id`, NULL as `properties`, NULL as `subject`, 1595433593422 as `updated_at` union all select 'plugin::upload.assets.create' as `action`, '[]' as `conditions`, 1595433593431 as `created_at`, NULL as `fields`, 18 as `id`, NULL as `properties`, NULL as `subject`, 1595433593435 as `updated_at` union all select 'plugin::upload.assets.update' as `action`, '[]' as `conditions`, 1595433593444 as `created_at`, NULL as `fields`, 19 as `id`, NULL as `properties`, NULL as `subject`, 1595433593448 as `updated_at` union all select 'plugin::upload.assets.download' as `action`, '[]' as `conditions`, 1595433593456 as `created_at`, NULL as `fields`, 20 as `id`, NULL as `properties`, NULL as `subject`, 1595433593460 as `updated_at` union all select 'plugin::upload.assets.copy-link' as `action`, '[]' as `conditions`, 1595433593469 as `created_at`, NULL as `fields`, 21 as `id`, NULL as `properties`, NULL as `subject`, 1595433593474 as `updated_at` union all select 'plugin::content-manager.explorer.delete' as `action`, '["admin::is-creator"]' as `conditions`, 1595433593646 as `created_at`, NULL as `fields`, 36 as `id`, NULL as `properties`, 'api::restaurant.restaurant' as `subject`, 1595433593651 as `updated_at` union all select 'plugin::upload.read' as `action`, '["admin::is-creator"]' as `conditions`, 1595433593671 as `created_at`, NULL as `fields`, 38 as `id`, NULL as `properties`, NULL as `subject`, 1595433593674 as `updated_at` union all select 'plugin::upload.assets.create' as `action`, '[]' as `conditions`, 1595433593681 as `created_at`, NULL as `fields`, 39 as `id`, NULL as `properties`, NULL as `subject`, 1595433593685 as `updated_at` union all select 'plugin::upload.assets.update' as `action`, '["admin::is-creator"]' as `conditions`, 1595433593691 as `created_at`, NULL as `fields`, 40 as `id`, NULL as `properties`, NULL as `subject`, 1595433593696 as `updated_at` union all select 'plugin::upload.assets.download' as `action`, '[]' as `conditions`, 1595433593703 as `created_at`, NULL as `fields`, 41 as `id`, NULL as `properties`, NULL as `subject`, 1595433593707 as `updated_at` union all select 'plugin::upload.assets.copy-link' as `action`, '[]' as `conditions`, 1595433593714 as `created_at`, NULL as `fields`, 42 as `id`, NULL as `properties`, NULL as `subject`, 1595433593718 as `updated_at` union all select 'plugin::content-manager.explorer.create' as `action`, '[]' as `conditions`, 1595433593750 as `created_at`, '["author","review"]' as `fields`, 44 as `id`, NULL as `properties`, 'api::like.like' as `subject`, 1595433593754 as `updated_at` union all select 'plugin::content-manager.explorer.create' as `action`, '[]' as `conditions`, 1595433593774 as `created_at`, '["content","note","author","likes","restaurant"]' as `fields`, 46 as `id`, NULL as `properties`, 'api::review.review' as `subject`, 1595433593778 as `updated_at` union all select 'plugin::content-manager.explorer.create' as `action`, '[]' as `conditions`, 1595433593786 as `created_at`, '["username","email","provider","password","resetPasswordToken","confirmed","blocked","role","reviews","likes","picture"]' as `fields`, 47 as `id`, NULL as `properties`, 'plugin::users-permissions.user' as `subject`, 1595433593790 as `updated_at` union all select 'plugin::content-manager.explorer.read' as `action`, '[]' as `conditions`, 1595433593812 as `created_at`, '["author","review"]' as `fields`, 49 as `id`, NULL as `properties`, 'api::like.like' as `subject`, 1595433593817 as `updated_at` union all select 'plugin::content-manager.explorer.read' as `action`, '[]' as `conditions`, 1595433593836 as `created_at`, '["content","note","author","likes","restaurant"]' as `fields`, 51 as `id`, NULL as `properties`, 'api::review.review' as `subject`, 1595433593839 as `updated_at` union all select 'plugin::content-manager.explorer.read' as `action`, '[]' as `conditions`, 1595433593847 as `created_at`, '["username","email","provider","password","resetPasswordToken","confirmed","blocked","role","reviews","likes","picture"]' as `fields`, 52 as `id`, NULL as `properties`, 'plugin::users-permissions.user' as `subject`, 1595433593852 as `updated_at` union all select 'plugin::content-manager.explorer.update' as `action`, '[]' as `conditions`, 1595433593871 as `created_at`, '["author","review"]' as `fields`, 54 as `id`, NULL as `properties`, 'api::like.like' as `subject`, 1595433593876 as `updated_at` union all select 'plugin::content-manager.explorer.update' as `action`, '[]' as `conditions`, 1595433593896 as `created_at`, '["content","note","author","likes","restaurant"]' as `fields`, 56 as `id`, NULL as `properties`, 'api::review.review' as `subject`, 1595433593900 as `updated_at` union all select 'plugin::content-manager.explorer.update' as `action`, '[]' as `conditions`, 1595433593908 as `created_at`, '["username","email","provider","password","resetPasswordToken","confirmed","blocked","role","reviews","likes","picture"]' as `fields`, 57 as `id`, NULL as `properties`, 'plugin::users-permissions.user' as `subject`, 1595433593913 as `updated_at` union all select 'plugin::content-manager.explorer.delete' as `action`, '[]' as `conditions`, 1595433593920 as `created_at`, NULL as `fields`, 58 as `id`, NULL as `properties`, 'api::category.category' as `subject`, 1595433593925 as `updated_at` union all select 'plugin::content-manager.explorer.delete' as `action`, '[]' as `conditions`, 1595433593933 as `created_at`, NULL as `fields`, 59 as `id`, NULL as `properties`, 'api::like.like' as `subject`, 1595433593937 as `updated_at` union all select 'plugin::content-manager.explorer.delete' as `action`, '[]' as `conditions`, 1595433593946 as `created_at`, NULL as `fields`, 60 as `id`, NULL as `properties`, 'api::restaurant.restaurant' as `subject`, 1595433593950 as `updated_at` union all select 'plugin::content-manager.explorer.delete' as `action`, '[]' as `conditions`, 1595433593958 as `created_at`, NULL as `fields`, 61 as `id`, NULL as `properties`, 'api::review.review' as `subject`, 1595433593963 as `updated_at` union all select 'plugin::content-manager.explorer.delete' as `action`, '[]' as `conditions`, 1595433593973 as `created_at`, NULL as `fields`, 62 as `id`, NULL as `properties`, 'plugin::users-permissions.user' as `subject`, 1595433593977 as `updated_at` union all select 'plugin::content-type-builder.read' as `action`, '[]' as `conditions`, 1595433593985 as `created_at`, NULL as `fields`, 63 as `id`, NULL as `properties`, NULL as `subject`, 1595433593990 as `updated_at` union all select 'plugin::upload.read' as `action`, '[]' as `conditions`, 1595433594038 as `created_at`, NULL as `fields`, 67 as `id`, NULL as `properties`, NULL as `subject`, 1595433594043 as `updated_at` union all select 'plugin::upload.assets.create' as `action`, '[]' as `conditions`, 1595433594051 as `created_at`, NULL as `fields`, 68 as `id`, NULL as `properties`, NULL as `subject`, 1595433594056 as `updated_at` union all select 'plugin::upload.assets.update' as `action`, '[]' as `conditions`, 1595433594063 as `created_at`, NULL as `fields`, 69 as `id`, NULL as `properties`, NULL as `subject`, 1595433594067 as `updated_at` union all select 'plugin::upload.assets.download' as `action`, '[]' as `conditions`, 1595433594076 as `created_at`, NULL as `fields`, 70 as `id`, NULL as `properties`, NULL as `subject`, 1595433594080 as `updated_at` union all select 'plugin::upload.assets.copy-link' as `action`, '[]' as `conditions`, 1595433594088 as `created_at`, NULL as `fields`, 71 as `id`, NULL as `properties`, NULL as `subject`, 1595433594093 as `updated_at` union all select 'plugin::upload.settings.read' as `action`, '[]' as `conditions`, 1595433594100 as `created_at`, NULL as `fields`, 72 as `id`, NULL as `properties`, NULL as `subject`, 1595433594105 as `updated_at` union all select 'plugin::content-manager.single-types.configure-view' as `action`, '[]' as `conditions`, 1595433594112 as `created_at`, NULL as `fields`, 73 as `id`, NULL as `properties`, NULL as `subject`, 1595433594116 as `updated_at` union all select 'plugin::content-manager.collection-types.configure-view' as `action`, '[]' as `conditions`, 1595433594123 as `created_at`, NULL as `fields`, 74 as `id`, NULL as `properties`, NULL as `subject`, 1595433594127 as `updated_at` union all select 'plugin::content-manager.components.configure-layout' as `action`, '[]' as `conditions`, 1595433594134 as `created_at`, NULL as `fields`, 75 as `id`, NULL as `properties`, NULL as `subject`, 1595433594138 as `updated_at` union all select 'plugin::users-permissionss.roles.create' as `action`, '[]' as `conditions`, 1595433594145 as `created_at`, NULL as `fields`, 76 as `id`, NULL as `properties`, NULL as `subject`, 1595433594149 as `updated_at` union all select 'plugin::users-permissionss.roles.read' as `action`, '[]' as `conditions`, 1595433594156 as `created_at`, NULL as `fields`, 77 as `id`, NULL as `properties`, NULL as `subject`, 1595433594160 as `updated_at` - SQLITE_ERROR: table admin_permissions has no column named fields] {
  errno: 1,
  code: 'SQLITE_ERROR'
}

Looking at previous issues which provide a similar error to this, people have recommended to open the target old_data.db file and drop the "fields" column in the V3 "strapi_permission" table. Doing so will allow the migration to run its course and "successfully" complete. However, if I then try to run "npm run develop", I get this:

[2023-01-05 16:45:11.427] debug: ⛔️ Server wasn't able to start properly.
[2023-01-05 16:45:11.428] error: Cannot read properties of null (reading 'fields')
TypeError: Cannot read properties of null (reading 'fields')
    at Array.<anonymous> (C:\MYOXYGEN-REPOS\PCC Backend V4\demomigration\node_modules\@strapi\admin\server\services\content-type.js:167:21)    
    at Array.map (<anonymous>)
    at Object.cleanPermissionFields (C:\MYOXYGEN-REPOS\PCC Backend V4\demomigration\node_modules\@strapi\admin\server\services\content-type.js:163:22)
    at Object.cleanPermissionsInDatabase (C:\MYOXYGEN-REPOS\PCC Backend V4\demomigration\node_modules\@strapi\admin\server\services\permission\queries.js:163:26)
    at async module.exports (C:\MYOXYGEN-REPOS\PCC Backend V4\demomigration\node_modules\@strapi\admin\server\bootstrap.js:88:3)
    at async Strapi.runLifecyclesFunctions (C:\MYOXYGEN-REPOS\PCC Backend V4\demomigration\node_modules\@strapi\strapi\lib\Strapi.js:542:7)    
    at async Strapi.bootstrap (C:\MYOXYGEN-REPOS\PCC Backend V4\demomigration\node_modules\@strapi\strapi\lib\Strapi.js:469:5)
    at async Strapi.load (C:\MYOXYGEN-REPOS\PCC Backend V4\demomigration\node_modules\@strapi\strapi\lib\Strapi.js:478:5)
    at async Strapi.start (C:\MYOXYGEN-REPOS\PCC Backend V4\demomigration\node_modules\@strapi\strapi\lib\Strapi.js:212:9)

Steps to reproduce the behavior

1. Clone Strapi's v3 migration example: https://github.com/PaulBratslavsky/demoMigration (I cloned the following commit: a0a859fed0b628305f091f39c51beb5fda34b82a)
2. Seed the data using the script within the repo. For the seeder to run properly, you may need to apply my fix that I've specified in an issue within the demo repo: yarn seed outputs "Failed to move C:\test\demoMigration\data\uploads to public\uploads" PaulBratslavsky/demoMigration#1 (comment)
3. Use codemods (I used npx when the npm package was at v1.3.0: https://www.npmjs.com/package/@strapi/codemods/v/1.3.0) and follow Strapi's migration walkthrough on YouTube (https://www.youtube.com/watch?v=NSvdQKVvV9k)
4. Clone the migration-scripts repo (I cloned at the following commit: d660a7e)
5. In the package.json of the v3-sql-v4-sql folder, you should specify "@vscode/sqlite3": "^5.1.2-vscode". I had to upgrade this package due to the following issue with this package: error D:\vscode-main\node_modules\@vscode\sqlite3: Command failed. microsoft/vscode#152839.
6. npm install the rest of the packages in v3-sql-v4-sql, as normal.
7. Ensure you have a copy of the seeded v3 database; I've called this old_data.db.
8. Ensure you also have a fresh v4 database (I've left this as the defaultly-named data.db); I didn't create any admin users prior to running the script.
9. In the v3-sql-v4-sql, copy the .env.sqlite.example file and fill out the required info, including where your v3 and v4 database file is located. Save this copied file as .env.
10. In the v3-sql-v4-sql folder, run npm run start.
11. You should now see the first error I previously pasted.
12. Open the v3 database in a DB editor of your choice. Drop the fields column within the strapi_permission column. Re-run the migration script with npm run start. It should now claim it's successfully completed.
13. Try to run the V4 Strapi application with the migrated V4 database via npm run develop. You should now see the second error I've pasted.

Expected behavior

The database successfully migrates, all permissions are preserved, and I can use Strapi V4 with my migrated data.

Screenshots

Above is a screenshot of node_modules@strapi\admin\server\services\content-type.js:167:21, where the error after "successfully migrating" occurs. I'd like to draw attention to properties: { fields }. I'm unsure how deleting the fields column works for other users which face a similar issue to this, as Strapi then looks for the missing column

More screenshots can be provided on request.

Code snippets

Not applicable, but can provide on-request.

Additional context

Not applicable, but can provide on-request.

[pele7208]

I had this issue. I was able to get past it by first upgrading to the latest version 3 of strapi first (3.6.11). Then migrate that db to version 4. I still ran into one issue here migration-scripts/v3-sql-v4-sql/migrate/migrateFiles.js:54:57. I just removed the offending code ( || related_type) for now and haven't had a chance to dig into yet as I want to do some smoke test to make sure it worth proceeding. I know I was frustrated by this so I hope this helps you and please share any findings. Thanks.

[drina-bitlogic]

I have exactly the same problem. Did you find any solution?

[alejandrogsk]

I'm using stapi and postgresql and have a similar issue, this is the message:
error: insert into "public"."admin_permissions" ("action", "conditions", "created_at", "fields", "id", "properties", "subject", "updated_at") values ($1, $2, $3, $4, $5, $6, $7, $8),($9, $10, $11, $12, $13, $14, $15, $16)

[pele7208]

Be sure to first upgrade to the latest version of 3 of strapi as I mentioned above. That was my issue. The incremental step created the missing column then I was able to upgrade to version 4.

[abanchev]

This will fix the issue by using either the properties column, and if it does not exist it will try to read the fields column from the old db

[Priya2994]

This will fix the issue by using either the properties column, and if it does not exist it will try to read the fields column from the old db

Hi @abanchev,

Can you let me know in which file do we need to do the above changes ?

[Priya2994]

Hi All,

Can anyone let me know how to solve this issue. I am getting this error after doing Strapi data migration from SQL v3 to SQL V4

[abanchev]

@Priya2994

Here is a patch

diff --git a/v3-sql-v4-sql/migrate/helpers/adminHelpers.js b/v3-sql-v4-sql/migrate/helpers/adminHelpers.js
index fce9d12..6a1d00c 100644
--- a/v3-sql-v4-sql/migrate/helpers/adminHelpers.js
+++ b/v3-sql-v4-sql/migrate/helpers/adminHelpers.js
@@ -47,11 +47,11 @@ async function migrateAdminPermissions() {
     const items = await dbV3(resolveSourceTableName(source))
       .limit(BATCH_SIZE)
       .offset(page * BATCH_SIZE);
-    const migratedItems = migrateItems(items, ({ role, ...item }) => ({
+    const migratedItems = migrateItems(items, ({ role, fields, properties, ...item }) => ({
       ...item,
       action: migrateUids(item.action),
       subject: migrateSubject(item.subject),
-      properties: migrateProperties(item.properties),
+      properties: migrateProperties(properties ?? { fields: JSON.parse(fields) }),
       conditions: isPGSQL ? JSON.stringify(item.conditions) : item.conditions,
     }));
     const roleLinks = items.map((item) => ({
     ```