How to port an extra column ("fields") from "strapi_permission" to "admin_permissions"

[radu-reason]

Bug report

Required System information

• Node.js version: v14.18.3
• NPM version: 0.34.0
• Source Strapi version: 3.6.3
• Target Strapi version: 4.1.12
• Source Database: PostgreSQL 12.9 (Ubuntu 12.9-0ubuntu0.20.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.3.0-17ubuntu1~20.04) 9.3.0, 64-bit
• Target Database: same as above
• Operating system: Linux Mint 20.2
• Which script are you running: v3-sql-v4-sql, pg

Describe the bug

I have an extra column on v3's strapi_permission table. At the moment I'm not sure if it's being used by the lib or the project. What would be the best way to port it to v4.

I get this error when trying to migrate SQL data, v3 to v4:

column "fields" of relation "admin_permissions" does not exist

Steps to reproduce the behavior

1.Follow the instruction from README file
2. See errors

Expected behavior

Not to have this error because afaik there isn't any fields column in v4's admin_permissions table.

Screenshots

n/a

Code snippets

Longer stack:

yarn start
yarn run v1.22.18
$ node index.js
Migrating Core Store TBA
Migrating 100/197 items from core_store to strapi_core_store_settings
core_store batch #1
core_store batch #2
Migrating Admin
Migrating 9 items from strapi_role to admin_roles
strapi_role batch #1
Migrating 109 items from strapi_administrator to admin_users
strapi_administrator batch #1
strapi_administrator batch #2
strapi_administrator batch #3
Migrating 109 items from strapi_users_roles to admin_users_roles_links
strapi_users_roles batch #1
strapi_users_roles batch #2
strapi_users_roles batch #3
Migrating 871 items from strapi_permission to admin_permissions
strapi_permission batch #1
(node:66281) UnhandledPromiseRejectionWarning: error: insert into "admin_permissions" ("action", "conditions", "created_at", "fields", "id", "properties", "subject", "updated_at") values ($1, $2, $3, $4, $5, $6, $7, $8),  ($393, $394, $395, $396, $397, $398, $399, $400) - column "fields" of relation "admin_permissions" does not exist
    at Parser.parseErrorMessage (strapi-migration-scripts/v3-sql-v4-sql/node_modules/pg-protocol/dist/parser.js:287:98)
    at Parser.handlePacket (strapi-migration-scripts/v3-sql-v4-sql/node_modules/pg-protocol/dist/parser.js:126:29)
    at Parser.parse (strapi-migration-scripts/v3-sql-v4-sql/node_modules/pg-protocol/dist/parser.js:39:38)
    at Socket.<anonymous> (strapi-migration-scripts/v3-sql-v4-sql/node_modules/pg-protocol/dist/index.js:11:42)
    at Socket.emit (events.js:400:28)
    at addChunk (internal/streams/readable.js:293:12)
    at readableAddChunk (internal/streams/readable.js:267:9)
    at Socket.Readable.push (internal/streams/readable.js:206:10)
    at TCP.onStreamRead (internal/stream_base_commons.js:188:23)
(Use `node --trace-warnings ...` to show where the warning was created)
(node:66281) UnhandledPromiseRejectionWarning: Unhandled promise rejection. This error originated either by throwing inside of an async function without a catch block, or by rejecting a promise which was not handled with .catch(). To terminate the node process on unhandled promise rejection, use the CLI flag `--unhandled-rejections=strict` (see https://nodejs.org/api/cli.html#cli_unhandled_rejections_mode). (rejection id: 2)
(node:66281) [DEP0018] DeprecationWarning: Unhandled promise rejections are deprecated. In the future, promise rejections that are not handled will terminate the Node.js process with a non-zero exit code.

Additional context

n/a

[Meljesson]

In your database, write a query that either drops the fields column in v3 or adds a fields column in v4

[derrickmehaffy]

Indeed, this looks like a customization that won't be covered by these scripts as it looks like you modified your admin permissions that we don't have normally.

If you aren't actively using this, you should drop it.

[derrickmehaffy]

With no new information I'll go ahead and mark this issue as closed

[derrickmehaffy]

Reopening because @PaulBratslavsky just hit the same issue and is sending me over his test project and test database.

[PritamSangani]

The "fields" column was renamed to "properties" it seems as part of the i18n feature (v3.6.0) back in April 2021. Any project created before this date will have this issue as the "fields" column was not removed as part of the data migration to the "properties" column.

[derrickmehaffy]

@martincapek this is one of those edge cases 😓 we prob should just add a quick check to ignore this one.

[martincapek]

It is possibly duplicate of #26 for own fields you have to create custom migration right now since #26 will be columns that are not generated in strapi v4 ignored.

[derrickmehaffy]

This -should- be fixed in #30 marking as closed unless someone hits the issue again.

[VincentDugard]

Hello i'm getting this error even with this MR merged

code: 'ER_BAD_FIELD_ERROR',
errno: 1054,
sqlMessage: "Unknown column 'fields' in 'field list'",
sqlState: '42S22',
index: 0,

[LilyLauben]

Also getting this error:

Migrating 70 items from strapi_permission to admin_permissions
strapi_permission batch #1
/srv/strapi/migration-scripts-main/v3-sql-v4-sql/node_modules/pg-protocol/dist/parser.js:287
        const message = name === 'notice' ? new messages_1.NoticeMessage(length, messageValue) : new messages_1.DatabaseError(messageValue, length, name);
                                                                                                 ^

error: insert into "admin_permissions" ("action", "conditions", "created_at", "fields", "id", "properties", "subject", "updated_at") values ($1, $2, $3...)  - column "fields" of relation "admin_permissions" does not exist

[derrickmehaffy]

@VincentDugard / @andresfelipe9619 / @LilyLauben from what I can see that column is no longer required. Can you please do the following:

• Take a database back (of course)
• Use a SQL CLI or GUI client to manually go in and delete that column
• Test migration again

The PR I mentioned should take care of this but to not hold you all back I want to see if manually cleaning would resolve the issue.

[LilyLauben]

After removing fields from the strapi_permission table in my v3 database, I was able to run the migration script with no error 👍

[andresfelipe9619]

@derrickmehaffy Thanks for the response.
So far no luck… I deleted the column and tested the script again, but this time is another issue.
UnhandledPromiseRejectionWarning: error: delete from "up_users_role_links" - relation "up_users_role_links" does not exist

[derrickmehaffy]

@derrickmehaffy Thanks for the response.
So far no luck… I deleted the column and tested the script again, but this time is another issue.
UnhandledPromiseRejectionWarning: error: delete from "up_users_role_links" - relation "up_users_role_links" does not exist

That sounds like you have some corruption in your permissions table, some permissions without roles they are linked to.

The script won't be able to handle that.

[derrickmehaffy]

Or sorry some users without roles or users with roles that don't exist anymore.

[andresfelipe9619]

@derrickmehaffy Thanks for the response, I will take a look tonight and let you know

[derrickmehaffy]

I was talking to another user that the issue was around the relation they had with users-permissions wasn't migrated properly at the code level, can you check your users-permissions schema file to see if the other side of the relation was correctly added?

[MotaZor94]

Hi everyone, I had this same issue and I deleted the fields column, but now after deleting it I get this error:

Migrating 229 items from strapi_permission to admin_permissions strapi_permission batch #1 (node:18128) UnhandledPromiseRejectionWarning: error: insert into "admin_permissions" ("action", "conditions", "created_at", "id", "properties", "subject", "updated_at") values ($1, $2, $3, $4, $5, $6, $7), ($8, $9, $10, $11, $12, $13, $14), ($15, $16, $17, $18, $19, $20, $21), ($22, $23, $24, $25, $26, $27, $28), ($29, $30, $31, $32, $33, $34, $35), ($36, $37, $38, $39, $40, $41, $42), ($43, $44, $45, $46, $47, $48, $49), ($50, $51, $52, $53, $54, $55, $56), ($57, $58, $59, $60, $61, $62, $63), ($64, $65, $66, $67, $68, $69, $70), ($71, $72, $73, $74, $75, $76, $77), ($78, $79, $80, $81, $82, $83, $84), ($85, $86, $87, $88, $89, $90, $91), ($92, $93, $94, $95, $96, $97, $98), ($99, $100, $101, $102, $103, $104, $105), ($106, $107, $108, $109, $110, $111, $112), ($113, $114, $115, $116, $117, $118, $119), ($120, $121, $122, $123, $124, $125, $126), ($127, $128, $129, $130, $131, $132, $133), ($134, $135, $136, $137, $138, $139, $140), ($141, $142, $143, $144, $145, $146, $147), ($148, $149, $150, $151, $152, $153, $154), ($155, $156, $157, $158, $159, $160, $161), ($162, $163, $164, $165, $166, $167, $168), ($169, $170, $171, $172, $173, $174, $175), ($176, $177, $178, $179, $180, $181, $182), ($183, $184, $185, $186, $187, $188, $189), ($190, $191, $192, $193, $194, $195, $196), ($197, $198, $199, $200, $201, $202, $203), ($204, $205, $206, $207, $208, $209, $210), ($211, $212, $213, $214, $215, $216, $217), ($218, $219, $220, $221, $222, $223, $224), ($225, $226, $227, $228, $229, $230, $231), ($232, $233, $234, $235, $236, $237, $238), ($239, $240, $241, $242, $243, $244, $245), ($246, $247, $248, $249, $250, $251, $252), ($253, $254, $255, $256, $257, $258, $259), ($260, $261, $262, $263, $264, $265, $266), ($267, $268, $269, $270, $271, $272, $273), ($274, $275, $276, $277, $278, $279, $280), ($281, $282, $283, $284, $285, $286, $287), ($288, $289, $290, $291, $292, $293, $294), ($295, $296, $297, $298, $299, $300, $301), ($302, $303, $304, $305, $306, $307, $308), ($309, $310, $311, $312, $313, $314, $315), ($316, $317, $318, $319, $320, $321, $322), ($323, $324, $325, $326, $327, $328, $329), ($330, $331, $332, $333, $334, $335, $336), ($337, $338, $339, $340, $341, $342, $343), ($344, $345, $346, $347, $348, $349, $350) - invalid input syntax for type json

Any idea how to debug this? Just to see where the issue is actually happening.

Also I wasn't able to set session_replication_role to replica; since both the v3 and V4 databases are running on Heroku, could this issue have something to do with that? I get this error:

UnhandledPromiseRejectionWarning: error: set session_replication_role to replica; - permission denied to set parameter "session_replication_role"

@derrickmehaffy I know the second part of my issue is unrelated here but do you have any idea how that could be sorted?

I've commented out
if (isPGSQL) { await dbV4.raw('set session_replication_role to replica;'); }
from index.js file and the issue is gone but I assume it could create some problems later on in the migration.

[MotaZor94]

After removing fields from the strapi_permission table in my v3 database, I was able to run the migration script with no error 👍

@LilyLauben Is your database hosted on Heroku by any chance?

[MotaZor94]

Hi @derrickmehaffy, looks like, after the latest merge, the conditions column in the admin_permissions table throws invalid input syntax for type JSON which I mentioned above, changing it back to
conditions: JSON.stringify(item.conditions),
fixed my issue.
However, I still have an issue with the set_session_replication role to replica with Heroku.

[andresfelipe9619]

@derrickmehaffy Still facing the issues, this is my schema:

Also, as pointed out from @MotaZor94, I've had to stringify properties & conditions.

[andresfelipe9619]

@derrickmehaffy These are my tables for strapi-permissions generated from v4, seems like up_users_role_links does not exist but users_permissions_user_role_links does exist, seems like a naming issue? Should I rename those tables, or am I missing something to properly migrate the table names?

Thanks!

[derrickmehaffy]

Hi @derrickmehaffy, looks like, after the latest merge, the conditions column in the admin_permissions table throws invalid input syntax for type JSON which I mentioned above, changing it back to conditions: JSON.stringify(item.conditions), fixed my issue. However, I still have an issue with the set_session_replication role to replica with Heroku.

Thanks @MotaZor94 , I've reverted the commit for now until we can dive deeper

[derrickmehaffy]

@derrickmehaffy These are my tables for strapi-permissions generated from v4, seems like up_users_role_links does not exist but users_permissions_user_role_links does exist, seems like a naming issue? Should I rename those tables, or am I missing something to properly migrate the table names?

Thanks!

Are you working on the same database? These migration scripts expect that you have two different databases, one for v3 and one for v4

[andresfelipe9619]

@derrickmehaffy No, I'm working with different database, the screenshot was for the v4 after running the api in develop mode with an empty db

[derrickmehaffy]

@derrickmehaffy No, I'm working with different database, the screenshot was for the v4 after running the api in develop mode with an empty db

Those users-permissions* tables should not exist as they aren't used in v4. We use the up_* versions now.

I can't seem to reproduce that logic during a test migration currently.

[andresfelipe9619]

@derrickmehaffy I can share my database for you to reproduce it if you want

[derrickmehaffy]

@derrickmehaffy I can share my database for you to reproduce it if you want

That would be great. Are you on our community Discord? If so PM me directly: dmehaffy#1337

[derrickmehaffy]

I believe this issue is related to strapi/codemods#48. If someone could check with this fix and double check your code-base to ensure you have the proper collectionName set on users-permissions.