Need "SET NAMES utf8"

[phplego]

I use mysql and I need execute "SET NAMES utf8" after connection. How can I do that?

[dankohn]

How about:

sequelize.query("SET NAMES utf8;")

[phplego]

sequelize.query("SET NAMES utf8;")

It is not works because this query must be executed after establish EACH new connection. But I don't know how to controll connection factory without chainging source code :(

[phplego]

So far I made this ugly hack in connector-manager.js inside connect() function:

  var connect = function(done) {
    var connection = mysql.createConnection({
      host: this.config.host,
      port: this.config.port,
      user: this.config.username,
      password: this.config.password,
      database: this.config.database
    })

    console.log("SET NAMES utf8");
    connection.query("SET NAMES utf8");   // Bad idea, but what can i do?

    // client.setMaxListeners(self.maxConcurrentQueries)
    this.isConnecting = false

    done(null, connection)
  }

[sdepold]

you could probably wait at least for the query to succeed :)

connection.query('set names utf8').success(function() {
done(null, connection)
})

this.isConnecting = false

Sascha Depold
Gesendet mit Sparrow (http://www.sparrowmailapp.com/?sig)

Am Dienstag, 8. Januar 2013 um 07:39 schrieb Дубров Олег:

So far I made this ugly hack in connector-manager.js inside connect() function:
var connect = function(done) { var connection = mysql.createConnection({ host: this.config.host, port: this.config.port, user: this.config.username, password: this.config.password, database: this.config.database }) console.log("SET NAMES utf8"); connection.query("SET NAMES utf8"); // Bad idea, but what can i do? // client.setMaxListeners(self.maxConcurrentQueries) this.isConnecting = false done(null, connection) }

—
Reply to this email directly or view it on GitHub (https://github.com/sdepold/sequelize/issues/395#issuecomment-11986414).

[jacksleight]

You don't need to do this (if you want UTF-8) as the mysql package takes care of setting the connection's charset, and the default is UTF8_GENERAL_CI. You can check this by running:

sequelize.query("SHOW VARIABLES LIKE 'character_set_%'").success(function(data) {
    console.log(data);
});

Look for these three:

[ { Variable_name: 'character_set_client', Value: 'utf8' },
  { Variable_name: 'character_set_connection', Value: 'utf8' },
...
  { Variable_name: 'character_set_results', Value: 'utf8' },
...

Check /node_modules/mysql/lib/ConnectionConfig.js line 38 for the code that sets the default encoding.

This of course is no use to people who want to use something other than UTF-8. It'd be really useful if Sequelize could provide a method to parse a value through to the mysql package's charset option.

[janmeier]

@jacksleight thanks for pointing out that node-mysql already has a charset option. In that case this is already fully possible. This test

sequelize/test/configuration.test.js

Line 102 in cf4ab02

shows how to pass extra options to the mysql lib so @phplego could do:

var sequelize = new Sequelize('dbname', 'root', 'pass', {
    port: 999,
    dialectOptions: {
        charset: 'something',
    }
})
var config = sequelize.config

[treejanitor]

Gotta note that I wish this were documented a little better. It's pretty common to need to insert Twitter emoji characters into a MySQL db, and it took me a while to discover this twist. Ended up being easier to actually trace it through the code. Anyhoo... I'd bet the charset option is commonly troublesome.

[janmeier]

@treejanitor - Docs PRs always welcome :)

[jontelm]

What was the solution to this problem?

[sushantdhiman]

this comment

[jontelm]

@sushantdhiman Thanks, I don't see the query get executed but "SET NAMES utf8;", but it's maybe the same as setting setting the charset.

[pizzinatto22]

I'm facing the very same problem, using sequelize 4.38.0.

Using:

var sequelize = new Sequelize(config.database, config.username, config.password, {
  host: "remote.address.com",
  dialect: 'mysql',
  operatorsAliases: false,
  timezone: 'America/Sao_Paulo',
  dialectOptions: { charset: 'utf8' } 
});

And then

sequelize.query("show variables like 'character%'").then(result => {
        console.log(result);
});

Results in:

[ [ TextRow { Variable_name: 'character_set_client', Value: 'latin1' },
    TextRow { Variable_name: 'character_set_connection', Value: 'latin1' },
    TextRow { Variable_name: 'character_set_database', Value: 'utf8' },
    TextRow { Variable_name: 'character_set_filesystem', Value: 'binary' },
    TextRow { Variable_name: 'character_set_results', Value: 'latin1' },
    TextRow { Variable_name: 'character_set_server', Value: 'latin1' },
    TextRow { Variable_name: 'character_set_system', Value: 'utf8' },
    TextRow {
      Variable_name: 'character_sets_dir',
      Value: '/usr/share/mysql/charsets/' } ],
  [ TextRow { Variable_name: 'character_set_client', Value: 'latin1' },
    TextRow { Variable_name: 'character_set_connection', Value: 'latin1' },
    TextRow { Variable_name: 'character_set_database', Value: 'utf8' },
    TextRow { Variable_name: 'character_set_filesystem', Value: 'binary' },
    TextRow { Variable_name: 'character_set_results', Value: 'latin1' },
    TextRow { Variable_name: 'character_set_server', Value: 'latin1' },
    TextRow { Variable_name: 'character_set_system', Value: 'utf8' },
    TextRow {
      Variable_name: 'character_sets_dir',
      Value: '/usr/share/mysql/charsets/' } ] ]

Doing

sequelize.query("set names utf8").then(() => {
        sequelize.query("show variables like 'character%'").then(result => {
                console.log(result);
        });
});

The output is

[ [ TextRow { Variable_name: 'character_set_client', Value: 'utf8' },
    TextRow { Variable_name: 'character_set_connection', Value: 'utf8' },
    TextRow { Variable_name: 'character_set_database', Value: 'utf8' },
    TextRow { Variable_name: 'character_set_filesystem', Value: 'binary' },
    TextRow { Variable_name: 'character_set_results', Value: 'utf8' },
    TextRow { Variable_name: 'character_set_server', Value: 'latin1' },
    TextRow { Variable_name: 'character_set_system', Value: 'utf8' },
    TextRow {
      Variable_name: 'character_sets_dir',
      Value: '/usr/share/mysql/charsets/' } ],
  [ TextRow { Variable_name: 'character_set_client', Value: 'utf8' },
    TextRow { Variable_name: 'character_set_connection', Value: 'utf8' },
    TextRow { Variable_name: 'character_set_database', Value: 'utf8' },
    TextRow { Variable_name: 'character_set_filesystem', Value: 'binary' },
    TextRow { Variable_name: 'character_set_results', Value: 'utf8' },
    TextRow { Variable_name: 'character_set_server', Value: 'latin1' },
    TextRow { Variable_name: 'character_set_system', Value: 'utf8' },
    TextRow {
      Variable_name: 'character_sets_dir',
      Value: '/usr/share/mysql/charsets/' } ] ]

But set names utf8; is valid only for current connection. After a few seconds, this connection is destroyed...

sequelize:pool connection released +1ms
sequelize:connection:mysql connection disconnected +10s
sequelize:pool connection destroy +10s

...and the new ones come back to latin1.

What can I do to solve this?

[sushantdhiman]

You can use afterConnect hook on sequelize, so after each connection is connected you can call SET NAMES utf8

sequelize.afterConnect(connection => {
   return Promise.fromCallback(callback => connection.query('SET NAMES UTF8', callback);
});