An error getting clusters resources information on GUI ACS 4.22

[sptest232]

Hello!
Please help me to resolve the problem.
After update ACS 4.19 to 4.22 the problem appears.
I have several clusters and a couple of zones and when i try to get any cluster's resources information i have the error.

ERROR [c.c.a.ApiServer] (qtp253011924-8949396:[ctx-4dea2d0b, ctx-4da616cc]) (logid:ee49da2c) unhandled exception executing api command: [Ljava.lang.String;@3f55c3f6 com.cloud.utils.exception.CloudRuntimeException: DB Exception on: SELECT pci, type, SUM(vmcount) FROM (SELECT MAX(IF(offering.name = 'pciDevice',value,'')) AS pci, MAX(IF(offering.name = 'vgpuType', value,'')) AS type, COUNT(DISTINCT vm.id) AS vmcount FROM service_offering_details offering INNER JOIN vm_instance vm ON offering.service_offering_id = vm.service_offering_id INNER JOIN `cloud`.`host` ON vm.host_id = host.id WHERE vm.state = 'Running' AND host.data_center_id = ? AND host.cluster_id = ? GROUP BY vm.service_offering_id) results GROUP BY pci, type
        at com.cloud.vm.dao.VMInstanceDaoImpl.countVgpuVMs(VMInstanceDaoImpl.java:850)
        at jdk.internal.reflect.GeneratedMethodAccessor819.invoke(Unknown Source)
        at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.base/java.lang.reflect.Method.invoke(Method.java:569)
        at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:344)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:198)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
        at com.cloud.utils.db.TransactionContextInterceptor.invoke(TransactionContextInterceptor.java:34)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:175)
        at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:97)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
        at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:215)
        at jdk.proxy3/jdk.proxy3.$Proxy119.countVgpuVMs(Unknown Source)
        at com.cloud.api.ApiDBUtils.getVgpuVmsCount(ApiDBUtils.java:1364)
        at com.cloud.api.ApiResponseHelper.createCapacityResponse(ApiResponseHelper.java:2203)
        at org.apache.cloudstack.api.command.admin.resource.ListCapacityCmd.execute(ListCapacityCmd.java:131)
        at com.cloud.api.ApiDispatcher.dispatch(ApiDispatcher.java:173)
        at com.cloud.api.ApiServer.queueCommand(ApiServer.java:854)
        at com.cloud.api.ApiServer.handleRequest(ApiServer.java:668)
        at com.cloud.api.ApiServlet.processRequestInContext(ApiServlet.java:416)
        at com.cloud.api.ApiServlet$1.run(ApiServlet.java:193)
        at org.apache.cloudstack.managed.context.impl.DefaultManagedContext$1.call(DefaultManagedContext.java:56)
        at org.apache.cloudstack.managed.context.impl.DefaultManagedContext.callWithContext(DefaultManagedContext.java:103)
        at org.apache.cloudstack.managed.context.impl.DefaultManagedContext.runWithContext(DefaultManagedContext.java:53)
        at com.cloud.api.ApiServlet.processRequest(ApiServlet.java:190)
        at com.cloud.api.ApiServlet.doGet(ApiServlet.java:144)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:645)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:750)
        at org.eclipse.jetty.servlet.ServletHolder$NotAsync.service(ServletHolder.java:1450)
        at org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:799)
        at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:554)
        at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:143)
        at org.eclipse.jetty.security.SecurityHandler.handle(SecurityHandler.java:600)
        at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:127)
        at org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:235)
        at org.eclipse.jetty.server.session.SessionHandler.doHandle(SessionHandler.java:1624)
        at org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:233)
        at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1440)
        at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:188)
        at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:505)
        at org.eclipse.jetty.server.session.SessionHandler.doScope(SessionHandler.java:1594)
        at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:186)
        at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1355)
        at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:141)
        at org.eclipse.jetty.server.handler.gzip.GzipHandler.handle(GzipHandler.java:772)
        at org.eclipse.jetty.server.handler.HandlerCollection.handle(HandlerCollection.java:146)
        at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:127)
        at org.eclipse.jetty.server.Server.handle(Server.java:516)
        at org.eclipse.jetty.server.HttpChannel.lambda$handle$1(HttpChannel.java:487)
        at org.eclipse.jetty.server.HttpChannel.dispatch(HttpChannel.java:732)
        at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:479)
        at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:277)
        at org.eclipse.jetty.io.AbstractConnection$ReadCallback.succeeded(AbstractConnection.java:311)
        at org.eclipse.jetty.io.FillInterest.fillable(FillInterest.java:105)
        at org.eclipse.jetty.io.ChannelEndPoint$1.run(ChannelEndPoint.java:104)
        at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.runTask(EatWhatYouKill.java:338)
        at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.doProduce(EatWhatYouKill.java:315)
        at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.tryProduce(EatWhatYouKill.java:173)
        at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.run(EatWhatYouKill.java:131)
        at org.eclipse.jetty.util.thread.ReservedThreadExecutor$ReservedThread.run(ReservedThreadExecutor.java:409)
        at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:883)
        at org.eclipse.jetty.util.thread.QueuedThreadPool$Runner.run(QueuedThreadPool.java:1034)
        at java.base/java.lang.Thread.run(Thread.java:840)
Caused by: java.sql.SQLException: No operations allowed after statement closed.
        at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:130)
        at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:98)
        at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:90)
        at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:64)
        at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:74)
        at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:82)
        at com.mysql.cj.jdbc.ClientPreparedStatement.setLong(ClientPreparedStatement.java:1545)
        at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.setLong(HikariProxyPreparedStatement.java)
        at com.cloud.vm.dao.VMInstanceDaoImpl.countVgpuVMs(VMInstanceDaoImpl.java:837)
        ... 62 more
Caused by: com.mysql.cj.exceptions.StatementIsClosedException: No operations allowed after statement closed.
        at jdk.internal.reflect.GeneratedConstructorAccessor2839.newInstance(Unknown Source)
        at java.base/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
        at java.base/java.lang.reflect.Constructor.newInstanceWithCaller(Constructor.java:500)
        at java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:481)
        at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:62)
        at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:86)
        at com.mysql.cj.jdbc.StatementImpl.checkClosed(StatementImpl.java:336)
        at com.mysql.cj.jdbc.ClientPreparedStatement.setLong(ClientPreparedStatement.java:1542)
        ... 64 more

[vishesh92]

@sptest232 Are you consistently getting this error? If you are getting this error consistently, does it go away after managmener server restart?

[TadiosAbebe]

@vishesh92 , Is this a bug, i tested it on 3 different installation (one standalone on baremetal, one a 3 node cluster on KVM, one a 5 node cluster on baremetal), and 2 of them have the above specified issue(the three node and 5 node cluster). All three of them were upgraded from 4.20.1. And the error doesn't go away even after management server restart. I can not seem to figure out the root cause for this.

[TadiosAbebe]

I just noticed the same issue on my test environment that i have upgraded from 4.20.1 -> 4.22. The same exception is raised whenever i go to the resource section of a zone, pod and cluster. In addition, the home dashboard resource section also doesn't show resource count and utilization.

Executing the same query on mysql shell just returns empty set

MariaDB [cloud]> SELECT
    ->     pci,
    ->     type,
    ->     SUM(vmcount) AS total_vm_count
    -> FROM (
    ->     SELECT
    ->         MAX(IF(offering.name = 'pciDevice', offering.value, '')) AS pci,
    ->         MAX(IF(offering.name = 'vgpuType', offering.value, '')) AS type,
    ->         COUNT(DISTINCT vm.id) AS vmcount
    ->     FROM service_offering_details offering
    ->     INNER JOIN vm_instance vm
    ->         ON offering.service_offering_id = vm.service_offering_id
    ->     INNER JOIN cloud.host host
    ->         ON vm.host_id = host.id
    ->     WHERE
    ->         vm.state = 'Running'
    ->         AND host.data_center_id = 1
    ->     GROUP BY vm.service_offering_id
    -> ) results
    -> GROUP BY pci, type;
Empty set (0.020 sec)

[rajujith]

@sptest232 @TadiosAbebe are still facing this issue. Do you use db.cloud.uri=jdbc:mariadb in the db.properties of the management server?
What is the database server in use, MySQL or MariaDB?

[zergawcloud]

@rajujith , I am using MariaDB database cluster, and yes my db.cloud.uri is jdbc:mariadb

# CloudStack database settings
db.cloud.username=cloud
db.cloud.password=ENC(fK02lSieQWH543lmdnJGfjmZiMSCG1pcvfgGzsUSsuDEFK0Ba8QQ)
db.cloud.host=172.28.236.31
db.cloud.driver=jdbc:mysql
db.cloud.port=3306
db.cloud.name=cloud

# Connection URI to the database "cloud". When this property is set, only the following properties will be used along with it: db.cloud.maxActive, db.cloud.maxIdle, db.cloud.maxWait, db.cloud.username, db.cloud.password, db.cloud.driver, db.cloud.validationQuery, db.cloud.isolation.level. Other properties will be ignored.
db.cloud.uri=jdbc:mariadb:sequential://172.28.236.31:3306,172.28.236.32:3306,172.28.236.33:3306/cloud?autoReconnect=true&prepStmtCacheSize=517&cachePrepStmts=true&sessionVariables=sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'&serverTimezone=UTC

But currently, I'm not really sure on how i can translate my db.cloud.uri from the jdbc:mariadb to jdbc:mysql. Just changing mariadb to mysql in the uri and removing sequential shows 2026-03-03 07:28:36,085 ERROR [c.c.u.d.DbUtil] (main:[]) (logid:) GET_LOCK() throws exception java.sql.SQLException: Operation not allowed for a result set of type ResultSet.TYPE_FORWARD_ONLY.

[vishesh92]

Created an issue here: #12712
This is happening with mariadb driver only as of now.

[vishesh92]

During my testing, this was happening when jdbc driver was set to mariadb. Switching to mysql driver for jdbc fixes the issue.

I have fixed it for both of the jdbc drivers in the PR: #12713 which is merged. Closing this discussion.

[GutoVeronezi]

Hello guys,

Just for completeness of the information:

Inside method TransactionLegacy#prepareAutoCloseStatement, the method TransactionLegacy#closePreviousStatement is called. Therefore, when making two consecutive calls of method TransactionLegacy#prepareAutoCloseStatement, the first statement is closed before it is executed, resulting in the observed situation. HikariCP and DBCP have different workflows to close statements. When using HikariCP, it enables statement caching (only supported by the MySQL JDBC driver) and has a workflow that allows the first statement to still work after being "closed" by the consecutive method call. However, DBCP closes the statement right away. Therefore, when using HikariCP + MariaDB, DBCP + MariaDB, or DBCP + MySQL, the first statement will always be closed, causing the situation. The situation was fixed with PR #12713.

---

But currently, I'm not really sure on how i can translate my db.cloud.uri from the jdbc:mariadb to jdbc:mysql. Just changing mariadb to mysql in the uri and removing sequential shows 2026-03-03 07:28:36,085 ERROR [c.c.u.d.DbUtil] (main:[]) (logid:) GET_LOCK() throws exception java.sql.SQLException: Operation not allowed for a result set of type ResultSet.TYPE_FORWARD_ONLY.

@zergawcloud, regarding this exception, it occurs because scrollTolerantForwardOnly flag is not being set. PR #8753 updated the MySQL JDBC driver dependencies, also changing from version 8.0.19 to version 8.0.33. In version 8.0.20, a change was made to correct a bug with a ResultSet configuration:

When creating a Statement, the specification of the resultSetType parameter was not respected, so the ResultSet type was always defined as ResultSet.TYPE_FORWARD_ONLY. With this correction, the resultSetType parameter is now respected. Furthermore, type validation was added so that calling the beforeFirst, afterLast, first, last, absolute, relative, or previous methods will result in an exception if the ResultSet type is ResultSet.TYPE_FORWARD_ONLY. (Error #30474158)

In version 8.0.24, the scrollTolerantForwardOnly property was implemented for backward compatibility:

A new connection property, scrollTolerantForwardOnly, was introduced, preserving the inherited behavior from Connector/J 8.0.17 and earlier, tolerating backward and absolute cursor movements in result sets of type ResultSet.TYPE_FORWARD_ONLY. This serves to maintain compatibility with legacy code that leveraged the old behavior. See the scrollTolerantForwardOnly description for details. (Error #31747910)

PR #8753 added this flag hardcoded when using the standard parameters; however, when using the custom URI settings, it is necessary to add the flag scrollTolerantForwardOnly as true to the URI for the connection to work properly. I have never validated failover/HA with the MySQL driver, though.