We upgrade from ESXi 3.5 to 4.1, new server hw, new DB as well (ORacle 10g).
The vCenter Service Status is showing Storage Management Service "Service initialization failed"
sms.log file shows numerous errors like:
2010-12-28 11:45:28,332 [pool-38-thread-2] ERROR com.vmware.vim.sms.provider.VcProviderImpl - PopulateStorageInfoTask Failed : com.ibatis.common.jdbc.exception.NestedSQLException:
--- The error occurred in com/vmware/vim/sms/data/xml/CacheDbQuerySqlMap.xml.
--- The error occurred while applying a parameter map.
--- Check the insert_scsiTarget-InlineParameterMap.
--- Check the statement (update failed).
--- Cause: org.h2.jdbc.JdbcSQLException: NULL not allowed for column ENTITYID [90006-64]
After much research, I found the query in the V:\Program Files\VMware\Infrastructure\tomcat\webapps\sms\WEB-INF\services\smService\com\vmware\vim\sms\data\xml\VcDbQueryOracleSqlMap.xml that is actually returning the NULL (and it is a NULL). The storage in question is iSCSI (DELLon backend)
iSCSI piece of Query:
SELECT DISTINCT PSA_TARGET_TRANSPORT.PORT_WWN AS PORT_WWN,
PSA_TARGET_TRANSPORT.NODE_WWN AS NODE_WWN,
PSA_TARGET_TRANSPORT.ISCSI_NAME AS ISCSI_NAME,
PSA_TARGET_TRANSPORT.ISCSI_ALIAS AS ISCSI_ALIAS,
CASE PSA_TARGET_TRANSPORT.VPX_TYPE
WHEN N'vim.host.ParallelScsiTargetTransport' THEN 'parallelScsi'
WHEN N'vim.host.FibreChannelTargetTransport' THEN 'fc'
WHEN N'vim.host.InternetScsiTargetTransport' THEN 'iscsi'
ELSE 'block'
END AS TARGET_TYPE,
'unknown' AS VENDOR,
CASE VPX_TYPE
WHEN N'vim.host.FibreChannelTargetTransport' THEN PORT_WWN
ELSE ISCSI_NAME
END AS ENTITY_ID
FROM VPX_PSA_TARGET_TRANSPORT PSA_TARGET_TRANSPORT
WHERE (PSA_TARGET_TRANSPORT.VPX_TYPE != N'vim.host.ParallelScsiTargetTransport' AND
PSA_TARGET_TRANSPORT.VPX_TYPE != N'vim.host.BlockAdapterTargetTransport')
The big question is, how do I get the ISCSI_NAME NOT to be NULL? Does anyone have any ideas?
TIA,
-=john=-
Welcome to the Communities.
I would file a Service Request with VMware.
i strongly agreed with DSTAVERT, stop doing anything that you're unaware (you might lose everything), and let the support take over it. Good Luck!.
OK - I will do that today.
Thanks
Hi,
Just wondering, did you get this issue resolved. We are seeing the exact same problem.
VSphere 4.1
Oracle 11g DB
Thanks
Adam
No, unfortunately I have not had the time to open an issue with support. Since things are working, this got pushed to the bottem of the list. Hope to get back to it in a few weeks.
I've logged a support ticket with VMWare, I'll post here with the results.
Did you get any reponse to this from VMware support?
I'm "still" working through this with VMWare support. The latest series of troubleshooting steps we did was to modify the vcdb.propterties file. This post describes quite well the file and how to change it.
http://communities.vmware.com/message/1662294?tstart=0
I should state though that while I was hopeful, it had no impact on our particular issue. VMWare support are still working on our problem and I will update when / if we get a fix.
dotcom,
I am also having the same problem, only the "stoage views" does not work, the rest OK!
The error is also identical "NULL column is not allowed EntityID [90006-64]. "
I use vCenter 4.1 Update with Oracle 10g ...
I tried to change the JDBC and did not work, everything works except the storage views.
Finding news / Solution, do not forget to let us know. Thanks
Still working this through with VMWare support. They sent me a new VcDbQueryOracleSqlMap.xml file. I notice however that the file they sent me is older than the one currently installed. It did not fix the problem however, the errors in the sms.log file are now different. I am now seeing the following:
--
2011-04-19 12:16:26,305 [Thread-9] ERROR com.vmware.vim.sms.provider.VcProviderImpl - Failed populating service cache
com.ibatis.common.jdbc.exception.NestedSQLException:
--- The error occurred in com/vmware/vim/sms/data/xml/VcDbQueryOracleSqlMap.xml.
--- The error occurred while applying a parameter map.
--- Check the get_vpx_scsiPath-InlineParameterMap.
--- Check the statement (query failed).
--- Cause: java.sql.SQLException: ORA-00904: "VPX_HOST_MULTIPATH_LUN"."PREFER": invalid identifier
at com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryWithCallback(GeneralStatement.java:185)
at com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryForList(GeneralStatement.java:123)
at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForList(SqlMapExecutorDelegate.java:615)
at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForList(SqlMapExecutorDelegate.java:589)
at com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.queryForList(SqlMapSessionImpl.java:118)
at com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.queryForList(SqlMapSessionImpl.java:122)
at com.ibatis.sqlmap.engine.impl.SqlMapClientImpl.queryForList(SqlMapClientImpl.java:99)
at com.vmware.vim.sms.provider.VcProviderImpl.retrieveVcData(Unknown Source)
at com.vmware.vim.sms.provider.VcProviderImpl.sync(Unknown Source)
at com.vmware.vim.sms.ServiceImpl.syncServiceCache(Unknown Source)
at com.vmware.vim.sms.ServiceImpl.init(Unknown Source)
at com.vmware.vim.sms.ServiceImpl.access$100(Unknown Source)
at com.vmware.vim.sms.ServiceImpl$ServiceInitializer.run(Unknown Source)
Caused by: java.sql.SQLException: ORA-00904: "VPX_HOST_MULTIPATH_LUN"."PREFER": invalid identifier
at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:74)
at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:131)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:204)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:455)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:413)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:1034)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:194)
at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:791)
at oracle.jdbc.driver.T4CPreparedStatement.executeMaybeDescribe(T4CPreparedStatement.java:866)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1186)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3387)
at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3488)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:1086)
at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:169)
at com.ibatis.sqlmap.engine.execution.SqlExecutor.executeQuery(SqlExecutor.java:186)
at com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.sqlExecuteQuery(GeneralStatement.java:205)
at com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryWithCallback(GeneralStatement.java:173)
... 12 more
2011-04-19 12:16:26,305 [Thread-9] DEBUG com.vmware.vim.sms.provider.VcProviderImpl - Checking in inactiveCacheDbClient
2011-04-19 12:16:26,305 [Thread-9] DEBUG com.vmware.vim.sms.provider.VcProviderImpl - Checking in vcDbClient
2011-04-19 12:16:26,305 [Thread-9] DEBUG com.vmware.vim.sms.ServiceImpl - Checking in inactiveCacheDbClient
2011-04-19 12:16:26,305 [Thread-9] DEBUG com.vmware.vim.sms.util.SimpleTimeCounter - TIMER STOPPED: Service cache sync
2011-04-19 12:16:26,305 [Thread-9] DEBUG com.vmware.vim.sms.util.SimpleTimeCounter - TIME TAKEN: Service cache sync: 1.021
2011-04-19 12:16:26,305 [Thread-9] INFO com.vmware.vim.sms.HealthAgentImpl - Changing health status from PROVIDER_SYNC_IN_PROGRESS to PROVIDER_SYNC_FAILED
2011-04-19 12:16:26,305 [Thread-9] DEBUG com.vmware.vim.sms.HealthAgentImpl - Publishing service health information
2011-04-19 12:16:26,306 [Thread-9] INFO com.vmware.vim.sms.HealthAgentImpl - Changing health status from PROVIDER_SYNC_FAILED to INIT_FAILED
2011-04-19 12:16:26,306 [Thread-9] DEBUG com.vmware.vim.sms.HealthAgentImpl - Publishing service health information
2011-04-19 12:16:26,307 [Thread-9] ERROR com.vmware.vim.sms.ServiceImpl - Unknown exception encountered during service initialization
com.vmware.vim.sms.fault.ProviderSyncFault
at com.vmware.vim.sms.provider.VcProviderImpl.sync(Unknown Source)
at com.vmware.vim.sms.ServiceImpl.syncServiceCache(Unknown Source)
at com.vmware.vim.sms.ServiceImpl.init(Unknown Source)
at com.vmware.vim.sms.ServiceImpl.access$100(Unknown Source)
at com.vmware.vim.sms.ServiceImpl$ServiceInitializer.run(Unknown Source)
2011-04-19 12:16:26,307 [Thread-9] INFO com.vmware.vim.sms.ServiceImpl$ServiceInitializer - Retry #7 in 30 seconds
--
Thank dotcom!
Anyway ... taking new to solving this problem, let us know!
Excellent Week!
Hi DotCom!
Any progress with Suport VMWare ?
Tks!
Latest update from VMWare is that they think it may be a bug. It is being passed through to engineering. Will post back here when I have more information.
OK, so after much log sending back and forth with VMWare Tech Support. We finally have this issue resolved. I must stress that this solution is probably only relevant to our environment and i strongly advise not just blindly running the sql commands against your DB. Though there is prlobably no harm in running the query. Hopefully it might point anyone else with this problem in the right direction and lead to an earlier resolution.
Anyway, here's what worked for us:
1. take the backup of VCDB.
2. First execute the below query to check it only lists the rows with VPX_TYPE=vim.host.InternetScsiTargetTransport and ISCSI_NAME null.
======
select * from VPX_PSA_TARGET_TRANSPORT where ISCSI_NAME is NULL AND
VPX_TYPE='vim.host.InternetScsiTargetTransport';
======
3. If so, execute the below query to delete those rows
======
delete from VPX_PSA_TARGET_TRANSPORT where ISCSI_NAME is NULL AND
VPX_TYPE='vim.host.InternetScsiTargetTransport';
======
3. Restart VMware vCenter Server and VC Web Management service and see if storage view shows up.