Smarts NCM 9.x: Is it possible to increase the string data field limit of 255 characters in the DB?

Products

VMware Smart Assurance

Issue/Introduction

Symptoms:

When attempting to update the additional text of a Job using the API service, an exception occurs and the field is not updated, jboss.log shows below:

2013-08-16 14:46:41,088 ERROR [org.jboss.ejb.plugins.LogInterceptor] (http-0.0.0.0-8881-1) TransactionRolledbackException in method: public abstract void com.powerup.configmgr.server.services.api.ApiService.setBusinessDataValue(com.powerup.

configmgr.common.api.ResourceIdentityInfo,com.powerup.configmgr.common.api.ResourceIdentityInfo,java.lang.String) throws com.powerup.configmgr.common.api.ApiBusinessException,com.powerup.configmgr.common.api.businessdata.NoSuchBusinessAttrException,com.powerup.configmgr.common.api.InvalidDataException,com.powerup.configmgr.common.api.security.AccessDeniedException,com.powerup.configmgr.common.api.

LicensingException,com.powerup.configmgr.common.api.ApiSystemException,java.rmi.

RemoteException, causedBy:

class com.powerup.common.CMSystemException:Error flushing Hibernate session - Could not execute JDBC batch update

at com.powerup.common.hibernate.SessionHelper.flushSession(SessionHelper.java:78)

at com.powerup.common.hibernate.SessionHelper.cleanUp(SessionHelper.java:46)

at com.powerup.common.ejb.BaseSessionBean.cleanUp(BaseSessionBean.java:66)

at com.powerup.configmgr.server.services.businessdata.ejb.BusinessDataSe

rviceBean.updateBusinessDataValuesForResources(BusinessDataServiceBean.java:262)

Environment

VMware Smart Assurance - NCM

Cause

It would also be useful to turn on debug hibernate logging, as the error above is caused during flushing hibernate session. The way to enable this is:

In File:

$VOYENCE_HOME/jboss/server/vc-server/deploy/1vc.sar/vc-hibernate.sar/hibernate.cfg.xml

Change:

false

To:

true

in entries:

<property name="show_sql">false</property>

and restart services.

Note: server.log will grow very large when the debug logging for hibernate is enabled. Be sure to change the value back to false once logs have been gathered.

Here is the debug output in server.log:

2013-08-19 16:04:55,728 ERROR [org.hibernate.util.JDBCExceptionReporter] (http-0.0.0.0-8881-1) Batch entry 0 insert into cm_job_datafields (version, job_id, v0, v1, v2, v3, v4, v5, v6, v7, v8, v9, v10, v11, v12, v13, v14, v15, v16, v17, v18, v19, v20, v21, v22, v23, v24, v25, v26, v27, v28, v29, job_datafields_id) values ('0', '<stream of 16 bytes>', '[sys_id=''c23ccc01312a1c007243b1ff18e9d782'' service_properties=''Incident,Change request,Basic monitoring,Full monitoring,Con

figuration monitoring,Performance reporting,Event,Notification management'' destination=''{"token":"jkou223h87","action":"ci_deployment_status","url":"https://didatatraining.service-now.com/SWSInboundService.do?SOAP"}'']', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '<stream of 16 bytes>') was aborted. Call getNextException to see the cause.

2013-08-19 16:04:55,728 WARN [org.hibernate.util.JDBCExceptionReporter] (http-0.0.0.0-8881-1) SQL Error: 0, SQLState: 22001

2013-08-19 16:04:55,728 ERROR [org.hibernate.util.JDBCExceptionReporter] (http-0.0.0.0-8881-1) ERROR: value too long for type character varying(255)

2013-08-19 16:04:55,730 ERROR [org.hibernate.event.def.AbstractFlushingEventListener] (http-0.0.0.0-8881-1) Could not synchronize database state with session

org.hibernate.exception.DataException: Could not execute JDBC batch update

The hibernate debug information gives the answer: the data field can only accommodate 255 characters, and when more than 255 characters are sent, this error is received.

Resolution

Workaround:

Increase the character limit to 400 so that it accommodates 255+ characters.

1. Login to DB (su - pgdba, psql voyencedb voyence) and run below command:

ALTER TABLE cm_job_datafields ALTER COLUMN v0 TYPE character varying(400);

2. Run it for all data filed columns starting from v0 to v39 by replacing the column name in above command and restart vcmaster service.