How to insert Chinese characters into a MySQL database from Node.js?

Question

I found what was the problem.

the point is delay. It was a problem that my input was faster than mysql server accepted data.

So I want to close this question and create a new question on how to create a delay in sql foreach.

It seems weird to me.

First I input the following query in MySQL CLI:

INSERT INTO table1 VALUES (null, 女神在, now(), 0, 0);

It works fine. The data gets inserted as expected.

Then, I input the same query from a Node.js MySQL script.

app.get('/db', (req, res) => {
    let var1 = 'english word'
    let sql = 'INSERT INTO filelist VALUES (null, ?, ?, ?, ?)';
    let addeddate = 'now()';
    let isdeleted = '0';
    let ismodified = '0';
    let params = [var1, addeddate, isdeleted, ismodified];

    connection.query(sql, params,
        (err, rows, fields) => {
            if(err) {
                console.log(err);
            } else {
                res.send(rows);
                console.log(rows);
            }
        }
    )
    res.render('view');
})

It works fine too, but if I change the value of var1 to Chinese letters, I get the following error:

C:\Users\myapp\node_modules\mysql\lib\protocol\Parser.js:437
      throw err; // Rethrow non-MySQL errors
      ^

Error [ERR_HTTP_HEADERS_SENT]: Cannot set headers after they are sent to the client
    at ServerResponse.setHeader (_http_outgoing.js:526:11)
    at ServerResponse.header (C:\Users\myapp\node_modules\express\lib\response.js:771:10)
    at ServerResponse.send (C:\Users\myapp\node_modules\express\lib\response.js:170:12)
    at ServerResponse.json (C:\Users\myapp\node_modules\express\lib\response.js:267:15)
    at ServerResponse.send (C:\Users\myapp\node_modules\express\lib\response.js:158:21)
    at Query.<anonymous> (C:\Users\myapp\app_file.js:166:21)
    at Query.<anonymous> (C:\Users\myapp\node_modules\mysql\lib\Connection.js:526:10)
    at Query._callback (C:\Users\myapp\node_modules\mysql\lib\Connection.js:488:16)
    at Query.Sequence.end (C:\Users\myapp\node_modules\mysql\lib\protocol\sequences\Sequence.js:83:24)
    at Query._handleFinalResultPacket (C:\Users\myapp\node_modules\mysql\lib\protocol\sequences\Query.js:149:8) {
  code: 'ERR_HTTP_HEADERS_SENT'
}
Program node app_file.js exited with code 1

res.render on browser is totally fine. even if var1 is Chinese.

My default character set of db and table is 'utf8_general_ci' of course. I used util.format or utf8.encode too, but I got the same error.

I followed Emoji are not inserting in database node js mysql

And I succedeed in inputing "A data" into my table, but the error message still occurs.

I tried again with 'Array', which is what I originally wanted, but it failed.

I found show variables like 'c%'; on google and I typed in mysql cli.

character_set_client        utf8
character_set_connection    utf8
character_set_database      utf8
character_set_filesystem    binary
character_set_results   
character_set_server        latin1
character_set_system        utf8
character_sets_dir          /rdsdbbin/mysql-5.7.22.R5/share/charsets/
check_proxy_users           OFF
collation_connection        utf8_general_ci
collation_database          utf8_general_ci
collation_server            latin1_swedish_ci
completion_type             NO_CHAIN

it seems almost every parts are utf8 but server is latin.

is it critical to me? and then how to change this?

To be honest, I'm not too familiar with Node.js. But, from other methods, I know that it's typically not enough to make the database environment be utf-8 if the application environment is not. For instance, in PHP oop style when dealing with prepared statements, we declare the charset of the query as well. Maybe there's a similar thing in Node.js? Example of PHP oop style: `$charset = 'utf8mb4';`and `$conn->set_charset($charset);` — Martin, Mar 15 '20 at 12:15
I googled with " charset utf8mb4 ", and i found https://stackoverflow.com/questions/36280148/emoji-are-not-inserting-in-database-node-js-mysql, now I'll try it — E.Laemas Kim, Mar 15 '20 at 12:22

Martin · Answer 1 · 2020-03-15T12:40:41.240

0

Going through your question and your current style of debugging, it's safe to say that the problem lies in within the application, and not the database, since you are able to insert Chinese letters manually.

With that said, while your database environment is utf-8, your application might not be. When dealing with OOP and prepared statements, we usually define a charset for our connection so that we make sure that our queries etc. are in the correct formats.

Example:

var connection = mysql.createConnection({
    host : 'your_ip',
    user : 'user',
    password : 'password',
    database : 'db',
    charset : 'utf8mb4'
});

Make sure to restart your Node server after this implementation.

edited Mar 15 '20 at 12:40

answered Mar 15 '20 at 12:27

Martin

2,326
1
12
22

Have you tried restarting your node server after implementing the charset? @E.LaemasKim – Martin Mar 15 '20 at 12:34
of course. actually, I rebooted my pc.... too much action but i should do anything... – E.Laemas Kim Mar 15 '20 at 12:35

How to insert Chinese characters into a MySQL database from Node.js?

1 Answers1