Defining index for a model in @model decorator

[rkterungwa16]

To configure the @model decorator and define index for a model, the loopback4 documentation refers us to loopback3. In the loopback3 documentation, the recommended format for model configuration is as shown below:

@model({
  settings: {
    strict: 'filter',
    sqldb: {
      table: 'cashtoken_transaction',
    },
    indexes: {
      cashtoken_transaction_index: {
        keys: {
          internal_ref: 1,
          external_ref: 1
        },
        options: {
          unique: true
        },
      }
    }
  }
})

However this throws an error on creating a migration (see below). My datasource is SQL.

Cannot migrate database schema { Error: ER_TOO_LONG_KEY: Specified key was too long; max key length is 3072 bytes
   at Query.Sequence._packetToError (C:\Users\Supersoft\cashtoken-core\node_modules\mysql\lib\protocol\sequences\Sequence.js:47:14)
   at Query.ErrorPacket (C:\Users\Supersoft\cashtoken-core\node_modules\mysql\lib\protocol\sequences\Query.js:77:18)
   at Protocol._parsePacket (C:\Users\Supersoft\cashtoken-core\node_modules\mysql\lib\protocol\Protocol.js:278:23)
   at Parser.write (C:\Users\Supersoft\cashtoken-core\node_modules\mysql\lib\protocol\Parser.js:76:12)
   at Protocol.write (C:\Users\Supersoft\cashtoken-core\node_modules\mysql\lib\protocol\Protocol.js:38:16)
   at Socket.<anonymous> (C:\Users\Supersoft\cashtoken-core\node_modules\mysql\lib\Connection.js:91:28)
   at Socket.<anonymous> (C:\Users\Supersoft\cashtoken-core\node_modules\mysql\lib\Connection.js:502:10)
   at Socket.emit (events.js:189:13)
   at addChunk (_stream_readable.js:284:12)
   at readableAddChunk (_stream_readable.js:265:11)
   at Socket.Readable.push (_stream_readable.js:220:10)
   at TCP.onStreamRead [as onread] (internal/stream_base_commons.js:94:17)
   --------------------
   at Protocol._enqueue (C:\Users\Supersoft\cashtoken-core\node_modules\mysql\lib\protocol\Protocol.js:144:48)
   at PoolConnection.query (C:\Users\Supersoft\cashtoken-core\node_modules\mysql\lib\Connection.js:200:25)
   at runQuery (C:\Users\Supersoft\cashtoken-core\node_modules\loopback-connector-mysql\lib\mysql.js:197:16)
   at executeWithConnection (C:\Users\Supersoft\cashtoken-core\node_modules\loopback-connector-mysql\lib\mysql.js:239:7)
   at Ping.onOperationComplete (C:\Users\Supersoft\cashtoken-core\node_modules\mysql\lib\Pool.js:110:5)
   at Ping.<anonymous> (C:\Users\Supersoft\cashtoken-core\node_modules\mysql\lib\Connection.js:502:10)
   at Ping._callback (C:\Users\Supersoft\cashtoken-core\node_modules\mysql\lib\Connection.js:468:16)
   at Ping.Sequence.end (C:\Users\Supersoft\cashtoken-core\node_modules\mysql\lib\protocol\sequences\Sequence.js:83:24)
   at Ping.Sequence.OkPacket (C:\Users\Supersoft\cashtoken-core\node_modules\mysql\lib\protocol\sequences\Sequence.js:92:8)
   at Protocol._parsePacket (C:\Users\Supersoft\cashtoken-core\node_modules\mysql\lib\protocol\Protocol.js:278:23)
   at Parser.write (C:\Users\Supersoft\cashtoken-core\node_modules\mysql\lib\protocol\Parser.js:76:12)
   at Protocol.write (C:\Users\Supersoft\cashtoken-core\node_modules\mysql\lib\protocol\Protocol.js:38:16)
   at Socket.<anonymous> (C:\Users\Supersoft\cashtoken-core\node_modules\mysql\lib\Connection.js:91:28)
   at Socket.<anonymous> (C:\Users\Supersoft\cashtoken-core\node_modules\mysql\lib\Connection.js:502:10)
   at Socket.emit (events.js:189:13)
   at addChunk (_stream_readable.js:284:12)
 code: 'ER_TOO_LONG_KEY',
 errno: 1071,
 sqlMessage: 'Specified key was too long; max key length is 3072 bytes',
 sqlState: '42000',
 index: 0,
 sql:
  'ALTER TABLE `CashtokenTransaction` ADD UNIQUE INDEX  `cashtoken_transaction_index` (internal_ref,external_ref)',
 statusCode: 500 }

When I modify the configuration object as shown below:

@model({
  settings: {
    strict: 'filter',
    sqldb: {
      table: 'user',
    },
    indexes: {
      phone_number: {
        keys: {
          phone_number: 1
        },
        options: { unique: true },
      },
      email: {
        keys: {
          email: 1
        },
        options: {
          unique: true
        }
      },
      nlrc_code: {
        keys: {
          nlrc_code: 1
        },
        options: {
          unique: true
        }
      }
    }
  }
})

Migration works fine.

[mightytyphoon]

@rkterungwa16

@model()
export class Model extends Entity {
  @property({type: 'number', id: true , generated: true }) id: number; // id true for using it as an index and generated true to increment automatically in db.
}

About having several indexes I am not sure if using unique still works. I will test later this weekend I guess.

[dhmlau]

@jannyHou @bajtos, you're more familiar with indexes. Could you please take a look? Thanks.

[bajtos]

Based on the log, we are executing the following SQL command to create the index:

ALTER TABLE `CashtokenTransaction` 
ADD UNIQUE INDEX  `cashtoken_transaction_index` 
(internal_ref,external_ref)',

The database responds with the following error:

Specified key was too long; max key length is 3072 bytes

What SQL data types are used by internal_ref and external_ref columns? Is it possible that when you add the maximum allowed length of these two columns together, the combined size is larger than 3072 bytes?

(A side note: we are looking into a more ergonomic syntax for defining indexes, see #2712. The issue described here is not related to LoopBack syntax though.)

[rkterungwa16]

Hello @bajtos internal_ref and external_ref are both strings. I have tested same configuration for other data types, and same error is thrown. Thanks for the response.

[bajtos]

We need to find out how is loopback-connector-mysql mapping string type to database type. I think we use varchar(X), possibly with the default size 4069.

@rkterungwa16 is there any limit on the size of your internal_ref and external_ref properties? I think MySQL connector recognizes length and limit fields.

@model()
class MyModel extends Entity {
  @property({
    type: 'string',
    length: 1024
  })
  internal_ref: string

 // etc.
}

You can find the exact column size in the debug log - look for the SQL statement CREATE TABLE.

[dhmlau]

@rkterungwa16, we think the above comment has answered your question, so I'd like to close this issue now. Thanks.