v3Mongo->v3SQL - Default values not applied to tables when attributes undefined

Xander-SquareKicker commented 1 year ago

Bug report

This issue is only impactful because: The properties field in 'strapi_permission' is not-required, and has a default value of {}, so realistically everyone should have this in their MongoDB. However, some may have migrated from older versions, and only have the 'fields' property. This could also silently cause other issues with long-time mongo databases being migrated

Required System information

Node.js version: v14.21.1
NPM version: v9.1.2
Source Strapi version: 3.6.10
Target Strapi version: 3.6.10
Source Database: MongoDB
Target Database: PostgreSQL
Operating system: Mac
Which script are you running: Mongov3->SQLv3

Describe the bug

When an attribute that has a default value is undefined, and the migration assistant copies it to the SQL, it will be copied as NULL. Inside the SQL, there is no 'DEFAULT' specified for columns that have a 'default' value in the core_store entry

Steps to reproduce the behaviour

Go to your MongoDB (Strapi v3) database editor (MongoDB Compass)
Go to 'strapi_permission' collection
Choose an entry with a properties field, and delete that attribute.
Save/Replace/Update your changes on that entry
Run the migration assistant
Query SELECT * FROM strapi_permission WHERE properties IS NULL;
You will have at least 1 entry

Additional steps for why this impacts Strapi:

Run yarn install && yarn develop in your app directory
The run will fail & exit with code 1 - this is directly related to the 'properties' field being 'null' instead of '{}' (default value) Note: While this is unlikely, this could cause more hidden issues for other columns elsewhere that expect a default value.

Expected behaviour

Entries which have default values in core_store, will have their default values inserted into the SQL if it is otherwise left NULL during insertion.

Screenshots

Code snippets

Strapi_permission core_store structure - Note: relevant fields are 'attributes>properties|conditions', which has 'default: {}'

   "uid":"strapi::permission",
   "collectionName":"strapi_permission",
   "kind":"collectionType",
   "info":{
      "name":"Permission",
      "description":""
   },
   "options":{
      "timestamps":[
         "createdAt",
         "updatedAt"
      ]
   },
   "pluginOptions":{
      "content-manager":{
         "visible":false
      },
      "content-type-builder":{
         "visible":false
      }
   },
   "attributes":{
      "action":{
         "type":"string",
         "minLength":1,
         "configurable":false,
         "required":true
      },
      "subject":{
         "type":"string",
         "minLength":1,
         "configurable":false,
         "required":false
      },
      "properties":{
         "type":"json",
         "configurable":false,
         "required":false,
         "default":{

         }
      },
      "conditions":{
         "type":"json",
         "configurable":false,
         "required":false,
         "default":[

         ]
      },
      "role":{
         "configurable":false,
         "model":"role",
         "plugin":"admin"
      }
   }
}

Additional context

Note: This error only applies to long-term mongoDB users who's data has persisted long enough to not have a 'properties' field in strapi_permissions/have the old 'fields' column, and are attempting to migrate. This issue is for when an attribute does not have a 'non-required' attribute specified (that has a default value)

For long-time mongoDB users, they might have production databases without properties, or have the old 'fields' attribute. When the properties field in mongoDB is undefined, it is migrated as null to SQL. Null values properties cause 'Type Error: Cannot convert undefined or null to object' error in /Path/To/Strapi/app/node_modules/strapi-admin/services/role.js:51:17

Xander-SquareKicker commented 1 year ago

Patch for the specific 'properties is null' issue. Stacktrace: [2022-12-07T00:19:45.746Z] error bootstrap function in admin failed [2022-12-07T00:19:45.747Z] error TypeError: Cannot convert undefined or null to object at Function.entries (<anonymous>) at sortPermissionProperties (/PathToStrapi/app/node_modules/strapi-admin/services/role.js:51:17) at Array.map (<anonymous>) at arePermissionsEqual (/PathToStrapi/app/node_modules/strapi-admin/services/role.js:60:6) at f (/PathToStrapi/app/node_modules/lodash/lodash.min.js:10:238) at Hr (/PathToStrapi/app/node_modules/lodash/lodash.min.js:34:209) at /PathToStrapi/app/node_modules/lodash/lodash.min.js:117:39 at n (/PathToStrapi/app/node_modules/lodash/lodash.min.js:9:117) at /PathToStrapi/app/node_modules/lodash/lodash.min.js:73:58 at l (/PathToStrapi/app/node_modules/lodash/lodash.min.js:58:213) at assignPermissions (/PathToStrapi/app/node_modules/strapi-admin/services/role.js:355:28) at async Object.resetSuperAdminPermissions (/PathToStrapi/app/node_modules/strapi-admin/services/role.js:430:3) at async module.exports (/PathToStrapi/app/node_modules/strapi-admin/config/functions/bootstrap.js:51:3) at async Strapi.load (/PathToStrapi/app/node_modules/strapi/lib/Strapi.js:379:5) at async Strapi.start (/PathToStrapi/app/node_modules/strapi/lib/Strapi.js:196:9) error Command failed with exit code 1. Is to 1. Set the default value for the properties column to '{}', or 2. Manually run UPDATE strapi_permission SET properties = '{}' WHERE properties IS NULL;

Xander-SquareKicker commented 1 year ago

Providing a fix to this could introduce more issues than it solves, by abstracting the issues with the data away from the developer attempting to migrate

strapi / migration-scripts