sync() with new alter mode fails on second run when using CONSTRAINTS #7606
Comments
|
No idea if this is safe, but needed a quick fix for testing: function dropForeignKeyConstraints(database) {
//this is a hack for dev only!
//todo: check status of posted github issue, https://github.com/sequelize/sequelize/issues/7606
const queryInterface = database.getQueryInterface();
return queryInterface.showAllTables()
.then(tableNames => {
return Promise.all(tableNames.map(tableName => {
return queryInterface.showConstraint(tableName)
.then(constraints => {
return Promise.all(constraints.map(constraint => {
if (constraint.constraintType === 'FOREIGN KEY') {
return queryInterface.removeConstraint(tableName, constraint.constraintName);
}
}));
});
}));
})
.then(() => database);
}Run before sync to drop all foreign keys. BRUTAL! |
|
Any news on this? |
|
Any updates on this? |
|
This is a bit of a pain... is anyone working on a fix? |
|
Any news on this? |
|
@the solution would be to check for existing constraint as a method exists to do it. if (this.sequelize.options.dialect === 'sqlite') {
// sqlite needs some special treatment as it cannot change a column
return SQLiteQueryInterface.changeColumn.call(this, tableName, attributes, options);
} else {
/************************************************************
* HERE IS WHERE I CHECK FOR AN EXISTING CONSTRAINT
************************************************************/
if (attributes[attributeName].references) {
this.showConstraint(tableName, `${tableName}_${attributeName}_foreign_idx`).then((result) => {
if (result.length === 0) {
const query = this.QueryGenerator.attributesToSQL(attributes);
const sql = this.QueryGenerator.changeColumnQuery(tableName, query);
return this.sequelize.query(sql, options);
}
});
} else {
const query = this.QueryGenerator.attributesToSQL(attributes);
const sql = this.QueryGenerator.changeColumnQuery(tableName, query);
return this.sequelize.query(sql, options);
}
}It is not a long term solution as some code has been duplicated. |
|
@cyrille-gaucher I added the code written but it's still giving me the |
|
@joncorrin You're right. This fix is only checking foreign key constraints. It needs to be improved to get it work for any constraint. |
|
@cyrille-gaucher of course. I see now. Any idea on how I could get it to check for unique constraints? |
|
@joncorrin Could you provide an model example which fails please? Also, which version of sequelize are you using? |
|
Yes, here is my user model which has a unique constraint on the email attribute. It's causing the error anytime I had a new model with a relation to user or add to the user model. |
|
@cyrille-gaucher ^^^ |
|
@joncorrin I could not reproduce the error in your use case, I'm sorry. Maybe we are not using the same SQL engine (I'm using mysql). What are you using? |
|
Ah yes, @cyrille-gaucher , I am using Postgresql. This has been a reocurring issue for me anytime I want to use .sync() to alter a table related to or of the User Model. I appreciate your help and time! |
|
Each |
|
@joncorrin Same issue and I am using MySQL, I have a lot of sync issues. However my structure isn't as complex as yours. @skn3 development fix has temporarily solved most of my issues, have you tried his |
|
@miles-collier I haven't. He suggested not to use in production and I'll need to do altering in prod for the most part. I'm sticking to migrations unless someone is able to help out or this gets a fix. I'm in no place to implement this myself. |
|
@joncorrin Automatic alterations to production aren't recommended anyways. from the docs: Lots of other sources claim heavy risk for using these alterations, it's still in it's infancy. |
|
Maybe there's something a bit less brutal to do. Ignoring what I've written above, the following would work for all dialect I think (still starting line 556): if (this.sequelize.options.dialect === 'sqlite') {
// sqlite needs some special treatment as it cannot change a column
return SQLiteQueryInterface.changeColumn.call(this, tableName, attributes, options);
} else {
this.showConstraint(tableName).then((results) => {
const query = this.QueryGenerator.attributesToSQL(attributes);
const sql = this.QueryGenerator.changeColumnQuery(tableName, query);
const constraintName = this.QueryGenerator.generateConstraintName(tableName, query[attributeName], attributeName);
if (constraintName) {
// Constraint index
const index = results.findIndex(result => result.constraintName === constraintName);
if (index !== -1) {
return;
}
}
return this.sequelize.query(sql, options);
});
}Then each MYSQL (tested) / MSSQL (not tested): generateConstraintName(tableName, query, attributeName) {
if (query.match(/REFERENCES/)) {
return tableName + '_' + attributeName + '_foreign_idx';
}
return null;
}POSTGRESQL (not tested): generateConstraintName(tableName, query, attributeName) {
if (query.match(/UNIQUE;*$/)) {
return attributeName + '_unique_idx'
}
if (query.match(/REFERENCES/)) {
return attributeName + '_foreign_idx'
}
} |
It worked kind of. I received this error multiple times before the server started up. But checking my db it seemed to have worked anyways. Did you mean to use |
|
@joncorrin Yes, my bad. Updated! |
|
is there solution to this yet |
|
+1 |
|
Fixed by bb2d0bd |
|
@sushantdhiman I don't think it works for unique constraints 😕 |
|
Hello Could you reopen this, I am using sequelize version 4.33.4, and I still have the issue. 'use strict';
module.exports = (sequelize, DataTypes) => {
var Task = sequelize.define('Task', {
title: DataTypes.STRING,
});
Task.associate = function (models) {
models.Task.belongsTo(models.User, {
onDelete: "CASCADE",
foreignKey: {
allowNull: false
}
});
};
return Task;
};'use strict';
module.exports = (sequelize, DataTypes) => {
var User = sequelize.define('User', {
username: DataTypes.STRING,
email: DataTypes.STRING
});
User.associate = function(models) {
models.User.hasMany(models.Task);
};
return User;
}; |
|
just a note, you also encounter this error if sync is being called twice. In my project it was called in index.js in models and then in app.js |
|
why is it closed? how to fix it? i tried on sequelize 3.32.1 , 3.27 - nothing works! =( |
|
I think the issue is still open. I have long since given up on alter mode, though, and just adopted migrations. Migrations are the only way to go once you are in production, IMHO. |
|
@radcapitalist I run alter as a step before deployment and it was fixed for me with this ticket. |
|
+1 |
|
+1 |
1 similar comment
|
+1 |
|
Workaround that I found: const Voucher = sequelize.define('Voucher', {
code: {
type: DataTypes.STRING
},
receiver_name: {
type: DataTypes.STRING,
},
}, {
indexes: [
{
unique: true,
fields: ['code']
}
]
});Don't know why, but it works with |
|
+1 |
|
I had a similar problem. My solution was setting names to index. Probably the autogenerated name was too long. An autogenerated name is built from fields's names. After setting the name all work fine |
|
Yes, thank you! My index name was exactly 1 character too long. I wondered why the error message cut of the last character. Now i know why. |
|
Hello everyone, I would like to ask a favor - This issue is old and has a lot of discussion and clutter, can someone please open a new issue summarizing the problem that still exists, providing an SSCCE, and linking to this issue? This will let me and others tackle the issue further. |
papb
added
the
status: awaiting response
|
Still experiencing this issue - A fix would be really important! |
Can confirm that this still causes the issue. |
|
I already had a table and I used alter so that the data is not gone. Then I created a second model and when I synced sequelize only picked the previous model. The second model was ignored. I changed it to force but still ignores the second model. |
github-actions
bot
removed
the
status: awaiting response
This Works for me 🙌 |
and others
What you are doing?
What do you expect to happen?
Being able to run the example multiple times without fail
What is actually happening?
On second run, an error occurs:
Dialect: postgres
Database version: 9.6.2
Sequelize version: git master
The text was updated successfully, but these errors were encountered: