Index names are not handled gracefully when they are too long

[michaelstokes93]

Bug report

Required System information

• Node.js version: 14.18.2
• NPM version: 6.14.15
• Strapi version: 4.1.7
• Database: PostgreSQL
• Operating system: Ubuntu

Describe the bug

I have a Strapi version 3.6.9 project and have created a new Strapi project using 4.1.7 with the same collection-types and components. On this occasion, I decided not to migrate and to re-create manually as part of an exercise.

I am experiencing a create index already exists error when creating a one-to-one relational field on a component.

"reference_cell": {
      "type": "relation",
      "relation": "oneToOne",
      "target": "api::reference-cell.reference-cell"
    }

On doing so, I get the following error:

[
  {
    sql: 'create table "public"."components_readings_information_tp_informations_reference_cell_links" ("tp_information_id" integer null, "reference_cell_id" integer null)',
    bindings: []
  },
  {
    sql: 'create index "components_readings_information_tp_informations_reference_cell_links_fk" on "public"."components_readings_information_tp_informations_reference_cell_links" ("tp_information_id")',
    bindings: []
  },
  {
    sql: 'create index "components_readings_information_tp_informations_reference_cell_links_inv_fk" on "public"."components_readings_information_tp_informations_reference_cell_links" ("reference_cell_id")',
    bindings: []
  }
]
[2022-03-20 11:20:08.690] debug: ⛔️ Server wasn't able to start properly.
[2022-03-20 11:20:08.692] error: create index "components_readings_information_tp_informations_reference_cell_links_fk" on "public"."components_readings_information_tp_informations_reference_cell_links" ("tp_information_id") - relation "components_readings_information_tp_informations_reference_cell_" already exists
error: create index "components_readings_information_tp_informations_reference_cell_links_fk" on "public"."components_readings_information_tp_informations_reference_cell_links" ("tp_information_id") - relation "components_readings_information_tp_informations_reference_cell_" already exists

Please note that I have a one-to-one relational field on another component for a different collection-type of which is fault-type of which there is no error.

I have tried creating new databases with and without this field. Application runs fine without this additional field. Only seems to occur with this.

Steps to reproduce the behavior

1. Create one-to-one relational field on component
2. See create index ... already exists error

Expected behavior

Should be able to create one-to-one relational field on component like have done
with other component.

Screenshots

Code snippets

"reference_cell": {
      "type": "relation",
      "relation": "oneToOne",
      "target": "api::reference-cell.reference-cell"
    }

{
  "kind": "collectionType",
  "collectionName": "fault_types",
  "info": {
    "singularName": "fault-type",
    "pluralName": "fault-types",
    "displayName": "fault type"
  },
  "options": {
    "draftAndPublish": true
  },
  "pluginOptions": {},
  "attributes": {
    "name": {
      "type": "string"
    },
    "description": {
      "type": "text"
    }
  }
}

{
  "collectionName": "components_readings_information_faults",
  "info": {
    "displayName": "fault",
    "icon": "angle-down",
    "description": ""
  },
  "options": {},
  "attributes": {
    "fault_desc": {
      "type": "text"
    },
    "fault_type": {
      "type": "relation",
      "relation": "oneToOne",
      "target": "api::fault-type.fault-type"
    }
  }
}

Note that no errors occur when adding fault-type relational field to faults component.

Additional context

Sorry if this isn't a bug but I have tried many attempts to understand otherwise why this is occurring. I have also raised a question at https://forum.strapi.io/t/create-index-already-exists/16835

[strapi-bot]

This issue has been mentioned on Strapi Community Forum. There might be relevant details there:

https://forum.strapi.io/t/create-index-already-exists/16835/2

[DanielASMPT]

it's the same for me, if you find a solution, please let me know.

[peastuti]

Have you found a solution?

[pallxk]

Your collection name and field name combined is too long for postgres, you have to keep the generated identifies (components_readings_information_tp_informations_reference_cell_links, components_readings_information_tp_informations_reference_cell_links_fk, components_readings_information_tp_informations_reference_cell_links_inv_fk) <= 63 characters.

Strapi should deal with this gracefully or at least warn it though.

[peastuti]

I fixed it changing the collectionName after the generation performed by strapi:

"collectionName": "components_s_in_s_ins",

I don't know if it is the best solution, it should also be possible to extend the 63 characters in Postgres..

[kgrosvenor]

I fixed it changing the collectionName after the generation performed by strapi:

"collectionName": "components_s_in_s_ins",

I don't know if it is the best solution, it should also be possible to extend the 63 characters in Postgres..

Looks like a good solution, trying it out! Thanks for the info

[derrickmehaffy]

Closing in favor of tracking issue: #13117

[jonnow-libraryon]

I had this exact same issue, the solution was to shorten the component name, this removed the issue immediately.

For me it was a PostgreSQL generated issue - the name of the component that held the relationship was too long.

When Strapi builds the database it creates foreign keys by appending the component name to the collection type name.

An extreme example:
Component components_aggregators_sci_fi_movie_director_interviews references, with a one to many link, the collection interviews_with_sci_fi_directors.

At build this attempts to create a foreign key index by combining these together and adding _links_fk to give something along the lines of: components_aggregators_sci_fi_movie_director_interviewsinterviews_with_sci_fi_directors_links_fk
Which is 96 characters long. PostgreSQL only allows up to 63 characters (63 bytes) and then truncates the rest. So it ends up with components_aggregators_sci_fi_movie_director_interviewsintervie. If I understand it correctly, another index is also created for the primary key. Again trying to use the same combination and again resulting in the truncated version which is now a duplicate and throws the error:
create index "components_aggregators_sci_fi_movie_director_interviewsinterviews_with_sci_fi_directors_links_fk" on "components_aggregators_sci_fi_movie_director_interviewsinterviews_with_sci_fi_directors_links" ("components_aggregators_sci_fi_movie_director_interviews_id") - relation "components_aggregators_sci_fi_movie_director_interviewsintervie" already exists

I wasted many hours of debugging before discovering this. Shortening the component name removed the issue immediately.