Specified key was too long; max key length is 767 bytes using knex

Question

I've created the following transaction:

exports.up = async (knex, Promise) => (await knex.schema.hasTable('settings'))
    ? null
    : knex.schema.createTable('settings', function (table) {
        table.increments('id')
        table.string('name').notNullable().unique()
        table.string('value').notNullable()

        table.primary('name')
    }).then(() => {
        return knex('settings').insert([
            { name: 'sandbox', value: 'off'},
            { name: 'promo', value: 'off'}
        ]);
    });

exports.down = async (knex, Promise) => (await knex.schema.hasTable('settings'))
    ? knex.schema.dropTable('settings')
    : null

essentially I'm trying to create a settings table which define two primary keys, an id and a name. The problem is that I get this error:

migration failed with error: alter table settings add unique settings_name_unique(name) - Specified key was too long; max key length is 767 bytes

how can I fix?

Answer 1

Specified key was too long; max key length is 767 bytes is coming from MySQL and is explained in this answer. The default length of a string in knex is 255. With four byte utf8mb4 characters this is 1,020 bytes. Yes, this is very annoying.

You can fix this by using the dynamic row format. This is the default for new tables since 5.7.9, so you might also look into upgrading MySQL.

Or you can set a lower max length for the string. You can do this by passing a length into string. With four byte characters that's 767 / 4 = 191 characters.

table.string('name', 191).notNullable().unique()