Getting empty result

[nitins0202]

We are running our NodeJS application on kubernetes and retrieving data from bluemix dashdb.
We are using the ibm_db library for connecting to the dashdb.

There is one scenario we are getting empty result.

Step to reproduce it:

1. Server started and dashdb connection established.
2. Call rest endpoint to get the data
3. NodeJS application fetch the data from dashdb and successfully returned.
4. Somehow on server network got disconnected and there where no request came for data.
5. After sometime(1-2 minute) network got connected.
6. Call rest endpoint to get the data again
7. This time we received empty array [] and there is not error on server.

We are using the single db connection.

Could you please help on this issue, how we can resolve or handle this issue.

[vbutoma]

What version do you use ? Try to use 2.4.0 version.

We had the same problem for version 2.4.1 and upper. Our application received empty array as result and no errors were risen.
But after rollback to version 2.4.0 we received the communication error as expected.

[vbutoma]

@bimalkjha are you going to fix this issue in the next versions of the package?

[bimalkjha]

@vbutoma Yes.

[obiwann]

Is this issue fixed?

[ZacharyKearns]

Is there an ETA on when this issue will be fixed? It is affecting an app that runs in an environment with Node 10 so needs to use version 2.5.0 and can't roll back to 2.4.0

[bimalkjha]

@ZacharyKearns we are working on this issue and will update soon about the fix. Btw, can you try ibm_db@2.5.1 and check? Thanks.

[bimalkjha]

@nitins0202 @vbutoma @ZacharyKearns By following your steps to reproduce the issue, the connection error is expected as network was broken and hence server has closed the connection with client. So, SQL30081N is expected here and that is normal behavior.
We can reproduce this behavior by executing node test\test-bad-pool-connection-issue-42.js file. We need to update connection information in ibm_db\test\config.testConnectionStrings.json file before running this test file. Also, we need to update last line in file test-bad-pool-connection-issue-42.js and change 3000 to 30000 to wait for 30 second before it retries the connection. Within this 30 seconds, we need to restart server by running db2stop force; db2start command on the db2 server system.

As you shared, I am able to see the empty result in queries after the SQL30081N error and is reproducible using test-bad-pool-connection-issue-42.js script too. We can enhance ibm_db code to not return empty result but return "Invalid connection handle" or "No database connection exist" error.

If you do not want SQL30081N error when network breaks down and reconnect automatically, you need to configure ACR (Automatic Client Reroute) in your system for the target database, so that ibm_db can internally try to reconnect with the server when such event occurs. There are many issues in closed state for ibm_db where users have configured ACR in db2dsdriver.cfg file to avoid SQL30081N error.

To avoid SQL30081N error, please copy below lines and save it as db2dsdriver.cfg file under ...\ibm_db\installer\clidriver\cfg directory.

<configuration>
    <dsncollection>
        <dsn alias="mydsn" name="sample" host="server.ibm.com" port="60000"/>
    </dsncollection>
    <databases>
        <database name="sample" host="server.ibm.com" port="60000">
            <!-- automatic client reroute (ACR)  -->
            <parameter name="userid" value="newton"/>
            <parameter name="password" value="secret"/>
            <acr>
                <parameter name="enableACR" value="true"/>
                <parameter name="enableSeamlessACR" value="true"/>
                <parameter name="acrRetryInterval" value="2"/>
                <parameter name="maxAcrRetries" value="30"/>
                <parameter name="affinityFailbackInterval" value="2"/>
                <alternateserverlist>
                    <server name="S1" hostname="server.ibm.com" port="60000"/>
                    <server name="S2" hostname="server.ibm.com" port="60000"/>
                </alternateserverlist>
                <affinitylist>
                  <list name="list1" serverorder="S1,S2"/>
                </affinitylist>
                <clientaffinitydefined>
                    <client name="client1" hostname="localhost" listname="list1"/>
                </clientaffinitydefined>
            </acr>
        </database>
     </databases>
</configuration>

Please update your database connection information in this file before use. You need to update values for: database name, hostname, port, userid and password only in this file. Then, cd to clidriver\bin directory and execute db2cli validate -dsn mydsn -connect command from command prompt to make sure contents of db2dsdriver.cfg file is correct and it is able to connect to target database server successfully. You should see [SUCCESS] message near end of the output of this command. Now, you are good to go.

Please share the result after using the db2dsdriver.cfg file.

Thanks and Regards,
Bimal Jha

[ZacharyKearns]

Returning a string instead of an empty result set would be better.

Configuring the ACR is not an option because the database connection information cannot be exposed in node_modules for security reasons.

I am also curious why every connection in the connection pool returns an empty result set on SQL30081N error. It would be good to be able to remove a stale connection from the pool instead of having to re initialize the pool every time.

[bimalkjha]

@ZacharyKearns I got your point. I found the reason for your connection pool issue too. I have fix ready and under testing. With new changes, there would not be any empty result. The connection was not getting invalidated post SQL30081N error and causing the empty result and pool issue. After fix, the stale connection would be removed from the pool. I'll commit the changes soon into master and update you. Thanks.

[bimalkjha]

@ZacharyKearns Please use the latest code from master and verify the fix. Thanks.

[bimalkjha]

ibm_db@2.5.2 has fixed this issue. Hence closing it. Thanks.