search

loopbackio / loopback-connector-mysql

Loopback Connector for MySQL
Other
125 stars 183 forks source link

ER_TOO_BIG_ROWSIZE, when creating user and application model #36

Closed Kampfgnom closed 7 years ago

Kampfgnom commented 10 years ago

I am getting the following error, by letting loopback create my Tables:

{ [Error: ER_TOO_BIG_ROWSIZE: Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. You have to change some columns to TEXT or BLOBs]
  code: 'ER_TOO_BIG_ROWSIZE',
  errno: 1118,
  sqlState: '42000',
  index: 0 }

I assume it is due to line 1026 in lib/mysql.js. Once you have two columns of size 32k, the rows become to large.

raymondfeng commented 10 years ago
  1. How does your model look like?
  2. You can customize the mysql column for your model properties, such as:
{myProp: {type: String, mysql: {columnName: 'MY_PROP', dataType: 'VARCHAR', dataLength: 32}}
Kampfgnom commented 10 years ago

The columns that are too long are the default columns of user: credentials and challenges are both VARCHAR(32k).

I now worked around the issue by "overriding" the properties in my derived model:

  "user": {
    "properties": {   
      "credentials": {
        "dataType": "TEXT"
      },    
      "challenges": {
        "dataType": "TEXT"
      },
...

Same goes for some properties of the application model:


      "collaborators": {
        "dataType": "TEXT"
      },    
      "callbackUrls": {
        "dataType": "TEXT"
      },    
      "pushSettings": {
        "type": "TEXT"
      },    
      "authenticationSchemes": {
        "type": "TEXT"
      }
raymondfeng commented 10 years ago

I see. Maybe we should set the default length to a lower number or map the object to TEXT.

zhoucen commented 10 years ago

I have the same problem.And I found This.

http://dev.mysql.com/doc/refman/5.5/en/column-count-limit.html

Every table (regardless of storage engine) has a maximum row size of 65,535 bytes. Storage engines may place additional constraints on this limit, reducing the effective maximum row size

So I think just map model to a table in mysql still has hidden dangers.Just like:

To long String

To many property in model
johnsoftek commented 10 years ago

I don't think it's a good idea to casually use TEXT rather than VARCHAR.

Each BLOB or TEXT value is represented internally by a separately allocated object.
This is in contrast to all other data types, for which storage is allocated once
per column when the table is opened.

Instead, just use a more reasonable length e.g. 100 or 1000.

    "properties": {
      "credentials": {
        "type": "String",
        "length": 1024
      },
      "challenges": {
        "type": "String",
        "length": 1024
      }
    },
raymondfeng commented 10 years ago

We understand the expense, which is not ideal.

  1. We'll remove credentials/challenges from user model in LoopBack 2.x as now we have loopback-passport module that stores credentials on a separate model.
  2. At the moment, we map Object/JSON type to TEXT for MySQL. If the property is string, it will be mapped to VARCHAR. We also support custom mapping too.
johnsoftek commented 10 years ago

Raymond,

Any release date for Loopback 2.0?

John Murphy

On 21 June 2014 13:49, Raymond Feng notifications@github.com wrote:

We understand the expense, which is not ideal.

1.

We'll remove credentials/challenges from user model in LoopBack 2.x as now we have loopback-passport module that stores credentials on a separate model. 2.

At the moment, we map Object/JSON type to TEXT for MySQL. If the property is string, it will be mapped to VARCHAR. We also support custom mapping too.

— Reply to this email directly or view it on GitHub https://github.com/strongloop/loopback-connector-mysql/issues/36#issuecomment-46743737 .

kimsungwhee commented 10 years ago

I have the same problem { [Error: ER_TOO_LONG_KEY: Specified key was too long; max key length is 767 bytes] code: 'ER_TOO_LONG_KEY', errno: 1071, sqlState: '42000', index: 0 }

fabien commented 10 years ago

@kimsungwhee what version of MySQL are you using? Are you using InnoDB or MyISAM tables?

/cc @raymondfeng

avqdev commented 9 years ago

FYI, "resolved" the issue by switching to latin-1 default collation instead of utf-8 in my database schema (MySQL 5.6, InnoDB).

stringbeans commented 9 years ago

this is still occurring when trying to generate the Application table in MySQL

ih2502mk commented 9 years ago

Hi, I'm experiencing the same problem as @stringbeans. This is what I get when debug mode for mysql dataSource is enabled (I formatted it a little for better readability):

--> ComQueryPacket
{ command: 3,
  sql: 'CREATE TABLE `Application` (
  `id` VARCHAR(255) NOT NULL PRIMARY KEY,
  `realm` VARCHAR(512) NULL,
  `name` VARCHAR(512) NOT NULL,
  `description` VARCHAR(512) NULL,
  `icon` VARCHAR(512) NULL,
  `owner` VARCHAR(512) NULL,
  `collaborators` VARCHAR(4096) NULL,
  `email` VARCHAR(512) NULL,
  `emailVerified` TINYINT(1) NULL,
  `url` VARCHAR(512) NULL,
  `callbackUrls` VARCHAR(4096) NULL,
  `permissions` VARCHAR(4096) NULL,
  `clientKey` VARCHAR(512) NULL,
  `javaScriptKey` VARCHAR(512) NULL,
  `restApiKey` VARCHAR(512) NULL,
  `windowsKey` VARCHAR(512) NULL,
  `masterKey` VARCHAR(512) NULL,
  `pushSettings` VARCHAR(4096) NULL,
  `authenticationEnabled` TINYINT(1) NULL,
  `anonymousAllowed` TINYINT(1) NULL,
  `authenticationSchemes` VARCHAR(4096) NULL,
  `status` VARCHAR(512) NULL,
  `created` DATETIME NULL,
  `modified` DATETIME NULL
)' }

<-- ErrorPacket
{ fieldCount: 255,
  errno: 1118,
  sqlStateMarker: '#',
  sqlState: '42000',
  message: 'Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs' }

Looks like all fields in Application model that are not just single values but have structure like for example pushSettings default to type VARCHAR(4096). There is 5 of those, and that's 20k+ bytes right there. I guess one could just override model configuration for Application model and provide a "mysql": {...} entry for those fields and turn them into BLOBS.

My question is: Is it OK to have those fields as BLOBS?

bajtos commented 9 years ago

@raymondfeng what's the status of this issue? Has it been addressed yet? Is it a "bug" or an "enhancement"?

Kampfgnom commented 8 years ago

I am still seeing this error with the latest loopback.

I would consider it a bug, since you cannot attach the Application model to a MySQL datasource without changing its properties.

Why aren't more people having a problem with this?

raymondfeng commented 8 years ago

What version of mysql connector do you use? The latest code maps such properties to MySQL TEXT. See https://github.com/strongloop/loopback-connector-mysql/blob/master/lib/migration.js#L427

Octopixell commented 8 years ago

EDIT: I just noticed it's not exactly the same issue as the OP, sorry for that. It might be related though. I do see a post by @kimsungwhee with the same error.

@raymondfeng I'm getting this error as well. It seems to struggle with the id column of the AccessToken model for the MySQL database.

It returns ER_TOO_LONG_KEY: Specified key was too long; max key length is 767 bytes and the only table that doesn't get created is the AccessToken table.

Full stacktrace from error:

<-- ErrorPacket
{ fieldCount: 255,
  errno: 1071,
  sqlStateMarker: '#',
  sqlState: '42000',
  message: 'Specified key was too long; max key length is 767 bytes' }

{ [Error: ER_TOO_LONG_KEY: Specified key was too long; max key length is 767 bytes]
  code: 'ER_TOO_LONG_KEY',
  errno: 1071,
  sqlState: '42000',
  index: 0 }
/Users/marco/taxiid/api/taxiid-api/node_modules/loopback-connector-mysql/node_modules/mysql/lib/protocol/Parser.js:82
        throw err;
              ^
Error: ER_TOO_LONG_KEY: Specified key was too long; max key length is 767 bytes
    at Query.Sequence._packetToError (/Users/marco/taxiid/api/taxiid-api/node_modules/loopback-connector-mysql/node_modules/mysql/lib/protocol/sequences/Sequence.js:48:14)
    at Query.ErrorPacket (/Users/marco/taxiid/api/taxiid-api/node_modules/loopback-connector-mysql/node_modules/mysql/lib/protocol/sequences/Query.js:83:18)
    at Protocol._parsePacket (/Users/marco/taxiid/api/taxiid-api/node_modules/loopback-connector-mysql/node_modules/mysql/lib/protocol/Protocol.js:274:23)
    at Parser.write (/Users/marco/taxiid/api/taxiid-api/node_modules/loopback-connector-mysql/node_modules/mysql/lib/protocol/Parser.js:77:12)
    at Protocol.write (/Users/marco/taxiid/api/taxiid-api/node_modules/loopback-connector-mysql/node_modules/mysql/lib/protocol/Protocol.js:39:16)
    at Socket.<anonymous> (/Users/marco/taxiid/api/taxiid-api/node_modules/loopback-connector-mysql/node_modules/mysql/lib/Connection.js:96:28)
    at Socket.emit (events.js:107:17)
    at readableAddChunk (_stream_readable.js:163:16)
    at Socket.Readable.push (_stream_readable.js:126:10)
    at TCP.onread (net.js:538:20)
    --------------------
    at Protocol._enqueue (/Users/marco/taxiid/api/taxiid-api/node_modules/loopback-connector-mysql/node_modules/mysql/lib/protocol/Protocol.js:135:48)
    at PoolConnection.query (/Users/marco/taxiid/api/taxiid-api/node_modules/loopback-connector-mysql/node_modules/mysql/lib/Connection.js:201:25)
    at Object.eval [as forward] (eval at recompile (/usr/local/lib/node_modules/strongloop/node_modules/strong-supervisor/node_modules/strong-agent/lib/proxy.js:245:15), <anonymous>:4:32)
    at PoolConnection.query (eval at wrap (/usr/local/lib/node_modules/strongloop/node_modules/strong-supervisor/node_modules/strong-agent/lib/proxy.js:201:20), <anonymous>:3:21)
    at runQuery (/Users/marco/taxiid/api/taxiid-api/node_modules/loopback-connector-mysql/lib/mysql.js:146:16)
    at executeWithConnection (/Users/marco/taxiid/api/taxiid-api/node_modules/loopback-connector-mysql/lib/mysql.js:188:7)
    at Ping.onOperationComplete [as _callback] (/Users/marco/taxiid/api/taxiid-api/node_modules/loopback-connector-mysql/node_modules/mysql/lib/Pool.js:99:5)
    at Ping.Sequence.end (/Users/marco/taxiid/api/taxiid-api/node_modules/loopback-connector-mysql/node_modules/mysql/lib/protocol/sequences/Sequence.js:96:24)
    at Ping.Sequence.OkPacket (/Users/marco/taxiid/api/taxiid-api/node_modules/loopback-connector-mysql/node_modules/mysql/lib/protocol/sequences/Sequence.js:105:8)
    at Protocol._parsePacket (/Users/marco/taxiid/api/taxiid-api/node_modules/loopback-connector-mysql/node_modules/mysql/lib/protocol/Protocol.js:274:23)
Octopixell commented 8 years ago

Ok so I just confirmed it's an issue with the id column in the AccessToken table.. We're using utf8mb4_general_ci as the collation. I found this answer on StackOverflow which pointed me in the right direction and fixed it, for now.. http://stackoverflow.com/a/31474509

I went into the /node_modules/loopback/common/models/access-token.json and changed it to the following: (which gives the id column a VARCHAR(191)). Is there a way to do this elegantly without overwriting built-in models?

{
  "name": "AccessToken",
  "properties": {
    "id": {
      "type": "string",
      "id": true,
      "mysql": {
        "dataType": "VARCHAR",
        "dataLength": 191
      }
    },
    "ttl": {
      "type": "number",
      "ttl": true,
      "default": 1209600,
      "description": "time to live in seconds (2 weeks by default)"
    },
    "created": {
      "type": "Date"
    }
  },
  "relations": {
    "user": {
      "type": "belongsTo",
      "model": "User",
      "foreignKey": "userId"
    }
  },
  "acls": [
    {
      "principalType": "ROLE",
      "principalId": "$everyone",
      "permission": "DENY"
    },
    {
      "principalType": "ROLE",
      "principalId": "$everyone",
      "property": "create",
      "permission": "ALLOW"
    }
  ]
}
LoicMahieu commented 8 years ago

By default, an index key for a single-column index can be up to 767 bytes http://dev.mysql.com/doc/refman/5.7/en/innodb-restrictions.html

There is a limitation on innodb engine when using UTF-8 charset. VARCHAR can not have a length greater than 255 characters.

For example, you might hit this limit with a column prefix index of more than 255 characters on a TEXT or VARCHAR column, assuming a UTF-8 character set and the maximum of 3 bytes for each character. http://dev.mysql.com/doc/refman/5.7/en/innodb-restrictions.html

By default, this mysql connection set 512 by default: ./lib/migration.js#L472

I don't see a way around the problem. There is actually no way to overwrite this value accros the application. Obviously it's possible to set the length at the model registration but it could not work with loopback models etc...

LoicMahieu commented 8 years ago

I think this error appears only when we create an index. Here a example of table created for a polymorphic relation, which requires two foreign keys, a VARCHAR and a INT :

CREATE TABLE `Foo` (
  `id` INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `referenceType` VARCHAR(512) NULL,
  `ownerId` INT(11) NULL,
   INDEX `referenceType`  (`referenceType`)
)

Throws error :

Error : Index column size too large. The maximum column size is 767 bytes.

But without INDEX, it works:

CREATE TABLE `Foo` (
  `id` INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `referenceType` VARCHAR(512) NULL,
  `ownerId` INT(11) NULL
)
loay commented 7 years ago

I think this issue is already fixed. Can someone confirm please ? Thanks.

Mayeu commented 7 years ago

Hello,

Could there be a bugfix version released with this the fix? From the commit log it seems that the fix is in the middle of the next major version development, does that mean we will have to wait for the v3 to have a fix for that?

Thanks :)

loay commented 7 years ago

Hi @Mayeu I just pushed a pr that fixed the indexing issue, so I was wondering if you are facing the same problem by testing. Thanks.

Mayeu commented 7 years ago

Ha sorry, actually after retesting and ensuring I had a clean setup, this patch does not solve the issue for me :/ I am in the same case that @Octopixell describe, i.e. where the VARCHAR should be 191. If I adapt the patch to 191 everything goes right without any issue.

loay commented 7 years ago

@Mayeu I am glad it is working for you. It is just a question of reducing the length of the parameter, so the index length can fit 767. Is this good to close?

Mayeu commented 7 years ago

Well, as far as my research went, it seems that when you have a table using utf-8, MySQL only encode them using 1 to 3 bytes (thus, not encoding every utf-8 codepoints). This limit you to a VARCHAR(255), since 255 * 3 = 765.

But if you use utf-8mb4 (the one we are using), MySQL encode everything between 1 and 4 bytes, then encoding all utf-8 codepoints, and this leads to VARCHAR(191) since 191 * 4 = 764.

So yeah, reducing the length of the parameter seems to be the right path :)

alemhnan commented 7 years ago

I'm having the same issue. I'm working with different project using loopback and mysql. I've scaffolded one just now and got same problem mentioned here using the last stable version (2.4.0).

Nevertheless using the latest commit as the moment of writing ( "loopback-connector-mysql": "git://github.com/strongloop/loopback-connector-mysql#37bb679f6e5527651d98bf1e9cdb25693ef799f9") it works indeed as expected.

I guess that to have that landed in npm we have to have for loopback 3.0 to be finalized?

b-admike commented 7 years ago

I believe this issue is fixed. I am unable to reproduce all the types of issues mentioned in this thread.

mysql> show columns from province; +------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+--------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(512) | YES | | NULL | | | countryId | int(11) | YES | | NULL | | | provinceId | int(11) | YES | | NULL | | +------------+--------------+------+-----+---------+----------------+ 4 rows in set (0.00 sec)

mysql> show index from province; +----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | province | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | | | +----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 1 row in set (0.01 sec)

mysql> show index from country; +---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | country | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | | | +---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 1 row in set (0.00 sec)


Versions used:

| ~/loopback/triage/loopback-sandbox/apps/mysql/mysql-36 @ biniams-mbp (badmike) | => node -v v4.8.0 | ~/loopback/triage/loopback-sandbox/apps/mysql/mysql-36 @ biniams-mbp (badmike) | => npm -v 2.15.11 ___ | ~/loopback/triage/loopback-sandbox/apps/mysql/mysql-36 @ biniams-mbp (badmike) | => npm ls loopback-connector-mysql mysql-template@1.0.0 /Users/badmike/loopback/triage/loopback-sandbox/apps/mysql/mysql-36 └── loopback-connector-mysql@5.0.0

___ | ~/loopback/triage/loopback-sandbox/apps/mysql/mysql-36 @ biniams-mbp (badmike) | => npm ls --depth=0 mysql-template@1.0.0 /Users/badmike/loopback/triage/loopback-sandbox/apps/mysql/mysql-36 ├── compression@1.7.0 ├── cors@2.8.3 ├── eslint@3.19.0 ├── eslint-config-loopback@8.0.0 ├── helmet@1.3.0 ├── loopback@3.8.0 ├── loopback-boot@2.25.0 ├── loopback-component-explorer@4.2.0 ├── loopback-connector-mysql@5.0.0 ├── nsp@2.6.3 ├── serve-favicon@2.4.3 └── strong-error-handler@2.1.0



Here a link to sandbox app I used to verify this issue: https://github.com/b-admike/loopback-sandbox/tree/master/apps/mysql/mysql-36. Closing issue, but feel free to re-open if needed.
davidsandoz commented 7 years ago

@b-admike Did you try to reproduce with tables using utf-8mb4? For me it still happens with this encoding. I was able to solve the issue by doing this change: https://github.com/davidsandoz/loopback-connector-mysql/commit/6029a8e3729e523c3ae1a7e730f674ed1ed8997c

Undrium commented 7 years ago

utf-8mb4 still doesn't work for AccessToken's ID.

b-admike commented 7 years ago

I did not try to reproduce using utf-8mb4 encoding. Re-opening this issue.

b-admike commented 7 years ago

@davidsandoz @Undrium How did you set utf8mb4 encoding? I used the following properties in my datasource config, but still seeing the same results after automigrating the models:

  "mysqlDs": {
    "host": "localhost",
    "port": 3306,
    "url": "",
    "database": "testdb",
    "password": "pass",
    "name": "mysqlDs",
    "user": "root",
    "connector": "mysql",
    "charset": "utf8mb4",
    "collation": "utf8mb4"
  }
davidsandoz commented 7 years ago

@b-admike: I set it when I manually create the MySQL database.

I believe that setting charset and collation when defining the datasource is simply telling LoopBack what charset/collation is used for the database, but it doesn't set them for the database.

Also, I don't think that the collation can just be utf8mb4. I have it set to utf8mb4_general_ci.

b-admike commented 7 years ago

@davidsandoz Thank you. I created the database manually as well using the proper encoding and set collation to utf8mb4_general_ci in my datasource config, but I am still not able to reproduce. Can you share the versions of LoopBack you are using as I did in https://github.com/strongloop/loopback-connector-mysql/issues/36#issuecomment-314621803?

mysql> CREATE DATABASE `testdb` CHARACTER SET `utf8mb4` COLLATE `utf8mb4_general_ci`;
Query OK, 1 row affected (0.00 sec)

mysql> use testdb;
Database changed
mysql> SELECT @@character_set_database, @@collation_database;
+--------------------------+----------------------+
| @@character_set_database | @@collation_database |
+--------------------------+----------------------+
| utf8mb4                  | utf8mb4_general_ci   |
+--------------------------+----------------------+
1 row in set (0.00 sec)

After doing automigrate successfully, here are my tables:

mysql> show tables;
+------------------+
| Tables_in_testdb |
+------------------+
| country          |
| myATModel        |
| myAppModel       |
| myUser           |
| province         |
+------------------+
5 rows in set (0.00 sec)

mysql> describe myAppModel;
+-----------------------+--------------+------+-----+---------+-------+
| Field                 | Type         | Null | Key | Default | Extra |
+-----------------------+--------------+------+-----+---------+-------+
| id                    | varchar(255) | NO   | PRI | NULL    |       |
| realm                 | varchar(512) | YES  |     | NULL    |       |
| name                  | varchar(512) | NO   |     | NULL    |       |
| description           | varchar(512) | YES  |     | NULL    |       |
| icon                  | varchar(512) | YES  |     | NULL    |       |
| owner                 | varchar(512) | YES  |     | NULL    |       |
| collaborators         | text         | YES  |     | NULL    |       |
| email                 | varchar(512) | YES  |     | NULL    |       |
| emailVerified         | tinyint(1)   | YES  |     | NULL    |       |
| url                   | varchar(512) | YES  |     | NULL    |       |
| callbackUrls          | text         | YES  |     | NULL    |       |
| permissions           | text         | YES  |     | NULL    |       |
| clientKey             | varchar(512) | YES  |     | NULL    |       |
| javaScriptKey         | varchar(512) | YES  |     | NULL    |       |
| restApiKey            | varchar(512) | YES  |     | NULL    |       |
| windowsKey            | varchar(512) | YES  |     | NULL    |       |
| masterKey             | varchar(512) | YES  |     | NULL    |       |
| pushSettings          | text         | YES  |     | NULL    |       |
| authenticationEnabled | tinyint(1)   | YES  |     | NULL    |       |
| anonymousAllowed      | tinyint(1)   | YES  |     | NULL    |       |
| authenticationSchemes | text         | YES  |     | NULL    |       |
| status                | varchar(512) | YES  |     | NULL    |       |
| created               | datetime     | YES  |     | NULL    |       |
| modified              | datetime     | YES  |     | NULL    |       |
+-----------------------+--------------+------+-----+---------+-------+
24 rows in set (0.01 sec)

mysql> describe myATModel;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id      | varchar(255) | NO   | PRI | NULL    |       |
| ttl     | int(11)      | YES  |     | NULL    |       |
| scopes  | text         | YES  |     | NULL    |       |
| created | datetime     | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+
4 rows in set (0.01 sec)
davidsandoz commented 7 years ago

@b-admike: Also make sure that MySQL tables are using the InnoDB engine.

Versions used:

$ node -v
v8.1.2
$ npm -v 
5.0.4
$ npm ls --depth=0
├── compression@1.7.0
├── cors@2.8.4
├── eslint@3.19.0
├── eslint-config-loopback@8.0.0
├── helmet@1.3.0
├── loopback@3.8.0
├── loopback-boot@2.24.1
├── loopback-cli@2.6.0
├── loopback-component-explorer@4.2.0
├── loopback-connector-mysql@4.2.0
├── loopback-migration-tool@1.4.0
├── nsp@2.7.0
├── serve-favicon@2.4.3
└── strong-error-handler@2.2.0
b-admike commented 7 years ago

Yeah it looks like the tables are using the InnoDB engine:

mysql> show table status where `Name`="myAppModel";
+------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
| Name       | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation          | Checksum | Create_options | Comment |
+------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
| myAppModel | InnoDB |      10 | Dynamic    |    0 |              0 |       16384 |               0 |            0 |         0 |           NULL | 2017-07-27 17:53:56 | NULL        | NULL       | utf8mb4_general_ci |     NULL |                |         |
+------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
1 row in set (0.00 sec)

mysql> show table status where `Name`="myATModel";
+-----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
| Name      | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation          | Checksum | Create_options | Comment |
+-----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
| myATModel | InnoDB |      10 | Dynamic    |    0 |              0 |       16384 |               0 |            0 |         0 |           NULL | 2017-07-27 17:53:56 | NULL        | NULL       | utf8mb4_general_ci |     NULL |                |         |
+-----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
1 row in set (0.00 sec)
davidsandoz commented 7 years ago

Could you also try with the AccessToken model provided by LoopBack?

It seems to be the index in this table that triggers the issue and I'm not sure how different it is with the model you used in your example.

stale[bot] commented 7 years ago

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.

stale[bot] commented 7 years ago

This issue has been closed due to continued inactivity. Thank you for your understanding. If you believe this to be in error, please contact one of the code owners, listed in the CODEOWNERS file at the top-level of this repository.

jcolemorrison commented 6 years ago

For anyone else who runs into this issue, this is still a problem. Using utf8mb4 + auto migrating AccessToken results in the ER_TOO_LONG_KEY failure.