findOrCreate and MySQL sql_mode NO_AUTO_VALUE_ON_ZERO

[riemannzeta]

Just a note to anybody else who ends up scratching their head on this one.

Was getting the following

ame: 'SequelizeUniqueConstraintError',
message: 'Validation error',
errors:
[ { message: 'PRIMARY must be unique',
type: 'unique violation',
path: 'PRIMARY',
value: '0' } ],
fields: { PRIMARY: '0' } }

DESPITE the fact that my table was setup with an auto_increment id field (normally this is the problem for most people).

Sequelize was auto-generating an INSERT query with the id field set to value DEFAULT. Apparently DEFAULT means 0 to MySQL, and 0 (when that sql_mode is on) does not trigger an autoincrement.

I understand that this setting is on for my server for security reasons, so not sure whether or not there is a change that could be implemented in the ORM to avoid this... like I said, just a heads up.

[mickhansen]

Ah, that's a curious case, we use DEFAULT so we don't have empty queries.

[janmeier]

Actually mysql does support empty values - But { id: null } is passed to the insert query, so the path for empty values is never triggered

sequelize/lib/dialects/abstract/query-generator.js

Line 214 in eabab6f

} else if (this._dialect.supports['VALUES ()']) {

[janmeier]

And the null value is added here:

sequelize/lib/instance.js

Line 36 in eabab6f

defaults[this.Model.primaryKeyAttribute] = null;

- To fix postgres which does not allow empty values. I think it might be better to fix that problem in the query generator, rather than in instance.js though

[magp3]

I also had this issue, took a while before I found out what was causing this!

[Hugh-wong]

Maybe you should mark this issue to the document.