Error handling in mySQL in Node

[scripting]

The setting

1. I am using the Node.js mysql package to interface to the mySQL app that's running locally.

2. All queries are done by the Node app, locally.

3. The Node app is also an HTTP server. A couple of the calls to the server via HTTP result in local calls to the database. The Node app is basically a gateway for the database.

The problem

I do a query first thing in the morning but the connection from the Node app on the database is been lost. Here's the error I see in the terminal app.

The only call that the app makes at this time is within a function called runSqltext. Here's what it looks like.

function runSqltext (s, callback) {
	theSqlConnection.query (s, function (err, result) {
		if (err) {
			console.log (err.message);
			if (callback !== undefined) {
				callback (undefined);
				}
			}
		else {
			console.log ("result == " + utils.jsonStringify (result));
			if (callback !== undefined) {
				callback (result);
				}
			}
		});
	}

As you can see I am catching the error and displaying it on the console. There is no message on the console. So it seems the error is not flowing through this code.

I have read the error handling section of the Node package docs. They provide an easy way out:

connection.on('error', function() {});

But they also are careful to say that this is not the recommended procedure.

So what do I do?

Follow-up question

Since I now know that the connection can be lost, should I be checking for that, say every second, and re-establishing the connection when it's lost? How can I tell that the connection is lost?

I do this in my websockets code, so it's no big deal, just not sure what's going on here.

Thanks in advance for your help!

[jystervinou]

A few ideas. (i may be wrong)

1- Try logging with console.log (err.code) instead of console.log (err.message). You should get the same "connection lost" error code.

2- The app must be opening the connection to the MySQL server, do the requests, then not using the connection until the next morning. By this time, MySQL has closed the idling connection (the default is 8 hours ). I assume you are not explicitly closing the connection elsewhere in the code.

3- So either you must close the connection after you did all the requests. (using connection.end on your connection object)

4- Or you can use the connection pooling options. The app will maintain a pool of open connections and provide you with an opened one. (the doc explains that a ping to mysql is performed before providing one of the idle pooled connections, to check it is still indeed open)

[scripting]

JY, thanks for the help, as always! ;-)

1. I added code to log the code and added some more text to the console.log call so no matter what it won't be invisible.

2. Your inference is correct. The connection is idle. I didn't know that MySQL closes the connection after it's been idle. That's key info. I am not explicitly closing the connection.

Questions --

How expensive is it to close the connection and re-open it on each request? If it's not expensive, I'll just do that.

I'm confused as to why the program was terminated since I am checking for the error. This of course, is hard to test because the server has to be idle for eight hours to reproduce?

Ideally, I'd detect that the connection is closed before re-establishing it.

[jystervinou]

I'm confused as to why the program was terminated since I am checking for the error. This of course, is hard to test because the server has to be idle for eight hours to reproduce?

"Any type of connection error (handshake or network) is considered a fatal error"?

How expensive is it to close the connection and re-open it on each request? If it's not expensive, I'll just do that.

With MySQL i remember that it was "fast", so that is an easy option. But i guess that with a high traffic site, it would be no good.

I think the mysql node module Pooling option is meant to handle all this connection stuff for you, so you can count on a valid connection when you need one.

[scripting]

OK, I'll look at pooling.

[scripting]

JY, based on this thread, I changed the routine as follows --

function runSqltext (s, callback) {
	theSqlConnection.connect (function (err) {
		if (err) {
			console.log ("runSqltext: err.message == " + err.message);
			if (callback !== undefined) {
				callback (undefined);
				}
			}
		else {
			theSqlConnection.query (s, function (err, result) {
				if (err) {
					console.log ("runSqltext: err.code == " + err.code + ", err.message == " + err.message);
					if (callback !== undefined) {
						callback (undefined);
						}
					}
				else {
					console.log ("result == " + utils.jsonStringify (result));
					if (callback !== undefined) {
						callback (result);
						}
					}
				});
			}
		});
	}

And it crashes with an error --

sqltext == REPLACE INTO ratings (idprogram, screenname, rating, whenCreated, jsontext) VALUES ('00015', 'jy', '2',  '2017-10-13 10:59:39', '{}');
runSqltext: err.message == Cannot enqueue Handshake after already enqueuing a Handshake.

[scripting]

I'm going to undo that change -- I have to stop working on this, I have a meeting to get to! ;-)

[jystervinou]

maybe because you're connecting an already connected connection (now that's an ugly sentence :-) )

[scripting]

Something like that! I have more reading to do -- in a more quiet less rushed mode. *Thank you* for all your help, as always! :-)

…

On Fri, Oct 13, 2017 at 11:35 AM, jystervinou ***@***.***> wrote: maybe because you're connecting an already connected connection (now that's an ugly sentence :-) ) — You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub <#37 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/ABm9O9iVoBiFtcs2TL6eEzCxQ1VA7t8hks5sr4M1gaJpZM4P3FsC> .

[jystervinou]

Looking at Pooling connections, you need to:

• replace mysql.createConnection by mysql.createPool
• replace .connect by .getConnection (you get provided with a valid connection object in the callback, use that to call .query on)
• Add connection.release(); at the end of your .query block.

[jystervinou]

If someone knows another way -- that does not require to chain getConnection.. query.. release -- for each SQL request, i'm all ears.

[scripting]

I posted a link at the top of Scripting News to remind people this thread is here.

…

On Fri, Oct 13, 2017 at 11:53 AM, jystervinou ***@***.***> wrote: If someone knows another way -- that does not require to chain getConnection.. query.. release -- for each SQL request, i'm all ears. — You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub <#37 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/ABm9O4i2QYIQVoVhh4qNG50q2z_O-WiIks5sr4dfgaJpZM4P3FsC> .

[danderson3]

https://stackoverflow.com/questions/23399111/safely-keeping-mysql-connections-alive