ER_DUP_KEYNAME: Duplicate key name

[jonathanroze]

Hi there,

I'm trying to use Feathers-Knex with Mysql.

I've run this cmd "feathers generate authentication"

After, i run the server, all it's good but if i restart it again i have this message :

Error updating users table { Error: ER_DUP_KEYNAME: Duplicate key name 'users_email_unique'
    at Query.Sequence._packetToError (/Users/jonathanroze/Documents/server/node_modules/mysql/lib/protocol/sequences/Sequence.js:52:14)
    at Query.ErrorPacket (/Users/jonathanroze/Documents/server/node_modules/mysql/lib/protocol/sequences/Query.js:77:18)
    at Protocol._parsePacket (/Users/jonathanroze/Documents/server/node_modules/mysql/lib/protocol/Protocol.js:280:23)
    at Parser.write (/Users/jonathanroze/Documents/server/node_modules/mysql/lib/protocol/Parser.js:75:12)
    at Protocol.write (/Users/jonathanroze/Documents/server/node_modules/mysql/lib/protocol/Protocol.js:39:16)
    at Socket.<anonymous> (/Users/jonathanroze/Documents/server/node_modules/mysql/lib/Connection.js:103:28)
    at emitOne (events.js:96:13)
    at Socket.emit (events.js:188:7)
    at readableAddChunk (_stream_readable.js:176:18)
    at Socket.Readable.push (_stream_readable.js:134:10)
    at TCP.onread (net.js:548:20)
    --------------------
    at Protocol._enqueue (/Users/jonathanroze/Documents/server/node_modules/mysql/lib/protocol/Protocol.js:141:48)
    at Connection.query (/Users/jonathanroze/Documents/server/node_modules/mysql/lib/Connection.js:208:25)
    at /Users/jonathanroze/Documents/server/node_modules/knex/lib/dialects/mysql/index.js:152:18
    at Promise._execute (/Users/jonathanroze/Documents/server/node_modules/bluebird/js/release/debuggability.js:300:9)
    at Promise._resolveFromExecutor (/Users/jonathanroze/Documents/server/node_modules/bluebird/js/release/promise.js:483:18)
    at new Promise (/Users/jonathanroze/Documents/server/node_modules/bluebird/js/release/promise.js:79:10)
    at Client_MySQL._query (/Users/jonathanroze/server/node_modules/knex/lib/dialects/mysql/index.js:146:12)
    at Client_MySQL.query (/Users/jonathanroze/Roadtrip/server/node_modules/knex/lib/client.js:197:17)
    at Runner.<anonymous> (/Users/jonathanroze/Documents/server/node_modules/knex/lib/runner.js:146:36)
    at Runner.tryCatcher (/Users/jonathanroze/Documents/server/node_modules/bluebird/js/release/util.js:16:23)
    at Runner.query (/Users/jonathanroze/Documents/server/node_modules/bluebird/js/release/method.js:15:34)
    at Runner.<anonymous> (/Users/jonathanroze/Documents/server/node_modules/knex/lib/runner.js:193:19)
    at Runner.tryCatcher (/Users/jonathanroze/Documents/server/node_modules/bluebird/js/release/util.js:16:23)
    at Object.gotValue (/Users/jonathanroze/Documents/server/node_modules/bluebird/js/release/reduce.js:157:18)
    at Object.gotAccum (/Users/jonathanroze/Documents/server/node_modules/bluebird/js/release/reduce.js:144:25)
    at Object.tryCatcher (/Users/jonathanroze/Documents/server/node_modules/bluebird/js/release/util.js:16:23)
  code: 'ER_DUP_KEYNAME',
  errno: 1061,
  sqlState: '42000',
  index: 0 }

Do you have any idea?

[daffl]

Can you try if this happens with SQLite as well? The model should be set up to only initialize the table if it doesn't exist.

[NiluK]

Haven't tried this with SQLlite, but I can confirm this errors with postgres as well.

Error updating users table { error: relation "users_email_unique" already exists
    at Connection.parseE (/Users/niluk/projects/rettai-server-2/node_modules/pg/lib/connection.js:569:11)
    at Connection.parseMessage (/Users/niluk/projects/rettai-server-2/node_modules/pg/lib/connection.js:396:17)
    at Socket.<anonymous> (/Users/niluk/projects/rettai-server-2/node_modules/pg/lib/connection.js:132:22)
    at emitOne (events.js:96:13)
    at Socket.emit (events.js:191:7)
    at readableAddChunk (_stream_readable.js:178:18)
    at Socket.Readable.push (_stream_readable.js:136:10)
    at TCP.onread (net.js:560:20)
  name: 'error',
  length: 100,
  severity: 'ERROR',
  code: '42P07',
  detail: undefined,
  hint: undefined,
  position: undefined,
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'index.c',
  line: '793',
  routine: 'index_create' }

I temporarily fixed this by doing this:

module.exports = function (app) {
  const db = app.get('knexClient');

  db.schema.hasTable('users').then(function(exists) {
    if (!exists) {
      return db.schema.createTable('users', table => {
        table.increments('id');
        table.string('email').unique();
        table.string('password');
        table.string('googleId');
        table.string('facebookId');
        table.string('githubId');
      });
    }
  })
  .then(() => console.log('Updated users table'))
  .catch(e => console.error('Error updating users table', e));
  return db;
};

I think this may be a breaking change in knex-0.13. Will investigate further.

[ForsakenHarmony]

This was fixed in the generator, so you can close this?

knex/knex#1303

[daffl]

Indeed, it should be. Closing.