Cannot drop index ‘ix_email...as_spam’ when updating from 10.24.5 -> 10.24.10

Problems and solutions
1

Hi,

we’re getting the following error when updating from 10.24.5 to 10.24.10:

SQL: [alter table psys_email_template_send_log drop index ix_email_template_send_log_template_marked_as_spam]. Error message: [Cannot drop index ‘ix_email_template_send_log_template_marked_as_spam’: needed in a foreign key constraint]. Error Detail.

Deleting the index manually doesn’t work either.

Any idea?

2

You should be able to drop it using set foreign_key_checks=0;

3

This is a tricky one - MySQL / MariaDB has assigned this index for use with a foreign key because marked_as_spam is made up of two fields, the first being a FK field. So rather than create a new FK index just for the fk field, the engine uses this existing index.

If @sacha 's solution does not work, you will need to drop the foreign key constraint that is blocking it (I believe it will be the “Template” one).

You should then recreate the FK after everything else has run.

4

The solution is as Dom said:

  • delete the FK
  • delete the index
  • recreate the FK

Unfortunately it doesn’t solve the issue we hoped it solves:

and