SNOW-912451: Error issuing heartbeat call

[grmca2010]

Getting the below mentioned issue which is teriminating the connection in the PROD server.. Could you please let us know how to fix that..

We added clientSessionKeepAlive in the property.. but still its failing..

snowflakeDriver.configure({
insecureConnect: true,
clientSessionKeepAlive: true,
clientSessionKeepAliveHeartbeatFrequency: 3600,
});

const connectionString = {
clientSessionKeepAlive: true,
clientSessionKeepAliveHeartbeatFrequency: 3600,
};
// console.log(connectionString);
const connectionObj = snowflakeDriver.createConnection(connectionString);

{"level":"ERROR","message":"[8:11:10.002 PM]: Error issuing heartbeat call: Unable to perform operation using terminated connection."}
{"level":"ERROR","message":"[9:11:09.636 PM]: Error issuing heartbeat call: Unable to perform operation using terminated connection."}
{"level":"ERROR","message":"[10:11:09.637 PM]: Error issuing heartbeat call: Unable to perform operation using terminated connection."}
{"level":"ERROR","message":"[11:11:09.637 PM]: Error issuing heartbeat call: Unable to perform operation using terminated connection."}
{"level":"ERROR","message":"[12:11:09.637 AM]: Error issuing heartbeat call: Unable to perform operation using terminated connection."}
{"level":"ERROR","message":"[1:11:09.638 AM]: Error issuing heartbeat call: Unable to perform operation using terminated connection."}
{"level":"ERROR","message":"[2:11:09.637 AM]: Error issuing heartbeat call: Unable to perform operation using terminated connection."}
{"level":"ERROR","message":"[3:11:09.638 AM]: Error issuing heartbeat call: Unable to perform operation using terminated connection."}
{"level":"ERROR","message":"[4:11:09.639 AM]: Error issuing heartbeat call: Unable to perform operation using terminated connection."}
{"level":"ERROR","message":"[5:11:09.640 AM]: Error issuing heartbeat call: Unable to perform operation using terminated connection."}
{"level":"ERROR","message":"[6:11:09.638 AM]: Error issuing heartbeat call: Unable to perform operation using terminated connection."}
{"level":"ERROR","message":"[7:11:09.640 AM]: Error issuing heartbeat call: Unable to perform operation using terminated connection."}
Failed executed statement: select concat(current_date(),':',current_time()) t1;

[sfc-gh-dszmolka]

hi and thank you for raising this issue. from the looks of it, this can be perhaps a usage issue rather than a bug in the driver.
for example, if you wish to use clientSessionKeepAlive (and the heartbeat frequency), you only need to specify them in the connection options, in createConnection.

can you please create a minimal viable reproduction code and post it here ? i mean, a full script which when run, exhibits the symptoms. of course please redact the sensitive information (account, user, password, etc.)

[grmca2010]

const genericPool = require('generic-pool');
const snowflakeDriver = require('snowflake-sdk');
const logger = require('js-logging').console({ level: process.env.LOG_LEVEL });
const path = require('path');
const nodemailer = require('nodemailer');

snowflakeDriver.configure({
insecureConnect: true,
clientSessionKeepAlive: true,
clientSessionKeepAliveHeartbeatFrequency: 3600,
});
const config = require('./config/config.js').configJson;
function mailTransporter() {
const transporter = nodemailer.createTransport({
host: '',
port: 25,
});
return transporter;
}

let account;
const password = process.env.SNOWFLAKE_PWD;
if (!password) {
logger.error('No .env file found!');
process.exit(1);
}
let opts;
if (process.env.NODE_ENV === 'DEV') {
account = '';
opts = {
max: 10, // maximum size of the pool
min: 2, // minimum size of the pool
fifo: true, // oldest resources will be first to be allocated
autostart: true,
};
} else if (process.env.NODE_ENV === 'STAGE') {
account = ';
opts = {
max: 10, // maximum size of the pool
min: 2, // minimum size of the pool
fifo: true, // oldest resources will be first to be allocated
autostart: true,
};
} else if (process.env.NODE_ENV === 'PROD') {
account = '';
opts = {
max: 10, // maximum size of the pool
min: 2, // minimum size of the pool
fifo: true, // oldest resources will be first to be allocated
autostart: true,
};
}

const factory = {
async create() {
// console.log("Inside create connection..");
const connectionString = {
username: '',
password,
account,
warehouse: (process.env.NODE_ENV === 'PROD' ? '' : ''),
database: '',
schema: 'schema',
schema: 'schema',
clientSessionKeepAlive: true,
clientSessionKeepAliveHeartbeatFrequency: 3600,
};
// console.log(connectionString);
const connectionObj = snowflakeDriver.createConnection(connectionString);
return new Promise((resolve, reject) => {
connectionObj.connect((err, conn) => {
if (err) {
console.error('Unable to connect: ' + err.message);
reject(new Error(err.message));

    } else {
      console.log('Successfully connected to Snowflake, ID:', conn.getId());  
      resolve(conn);
    }
  });
});

},
async destroy(connectionObj) {
console.log("Inside destroy connection..");
await connectionObj.destroy();
},

};

const myPool = genericPool.createPool(factory, opts);
module.exports = myPool;

Added the code which shows you that how we connect the snowflake DB

[grmca2010]

added sample code.. can you check and let me what was mistake that we are making out

[sfc-gh-dszmolka]

thank you for providing the sample code ! i would add the following :

1. this is not necessarily a problem, but I still wanted to draw your attention that you don't necessarily need to define the connection pool outside of the Snowflake driver; as the driver already embeds and exposes generic-pool. Please see the documented method on how to create a connection pool with Snowflake nodejs driver
2. this seems a bit more suspicious to me; within factory the connection seems to be destroyed. Calling destroy on the Snowflake connection immediately terminates it without waiting for any in-flight requests to complete.
   If the driver were to attempt a heartbeat call (which implements the clientSessionKeepAlive) over such a connection , it would make sense that Error issuing heartbeat call: Unable to perform operation using terminated connection is encountered.

Perhaps the issue pops up as synergy from 1. and 2., I'm not sure. As a super simplistic example; here's a working version of a script connecting to Snowflake using a connectionPool then executing SELECT 1, hope it helps you debugging your application:

const snowflake = require('snowflake-sdk');

snowflake.configure({ logLevel : 'trace'});

const account = process.env.SFACCOUNT;
const username = process.env.SFUSER;
const password = process.env.SFPASS;
const role = 'process.env.SFROLE';
const warehouse = process.env.SFWH;

const poolSize = 10;

const pool = snowflake.createPool(
    {
      account: account,
      password: password,
      role: role,
      username: username,
      warehouse: warehouse,
      application: application,
      timeout: 30_000,
    },
    {
      evictionRunIntervalMillis: 30_000,
      idleTimeoutMillis: 30_000,
      max: poolSize,
      min: 0,
    },
  );

pool.use(async (clientConnection) =>
{
    const statement = await clientConnection.execute({
        sqlText: 'select 1;',
        complete: function (err, stmt, rows)
        {
            var stream = stmt.streamRows();
            stream.on('data', function (row)
            {
                console.log(row);
            });
            stream.on('end', function (row)
            {
                console.log('All rows consumed');
            });
        }
    });
});

[sfc-gh-dszmolka]

since there was no response for a while now and no driver issue is identified, i'm going to close this issue. if you still think something is going wrong on the driver side, please let me know and provide a reproduction of the issue and I'll reopen to investigate further.