I cannot connect to mysql database using node.js

Question

I am total beginner to database. I am using localhost(127.0.0.1) with port 9090. I successfully created mysql database, ran/used it in windows 10 command prompt and MySQL Workbench.

But when I try to run it with node.js, I fail to connect to server.

Below is index.js file I tried to run in windows command line.

var mysql = require('mysql');
var client = mysql.createConnection({
    user:'root',
    password:'password',
});
client.query('USE mydb');
client.query('SELECT * FROM PRODUCTS',function(error, result, fields){
if(error){
    console.log(error);
} else{
    console.log(result);
}
});

And it results in this error.

D:\Coding\VSCode\nodejs>node index.js

Error: connect ECONNREFUSED 127.0.0.1:3306
at TCPConnectWrap.afterConnect [as oncomplete] (net.js:1146:16)
--------------------
at Protocol._enqueue (D:\Coding\VSCode\nodejs\node_modules\mysql\lib\protocol\Protocol.js:144:48)
at Protocol.handshake (D:\Coding\VSCode\nodejs\node_modules\mysql\lib\protocol\Protocol.js:51:23)
at Connection.connect (D:\Coding\VSCode\nodejs\node_modules\mysql\lib\Connection.js:116:18)
at Connection._implyConnect (D:\Coding\VSCode\nodejs\node_modules\mysql\lib\Connection.js:454:10)
at Connection.query (D:\Coding\VSCode\nodejs\node_modules\mysql\lib\Connection.js:196:8)
at Object.<anonymous> (D:\Coding\VSCode\nodejs\index.js:10:8)
at Module._compile (internal/modules/cjs/loader.js:1063:30)
at Object.Module._extensions..js (internal/modules/cjs/loader.js:1092:10)
at Module.load (internal/modules/cjs/loader.js:928:32)
at Function.Module._load (internal/modules/cjs/loader.js:769:14) {
errno: -4078,
code: 'ECONNREFUSED',
syscall: 'connect',
address: '127.0.0.1',
port: 3306,
fatal: true
}

I am at the stage not knowing what I am not knowing. I tried to change port in createConnection() function like this

var client = mysql.createConnection({
user:'root',
password:'password',
port: '9090'
});

Then I get this error.

D:\Coding\VSCode\nodejs>node index.js

Error: ER_NOT_SUPPORTED_AUTH_MODE: Client does not support authentication protocol requested by 
server; consider upgrading MySQL client
at Handshake.Sequence._packetToError 
(D:\Coding\VSCode\nodejs\node_modules\mysql\lib\protocol\sequences\Sequence.js:47:14)
at Handshake.ErrorPacket 
(D:\Coding\VSCode\nodejs\node_modules\mysql\lib\protocol\sequences\Handshake.js:123:18)
at Protocol._parsePacket (D:\Coding\VSCode\nodejs\node_modules\mysql\lib\protocol\Protocol.js:291:23)
at Parser._parsePacket (D:\Coding\VSCode\nodejs\node_modules\mysql\lib\protocol\Parser.js:433:10)
at Parser.write (D:\Coding\VSCode\nodejs\node_modules\mysql\lib\protocol\Parser.js:43:10)
at Protocol.write (D:\Coding\VSCode\nodejs\node_modules\mysql\lib\protocol\Protocol.js:38:16)
at Socket.<anonymous> (D:\Coding\VSCode\nodejs\node_modules\mysql\lib\Connection.js:88:28)
at Socket.<anonymous> (D:\Coding\VSCode\nodejs\node_modules\mysql\lib\Connection.js:526:10)
at Socket.emit (events.js:315:20)
at addChunk (_stream_readable.js:309:12)
--------------------
at Protocol._enqueue (D:\Coding\VSCode\nodejs\node_modules\mysql\lib\protocol\Protocol.js:144:48)
at Protocol.handshake (D:\Coding\VSCode\nodejs\node_modules\mysql\lib\protocol\Protocol.js:51:23)
at Connection.connect (D:\Coding\VSCode\nodejs\node_modules\mysql\lib\Connection.js:116:18)
at Connection._implyConnect (D:\Coding\VSCode\nodejs\node_modules\mysql\lib\Connection.js:454:10)
at Connection.query (D:\Coding\VSCode\nodejs\node_modules\mysql\lib\Connection.js:196:8)
at Object.<anonymous> (D:\Coding\VSCode\nodejs\index.js:10:8)
at Module._compile (internal/modules/cjs/loader.js:1063:30)
at Object.Module._extensions..js (internal/modules/cjs/loader.js:1092:10)
at Module.load (internal/modules/cjs/loader.js:928:32)
at Function.Module._load (internal/modules/cjs/loader.js:769:14) {
code: 'ER_NOT_SUPPORTED_AUTH_MODE',
errno: 1251,
sqlMessage: 'Client does not support authentication protocol requested by server; consider upgrading 
MySQL client',
sqlState: '08004',
fatal: true
}

Debasis Das · Accepted Answer · 2020-11-23 07:55:02Z

1

1.Open your windows terminal and login : mysql -uroot -p ( then type you password )

use mysql;
update user set authentication_string=password(''), plugin='mysql_native_password' where user='root';

4 Restart you mysql db : sudo service mysql restart

answered Nov 23, 2020 at 7:55

Debasis Das

5693 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Debasis Das Over a year ago

@GodofCoding, check this link, may be you can get solution github.com/nodejs/help/issues/1555

user10988119 Over a year ago

Thank you, I just found out answer from other source too.

user10988119 · Accepted Answer · 2020-11-23 08:32:02Z

0

Doing this in MySQL Workbench solved the problem.

 ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password 
 BY 'YourRootPassword';

 FLUSH PRIVILEGES;

answered Nov 23, 2020 at 8:32

user10988119

Collectives™ on Stack Overflow

I cannot connect to mysql database using node.js

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related