1

We forgot to change charsets in our mysql db after migration on new server.

Current situation:

mysql> show variables like 'character%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | latin1                     |
| character_set_connection | latin1                     |
| character_set_database   | latin1                     |
| character_set_filesystem | binary                     |
| character_set_results    | latin1                     |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

mysql> show variables like 'collation%';
+----------------------+-------------------+
| Variable_name        | Value             |
+----------------------+-------------------+
| collation_connection | latin1_swedish_ci |
| collation_database   | latin1_swedish_ci |
| collation_server     | latin1_swedish_ci |
+----------------------+-------------------+

But table charset is utf-8:

mysql> show create table inbox\G
*************************** 1. row ***************************
Create Table: CREATE TABLE `inbox` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  # ...
  `message` varchar(2048) DEFAULT NULL
) ENGINE=InnoDB AUTO_INCREMENT=4001 DEFAULT CHARSET=utf8

We're using Node.js (0.10.*) for backend and latest felixge node-mysql (connection is UTF8_GENERAL_CI by default).

Before we've found incorrect config, there are a lot of messages saved to table (~4000), most of them are utf-8. Sure, currently we see a lot of question marks ?????????? instead of normal text.

I've tried node-iconv, but no luck.

var db = require('mysql').createConnection({
  // credentials
});

var iconv = new require('iconv').Iconv('latin1', 'utf-8');

db.query('select message from inbox where id = 42', function(err, result) {
  var msg = result[0].message;
  console.log(iconv.convert(msg).toString());
  db.destroy();
});

Is there any chance to recover original utf-8 data?