1

I have a NodeJS server running as a message forwarding service and I've noticed that although it works perfectly with english (ASCII) text, it fails to insert UTF-8 encoded strings into the MySQL database (using nodejs-mysql driver)

Things i've tried:

conn.query("INSERT INTO messages(content) VALUES("+mysql.escape(content)+")", function(err, result) {
    // ...
}

conn.query("INSERT INTO messages(content) VALUES(?)", [content], function(err, result) {
    // ...
}

Both don't work, they end up inserting strings like "???????" into the database.

The table is using latin1_swedish_ci as its default collation and InnoDB as the engine.

This isn't a database (MySQL) issue because I have the same code in PHP and it works fine (using PHP PDO)

This isn't a NodeJS (v8) issue either because the message is correctly forwarded in UTF-8 form, it's just that when it gets sent to the database to be stored it gets stored as a string like "????????" for example.

Example of garbled row:

enter image description here

Does anyone have any idea what could be wrong?

Thanks in advance.

David Xu
  • 5,555
  • 3
  • 28
  • 50

1 Answers1

1

The latin1_swedish_ci database table can't natively handle UTF-8. It can store UTF-8 in an encoded format which is how your PHP-PDO is working.

wilsotc
  • 800
  • 4
  • 10
  • Changed collation to utf8_general_ci , it's still showing ?????? when I try and insert unicode. – David Xu May 23 '14 at 20:49
  • What is your sql interface default character encoding when you do the insert? [link]http://dev.mysql.com/doc/refman/5.1/en/mysql-command-options.html#option_mysql_default-character-set – wilsotc May 23 '14 at 20:52
  • Yes. It looks like the database and server character set is latin1? – wilsotc May 23 '14 at 21:00
  • Yep I just changed it to this and it still isn't working, although now it's not inserting ????????, it's inserting nothing (its blank) : http://puu.sh/8Yh7Q.png – David Xu May 23 '14 at 21:03
  • There's also character_set_server http://stackoverflow.com/questions/3513773/change-mysql-default-character-set-to-utf8-in-my-cnf – wilsotc May 23 '14 at 21:09
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/54307/discussion-between-wilsotc-and-david-xu). – wilsotc May 23 '14 at 21:11