ER_LOCK_DEADLOCK error on Strapi start

[sdgroot]

We're currently encountering a critical issue that prevents Strapi from starting at all, either with or without develop mode. We've enabled i18n in Strapi. This is the error that we're getting:

yarn run start
yarn run v1.22.19
$ strapi start
[2023-06-08 12:07:39.425] debug: ⛔️ Server wasn't able to start properly.
[2023-06-08 12:07:39.427] error: insert into `navigations_localizations_links` (`inv_navigation_id`, `navigation_id`, `navigation_order`) values (1, 2, 1), (3, 2, 2), (4, 2, 3), (5, 2, 4), (6, 2, 5) on duplicate key update `navigation_order` = values(`navigation_order`) - ER_LOCK_DEADLOCK: Deadlock found when trying to get lock; try restarting transaction
Error: ER_LOCK_DEADLOCK: Deadlock found when trying to get lock; try restarting transaction
    at Query.Sequence._packetToError (/strapi_path/node_modules/mysql/lib/protocol/sequences/Sequence.js:47:14)
    at Query.ErrorPacket (/strapi_path/node_modules/mysql/lib/protocol/sequences/Query.js:79:18)
    at Protocol._parsePacket (/strapi_path/node_modules/mysql/lib/protocol/Protocol.js:291:23)
    at Parser._parsePacket (/strapi_path/node_modules/mysql/lib/protocol/Parser.js:433:10)
    at Parser.write (/strapi_path/node_modules/mysql/lib/protocol/Parser.js:43:10)
    at Protocol.write (/strapi_path/node_modules/mysql/lib/protocol/Protocol.js:38:16)
    at Socket.<anonymous> (/strapi_path/node_modules/mysql/lib/Connection.js:88:28)
    at Socket.<anonymous> (/strapi_path/node_modules/mysql/lib/Connection.js:526:10)
    at Socket.emit (node:events:513:28)
    at Socket.emit (node:domain:489:12)
    --------------------
    at Protocol._enqueue (/strapi_path/node_modules/mysql/lib/protocol/Protocol.js:144:48)
    at Connection.query (/strapi_path/node_modules/mysql/lib/Connection.js:198:25)
    at /strapi_path/node_modules/knex/lib/dialects/mysql/index.js:132:18
    at new Promise (<anonymous>)
    at Client_MySQL._query (/strapi_path/node_modules/knex/lib/dialects/mysql/index.js:126:12)
    at executeQuery (/strapi_path/node_modules/knex/lib/execution/internal/query-executioner.js:37:17)
    at Client_MySQL.query (/strapi_path/node_modules/knex/lib/client.js:146:12)
    at /strapi_path/node_modules/knex/lib/execution/transaction.js:363:24
    at new Promise (<anonymous>)
    at Client_MySQL.trxClient.query (/strapi_path/node_modules/knex/lib/execution/transaction.js:358:12)
error Command failed with exit code 1.

I'd also like to know why these queries are executed on Strapi startup in the first place? Is this something that the plugin does, or is this executed by Strapi? Even when all navigations_ tables are empty there seems to be data inserted into these tables when Strapi starts.

Additional info: strapi-plugin-navigation v2.2.9 Strapi v4.11.0 (also fails in v4.10.x) MySQL v8.0.30 yarn v1.22.19 npm v9.7.1

[cyp3rius]

Investigating. But all operations are triggered by Strapi logic. Try to set plugin on the first place of config/plugins.js file like:

{
   navigation: {
      enabled: true,
   },
   // ...
}

[cyp3rius]

Similar issue #345

[sdgroot]

Investigating. But all operations are triggered by Strapi logic. Try to set plugin on the first place of config/plugins.js file like:

{
   navigation: {
      enabled: true,
   },
   // ...
}

Thanks for your quick reply. The navigation plugin is the only plugin listed in this file. Please find the contents of plugins.js attached.

plugin.js

```js // eslint-disable-next-line no-unused-vars module.exports = ({ env }) => ({ // navigation config; if fields are modified in strapi this file should be updated. table: strapi_core_store_settings key: plugin_navigation_config navigation: { enabled: true, config: { additionalFields: [ { 'name': 'intro', 'label': 'Intro', 'type': 'string', 'required': false, 'multi': false, 'options': [ ], 'enabled': true }, { 'name': 'description', 'label': 'Description', 'type': 'string', 'required': false, 'multi': false, 'options': [ ], 'enabled': true }, { 'name': 'url_overview_icon', 'label': 'Url overview Icon', 'type': 'string', 'required': false, 'multi': false, 'options': [ ], 'enabled': true }, { 'name': 'url_overview_image', 'label': 'Url overview image', 'type': 'string', 'required': false, 'multi': false, 'options': [ ], 'enabled': true }, { 'name': 'url_monthly_favorite_image', 'label': 'Url monthly favorite image', 'type': 'string', 'required': false, 'multi': false, 'options': [ ], 'enabled': true }, { 'name': 'slug_monthly_favorite_product', 'label': 'Slug monthly favorite product', 'type': 'string', 'required': false, 'multi': false, 'options': [ ], 'enabled': true } ], contentTypes: [ 'api::product.product' ], contentTypesNameFields: { 'default': ['title', 'subject', 'name'], 'api::product.product': ['name', 'slug','url_card_image', 'card_text'] }, contentTypesPopulate: { 'api::product.product': [ 'tags' ] }, allowedLevels: 2, gql: { 'navigationItemRelated': [ 'Product' ] }, i18nEnabled: true, slugify: {}, pruneObsoleteI18nNavigations: false, pathDefaultFields: { 'api::product.product': ['slug'] }, cascadeMenuAttached: true } } }); ```

[loevstroem]

Any update on this?

[daan-ef2]

Yes, this is still an issue.

[loevstroem]

Incase anyone needs a temporary fix you can add "patch-package" and apply patch https://gist.github.com/loevstroem/11f1ed30f8c676dff2ef2df42046401b

[cyp3rius]

@loevstroem may I ask you to create a PR with fix based on your snippet?

[daan-ef2]

There is more wrong at the navigationSetupStrategy. Its still trying to insert navigations that already exists other then the default locale. Trying to debug it now, thanks for pointing out the locaiton @loevstroem.

[loevstroem]

@loevstroem may I ask you to create a PR with fix based on your snippet?

Absolutely.. Will submit later today.

[loevstroem]

There is more wrong at the navigationSetupStrategy. Its still trying to insert navigations that already exists other then the default locale. Trying to debug it now, thanks for pointing out the locaiton @loevstroem.

It seems like a conditionrace in Strapi trying to insert the locales at the same time. The patch should fix it. If its the correct solution @cyp3rius should know 😄

[daan-ef2]

Tried your patch but it still fails at lline 64. If i add a condition in the CreateNavigation function, where i check if the slug already exists and update instead of creation it works. But its not the prettiest fix. If someone would help me beautify the code.

const createNavigation = ({ strapi, payload, populate }) => {
 const exists = strapi.query("plugin::navigation.navigation").findOne({
    where: {
        slug: payload.slug,
      },
 })
if (!exists) {
   return strapi.query("plugin::navigation.navigation").create({
        data: {
          ...payload,
        },
        populate,
      });
}
return  strapi.query("plugin::navigation.navigation").update({
    data: {
      ...payload,
    },
    populate,
    where: {
      slug: payload.slug,
    },
  });
}

[cyp3rius]

Maybe something like that?

const createNavigation = async ({ strapi, payload, populate }) => {

  const where = {
    slug: payload.slug,
  };
  const data =  { ...payload };

  const exists = await strapi.query("plugin::navigation.navigation").findOne({ where  });
  if (!exists) {
     return strapi.query("plugin::navigation.navigation").create({
          data,
          populate,
        });
  }
return  strapi.query("plugin::navigation.navigation").update({
    data,
    populate,
    where,
  });
}

[cyp3rius]

Fixed in version v2.2.15