{alter: true} creates new index even if one already exists

[adrienbarreau]

What are you doing?

I use {alter: true} in dev mode to update the model.

sequelize
  .sync({ alter: true })
  .then(() => {
    console.error('Connected to db!');
    return Promise.resolve();
  })
  .catch(err => {
    console.error('Error connecting to db!');
    return Promise.reject(err);
  });

What do you expect to happen?

Update model and indexes

What is actually happening?

Every time the table is altered it adds new indexe.
For instance I now got 64 unique indexe on email. It should remove the indexe or at least do nothing if the constraint already exists.

Dialect: mysql
__Dialect version: mysql2 1.5.1
__Database version: Mysql 5.7
__Sequelize version: 4.32.2

[sushantdhiman]

Can you try with latest version. I added some tests, there were no duplicate indexes https://github.com/sequelize/sequelize/blob/master/test/integration/model/sync.test.js#L133-L209

[adrienbarreau]

I still have the issue with the latest version but I found the solution...

I used to declare indexes like that

const User = this.sequelize.define('testSync', {
            email: {
              type: Sequelize.STRING,
              unique: true
            },
          });

But when I declare them like that I do not have the issue anymore.

const User = this.sequelize.define('testSync', {
            email: {
              type: Sequelize.STRING
            },
          }, {
            indexes: [
              { fields: ['email'], unique: true }
            ]
          });

Thanks

[sushantdhiman]

So problem is still there when unique: true is defined in attribute? odd, Will try adding more tests

[adrienbarreau]

yes exactly !

[binbashtv]

Confirmed that this is still an issue in 4.37.6, using the solution from @adrienbarreau still resolves the issue.

[AlexKvazos]

Still an issue in 5.21.11

[fabrykowski]

I'm using version 6.3.5 and seeing exactly the same behaviour.

[Felix-Franz]

Here is a workaround to delete new indices after calling sequelize.sync({alter: true});

Be careful, this will delete all indices of the current database that ends with a number after an underline character (e.g. index_1).

const rawTables = await this.sequelize.query("SHOW TABLES");
const tables = rawTables[0].map(i => i[Object.keys(rawTables[0][0])[0]])
for (const t of tables){
	const rawKeys = await this.sequelize.query(`SHOW INDEX FROM ${t}`);
	const keys = rawKeys[0].map(i => i["Key_name"]).filter(i => i.match(/[a-zA-Z]+_\d+/))
	for (const k of keys)
		await this.sequelize.query(`ALTER TABLE ${t} DROP INDEX ${k}`)
}

[dmytro-shchurov]

This issue still exists for mysql dialect in 2022. The code below fixes this bug for me: you have to specify an index name explicitly.

BadgeUser.init({
      name: { type: DataTypes.STRING(32), allowNull: false },
      description: { type: DataTypes.STRING(128), allowNull: false }
    }, {
      sequelize,
      indexes: [
        { fields: ['name'], name: 'UQ_BadgeUser_Name', unique: true }
      ]
    });
    ```

[Amasaabu]

I'm currently experiencing this, on version 6.35.2

[AviStudio]

I confirm that the problem still persists! "sequelize": "^6.37.3"