Giter Club home page Giter Club logo

Comments (39)

jcolemorrison avatar jcolemorrison commented on June 3, 2024 2

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.

from loopback-connector-mysql.

LoicMahieu avatar LoicMahieu commented on June 3, 2024 1

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
)

from loopback-connector-mysql.

davidsandoz avatar davidsandoz commented on June 3, 2024 1

@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: davidsandoz@6029a8e

from loopback-connector-mysql.

raymondfeng avatar raymondfeng commented on June 3, 2024
  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}}

from loopback-connector-mysql.

Kampfgnom avatar Kampfgnom commented on June 3, 2024

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"
      }

from loopback-connector-mysql.

raymondfeng avatar raymondfeng commented on June 3, 2024

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

from loopback-connector-mysql.

zhoucen avatar zhoucen commented on June 3, 2024

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

from loopback-connector-mysql.

johnsoftek avatar johnsoftek commented on June 3, 2024

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
      }
    },

from loopback-connector-mysql.

raymondfeng avatar raymondfeng commented on June 3, 2024

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.

from loopback-connector-mysql.

johnsoftek avatar johnsoftek commented on June 3, 2024

Raymond,

Any release date for Loopback 2.0?

John Murphy

On 21 June 2014 13:49, Raymond Feng [email protected] wrote:

We understand the expense, which is not ideal.

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
#36 (comment)
.

from loopback-connector-mysql.

kimsungwhee avatar kimsungwhee commented on June 3, 2024

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 }

from loopback-connector-mysql.

fabien avatar fabien commented on June 3, 2024

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

/cc @raymondfeng

from loopback-connector-mysql.

avqdev avatar avqdev commented on June 3, 2024

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

from loopback-connector-mysql.

stringbeans avatar stringbeans commented on June 3, 2024

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

from loopback-connector-mysql.

ih2502mk avatar ih2502mk commented on June 3, 2024

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?

from loopback-connector-mysql.

bajtos avatar bajtos commented on June 3, 2024

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

from loopback-connector-mysql.

Kampfgnom avatar Kampfgnom commented on June 3, 2024

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?

from loopback-connector-mysql.

raymondfeng avatar raymondfeng commented on June 3, 2024

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

from loopback-connector-mysql.

Octopixell avatar Octopixell commented on June 3, 2024

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)

from loopback-connector-mysql.

Octopixell avatar Octopixell commented on June 3, 2024

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"
    }
  ]
}

from loopback-connector-mysql.

LoicMahieu avatar LoicMahieu commented on June 3, 2024

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...

from loopback-connector-mysql.

loay avatar loay commented on June 3, 2024

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

from loopback-connector-mysql.

Mayeu avatar Mayeu commented on June 3, 2024

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 :)

from loopback-connector-mysql.

loay avatar loay commented on June 3, 2024

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.

from loopback-connector-mysql.

Mayeu avatar Mayeu commented on June 3, 2024

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.

from loopback-connector-mysql.

loay avatar loay commented on June 3, 2024

@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?

from loopback-connector-mysql.

Mayeu avatar Mayeu commented on June 3, 2024

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 :)

from loopback-connector-mysql.

alemhnan avatar alemhnan commented on June 3, 2024

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?

from loopback-connector-mysql.

b-admike avatar b-admike commented on June 3, 2024

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

  • credentials and challenges properties are no longer part of the User base model class.
mysql> show columns from myUser;
+-------------------+--------------+------+-----+---------+----------------+
| Field             | Type         | Null | Key | Default | Extra          |
+-------------------+--------------+------+-----+---------+----------------+
| id                | int(11)      | NO   | PRI | NULL    | auto_increment |
| realm             | varchar(512) | YES  |     | NULL    |                |
| username          | varchar(512) | YES  |     | NULL    |                |
| password          | varchar(512) | NO   |     | NULL    |                |
| email             | varchar(512) | NO   |     | NULL    |                |
| emailVerified     | tinyint(1)   | YES  |     | NULL    |                |
| verificationToken | varchar(512) | YES  |     | NULL    |                |
+-------------------+--------------+------+-----+---------+----------------+
7 rows in set (0.00 sec)
  • properties which had VARCHAR(4096) column definitions in Application base model are now VARCHAR(512)
mysql> show columns from 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    |       |
+-----------------------+--------------+------+-----+---------+-------+
  • id property for AccessToken base model is now VARCHAR(255) in the MySQL database.
mysql> show columns from 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)
  • There are no indexes defined for polymorphic relations (tried a hasMany/belongsTo relation between two models). In my case, country model hasMany province:
mysql> show columns from country;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(11)      | NO   | PRI | NULL    | auto_increment |
| name  | varchar(512) | YES  |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

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
[email protected] /Users/badmike/loopback/triage/loopback-sandbox/apps/mysql/mysql-36
└── [email protected]

___________________    | ~/loopback/triage/loopback-sandbox/apps/mysql/mysql-36 @ biniams-mbp (badmike)
| => npm ls --depth=0
[email protected] /Users/badmike/loopback/triage/loopback-sandbox/apps/mysql/mysql-36
├── [email protected]
├── [email protected]
├── [email protected]
├── [email protected]
├── [email protected]
├── [email protected]
├── [email protected]
├── [email protected]
├── [email protected]
├── [email protected]
├── [email protected]
└── [email protected]

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.

from loopback-connector-mysql.

Undrium avatar Undrium commented on June 3, 2024

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

from loopback-connector-mysql.

b-admike avatar b-admike commented on June 3, 2024

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

from loopback-connector-mysql.

b-admike avatar b-admike commented on June 3, 2024

@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"
  }

from loopback-connector-mysql.

davidsandoz avatar davidsandoz commented on June 3, 2024

@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.

from loopback-connector-mysql.

b-admike avatar b-admike commented on June 3, 2024

@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 #36 (comment)?

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)

from loopback-connector-mysql.

davidsandoz avatar davidsandoz commented on June 3, 2024

@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
├── [email protected]
├── [email protected]
├── [email protected]
├── [email protected]
├── [email protected]
├── [email protected]
├── [email protected]
├── [email protected]
├── [email protected]
├── [email protected]
├── [email protected]
├── [email protected]
├── [email protected]
└── [email protected]

from loopback-connector-mysql.

b-admike avatar b-admike commented on June 3, 2024

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)

from loopback-connector-mysql.

davidsandoz avatar davidsandoz commented on June 3, 2024

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.

from loopback-connector-mysql.

stale avatar stale commented on June 3, 2024

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.

from loopback-connector-mysql.

stale avatar stale commented on June 3, 2024

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.

from loopback-connector-mysql.

Related Issues (20)

Recommend Projects

  • React photo React

    A declarative, efficient, and flexible JavaScript library for building user interfaces.

  • Vue.js photo Vue.js

    🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.

  • Typescript photo Typescript

    TypeScript is a superset of JavaScript that compiles to clean JavaScript output.

  • TensorFlow photo TensorFlow

    An Open Source Machine Learning Framework for Everyone

  • Django photo Django

    The Web framework for perfectionists with deadlines.

  • D3 photo D3

    Bring data to life with SVG, Canvas and HTML. 📊📈🎉

Recommend Topics

  • javascript

    JavaScript (JS) is a lightweight interpreted programming language with first-class functions.

  • web

    Some thing interesting about web. New door for the world.

  • server

    A server is a program made to process requests and deliver data to clients.

  • Machine learning

    Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.

  • Game

    Some thing interesting about game, make everyone happy.

Recommend Org

  • Facebook photo Facebook

    We are working to build community through open source technology. NB: members must have two-factor auth.

  • Microsoft photo Microsoft

    Open source projects and samples from Microsoft.

  • Google photo Google

    Google ❤️ Open Source for everyone.

  • D3 photo D3

    Data-Driven Documents codes.