How to detect database connection state changes (on/off)?

[mreis1]

Hello @xdenser,

One of the biggest challenges I'm facing right now is on how to properly detect if my database server was turned off in order to recreate my app's connection pool (built with a third party library node-pool).

Let me provide you some context about the problem.

I start a web server. In that web server a connection pool is created, then someone shuts down the database (for maintenance purposes). Since I cant detect when the database became unavailable my connection pool still resides in my app memory.

So, ...fetching a connection from the pool will return a "ghost" FBConnection object which will instantly shutdown the server if I attempt to use it.

My pooling service allows me to validate a pool resource before returning it to the client.

But after shutting down the database the connection.connected property is still true as I mentioned above.

Connection { inAsyncCall: false, inTransaction: false, connected: true }

Is there any changes of propagating an event that would update this connected state if the database goes off/on?

This would allow me to decide if the connection resource is still available and if a destroy/create of a new connection resource is required.

[mreis1]

In order to reproduce the issue reported above I created the repo below. The README.txt file explains step by step how to put it to work.

https://github.com/mreis1/node-firebird-libfbclient-crash-on-connection-lost

@xdenser Could you take a look? Thank you :)

[xdenser]

I think there is no way to detect if connection has gone down w/o sending some request. But you say it throws seg fault and process exits instead of throwing some exception? So this is what should be done: throw Javascript exception when the connection is not valid?

[xdenser]

Try to start and rollback transaction before returning connection from pool try { var trans = conn.startNewTransactionSync(); trans.rollbackSync(); } catch(e) { console.log("error", e); conn.disconnect(); // here conn.connected == false }

[xdenser]

Aha I have found error. I also get seg fault when starting transaction asynchronously.

[mreis1]

Oh great. I tried the method above but still got a segmentation fault. Also the link below is a video where I show a explicit case where segmentation fault occurs. I tried your latest changes in master but the same error occurs. https://www.dropbox.com/s/wizm8byq7qwlmwo/segmentation-fault.mp4?dl=0

[xdenser]

When you use implicit transation (default one in connection object) discconnect() method first tries to commit this transaction. But when this transaction cannot be closed - as connection has gone (or what ever reason) it cannot set connected to false, Because the reason is unknown. Solution : use explicit tranasactions and then you will be able to close connection even if DB server has gone down in the middle of transaction.

[mreis1]

I did run a few extra tests to check where else a seg fault 11 could be thrown. Turns out that there 2/3 situations.

I found a workaround to some of them that I hope will reduce the frequency of app crashes in my production apps but I know that this effort cames with a price since I'm introducing a extra overhead that impacts in the app performance.

In this document I describe each step necessary to reproduce those errors and I also explain how I managed to prevent seg faults - Maybe this can help you detecting the issue. I put a stacktrace that might be helpful to understand the cause of the problem. The capture was made using node's segfault-handler.

Here's the link to the document https://docs.google.com/document/d/1IvHw8Pab8y5fJ93sMF4Y2F1oHAxUm2MPQDMSYb7-BpA/edit?usp=sharing

[xdenser]

This code works for me. No Seg fault.

var conn = fb_binding.createConnection(); conn.connectSync(cfg.db, cfg.user, cfg.password, cfg.role); if(conn.connected) { console.log("Connected to database"); }

var res = conn.querySync("select * from rdb$relations"); console.log("Query result 1", res) // after this line i stop server

setTimeout(function() { console.log("Try again") var res = conn.query("select * from rdb$relations", function(err){ console.log("Error", err); });

}, 20000);

[mreis1]

In your example you don't run into a seg fault because you run querySync while the connection is still available and the implicit transaction is open at that moment.

If you run this, a seg fault will probably occur:

var conn = fb_binding.createConnection();
conn.connectSync(cfg.db, cfg.user, cfg.password, cfg.role);
if(conn.connected) {
console.log("Connected to database");
}

// var res = conn.querySync("select * from rdb$relations");
// console.log("Query result 1", res)
// after this line i stop server

setTimeout(function() {
console.log("Try again")
var res = conn.query("select * from rdb$relations", function(err){
console.log("Error", err);
});

}, 20000);

Here's a vid of my own reproducing the issue: https://www.dropbox.com/s/9v4it6r1t1i5mzr/segmentation-fault-2.mp4?dl=0

In the first example I run your code. In the second example I run your code without the querySync() that is called after creating the connection.

That's awkward, isn't it? :(

[xdenser]

Are you sure you are using version v0.1.3-snapshot - latest commit from master branch here on github?

[mreis1]

Nope, I forgot to mention that this last tests were made from a project that was still in v0.1.2. I did run the code above using the v.0.1.3-shapshot and at least this last test is passing.

Do you think the changes made in snapshot v0.1.3 might also fix the seg-fault that is thrown when running the async tx.commit() and the sync tx.newBlobSync?

Anyway I'll re-run my tests to see If i still get seg-fault in those 2. Thank you

[mreis1]

I did run the tests again using the version v0.1.3-snaptshot and updated the document here to include the results of my tests.

https://docs.google.com/document/d/1IvHw8Pab8y5fJ93sMF4Y2F1oHAxUm2MPQDMSYb7-BpA/edit#

Basically seg fault is still thrown at newBlobSync() and commit() methods. The query() seems to work just fine.

[xdenser]

with commit() last git commit should work

[mreis1]

I can confirm. commit() is working perfectly.

[mreis1]

Just tried this commit and the segfault still occurs: ref: https://github.com/xdenser/node-firebird-libfbclient/commit/d4a61ac37823d2242e193897b556e4378b30ab03

FATAL ERROR: v8::ToLocalChecked Empty MaybeLocal.
 1: node::Abort() [/usr/local/bin/node]
 2: node::FatalException(v8::Isolate*, v8::Local<v8::Value>, v8::Local<v8::Message>) [/usr/local/bin/node]
 3: v8::V8::ToLocalEmpty() [/usr/local/bin/node]
 4: Connection::NewBlobSync(Nan::FunctionCallbackInfo<v8::Value> const&) [/Users/marcio/Dev/heitz-ws/node_modules/firebird/build/Release/binding.node]
 5: Nan::imp::FunctionCallbackWrapper(v8::FunctionCallbackInfo<v8::Value> const&) [/Users/marcio/Dev/heitz-ws/node_modules/firebird/build/Release/binding.node]
 6: v8::internal::FunctionCallbackArguments::Call(void (*)(v8::FunctionCallbackInfo<v8::Value> const&)) [/usr/local/bin/node]
 7: v8::internal::MaybeHandle<v8::internal::Object> v8::internal::(anonymous namespace)::HandleApiCallHelper<false>(v8::internal::Isolate*, v8::internal::Handle<v8::internal::HeapObject>, v8::internal::Handle<v8::internal::HeapObject>, v8::internal::Handle<v8::internal::FunctionTemplateInfo>, v8::internal::Handle<v8::internal::Object>, v8::internal::BuiltinArguments) [/usr/local/bin/node]
 8: v8::internal::Builtin_Impl_HandleApiCall(v8::internal::BuiltinArguments, v8::internal::Isolate*) [/usr/local/bin/node]
 9: 0x20988d38463d