Skip to content

Error: Duplicate entry '0' for key 'PRIMARY' [SEQUELIZE-CLI] [MYSQL] #1525

New issue
New issue
Open
Open
Error: Duplicate entry '0' for key 'PRIMARY' [SEQUELIZE-CLI] [MYSQL]#1525
@eddrickk

Description

@eddrickk
eddrickk
opened on Dec 3, 2024

I just hosted a new application on cPanel. It was a nodejs app with sequelize-cli as backend, reactjs as frontend and mysql for the database. Also ran npm install, sequelize migration and seeders.

Every tables have auto-increment ID. Here I'm using "Assess_Users" table as the case.

Here is the migration file creating "Assess_Users" table :

async up(queryInterface, Sequelize) {
    await queryInterface.createTable('Assess_Users', {
      id: {
        allowNull: false,
        autoIncrement: true,
        primaryKey: true,
        type: Sequelize.INTEGER
      },
      username: {
        type: Sequelize.STRING,
        unique: true,
        allowNull: false,
      },
      password: {
        type: Sequelize.STRING,
        allowNull: false,
      },
      id_employee: {
        type: Sequelize.STRING,
        unique: true,
      },
      nama: {
        type: Sequelize.STRING
      },
      inisial: {
        type: Sequelize.STRING
      },
      role: {
        type: Sequelize.STRING
      },
      jenis_kelamin: {
        type: Sequelize.STRING
      },
      tanggal_lahir: {
        type: Sequelize.DATEONLY
      },
      pendidikan_terakhir: {
        type: Sequelize.STRING
      },
      tanggal_bergabung: {
        type: Sequelize.DATEONLY
      },
      unit_usaha: {
        type: Sequelize.STRING
      },
      jabatan: {
        type: Sequelize.STRING
      },
      level_jabatan: {
        type: Sequelize.STRING
      },
      total_assessment: {
        type: Sequelize.INTEGER,
        defaultValue: 0,
      },
      createdAt: {
        allowNull: false,
        type: Sequelize.DATE
      },
      updatedAt: {
        allowNull: false,
        type: Sequelize.DATE
      }
    });
  },

Since I migrated a seeder which contains one row of "Assess_Users" data, the table right now contains one row of data with id = 1.

Next thing I do was to upload new rows from the app in csv file, but it got error on the auto-increment as it doesn't adding value to the id, instead it keep stored as id = 0 and caused "Duplicate entry '0' for key 'PRIMARY'" error.

This is what it look like (the id stuck in 0) :
This is what it look like (the id stuck in 0)

And here is the error message :

node:internal/process/promises:288
triggerUncaughtException(err, true /\* fromPromise \*/);
^

Error
at Query.run (/home/portalpe/nodevenv/apihrdcdn/18/lib/node_modules/sequelize/lib/dialects/mysql/query.js:52:25)
at /home/portalpe/nodevenv/apihrdcdn/18/lib/node_modules/sequelize/lib/sequelize.js:315:28
at process.processTicksAndRejections (node:internal/process/task_queues:95:5)
at async MySQLQueryInterface.insert (/home/portalpe/nodevenv/apihrdcdn/18/lib/node_modules/sequelize/lib/dialects/abstract/query-interface.js:308:21)
at async Assess_User.save (/home/portalpe/nodevenv/apihrdcdn/18/lib/node_modules/sequelize/lib/model.js:2490:35)
at async Assess_User.create (/home/portalpe/nodevenv/apihrdcdn/18/lib/node_modules/sequelize/lib/model.js:1362:12)
at async CsvParserStream.\<anonymous\> (/home/portalpe/apihrdcdn/src/controllers/uploadController.js:60:15) {
name: 'SequelizeUniqueConstraintError',
errors: \[
ValidationErrorItem {
message: 'PRIMARY must be unique',
type: 'unique violation',
path: 'PRIMARY',
value: '0',
origin: 'DB',
instance: Assess_User {
dataValues: {
total_assessment: 0,
id: null,
username: 'abcdefgh',
password: '$2a$08$BlFyfDLQkaz5T4BCf11qDe13.XHzrjxADykYaISuId.TDe6sXMS.',
nama: 'xxx',
id_employee: '123456',
inisial: 'HTA',
role: 'Karyawan',
jenis_kelamin: 'Laki-laki',
tanggal_lahir: '2020-10-19',
pendidikan_terakhir: 'S1',
tanggal_bergabung: '2001-08-20',
unit_usaha: 'ho. iat',
jabatan: 'internal auditor',
level_jabatan: 'Officer',
email: '[email protected]',
atasan: 'ijklmnop',
jurusan_pendidikan: 'IPA',
updatedAt: 2024-11-25T03:47:31.852Z,
createdAt: 2024-11-25T03:47:31.852Z
},
\_previousDataValues: {
username: undefined,
password: undefined,
nama: undefined,
id_employee: undefined,
inisial: undefined,
role: undefined,
jenis_kelamin: undefined,
tanggal_lahir: undefined,
pendidikan_terakhir: undefined,
tanggal_bergabung: undefined,
unit_usaha: undefined,
jabatan: undefined,
level_jabatan: undefined,
email: undefined,
atasan: undefined,
jurusan_pendidikan: undefined
},
uniqno: 1,
\_changed: Set(16) {
'username',
'password',
'nama',
'id_employee',
'inisial',
'role',
'jenis_kelamin',
'tanggal_lahir',
'pendidikan_terakhir',
'tanggal_bergabung',
'unit_usaha',
'jabatan',
'level_jabatan',
'email',
'atasan',
'jurusan_pendidikan'
},
\_options: {
isNewRecord: true,
\_schema: null,
\_schemaDelimiter: '',
attributes: undefined,
include: undefined,
raw: undefined,
silent: undefined
},
isNewRecord: true
},
validatorKey: 'not_unique',
validatorName: null,
validatorArgs: \[\]
}
\],
parent: Error: Duplicate entry '0' for key 'PRIMARY'
at Packet.asError (/home/portalpe/nodevenv/apihrdcdn/18/lib/node_modules/mysql2/lib/packets/packet.js:738:17)
at Execute.execute (/home/portalpe/nodevenv/apihrdcdn/18/lib/node_modules/mysql2/lib/commands/command.js:29:26)
at Connection.handlePacket (/home/portalpe/nodevenv/apihrdcdn/18/lib/node_modules/mysql2/lib/connection.js:481:34)
at PacketParser.onPacket (/home/portalpe/nodevenv/apihrdcdn/18/lib/node_modules/mysql2/lib/connection.js:97:12)
at PacketParser.executeStart (/home/portalpe/nodevenv/apihrdcdn/18/lib/node_modules/mysql2/lib/packet_parser.js:75:16)
at Socket.\<anonymous\> (/home/portalpe/nodevenv/apihrdcdn/18/lib/node_modules/mysql2/lib/connection.js:104:25)
at Socket.emit (node:events:517:28)
at addChunk (node:internal/streams/readable:368:12)
at readableAddChunk (node:internal/streams/readable:341:9)
at Readable.push (node:internal/streams/readable:278:10) {
code: 'ER_DUP_ENTRY',
errno: 1062,
sqlState: '23000',
sqlMessage: "Duplicate entry '0' for key 'PRIMARY'",
sql: 'INSERT INTO `Assess_Users` (`id`,`username`,`password`,`id_employee`,`nama`,`inisial`,`role`,`jenis_kelamin`,`tanggal_lahir`,`pendidikan_terakhir`,`tanggal_bergabung`,`unit_usaha`,`jabatan`,`level_jabatan`,`total_assessment`,`email`,`atasan`,`jurusan_pendidikan`,`createdAt`,`updatedAt`) VALUES (DEFAULT,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);',
parameters: \[
'abcdefgh',
'$2a$08$BlFyfDLQkaz5T4BCf11qDe13.XHzrjxADykYaISuId.TDe6sXMS.',
'123456',
'xxx',
'HTA',
'Karyawan',
'Laki-laki',
'2020-10-19',
'S1',
'2001-08-20',
'ho. iat',
'internal auditor',
'Officer',
0,
'[email protected]',
'ijklmnop',
'IPA',
'2024-11-25 03:47:31',
'2024-11-25 03:47:31'
\]
},
original: Error: Duplicate entry '0' for key 'PRIMARY'
at Packet.asError (/home/portalpe/nodevenv/apihrdcdn/18/lib/node_modules/mysql2/lib/packets/packet.js:738:17)
at Execute.execute (/home/portalpe/nodevenv/apihrdcdn/18/lib/node_modules/mysql2/lib/commands/command.js:29:26)
at Connection.handlePacket (/home/portalpe/nodevenv/apihrdcdn/18/lib/node_modules/mysql2/lib/connection.js:481:34)
at PacketParser.onPacket (/home/portalpe/nodevenv/apihrdcdn/18/lib/node_modules/mysql2/lib/connection.js:97:12)
at PacketParser.executeStart (/home/portalpe/nodevenv/apihrdcdn/18/lib/node_modules/mysql2/lib/packet_parser.js:75:16)
at Socket.\<anonymous\> (/home/portalpe/nodevenv/apihrdcdn/18/lib/node_modules/mysql2/lib/connection.js:104:25)
at Socket.emit (node:events:517:28)
at addChunk (node:internal/streams/readable:368:12)
at readableAddChunk (node:internal/streams/readable:341:9)
at Readable.push (node:internal/streams/readable:278:10) {
code: 'ER_DUP_ENTRY',
errno: 1062,
sqlState: '23000',
sqlMessage: "Duplicate entry '0' for key 'PRIMARY'",
sql: 'INSERT INTO `Assess_Users` (`id`,`username`,`password`,`id_employee`,`nama`,`inisial`,`role`,`jenis_kelamin`,`tanggal_lahir`,`pendidikan_terakhir`,`tanggal_bergabung`,`unit_usaha`,`jabatan`,`level_jabatan`,`total_assessment`,`email`,`atasan`,`jurusan_pendidikan`,`createdAt`,`updatedAt`) VALUES (DEFAULT,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);',
parameters: \[
'abcdefgh',
'$2a$08$BlFyfDLQkaz5T4BCf11qDe13.XHzrjxADykYaISuId.TDe6sXMS.',
'123456',
'xxx',
'HTA',
'Karyawan',
'Laki-laki',
'2020-10-19',
'S1',
'2001-08-20',
'ho. iat',
'internal auditor',
'Officer',
0,
'[email protected]',
'ijklmnop',
'IPA',
'2024-11-25 03:47:31',
'2024-11-25 03:47:31'
\]
},
fields: { PRIMARY: '0' },
sql: 'INSERT INTO `Assess_Users` (`id`,`username`,`password`,`id_employee`,`nama`,`inisial`,`role`,`jenis_kelamin`,`tanggal_lahir`,`pendidikan_terakhir`,`tanggal_bergabung`,`unit_usaha`,`jabatan`,`level_jabatan`,`total_assessment`,`email`,`atasan`,`jurusan_pendidikan`,`createdAt`,`updatedAt`) VALUES (DEFAULT,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);'
}

Here what I have done so far :

  1. I tried to insert it manually from cPanel phpmyadmin and it worked for the id incremented by 1

  2. I also tried to reset the auto-increment value but it still does the same id = 0 when inserting data from the app.

  3. I managed to host the same app to another 2 servers I've got (as a test) and it worked perfectly (the auto-increment worked and no errors like written above)

This is what it supposed to look like (from the other servers that worked) :
This is what it supposed to look like (from the other servers that worked)

  1. I also managed to undo the migration and changed the database in sequelize config file yet it still not working

If this is a bug from sequelize, are there any ways possible to fix it?

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Type

    No type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions