ER_DUP_KEYNAME: Duplicate key name upload_folders_path_id_index

Questions and Answers Database-SQL
1
System Information
  • Strapi Version: 4.3.6
  • Operating System: linux, macOS
  • Database: MySQL
  • Node Version: 16
  • NPM Version: .15.0
  • Yarn Version: 1.22.17
[Strapi backend application error log]

[2022-08-31 15:21:26.287] debug: ⛔️ Server wasn't able to start properly.
[2022-08-31 15:21:26.288] error: alter table `upload_folders` add unique `upload_folders_path_id_index`(`path_id`) - ER_DUP_KEYNAME: Duplicate key name 'upload_folders_path_id_index'
Error: ER_DUP_KEYNAME: Duplicate key name 'upload_folders_path_id_index'
    at Query.Sequence._packetToError (/Users/Desktop/Project/mathflat/mathflat-headless-cms/node_modules/mysql/lib/protocol/sequences/Sequence.js:47:14)
    at Query.ErrorPacket (/Users/Desktop/Project/mathflat/mathflat-headless-cms/node_modules/mysql/lib/protocol/sequences/Query.js:79:18)
    at Protocol._parsePacket (/Users/Desktop/Project/mathflat/mathflat-headless-cms/node_modules/mysql/lib/protocol/Protocol.js:291:23)
    at Parser._parsePacket (/Users/Desktop/Project/mathflat/mathflat-headless-cms/node_modules/mysql/lib/protocol/Parser.js:433:10)

This happens irregularly but constantly when starting a strapi server or creating a new schema on several devices.
I’ve been solving this problem by running SQL script below.

set foreign_key_checks = 0;
drop table upload_folders;
drop table upload_folders_parent_links;
set foreign_key_checks  = 1;

This is not right solution obviously.
Help me figure out how to handle this issue.

2

@user19 I encountered this exact same issue when upgrading from Strapi version 4.2.3 to 4.3.8. The problem seems to occur consistently for any deployment when new schemas are added, causing a duplicated key error that prevents the server from starting properly. This issue appears to be related to how Strapi handles database migrations. Aside from the SQL script workaround mentioned in the thread, have you found a more effective and proper solution for resolving this issue? Thank you.

The following is my system information:

  • Strapi Version: v4.3.8
  • Operating System: linux, macOS
  • Database: MySQL
  • Node Version: v16.15.1
  • NPM Version: .v8.11.0