Core: Emoji into database

[aleks63]

ER_TRUNCATED_WRONG_VALUE_FOR_FIELD: Incorrect string value: '\xF0\x9F\x94\xA5",...' for column 'account' at row 1

And collation in db is utf8mb4_general_ci

[thetutlage]

Seems to be related to database engine more than AdonisJs, and why do you even store emojis to database, store the shortcode or unicode instead

[thetutlage]

Closing since no updates from the issue reporter

[dguzman1012]

I know this is a closed issue, but since nobody gave a truly solution i offer mine for others who are in the same situation...

As @aleks63 said, we actually have to set collation db/table/field as _utf8mb4_general_ci_, but we need to make a little modification on config/database.js as well:

connection: Env.get('DB_CONNECTION', 'sqlite'),
..
mysql: {
    client: 'mysql',
    connection: {
      host: Env.get('DB_HOST', 'localhost'),
      user: Env.get('DB_USER', 'root'),
      password: Env.get('DB_PASSWORD', ''),
      database: Env.get('DB_DATABASE', 'adonis'),
      charset: 'utf8mb4'
    }
  },
..

And that's it! adding _charset: 'utf8mb4'_ on DB connection settings solves this issue!

[kingeke]

@dguzman1012 Hello, thanks for this, adding the charset: 'utf8mb4' and collation: 'utf8mb4_unicode_ci', to the database config allows emojis to work, but now i'm getting an error when adding unique() to my columns. but removing this works fine.

Any ideas ?

here is the UserScheme

class UserSchema extends Schema {
  up() {
    this.create('users', (table) => {
      table.increments()
      table.string('fullname')
      table.string('username').notNullable().unique()
      table.string('email').notNullable().unique()
      table.string('password').notNullable()
      table.string('avatar').nullable()
      table.string('deviceID').nullable().unique()
      table.timestamps()
    })
  }

here is the error

{ Error: ER_TOO_LONG_KEY: Specified key was too long; max key length is 1000 bytes
    at Query.Sequence._packetToError (D:\wamp64\www\nodeAPI\node_modules\mysql\lib\protocol\sequences\Sequence.js:47:14)
    at Query.ErrorPacket (D:\wamp64\www\nodeAPI\node_modules\mysql\lib\protocol\sequences\Query.js:77:18)
    at Protocol._parsePacket (D:\wamp64\www\nodeAPI\node_modules\mysql\lib\protocol\Protocol.js:291:23)
    at Parser._parsePacket (D:\wamp64\www\nodeAPI\node_modules\mysql\lib\protocol\Parser.js:433:10)
    at Parser.write (D:\wamp64\www\nodeAPI\node_modules\mysql\lib\protocol\Parser.js:43:10)
    at Protocol.write (D:\wamp64\www\nodeAPI\node_modules\mysql\lib\protocol\Protocol.js:38:16)
    at Socket.<anonymous> (D:\wamp64\www\nodeAPI\node_modules\mysql\lib\Connection.js:91:28)
    at Socket.<anonymous> (D:\wamp64\www\nodeAPI\node_modules\mysql\lib\Connection.js:525:10)
    at Socket.emit (events.js:197:13)
    at addChunk (_stream_readable.js:288:12)
    at readableAddChunk (_stream_readable.js:269:11)
    at Socket.Readable.push (_stream_readable.js:224:10)
    at TCP.onStreamRead [as onread] (internal/stream_base_commons.js:145:17)
    --------------------
    at Protocol._enqueue (D:\wamp64\www\nodeAPI\node_modules\mysql\lib\protocol\Protocol.js:144:48)
    at Connection.query (D:\wamp64\www\nodeAPI\node_modules\mysql\lib\Connection.js:201:25)
    at D:\wamp64\www\nodeAPI\node_modules\knex\lib\dialects\mysql\index.js:154:18
    at Promise._execute (D:\wamp64\www\nodeAPI\node_modules\bluebird\js\release\debuggability.js:313:9)
    at Promise._resolveFromExecutor (D:\wamp64\www\nodeAPI\node_modules\bluebird\js\release\promise.js:488:18)
    at new Promise (D:\wamp64\www\nodeAPI\node_modules\bluebird\js\release\promise.js:79:10)
    at Client_MySQL._query (D:\wamp64\www\nodeAPI\node_modules\knex\lib\dialects\mysql\index.js:148:12)
    at Client_MySQL.query (D:\wamp64\www\nodeAPI\node_modules\knex\lib\client.js:202:17)
    at D:\wamp64\www\nodeAPI\node_modules\knex\lib\transaction.js:272:21
    at tryCatcher (D:\wamp64\www\nodeAPI\node_modules\bluebird\js\release\util.js:16:23)
    at Function.Promise.attempt.Promise.try (D:\wamp64\www\nodeAPI\node_modules\bluebird\js\release\method.js:39:29)
    at Client_MySQL.trxClient.query (D:\wamp64\www\nodeAPI\node_modules\knex\lib\transaction.js:269:34)
    at Runner.<anonymous> (D:\wamp64\www\nodeAPI\node_modules\knex\lib\runner.js:146:36)
    at Runner.tryCatcher (D:\wamp64\www\nodeAPI\node_modules\bluebird\js\release\util.js:16:23)
    at Runner.query (D:\wamp64\www\nodeAPI\node_modules\bluebird\js\release\method.js:15:34)
    at Runner.<anonymous> (D:\wamp64\www\nodeAPI\node_modules\knex\lib\runner.js:213:19)
  code: 'ER_TOO_LONG_KEY',
  errno: 1071,
  sqlMessage: 'Specified key was too long; max key length is 1000 bytes',
  sqlState: '42000',
  index: 0,
  sql:
   'alter table `users` add unique `users_username_unique`(`username`)' }

[dguzman1012]

Hey @kingeke. Where do you set the collation utf8mb4_unicode_ci? to all database?

As @aleks63 said, we actually have to set collation db/table/field as _utf8mb4_general_ci_,

I strongly recommend to set the utf8mb4_unicode_ci collation only to the field inside the database table. Not the entire database.

Please try with that.

King regards.

[lock[bot]]

This thread has been automatically locked since there has not been any recent activity after it was closed. Please open a new issue for related bugs.