JDBC Connection not working following Helsinki upgrade

[Steve Wiseman]

We are in the process of upgrading to Helsinki Patch 8 from Geneva Patch 8.

We have a JDBC connection to Netcool that currently uses the com.sybase.jdbc3.jdbc.SybDriver. In Helsinki when you click on Test Connection we receive the following error (this is the first line of it):

          java.sql.SQLException: java.sql.SQLException: JZ001: User name property 'servicenow-svc%26password=xxxxxxxxxx' too long. Maximum length is 30.

I have amended the password above to be x's. As a test I have amended the password/string to be below 30 characters and get this error instead:

        Error: java.sql.SQLException: java.sql.SQLException: JZ00L: Login failed. Examine the SQLWarnings chained to this exception for the reason(s).

There are a number of knowledge articles which appear to be related to this issue:

https://hi.service-now.com/kb_view.do?sysparm_article=KB0598060

https://hi.service-now.com/kb_view.do?sysparm_article=KB0598059

https://hi.service-now.com/kb_view.do?sysparm_article=KB0565069

I have also found the following thread:

https://community.servicenow.com/thread/248915

So, my understanding from what I have found is:

• The backend database in Helsinki has been changed from MySQL to MariaDB
• By default Helsinki will support the MariaDB driver
• To fix the issue we should upload a JAR file for the com.sybase.jdbc3.jdbc.SybDriver driver

The thing is we already have the JAR file uploaded. I have tried uploading a different version which I have found on https://osdn.net/projects/sfnet_id2d/downloads/jdbc%20drivers/jconn3.jar/ but continue to get the same error. The original one was from http://www.java2s.com/Code/Jar/j/Downloadjconn3jar.htm and was install to fix this issue we had when we upgraded to Geneva: https://hi.service-now.com/kb_view.do?sysparm_article=KB0551236

I have logged a call with ServiceNow and they think it is the following issue: https://hi.service-now.com/kb_view.do?sysparm_article=KB0597413. We don't have a script include called GigDBUtil and the error we are getting is not similar to the error in this article. I am awaiting further feedback on this from ServiceNow.

Has anybody come across this issue before or have an thoughts on what the issue could be and how to progress it?

[servicetrout]

Hi Steve,

    I am not sure your issue is exactly the same as ours was... but below is the documentation I posted in our wiki for the upgrade.

MariaDB is essentially just the new name for MySQL, so the drivers for MariaDB work fine with prior versions of MySQL.

It was not necessary for me to add the MySQL jar files, I simply did that to hang onto them for future reference, or in the event compatibility issues arise (but none have).

ServiceNow MID Server Updating

A ServiceNow MID server, running on sourservername.ucla.edu, enables access to sourservername.ucla.edu databases, which is critical to ServiceNow applications !

To update the MID Server (if nothing has changed with ServiceNow)

Download the file for latest version from ServiceNow (downloaded 64-bit .zip file for Helsinki)

If you download to your PC and then transfer to Prod with Win-SCP, make sure the transfer mode is set to BINARY !

Stop any running instances, using, sudo stop servicenow service

Make a tarball of the ./agent folder for safe keeping.

tar cvf ./agent_geneva.tar ./agent

Rename /opt/servicenow/midserver/agent folder to ~/agent_{yyyymmdd}

e.g sudo mv ./agent ./agent_20170106

Unzip the downloaded file, which explodes to create a new ./agent folder

sudo jar xvf mid.helsinki-03-16-2016__patch8-11-30-2016_{....}.linux.x86-64.zip

If that fails, you can try unzip mid.helsinki....64.zip

Most likely if you see errors unzipping, it means the file wasn't transferred via binary transfer.

Copy the previous config.xml and wrapper-override.conf file to the new agent (to preserve settings)

sudo cp -f ./agent_yyyymmdd/config.xml ./agent

sudo cp -f ./agent_yyyymmdd/conf/wrapper-override.conf ./agent/conf/

Set the new agent files to be executable

From /opt/servicenow/midserver/ folder run, sudo chmod -R u+x /agent

Start up the new agent

From inside the ~/agent folder run, sudo start.sh

Thereafter, you can use, sudo service servicenow restart

Validate

Login to ServiceNow (Production version), search for "MID", select server -> check properties

After first updating, you will need to Validate the server

Under Related Links, click the Validate link. If you don't see a Validate link it may not have updated correctly.

The properties should be the version you updated to.

Check Logs

Lastly, check the /opt/servicenow/midserver/agent/logs/agent0.log.0, and check to see if there were any problems.

[Steve Wiseman]

Thanks for getting back to me on this Glenn. From your upgrade notes It looks like there was no specific config in relation to the driver that you had to do to get it working so looks like our issues are different. I have tried re-upgrading the Mid Server but this has not made any difference. I may see if I can try downgrading the Mid Server back to Geneva but suspect that will just introduce whole lot of other issues!

[Steve Wiseman · Accepted solution]

We have managed to resolve the issue so am posting how we did it in case someone else has a similar problem in future.

After further reading and trying to understand how this all works better we added the following lines to the NetcoolConnectionTester Script Include:

  worker.addParameter("jdbc_user_name", connectionGr.user);

  worker.addParameter("jdbc_password", connectionGr.password);

We also removed the User ID and Password that was part of the connection string on the JDBC Connection record and then when we tested we got a 'String index out of range: -1' error. This is mentioned by Thomas Brown in this post, https://community.servicenow.com/thread/215039, and as he says,   it does seem like it is working even with the error (the JDBC Connection no longer had a state of Error   and did not display the error text in the record).

We then looked at configuring the Netcool Updater script and added the lines that Thomas suggests in his post (some were already in the script) and this resolved the issue:

jp.agent_correlator=jp.addParameter('agent_correlator',gs.generateGUID());

jp.addParameter("skip_sensor",true);

jp.addParameter("jdbc_user_name",this.userName);

jp.addParameter("jdbc_password",,"*****");  

jp.setDriver(this.driver);

In the same script we removed the user ID and password from the 'connectionString' in the initialize function.

We tested triggering the JDBC message and it then worked successfully.

[Chris M3]

Thanks a ton, you just saved me a ton of investigation with this post.