2

Goal: Do a simple query to the database.

Expected results: "please print something!" and the results from the query are printed on the terminal.

Actual results: Nothing is printed on the terminal.

Errors: No error message.

Here is the db.js file:

var mysql = require('mysql');
    
var connection = mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: 'root',
    database: 'todoDB'
});

connection.connect();

connection.query('SELECT * FROM categories', function (err, res, fields) {
    console.log("please print something!")
    if (err) throw err;
    console.log(res);
});

connection.end();

I execute this file using:

node db.js

On the mysql cli, I am able to do this query without any problem with the database name, credentials, and query given above.

I know that connection.connect() works since when I'm inputting the code below, the terminal prints "Database is connected!" I think the problem occurs at connection.query, but I am not sure why.

connection.connect(function(err){
    if(!err){
        console.log("Database is connected");
    } else {
        console.log("Error while connecting with database");
        console.log(err);
    }
});

I've looked through all the related questions on stackoverflow and tried them, but none of the solutions seems to resolve the problem that I have.

Emily Chiu
  • 112
  • 1
  • 12
  • A connection is implicitly established by invoking a query, so you don't need `connection.connect();` and you don't need `connection.end();`. Remove them both and try again. Since you are not running each step in your example asynchronously it is possible you're ending your connection before your query has completed depending on a number of variables such as: network speed, app resources, and the size of results generated by your query. – dusthaines Jul 02 '21 at 17:38
  • @dusthaines I removed the `connection.connect()` and `connection.end()`. There is still nothing that is printed on the terminal. – Emily Chiu Jul 02 '21 at 17:48
  • Try changing `if (err) throw err;` to... `if (err) console.log(err);` You can also add `debug: true` to your `mysql.createConnection` configs which will provide verbose details on all incoming and outgoing packets on `stdout`. – dusthaines Jul 02 '21 at 18:36
  • @dusthaines I changed the code and pasted the output of when I run `node db.js` [here](https://pastebin.com/9E2P1K8z) – Emily Chiu Jul 02 '21 at 18:50
  • Worth noting that his exact code works fine on my environment. – Alex Collette Jul 02 '21 at 19:05
  • On the mysql CLI, are you signing in with a password or using your system auth to login? Try logging in using something like mysql workbench to see if you have any issues with your creds. – Alex Collette Jul 02 '21 at 19:07
  • Agreed with Alex on both points. I am able to run a similar query without issue - but your `stdout` shows `HandshakeInitializationPacket > pluginData: caching_sha2_password`, whereas mine shows: `HandshakeInitializationPacket > pluginData: mysql_native_password`. So Alex might be onto something with auth to mysql idea. Also what version of `node` are you running? (in terminal run : `node -v`) – dusthaines Jul 02 '21 at 19:30
  • I just set up a mysql server on my local ubuntu machine, and by default it uses a auth_socket plugin. Check out this SO post and see if it lines up with what you are seeing. https://stackoverflow.com/questions/39281594/error-1698-28000-access-denied-for-user-rootlocalhost – Alex Collette Jul 02 '21 at 19:38
  • Since you are using `mysql v8` looks like it might be an issue with default authentication method. [See more here on a similar issue.](https://stackoverflow.com/questions/57719671/not-able-to-authenticate-my-node-jsv10-15-3-app-npm-mysql-v2-17-1-with-mysq) @alex-collette what version of `mysql` was used for the test unit you just spun up? – dusthaines Jul 02 '21 at 19:41
  • @dusthaines The version of node is this when I run `node -v`: v15.14.0. Just a note, usually when I start my termine, running this will give me v6.3.1, but I override it with `nvm use v15` @AlexCollette I'm doing it on mysql workbench and I have no problems with authenticating. – Emily Chiu Jul 02 '21 at 19:41
  • @dusthaines, im on v8.0.25. hmmmm... That was my main idea, Ill think on it more – Alex Collette Jul 02 '21 at 19:45
  • just to confirm, can you add `console.log('Version: ' + process.version);` to the top of `db.js` and ensure the app is running node v15 and not v6 – dusthaines Jul 02 '21 at 19:45
  • @dusthaines Adding the code above gives Version: v15.14.0 – Emily Chiu Jul 02 '21 at 19:48
  • good to know. probably the auth standard then and alex's answer is the way to go. [it seems this is a known issue with an open issue thread and pull request](https://github.com/mysqljs/mysql/issues/2382) – dusthaines Jul 02 '21 at 19:55

1 Answers1

3

So it looks like mySQL V8 uses caching_sha2_password as the new authentication standard, which the nodeJS plugin does not support. Connect to your db and try creating a new user that uses the native password auth type.

CREATE USER 'foo'@'localhost' IDENTIFIED WITH mysql_native_password BY 'bar';
Alex Collette
  • 1,664
  • 13
  • 26