Description of problem: The "version_order" data in the "rhq_bundle_version" table is not updated after a version bundle is deleted. This will cause an exception to be thrown when new bundle version is deployed. The exception from rhq-server-log4j.log file is: 2012-05-31 11:20:28,272 DEBUG [org.rhq.enterprise.server.util.CriteriaQueryGenerator] SELECT new org.rhq.core.domain.bundle.composite.BundleWithLatestVersionComposite( bundle.id, bundle.name, bundle.description, ( SELECT bv1.version FROM bundle.bundleVersions bv1 WHERE bv1.versionOrder = (SELECT MAX(bv2.versionOrder) FROM BundleVersion bv2 WHERE bv2.bundle.id = bundle.id) ) AS latestVersion, ( SELECT COUNT(bv3) FROM bundle.bundleVersions bv3 WHERE bv3.bundle.id = bundle.id) AS deploymentCount ) FROM Bundle bundle 2012-05-31 11:20:28,275 WARN [org.hibernate.util.JDBCExceptionReporter] SQL Error: 0, SQLState: 21000 2012-05-31 11:20:28,275 ERROR [org.hibernate.util.JDBCExceptionReporter] ERROR: more than one row returned by a subquery used as an expression 2012-05-31 11:20:28,275 DEBUG [org.rhq.enterprise.server.util.HibernatePerformanceMonitor] HibernateStats[ queries=0, xactions=0, loads=0, connects=1, time=3 ] for SLSB:BundleManagerBean.findBundlesWithLatestVersionCompositesByCriteria 2012-05-31 11:20:28,278 WARN [gwt-log] Sending exception to client: [1338459628276] javax.ejb.EJBException: javax.persistence.PersistenceException: org.hibernate.exception.DataException: could not execute query at org.jboss.ejb3.tx.Ejb3TxPolicy.handleExceptionInOurTx(Ejb3TxPolicy.java:63) at org.jboss.aspects.tx.TxPolicy.invokeInOurTx(TxPolicy.java:83) at org.jboss.aspects.tx.TxInterceptor$Required.invoke(TxInterceptor.java:191) at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101) at org.jboss.aspects.tx.TxPropagationInterceptor.invoke(TxPropagationInterceptor.java:95) at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101) at org.jboss.ejb3.stateless.StatelessInstanceInterceptor.invoke(StatelessInstanceInterceptor.java:62) at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101) at org.jboss.aspects.security.AuthenticationInterceptor.invoke(AuthenticationInterceptor.java:77) at org.jboss.ejb3.security.Ejb3AuthenticationInterceptor.invoke(Ejb3AuthenticationInterceptor.java:110) at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101) at org.jboss.ejb3.ENCPropagationInterceptor.invoke(ENCPropagationInterceptor.java:46) at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101) at org.jboss.ejb3.asynchronous.AsynchronousInterceptor.invoke(AsynchronousInterceptor.java:106) at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101) at org.jboss.ejb3.stateless.StatelessContainer.localInvoke(StatelessContainer.java:240) at org.jboss.ejb3.stateless.StatelessContainer.localInvoke(StatelessContainer.java:210) at org.jboss.ejb3.stateless.StatelessLocalProxy.invoke(StatelessLocalProxy.java:84) at $Proxy467.findBundlesWithLatestVersionCompositesByCriteria(Unknown Source) at org.rhq.enterprise.gui.coregui.server.gwt.BundleGWTServiceImpl.findBundlesWithLatestVersionCompositesByCriteria(BundleGWTServiceImpl.java:329) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) at java.lang.reflect.Method.invoke(Method.java:597) at com.google.gwt.user.server.rpc.RPC.invokeAndEncodeResponse(RPC.java:562) at com.google.gwt.user.server.rpc.RemoteServiceServlet.processCall(RemoteServiceServlet.java:188) at com.google.gwt.user.server.rpc.RemoteServiceServlet.processPost(RemoteServiceServlet.java:224) at com.google.gwt.user.server.rpc.AbstractRemoteServiceServlet.doPost(AbstractRemoteServiceServlet.java:62) at javax.servlet.http.HttpServlet.service(HttpServlet.java:710) at org.rhq.enterprise.gui.coregui.server.gwt.AbstractGWTServiceImpl.service(AbstractGWTServiceImpl.java:82) at javax.servlet.http.HttpServlet.service(HttpServlet.java:803) at sun.reflect.GeneratedMethodAccessor234.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) at java.lang.reflect.Method.invoke(Method.java:597) at org.apache.catalina.security.SecurityUtil$1.run(SecurityUtil.java:244) at java.security.AccessController.doPrivileged(Native Method) at javax.security.auth.Subject.doAsPrivileged(Subject.java:517) at org.apache.catalina.security.SecurityUtil.execute(SecurityUtil.java:276) at org.apache.catalina.security.SecurityUtil.doAsPrivilege(SecurityUtil.java:162) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:283) at org.apache.catalina.core.ApplicationFilterChain.access$000(ApplicationFilterChain.java:56) at org.apache.catalina.core.ApplicationFilterChain$1.run(ApplicationFilterChain.java:189) at java.security.AccessController.doPrivileged(Native Method) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:185) at org.rhq.enterprise.gui.coregui.server.filter.CacheControlFilter.doFilter(CacheControlFilter.java:70) at sun.reflect.GeneratedMethodAccessor272.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) at java.lang.reflect.Method.invoke(Method.java:597) at org.apache.catalina.security.SecurityUtil$1.run(SecurityUtil.java:244) at java.security.AccessController.doPrivileged(Native Method) at javax.security.auth.Subject.doAsPrivileged(Subject.java:517) at org.apache.catalina.security.SecurityUtil.execute(SecurityUtil.java:276) at org.apache.catalina.security.SecurityUtil.doAsPrivilege(SecurityUtil.java:218) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:230) at org.apache.catalina.core.ApplicationFilterChain.access$000(ApplicationFilterChain.java:56) at org.apache.catalina.core.ApplicationFilterChain$1.run(ApplicationFilterChain.java:189) at java.security.AccessController.doPrivileged(Native Method) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:185) at org.rhq.helpers.rtfilter.filter.RtFilter.doFilter(RtFilter.java:124) at sun.reflect.GeneratedMethodAccessor233.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) at java.lang.reflect.Method.invoke(Method.java:597) at org.apache.catalina.security.SecurityUtil$1.run(SecurityUtil.java:244) at java.security.AccessController.doPrivileged(Native Method) at javax.security.auth.Subject.doAsPrivileged(Subject.java:517) at org.apache.catalina.security.SecurityUtil.execute(SecurityUtil.java:276) at org.apache.catalina.security.SecurityUtil.doAsPrivilege(SecurityUtil.java:218) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:230) at org.apache.catalina.core.ApplicationFilterChain.access$000(ApplicationFilterChain.java:56) at org.apache.catalina.core.ApplicationFilterChain$1.run(ApplicationFilterChain.java:189) at java.security.AccessController.doPrivileged(Native Method) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:185) at org.jboss.web.tomcat.filters.ReplyHeaderFilter.doFilter(ReplyHeaderFilter.java:96) at sun.reflect.GeneratedMethodAccessor232.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) at java.lang.reflect.Method.invoke(Method.java:597) at org.apache.catalina.security.SecurityUtil$1.run(SecurityUtil.java:244) at java.security.AccessController.doPrivileged(Native Method) at javax.security.auth.Subject.doAsPrivileged(Subject.java:517) at org.apache.catalina.security.SecurityUtil.execute(SecurityUtil.java:276) at org.apache.catalina.security.SecurityUtil.doAsPrivilege(SecurityUtil.java:218) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:230) at org.apache.catalina.core.ApplicationFilterChain.access$000(ApplicationFilterChain.java:56) at org.apache.catalina.core.ApplicationFilterChain$1.run(ApplicationFilterChain.java:189) at java.security.AccessController.doPrivileged(Native Method) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:185) at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:230) at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:173) at org.jboss.web.tomcat.security.SecurityAssociationValve.invoke(SecurityAssociationValve.java:182) at org.jboss.web.tomcat.security.JaccContextValve.invoke(JaccContextValve.java:84) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:128) at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:104) at org.jboss.web.tomcat.service.jca.CachedConnectionValve.invoke(CachedConnectionValve.java:157) at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109) at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:241) at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:844) at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:583) at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:447) at java.lang.Thread.run(Thread.java:619) Caused by: javax.persistence.PersistenceException: org.hibernate.exception.DataException: could not execute query at org.hibernate.ejb.AbstractEntityManagerImpl.throwPersistenceException(AbstractEntityManagerImpl.java:629) at org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:73) at org.rhq.enterprise.server.util.CriteriaQueryRunner.getCollection(CriteriaQueryRunner.java:99) at org.rhq.enterprise.server.util.CriteriaQueryRunner.execute(CriteriaQueryRunner.java:69) at org.rhq.enterprise.server.bundle.BundleManagerBean.findBundlesWithLatestVersionCompositesByCriteria(BundleManagerBean.java:1545) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) at java.lang.reflect.Method.invoke(Method.java:597) at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:112) at org.jboss.ejb3.interceptor.InvocationContextImpl.proceed(InvocationContextImpl.java:166) at org.rhq.enterprise.server.common.PerformanceMonitorInterceptor.monitorHibernatePerformance(PerformanceMonitorInterceptor.java:26) at sun.reflect.GeneratedMethodAccessor132.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) at java.lang.reflect.Method.invoke(Method.java:597) at org.jboss.ejb3.interceptor.InvocationContextImpl.proceed(InvocationContextImpl.java:118) at org.rhq.enterprise.server.common.TransactionInterruptInterceptor.addCheckedActionToTransactionManager(TransactionInterruptInterceptor.java:77) at sun.reflect.GeneratedMethodAccessor131.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) at java.lang.reflect.Method.invoke(Method.java:597) at org.jboss.ejb3.interceptor.InvocationContextImpl.proceed(InvocationContextImpl.java:118) at org.rhq.enterprise.server.authz.RequiredPermissionsInterceptor.checkRequiredPermissions(RequiredPermissionsInterceptor.java:156) at sun.reflect.GeneratedMethodAccessor130.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) at java.lang.reflect.Method.invoke(Method.java:597) at org.jboss.ejb3.interceptor.InvocationContextImpl.proceed(InvocationContextImpl.java:118) at org.jboss.ejb3.interceptor.EJB3InterceptorsInterceptor.invoke(EJB3InterceptorsInterceptor.java:63) at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101) at org.jboss.ejb3.entity.TransactionScopedEntityManagerInterceptor.invoke(TransactionScopedEntityManagerInterceptor.java:54) at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101) at org.jboss.ejb3.AllowedOperationsInterceptor.invoke(AllowedOperationsInterceptor.java:47) at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101) at org.jboss.aspects.tx.TxPolicy.invokeInOurTx(TxPolicy.java:79) ... 97 more Caused by: org.hibernate.exception.DataException: could not execute query at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:77) at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43) at org.hibernate.loader.Loader.doList(Loader.java:2216) at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2104) at org.hibernate.loader.Loader.list(Loader.java:2099) at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:378) at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:338) at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:172) at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1121) at org.hibernate.impl.QueryImpl.list(QueryImpl.java:79) at org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:64) ... 128 more Caused by: org.postgresql.util.PSQLException: ERROR: more than one row returned by a subquery used as an expression at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2102) at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1835) at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257) at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:500) at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:388) at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:273) at sun.reflect.GeneratedMethodAccessor106.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) at java.lang.reflect.Method.invoke(Method.java:597) at org.postgresql.ds.jdbc23.AbstractJdbc23PooledConnection$StatementHandler.invoke(AbstractJdbc23PooledConnection.java:455) at $Proxy50.executeQuery(Unknown Source) at org.jboss.resource.adapter.jdbc.CachedPreparedStatement.executeQuery(CachedPreparedStatement.java:90) at org.jboss.resource.adapter.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:342) at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:187) at org.hibernate.loader.Loader.getResultSet(Loader.java:1787) at org.hibernate.loader.Loader.doQuery(Loader.java:674) at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:236) at org.hibernate.loader.Loader.doList(Loader.java:2213) ... 136 more Version-Release number of selected component (if applicable): JON 3.0.1 How reproducible: Always Steps to Reproduce: 1. Deploy bundle version 1; 2. Deploy bundle version 2; 3. Delete bundle version 1; 4. Try to deploy bundle version 3; Actual results: Bundle version 3 is not deployed and exception (as above) is logged in the rhq-server-log4j.log file. In JON UI, the following error message is shown: "Failed to load bundle with the latest version data" Expected results: Bundle version 3 is properly deployed. Additional info: Here is the content of the rhq_bundle_version table after steps to reproduce: 1. Deployed bundle version 1: select * from rhq_bundle_version id name description version version_order action config_def_id bundle_id 10051 deployServerPassword first deploy 1 0 10811 10041 id name description version version_order action config_def_id bundle_id Query Time=1 2. Deployed bundle version 2: select * from rhq_bundle_version id name description version version_order action config_def_id bundle_id 10051 deployServerPassword first deploy 1 0 10811 10041 10042 deployServerPassword first deploy 2 1 10802 10041 id name description version version_order action config_def_id bundle_id Query Time=0 3. Deleted bundle version 1: select * from rhq_bundle_version id name description version version_order action config_def_id bundle_id 10042 deployServerPassword first deploy 2 1 10802 10041 id name description version version_order action config_def_id bundle_id Query Time=0 4. Bundle version 3 - deployed: select * from rhq_bundle_version id name description version version_order action config_def_id bundle_id 10042 deployServerPassword first deploy 2 1 10802 10041 10061 deployServerPassword first deploy 3 1 10821 10041 id name description version version_order action config_def_id bundle_id Query Time=0
No help make this issue clear and better define, 'deploy' and 'deployed' as mentioned in comment #0 refers to the actual creation of the bundle and its versions. i.e. uploading a bundle ZIP containing version 1, then uploading a bundle ZIP that contains version 2, deleting bundle version 1, then uploading a bundle ZIP that contains version 3. Additionally, it appears that the upload and creation of bundle version 3 is successful but nothing is displayed in the UI due to the duplicate rows as described in comment #0. This means version 3 can not be deployed to a destination because the bundle is no longer available from the UI. However, if you create bundle version 4, the versions become visible again in the UI and the UI error goes away. Once version 4 has been created the rhq_bundle_version table looks like: Results for statement 1 of 1: SELECT name, version, version_order FROM rhq_bundle_version WHERE name = 'base_jee_test' name version version_order base_jee_test 1.0.1-SNAPSHOT 1 base_jee_test 1.0.2-SNAPSHOT 1 base_jee_test 1.0.3-SNAPSHOT 2 name version version_order Query Time=1
To provide even further details and correct a statement from comment #1: > ...This means version 3 can not be deployed to a destination because the bundle is no longer available from the UI.... You actually can still deploy the version to a destination. You just don't see the bundle listed on the Bundles (http://localhost:7080/coregui/#Bundles) page and you see mentioned error message in the UI. However, the navigation tree does not seem to be affected. You can still expand and select Destinations from the navigation tree and deploy the bundle via the destination page. Not sure what other issues these duplicate version_order rows will cause. Perhaps reverting to a previous version will be broken?
for the record, there is this unit test: org.rhq.enterprise.server.bundle.BundleManagerBeanTest.testCreateBundleVersionOrdering() that we can add to in order to check for this condition (unfortunately, it currently does not test for the case when the bundle version is deleted, which is why we didn't catch this bug).
git commit to master: f364f5f this also updates the unit tests in BundleManagerBeanTest to confirm the fix. for this BZ, we need to remember to cherry pick that commit to the proper jon 3.1.1 branch
commit to release/jon3.1.x branch: b42e0606e6c048b36632b40691facfd6b2487390
JON 3.1.1 ER1 build is available. Moving to ON_QA. https://brewweb.devel.redhat.com/buildinfo?buildID=226942
Created attachment 603086 [details] rhq-server-log
Created attachment 603087 [details] test-bundle-v1
Created attachment 603088 [details] test-bundle-v2
I was able to reproduce in 3.1.0. In 3.1.1.ER1 JON UI won't let me upload version 2 of the bundle "Failed to create destination, it may already exist. (Note, for an existing destination deploy from the Destination view)" see attached log and test bundles
** correction ** I can upload new versions. It's 'deploy' that I was having trouble with. I couldn't seem to deploy new version to the same destination.
verified. Disregard my previous comments (#10 and #11)
if you think you have this problem, run this manual SQL query to find out which bundle versions are duplicated: select name, version_order, count(version_order) from rhq_bundle_version t1 group by name, version_order having count(version_order) > 1; this will tell you which bundle name has more than one bundle version with the same version_order. These are the rows you need to manually update - either delete the from the UI or do a manual SQL UPDATE to fix the rows so you have monotonically increasing version_order values starting at 0.
Created attachment 703675 [details] CLI script to tell you what UPDATE SQL to execute to fix attached is a .js RHQ CLI script that you can use to determine if you need to run any SQL UPDATE commands to fix your DB due to this bug. You will only need to worry about this if you deleted bundles while running an RHQ version that had this bug. When you run this, it will look at all your bundle versions and try to determine what rows need to be fixed. At the end of the run, it will spit out a list of all UPDATE SQL statements you must execute on your DB to fix up the data. A run will look something like this: rhqadmin@localhost:7080$ exec -f BZ826995.js ______ Bundle: Small Bundle (10001) Bundle Version: 1.0 [1] (10001) UPDATE RHQ_BUNDLE_VERSION SET VERSION_ORDER=0 WHERE ID=10001 Bundle Version: 1.5 [1] (10002) Bundle Version: 3.0 [2] (10003) ______ Bundle: My Simple WAR Application (10002) Bundle Version: 1.0 [0] (10004) Bundle Version: 2.0 [1] (10005) ======== Execute these SQL UPDATE statements: ======== UPDATE RHQ_BUNDLE_VERSION SET VERSION_ORDER=0 WHERE ID=10001
Created attachment 703676 [details] CLI script to tell you what UPDATE SQL to execute to fix slightly fix script - have it output the SQL with ";" at the end of each line. When you run this script, you can easily copy-n-paste the UPDATE SQL statements into: http://localhost:7080/coregui/CoreGUI.html#Test/ServerAccess/SQL and click press the "Execute SQL" button.