Error: Can't create table (errno: 121 "Duplicate key on write or update")

[ddorstijn]

const db = new Database(new MySQLConnector({...}));

class Family extends Model {
    static table = 'families';
    
    static fields = {
        id: { type: DataTypes.INTEGER, primaryKey: true, autoIncrement: true },
        name: DataTypes.STRING,
        occassions: DataTypes.INTEGER,
    }
  
    static defaults = {
        occassions: 0,
    }
  
    static members() {
        return this.hasMany(Member);
    }
  }
  
class Member extends Model {
    static table = 'members';
    
    static fields = {
        id: { type: DataTypes.INTEGER, primaryKey: true, autoIncrement: true },
        name: DataTypes.STRING,
        attends: DataTypes.INTEGER,
    }
  
    static defaults = {
        attends: 0,
    }
  
    static family() {
        return this.hasOne(Family);
    }
  }

  Relationships.belongsTo(Member, Family);
  db.link([Family, Member]);
  await db.sync();

The above code generates the following error message. Note: This only happens on the second run. The first time when no tables do exist everything works perfectly. It seems to be recreating the tables every time which results in an error. db.sync({drop: true}) also works but I don't want to lose my data every single time I restart my program.

error: Uncaught (in promise) Error: Can't create table `trouwerij`.`members` (errno: 121 "Duplicate key on write or update")
      throw new Error(error.message);
            ^
    at PoolConnection.nextPacket (https://deno.land/x/[email protected]/src/connection.ts:185:13)
    at async PoolConnection.execute (https://deno.land/x/[email protected]/src/connection.ts:263:21)
    at async https://deno.land/x/[email protected]/src/client.ts:97:14
    at async Client.useConnection (https://deno.land/x/[email protected]/src/client.ts:107:14)
    at async Client.execute (https://deno.land/x/[email protected]/src/client.ts:96:12)
    at async MySQLConnector.query (https://deno.land/x/[email protected]/lib/connectors/mysql-connector.ts:79:22)
    at async Database.query (https://deno.land/x/[email protected]/lib/database.ts:240:21)
    at async Function.createTable (https://deno.land/x/[email protected]/lib/model.ts:172:5)
    at async Database.sync (https://deno.land/x/[email protected]/lib/database.ts:210:7)

[marcelc63]

Hi, I'm experiencing the same problem here.

To my knowledge, this issue ticket has some information regarding this: https://github.com/eveningkid/denodb/issues/252

Apparently, DenoDB does not have auto-sync yet. Hence when doing db.sync() it attempts to create tables again unless you drop it. I found that for tables without relationships, DenoDB will try to skip the creation as it detects that the table already exists. Nevertheless, it is not ideal.

What I end up doing is, I comment out db.sync() after the first run of the app. However, this becomes a problem when I want to update the model or add a new model.

Curious about solutions from others as well regarding this.

[ddorstijn]

Thanks for your reply. That is pretty rough. I had hoped to use this for a small personal project but I really dislike the idea of manually having to comment my code out for things to not break. It seems extremely prone to error.

[eveningkid]

Thank you @marcelc63, that's exactly the situation we're in at the moment.

We agree, this is not ideal and hopefully @vmasdani will shortly come up with something.

@ddorstijn for the time being, you might want to use another library if you're short on time. This might be functional in a few weeks time, so that's up to you!

Good day :)

[marcelc63]

Thanks for the hard work, looking forward to the updates :)