Just Can't Connect To MySQL From Node.js App . . .

[corinthian13]

I had no problem connecting to MySQL from within this node app when on Windows. I am now on Ubuntu 20.04.2, using Node 14.15.1 and MySQL version 8.0.27-0ubuntu0.20.04.1 for Linux on x86_64 ((Ubuntu)).

Below is the script to check connection to MySQL:

const mysql = require('mysql');

const queryNodeAppData = (query) =>
{
    console.log("In MySQL Query script now ...")
    console.log("Query: " + query);

    const conn = mysql.createConnection(
    {
        host: "127.0.0.1",
        database: "nodeapp",
        socketPath: "/var/run/mysqld/mysqld.sock",
        user: "root",
        password: "ziziziziz"
    });

    console.log("Connection user: " + conn.user);

    conn.connect(errconn =>
    {
        if (errconn) 
        {
            console.error('Error connecting: ' + errconn.stack);
            return "Invalid";
        }
        else
        {
            console.log('Connected as id ' + connection.threadId);
            return "Valid;"
        }
    });

};

module.exports = { queryNodeAppData };

CONSOLE OUTPUT

$ nodemon app
[nodemon] 2.0.14
[nodemon] to restart at any time, enter `rs`
[nodemon] watching path(s): *.*
[nodemon] watching extensions: js,mjs,json
[nodemon] starting `node app.js`
HTTP Server for NodeApp started at port 3000
We're in to the back end of NodeApp !
Someone has POSTed data to the Node server ...
URL trimmed endpoint: reg-user
Input Value: tamjk
In router.js now ...
Endpoint: reg-user       Data: tamjk
Handler: authen.js
In handler authen.js now ...
Mode: reg  Field: user  Value: tamjk
In format validation now ...
Format Validation Message: Valid format
Connecting to user-data database checking user ...
In MySQL Query script now ...
Query: SELECT user FROM users WHERE user = 'tamjk'
Connection user: undefined
Router returned message undefined
Callback Message: undefined
Message: undefined
Sent back response: undefined
_http_outgoing.js:696
    throw new ERR_INVALID_ARG_TYPE('first argument',
    ^

TypeError [ERR_INVALID_ARG_TYPE]: The first argument must be of type string or an instance of Buffer or Uint8Array. Received undefined
    at write_ (_http_outgoing.js:696:11)
    at ServerResponse.write (_http_outgoing.js:661:15)
    at reqCallback (/home/sandbar/Desktop/nodeapp-local/app.js:116:6)
    at IncomingMessage.<anonymous> (/home/sandbar/Desktop/nodeapp-local/app.js:87:10)
    at IncomingMessage.emit (events.js:327:22)
    at endReadableNT (_stream_readable.js:1327:12)
    at processTicksAndRejections (internal/process/task_queues.js:80:21) {
  code: 'ERR_INVALID_ARG_TYPE'
}
[nodemon] app crashed - waiting for file changes before starting...

There is no console log done within the conn.connect {.....} block. This leads me to think that no connection has been made. The final error that crashes the app is simple due to no "Valid" or "Invalid" string being returned - this in turn leads to an UNDEFINED value being written to the XHR response variable. Since the XHR's response was predefined as text, this crashes the app.

I just see nothing in the Ubuntu system logs for MySQL for any event like this.

Why won't Node connect to MySQL ?

[dougwilson]

There is no console log done within the conn.connect {.....} block. This leads me to think that no connection has been made.

The reason for this is because your app is crashing before mysql can either connect or fail to connect. You didn't include how you are calling queryNodeAppData, but it is impossible to use return within a callback. You can read more about how callbacks work in Node.js on their site: https://nodejs.org/en/knowledge/getting-started/control-flow/what-are-callbacks/

[corinthian13]

The cause of the crash is not the callback return statement. The console log output running the app clearly show the intended execution flow through app.js (commonServer) -> router.js -> handler.js -> format.js -> handler.js -> mysqldb.js (queryNodeAppData) -> handler.js -> router.js -> app.js (reqCallback) It crashes at the same point in app.js (i.e. when writing an undefined value to a string XHR response object) when a call is made to queryNodeAppdata using a callback parameter. For example, in the authen.js module, which delegates verification of users/emails/passwords within the app's user-data table, there would be a line like: return mysqldb.queryNodeAppData(query, authCallback); The authen.js module holds the authCallback(err, result, fields) function.

What fascinates me is how a mysql module Connection object is created without apparent error or complaint of inadequate parameters, etc. Looking at the mysql Connection.js source code, once it sees a socketPath parameter, it seeks to make a socket-based connection - otherwise it makes a TCP/IP one. Yet this Connection object seemingly cannot even attempt a connection since, if it did, some loggable error would be expected to emerge from the unsuccessful attempt.

[dougwilson]

Hi @corinthian13 I'm sorry you are having trouble. I was just taking a guess since the code was incomplete (I couldn't run it as-is to get the same error) and I also unfortunately don't know how your server is set up to understand why it won't connect to it.

I'm happy to help more, but I can only guess at what is missing, and it's difficult to guess correctly. You are always welcome to make a pull request for a fix if there is an issue you tracked it down to. Otherwise we are happy to help if we can get more information on how to repliacte the issue.

[corinthian13]

I've abbreviated the app into a single app.js script just to show the operation. In the code below, I just enter a username on the sign-up form and this becomes the first data received from the incoming XHR. As this is the user name a suitable query is then drafted to check if it already exists within the nodeapp.users database table. (A simple one-row table showing user, email and password)

Running the app in this form causes it to behave exactly as before and crash at

write(200, message) 

inside function reqCallback(res, message)

I am wondering seriously if there could be some MySQL configuration that prevents connection but would not send back any error info . . .

const http = require('http');       
const https = require('https');                                                                                      
const util = require('util');
const fs = require('fs');
const StringDecoder = require('string_decoder').StringDecoder;
const mysql = require('mysql');

                                                                                                                        // Set CORS headers ...
const headers = { 'Access-Control-Allow-Origin': '*',                                                                   // Allow XHR response from any origin
                  'Access-Control-Request-Method': '*',
                  'Access-Control-Allow-Headers': 'Content-Type',
                  'Access-Control-Allow-Methods': 'OPTIONS, POST, GET',
                  'Access-Control-Max-Age': 2592000
                };

let endpoint,                                                                                                           // Request endpoint string
    mode;                                                                                                               // Request mode, e.g. auth, logout, etc

/** Creates servers for HTTP user requests ...
 *  @param  {Object} req  - an XHR request object
 *  @param  {Object} res  - an XHR response object
 *  @return {Object} - the XHR response object
 * */
const httpServer = http.createServer((req, res) =>
{
    commonServer(req, res);
});

/** Creates servers for HTTP user requests ...
 *  @param  {Object} req  - an XHR request object
 *  @param  {Object} res  - an XHR response object
 *  @return {Object} - the XHR response object
 * */
// const httpsServer = https.createServer((req, res) =>
// {
//      commonServer(req, res);
// });

/** Serves the various user requests sent via HTTP to either of the Node.js servers above.
 *  @param  {Object} req  - an XHR request object
 *  @param  {Object} res  - an XHR response object
 *  @return {Object} - the XHR response object
 *  */
const commonServer = (req, res) => 
{
    console.log("We're in to the back end of NodeApp !");                                                               
                                                                                                                        // Global vars ...
    let field,                                                                                                          // Field name, e.g. 'email', 'password', etc                                                        
        data = '',                                                                                                      // Field value, e.g. 'j.murphy@gmail.com', etc
        message,                                                                                                        // Variable for validation feedback
        dashIndex;                                                                                                      // Index for hyphen in endpoint

    if (req.method === "GET")
    {

    }

    if(req.method === 'POST')
    {
        console.log('Someone has POSTed data to the Node server ...');                                                  // Log XHR method ...
        endpoint  = req.url.substring(1);
        console.log('URL trimmed endpoint: ' + endpoint);
        const decoder = new StringDecoder('UTF-8');
        let buffer = '';
                                                                                                                        // Initialize buffer string for input data ...
        req.on('data', (chunk) =>
        {
            buffer += decoder.write(chunk);                                                                             // Convert stream buffer to string
        });

        req.on('end', () =>                                                                                             // Extract mode and field from endpoint ...
        {   
            buffer += decoder.end();
            console.log("Input Value: " + buffer);
            data = buffer;
            // First data is user name
            let query = "SELECT user FROM users WHERE user = '" + data + "'";

            reqCallback(res, queryNodeAppData(query, authCallback));                                                                // Relay request, data and callback to router
        });                                                                                                             
    }
}

/** Queries the app's MySQL database 
  * @param {Object} query - the query presented to the MySQL DB
  * @param {Object} dbcallback - the callback function applied to the query response
 * */
const queryNodeAppData = (query, dbCallback) =>
{
    console.log("In MySQL Query script now ...")
    console.log("Query: " + query);

    const conn = mysql.createConnection(
    {
        socketPath: "/var/run/mysqld/mysqld.sock",
        database: "nodeapp",
        user: "root",
        password: "MY.$qu3@13r"
    });

    console.log("Connection user: " + conn.user);

    conn.connect(query, (errconn, result, fields) =>
    {
        if (errconn) 
        {
            console.error('Error connecting: ' + errconn.stack);
            dbCallback(errconn, null, null);
        }
        else
        {
            console.log('Connected as id ' + connection.threadId);
            dbCallback(null, result, fields);
        }
    });

};

/** Interprets user/email/password check in users table and returns response
  * @param {Object} err - the error occcurring, if any
  * @param {Object} result - the result array of the query
  * @param {Object} fields - the data fields of the result
 */ 
const authCallback = (err, result, fields) =>                                                                                                                                       // Callback function for checking user.
{

    console.log("In authCallback now ...");
    let field = fields[0],
        value = result[0][field];

    if (err != null)
    {
        console.log("Auth DB Error: " + err.message);
        message = "Error: " + err.message;
        throw err;
    }
    else if (result != null)
    {
        console.log("Field: " + field + "   Value: " + value  + "Mode " + mode + "   Message: " + message );
        console.log("Result length: " + result.length);
        if (result.length != 0)                                                                                                                                                                     // No matching record in users table ?
        {
            if (mode == 'reg' && field != 'password')                                                                                                                       // Registration => user/email string acceptable
            {
                message = field + ' taken';                                                                                     
            }
            else message = 'Valid';                                                                                                                                             // Login => valid user/password
        }
        else 
        {
            if (mode === 'login')                                                                                                                                                                   // No matching record in users table ?
            {
                message = field + ' invalid';                                                                                                                                               // Login => Invalid user/password
            }
            else if (mode === 'reg' && field != 'password')                                                                                                                 // Reg => Valid new user/email
            {
                message = 'Valid';                                                                                            
            }
        }   
    }
    return "Message";
}

/** Interprets user/email/password check in users table and returns response
 *  @param  {Object} res  - an XHR response object
  * @param {Object} message - text message to be passed as XHR response
 */ 
const reqCallback = (res, message)  =>                                                                                  // Callback function for checking user.
{
    console.log("Callback Message: " + message);                                                                                
                                                                                                                        // Prepare headers for http response ...
    if (headers === null)
    {
        console.log("Headers variable seems to be null ...");
    }
    if (message === null)
    {
        console.log("Result object seems to be null ...");
        message = "Null message";
    }
    console.log("Message: " + message);

    res.writeHead(200, headers);                                                                                        

    console.log('Sent back response: ' + message);
    res.write(message);
    res.end();
    return;                                                                                                                                                                                         
};  

// Start user event server listener ...

httpServer.listen(3000, () =>
{
    console.log("HTTP Server for NodeApp started at port 3000");                                                        // Log server listener startup
});

[mbaumgartl]

Seems you're using the connect() method incorrectly.

Please check the introduction example: https://github.com/mysqljs/mysql#introduction.

[corinthian13]

mbaumgartl:

No - breaking it into 2 separate instructions makes no difference to the output. The problem lies with MySQL connection protocols, I feel. My gut feeling is that there's some default configuration on my MySQL installation that's preventing the connection request from even being treated by MySQL - hence the lack of error output. I'll explore it some more . . .

[corinthian13]

mysql-error.log Still no access to MySQL database. I am getting into the mysql package's Connection.js script - I know this from a console.log(.) I placed in it. I am getting an undefined result from the connection attempt without any error info. This suggests to me that my attempt to connect to the MySQL server is being denied. But why ? I am running the Node app locally with a local instance of a MySQL database. Why should a connection attempt using a local host MySQL database with root/password data be denied ? I attach the MySQL error log file since I reinstalled MySQL Server on my Ubuntu 20.04 system today.

[corinthian13]

Problem Solved !

It seems from here that the npm package mysql has had issues with the tougher authentication process of MySQL 8 for 3 years now.

Although it has periodically been claimed that mysql has fixed the issues, these remain.

So the advice is to just use the package mysql2 instead of mysql.

Isn't it astonishing that no one on this issues forum had heard of this problem ?

[dougwilson]

Hello, sorry for your issue and glad you resolved it. This module supports the standard password authentication and does not make any claims that it supports the new mysql 8 auth modes. You can use mysql 8, just need to use the old password auth for the time being.

You're always welcome to contribute towards solving the issue if you like, or wait until someone else does so, if you don't want to use the password authentication. Another module is also a good choice.

There is already an open issue for adding the new mysql 8 auth types, so if that was your issue, then no need to keep this one open as a duplicate issue.

[corinthian13]

Well, I did have a good poke around within the mysql package, left console.log(..) statements to track errors and so on. The Connection.connect(..) function within mysql was executed though unsuccessful. I was struck at the old-style syntax of the mysql modules - surely the new arrow format would be useful for most functions ? But the main problem was that in testing the app above, NO ERROR MESSAGE WHATEVER came from mysql.

When I created a small Node app just to test MySQL connection I started to get the error that led me to a post from another struggler and thence to the final solution.

[dougwilson]

Hi @corinthian13 I'm sorry you didn't get any error message -- you should have been getting one, though. If you would like to help with that issue, would you be able to provide a complete app to run and instructions for how to get no error message or contribute a fix to get an error message working for this issue?

[corinthian13]

@dougwilson:

All sorted now. The mysql <-> MySQL connection was not the only issue with the code. There was also a callback reference error - and these always obfuscate other issues. Once these were sorted out and the working app re-run with mysql instead of mysql2, you get the correct error trace:

*Error connecting: Error: ER_NOT_SUPPORTED_AUTH_MODE: Client does not support authentication protocol requested by server; consider upgrading MySQL client at Handshake.Sequence._packetToError (/home/sandbar/Desktop/nodeapp-local/node_modules/mysql/lib/protocol/sequences/Sequence.js:47:14) at Handshake.ErrorPacket (/home/sandbar/Desktop/nodeapp-local/node_modules/mysql/lib/protocol/sequences/Handshake.js:123:18) at Protocol._parsePacket (/home/sandbar/Desktop/nodeapp-local/node_modules/mysql/lib/protocol/Protocol.js:291:23) at Parser._parsePacket (/home/sandbar/Desktop/nodeapp-local/node_modules/mysql/lib/protocol/Parser.js:433:10) at Parser.write (/home/sandbar/Desktop/nodeapp-local/node_modules/mysql/lib/protocol/Parser.js:43:10) at Protocol.write (/home/sandbar/Desktop/nodeapp-local/node_modules/mysql/lib/protocol/Protocol.js:38:16) at Socket. (/home/sandbar/Desktop/nodeapp-local/node_modules/mysql/lib/Connection.js:98:28) at Socket. (/home/sandbar/Desktop/nodeapp-local/node_modules/mysql/lib/Connection.js:554:10) at Socket.emit (events.js:400:28) at addChunk (internal/streams/readable.js:293:12)

at Protocol._enqueue (/home/sandbar/Desktop/nodeapp-local/node_modules/mysql/lib/protocol/Protocol.js:144:48)
at Protocol.handshake (/home/sandbar/Desktop/nodeapp-local/node_modules/mysql/lib/protocol/Protocol.js:51:23)
at Connection.connect (/home/sandbar/Desktop/nodeapp-local/node_modules/mysql/lib/Connection.js:126:18)
at Connection._implyConnect (/home/sandbar/Desktop/nodeapp-local/node_modules/mysql/lib/Connection.js:482:10)
at Connection.query (/home/sandbar/Desktop/nodeapp-local/node_modules/mysql/lib/Connection.js:212:8)
at Object.queryNodeAppData (/home/sandbar/Desktop/nodeapp-local/dbs/mysqldb.js:28:7)
at Object.handle (/home/sandbar/Desktop/nodeapp-local/handlers/authen.js:69:12)
at Object.route (/home/sandbar/Desktop/nodeapp-local/routers/router.js:37:11)
at IncomingMessage.<anonymous> (/home/sandbar/Desktop/nodeapp-local/app.js:48:17)
at IncomingMessage.emit (events.js:412:35)

Error: Error: ER_NOT_SUPPORTED_AUTH_MODE: Client does not support authentication protocol requested by server; consider upgrading MySQL client /home/sandbar/Desktop/nodeapp-local/app.js:65 throw err; ^*

*Error: ER_NOT_SUPPORTED_AUTH_MODE: Client does not support authentication protocol requested by server; consider upgrading MySQL client at Handshake.Sequence._packetToError (/home/sandbar/Desktop/nodeapp-local/node_modules/mysql/lib/protocol/sequences/Sequence.js:47:14) at Handshake.ErrorPacket (/home/sandbar/Desktop/nodeapp-local/node_modules/mysql/lib/protocol/sequences/Handshake.js:123:18) at Protocol._parsePacket (/home/sandbar/Desktop/nodeapp-local/node_modules/mysql/lib/protocol/Protocol.js:291:23) at Parser._parsePacket (/home/sandbar/Desktop/nodeapp-local/node_modules/mysql/lib/protocol/Parser.js:433:10) at Parser.write (/home/sandbar/Desktop/nodeapp-local/node_modules/mysql/lib/protocol/Parser.js:43:10) at Protocol.write (/home/sandbar/Desktop/nodeapp-local/node_modules/mysql/lib/protocol/Protocol.js:38:16) at Socket. (/home/sandbar/Desktop/nodeapp-local/node_modules/mysql/lib/Connection.js:98:28) at Socket. (/home/sandbar/Desktop/nodeapp-local/node_modules/mysql/lib/Connection.js:554:10) at Socket.emit (events.js:400:28) at addChunk (internal/streams/readable.js:293:12)

at Protocol._enqueue (/home/sandbar/Desktop/nodeapp-local/node_modules/mysql/lib/protocol/Protocol.js:144:48)
at Protocol.handshake (/home/sandbar/Desktop/nodeapp-local/node_modules/mysql/lib/protocol/Protocol.js:51:23)
at Connection.connect (/home/sandbar/Desktop/nodeapp-local/node_modules/mysql/lib/Connection.js:126:18)
at Connection._implyConnect (/home/sandbar/Desktop/nodeapp-local/node_modules/mysql/lib/Connection.js:482:10)
at Connection.query (/home/sandbar/Desktop/nodeapp-local/node_modules/mysql/lib/Connection.js:212:8)
at Object.queryNodeAppData (/home/sandbar/Desktop/nodeapp-local/dbs/mysqldb.js:28:7)
at Object.handle (/home/sandbar/Desktop/nodeapp-local/handlers/authen.js:69:12)
at Object.route (/home/sandbar/Desktop/nodeapp-local/routers/router.js:37:11)
at IncomingMessage.<anonymous> (/home/sandbar/Desktop/nodeapp-local/app.js:48:17)
at IncomingMessage.emit (events.js:412:35) {

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 }*