Restarting sequelize app with indexes causes ER_DUP_KEYNAME

[mlegenhausen]

Example:

var Sequelize = require('sequelize');

var sequelize = new Sequelize('sequelize-test', 'root', '');

var Model = sequelize.define('Model', {
    key: Sequelize.STRING
}, {
    indexes: [
        {
            name: 'key-index',
            fields: ['key']
        }
    ]
});

sequelize.sync();

First start everything works fine. Starting the app again generates following error:

Executing (undefined): CREATE TABLE IF NOT EXISTS `Models` (`id` INTEGER NOT NULL auto_increment , `key` VARCHAR(255), `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB;
Executing (undefined): CREATE INDEX `key-index` ON `Models` (`key`)
Possibly unhandled Error: ER_DUP_KEYNAME: Duplicate key name 'key-index'

Tested with current master version.

[mickhansen]

ping @janmeier

Is there anything like CREATE INDEX IF NOT EXISTS?

I assume indexes are dropped with tables currently for force: true, is that also the case for postgres where indexes are a bit different from mysql?

[janmeier]

I don't think there is a 'CREATE INDEX IF NOT EXISTS' . We could still do show indexes and only create those which don't exist

[mlegenhausen]

Thanks works great :)

[stevewillard]

@janmeier Any chance you could rev a version of sequelize for this? I'm running into the same problem, but need a released version.

[mlegenhausen]

Running currently with the git version with no bugs at all. The sequelize codebase is really well tested :)

[mickhansen]

@stevewillard any practical reason you can't use a commit hash?
@mlegenhausen We hardly ever commit anything that actually breaks the suite, and if we do its usually fixed immediately. The API does change sometimes though ;p

[stevewillard]

No, I guess not. For some reason I forgot you can use a hash -- I was concerned that I'd have to use #latest or something. Thanks!

[corbanb]

I am not sure this is fully working. I still get errors if I force sync on tables and try to rebuild with the smae indexes as before.

{ [SequelizeDatabaseError: ERROR:  relation "users_" already exists
]
  name: 'SequelizeDatabaseError',
  message: 'ERROR:  relation "users_" already exists\n',
  parent:
   { [Error: ERROR:  relation "users_" already exists
   ]
     severity: 'ERROR',
     sqlState: '42P07',
     messagePrimary: 'relation "users_" already exists',
     sourceFile: 'index.c',
     sourceLine: '779',
     sourceFunction: 'index_create',
     sql: 'CREATE UNIQUE INDEX "users_" ON "Users" (lower("email"))' },
  original:
   { [Error: ERROR:  relation "users_" already exists
   ]
     severity: 'ERROR',
     sqlState: '42P07',
     messagePrimary: 'relation "users_" already exists',
     sourceFile: 'index.c',
     sourceLine: '779',
     sourceFunction: 'index_create',
     sql: 'CREATE UNIQUE INDEX "users_" ON "Users" (lower("email"))' },
  sql: 'CREATE UNIQUE INDEX "users_" ON "Users" (lower("email"))' }

Any ideas?

[janmeier]

Please show your model definitions @corbanb

[corbanb]

@janmeier here you go.

class User {
    constructor(sequelize, DataTypes) {
        this.DataTypes = DataTypes;
        this.sequelize = sequelize;
        return sequelize.define('User', this.attributes, this.options);
    }
    get attributes() {
        return {
            username: {
                type: this.DataTypes.STRING,
                allowNull: false,
            },
            email: {
                type: this.DataTypes.STRING,
                allowNull: false,
            },
            name: {
                type: this.DataTypes.STRING,
            },
            motto: {
                type: this.DataTypes.TEXT,
            },
            password: {
                type: this.DataTypes.VIRTUAL,
                validate: {
                    min: {
                        args: 5,
                        msg: 'Password must be at least 5 characters.'
                    },
                    max: {
                        args: 50,
                        msg: 'Password must be less than 50 characters.'
                    }
                },
                set(val) {
                    // hash password
                    var newVal = encrypt(val);
                    this.setDataValue('password_encrypted', newVal);
                }
            },
            password_encrypted: {
                type: this.DataTypes.STRING
            },
        };
    }
    get options() {
        return {
            timestamps: true, // INFO: true is default and keeps createdAt and updatedAt timestamps
            paranoid: true, // INFO: sets deletedAt and enables Model.restore()
            instanceMethods: {},
            classMethods: this.classMethods,
            defaultScope: {},
            scopes: {},
            getterMethods: {},
            setterMethods: {},
            hooks: {},
            indexes: this.indexes
        };
    }
    get classMethods() {
        return {
            associate(models) {
                // associations can be defined here
                // Adds UserId to Credential
                this.hasMany(models.UserIdentity, {
                    onDelete: 'cascade',
                    hooks: true
                });
            }
        };
    }
    get indexes(){
        return [
            { username: 'Users_username', unique: true, fields: [this.sequelize.fn('lower', this.sequelize.col('username'))] },
            { email: 'Users_email', unique: true, fields: [this.sequelize.fn('lower', this.sequelize.col('email'))] }
        ];
    }
}

module.exports = function(sequelize, DataTypes) {
    return new User(sequelize, DataTypes);
};

[corbanb]

@janmeier let me know if you need anything else here. I am a little stumped why this would be happening