History | Log In     View a printable version of the current page.  
   HOME       BROWSE PROJECT       FIND ISSUES   
    QUICK SEARCH:  
Issue Details (XML | Word | Printable)

Key: SQL-23
Type: Bug Bug
Status: Resolved Resolved
Resolution: Fixed
Priority: Major Major
Assignee: Bob Swift
Reporter: Andy Brook
Votes: 0
Watchers: 0
Operations

If you were logged in you would be able to see more operations.
Confluence SQL Plugin

SQL queries not closing connections?

Created: 15/Aug/07 04:22 AM   Updated: 26/Apr/08 12:15 AM
Component/s: SQL macro
Affects Version/s: 3.3.0
Fix Version/s: 3.4.0

Time Tracking:
Not Specified

File Attachments: 1. Java Archive File sql-plugin-3.4.0-b1.jar (107 kb)
2. Java Archive File sql-plugin-3.4.0-b2.jar (107 kb)
Environment: windows2003/jboss420/sqlserver

Labels:


 Description  « Hide
I have a macro to pull out a count of users in 'confluence-users' , it works fine, but on exit I get a warning stack from jboss: 
02:15:43,490 WARN  [WrappedConnection] Closing a statement you left open, please do your own housekeeping
java.lang.Throwable: STACKTRACE
        at org.jboss.resource.adapter.jdbc.WrappedConnection.registerStatement(WrappedConnection.java:576)
        at org.jboss.resource.adapter.jdbc.WrappedStatement.<init>(WrappedStatement.java:62)
        at org.jboss.resource.adapter.jdbc.WrappedPreparedStatement.<init>(WrappedPreparedStatement.java:56)
        at org.jboss.resource.adapter.jdbc.WrappedConnection.prepareStatement(WrappedConnection.java:187)
        at org.swift.confluence.sql.SqlMacro.execute(SqlMacro.java:151)
        at org.swift.confluence.scriptutil.ScriptMacro.execute(ScriptMacro.java:489)
        at com.atlassian.renderer.v2.macro.ResourceAwareMacroDecorator.execute(ResourceAwareMacroDecorator.java:45)
        at com.atlassian.renderer.v2.components.MacroRendererComponent.processMacro(MacroRendererComponent.java:340)
        at com.atlassian.renderer.v2.components.MacroRendererComponent.makeMacro(MacroRendererComponent.java:241)
        at com.atlassian.renderer.v2.components.MacroRendererComponent.makeMacro(MacroRendererComponent.java:137)
        at com.atlassian.renderer.v2.components.MacroRendererComponent.handlePotentialMacro(MacroRendererComponent.java:110)

Is close explicitly called on Datasource connections?

My confluence-user membership counter:

{sql:datasource=java:/ConfluenceDS\|table=false}
select count(*) as "Current members of confluence-users" from EXTERNAL_ENTITIES
inner join EXTERNAL_MEMBERS on
EXTERNAL_ENTITIES.ID = EXTERNAL_MEMBERS.EXTENTITYID
inner join GROUPS on
EXTERNAL_MEMBERS.GROUPID = GROUPS.ID
where
GROUPS.GROUPNAME='confluence-users'{sql}


 All   Comments   Work Log   Change History   FishEye   Crucible   Related Builds      Sort Order: Ascending order - Click to sort in descending order
[ Permlink | « Hide ]
Andy Brook - 18/Mar/08 04:32 AM
Got some repeats of this in 2.7.2 
2008-03-18 00:25:01,968 WARN  [org.jboss.resource.adapter.jdbc.WrappedConnection] Closing a statement you left open, please do your own housekeeping
java.lang.Throwable: STACKTRACE
        at org.jboss.resource.adapter.jdbc.WrappedConnection.registerStatement(WrappedConnection.java:576)
        at org.jboss.resource.adapter.jdbc.WrappedStatement.<init>(WrappedStatement.java:62)
        at org.jboss.resource.adapter.jdbc.WrappedPreparedStatement.<init>(WrappedPreparedStatement.java:56)
        at org.jboss.resource.adapter.jdbc.WrappedConnection.prepareStatement(WrappedConnection.java:187)
        at org.swift.confluence.sql.SqlMacro.execute(SqlMacro.java:151)
        at org.swift.confluence.scriptutil.ScriptMacro.execute(ScriptMacro.java:489)
        at com.atlassian.renderer.v2.macro.ResourceAwareMacroDecorator.execute(ResourceAwareMacroDecorator.java:45)
        at com.atlassian.renderer.v2.components.MacroRendererComponent.executeMacro(MacroRendererComponent.java:297)
        at com.atlassian.renderer.v2.components.MacroRendererComponent.processMacro(MacroRendererComponent.java:255)
        at com.atlassian.renderer.v2.components.MacroRendererComponent.makeMacro(MacroRendererComponent.java:151)
        at com.atlassian.renderer.v2.components.WikiContentRendererHandler.handleMacro(WikiContentRendererHandler.java:18)
        at com.atlassian.renderer.v2.WikiMarkupParser.makeMacro(WikiMarkupParser.java:126)
        at com.atlassian.renderer.v2.WikiMarkupParser.makeMacro(WikiMarkupParser.java:121)
        at com.atlassian.renderer.v2.WikiMarkupParser.handlePotentialMacro(WikiMarkupParser.java:94)
        at com.atlassian.renderer.v2.WikiMarkupParser.parse(WikiMarkupParser.java:60)
        at com.atlassian.renderer.v2.components.MacroRendererComponent.render(MacroRendererComponent.java:47)
        at com.atlassian.renderer.v2.V2Renderer.render(V2Renderer.java:53)
        at com.atlassian.renderer.v2.V2SubRenderer.render(V2SubRenderer.java:44)
        at org.swift.confluence.cache.CacheMacroSupport.render(CacheMacroSupport.java:119)
        at org.swift.confluence.cache.CacheMacroSupport.render(CacheMacroSupport.java:129)
        at org.swift.confluence.cache.CacheMacro.execute(CacheMacro.java:174)
        at org.randombits.confluence.support.ConfluenceMacro.execute(ConfluenceMacro.java:54)
        at com.atlassian.renderer.v2.macro.ResourceAwareMacroDecorator.execute(ResourceAwareMacroDecorator.java:45)
        at com.atlassian.renderer.v2.components.MacroRendererComponent.executeMacro(MacroRendererComponent.java:297)
        at com.atlassian.renderer.v2.components.MacroRendererComponent.processMacro(MacroRendererComponent.java:255)
        at com.atlassian.renderer.v2.components.MacroRendererComponent.makeMacro(MacroRendererComponent.java:151)
        at com.atlassian.renderer.v2.components.WikiContentRendererHandler.handleMacro(WikiContentRendererHandler.java:18)
        at com.atlassian.renderer.v2.WikiMarkupParser.makeMacro(WikiMarkupParser.java:126)
        at com.atlassian.renderer.v2.WikiMarkupParser.makeMacro(WikiMarkupParser.java:121)
        at com.atlassian.renderer.v2.WikiMarkupParser.handlePotentialMacro(WikiMarkupParser.java:94)
        at com.atlassian.renderer.v2.WikiMarkupParser.parse(WikiMarkupParser.java:60)
        at com.atlassian.renderer.v2.components.MacroRendererComponent.render(MacroRendererComponent.java:47)
        at com.atlassian.renderer.v2.V2Renderer.render(V2Renderer.java:53)
        at com.atlassian.renderer.v2.V2SubRenderer.render(V2SubRenderer.java:23)
        at com.atlassian.confluence.renderer.v2.macros.PageIncludeMacro.fetchPageContent(PageIncludeMacro.java:103)
        at com.atlassian.confluence.renderer.v2.macros.PageIncludeMacro.execute(PageIncludeMacro.java:77)
        at com.atlassian.renderer.v2.macro.ResourceAwareMacroDecorator.execute(ResourceAwareMacroDecorator.java:45)
        at com.atlassian.renderer.v2.components.MacroRendererComponent.executeMacro(MacroRendererComponent.java:297)
        at com.atlassian.renderer.v2.components.MacroRendererComponent.processMacro(MacroRendererComponent.java:255)
        at com.atlassian.renderer.v2.components.MacroRendererComponent.makeMacro(MacroRendererComponent.java:151)
        at com.atlassian.renderer.v2.components.WikiContentRendererHandler.handleMacro(WikiContentRendererHandler.java:18)
        at com.atlassian.renderer.v2.WikiMarkupParser.makeMacro(WikiMarkupParser.java:126)
        at com.atlassian.renderer.v2.WikiMarkupParser.makeMacro(WikiMarkupParser.java:114)
        at com.atlassian.renderer.v2.WikiMarkupParser.handlePotentialMacro(WikiMarkupParser.java:99)
        at com.atlassian.renderer.v2.WikiMarkupParser.parse(WikiMarkupParser.java:60)
        at com.atlassian.renderer.v2.components.MacroRendererComponent.render(MacroRendererComponent.java:47)
        at com.atlassian.renderer.v2.V2Renderer.render(V2Renderer.java:53)
        at com.atlassian.renderer.v2.V2RendererFacade.convertWikiToXHtml(V2RendererFacade.java:57)
        at sun.reflect.GeneratedMethodAccessor301.invoke(Unknown Source)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
        at java.lang.reflect.Method.invoke(Method.java:597)
        at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:296)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:177)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:144)
        at com.atlassian.spring.interceptors.SpringProfilingInterceptor.invoke(SpringProfilingInterceptor.java:20)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:166)
        at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:107)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:166)
        at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)
        at $Proxy103.convertWikiToXHtml(Unknown Source)
        at com.atlassian.confluence.renderer.DefaultWikiStyleRenderer.convertWikiToXHtml(DefaultWikiStyleRenderer.java:19)
        at com.atlassian.confluence.util.ContentFacade.getWelcomeMessage(ContentFacade.java:67)
        at com.atlassian.confluence.dashboard.actions.DashboardAction.getWelcomeMessage(DashboardAction.java:37)
        at sun.reflect.GeneratedMethodAccessor490.invoke(Unknown Source)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
        at java.lang.reflect.Method.invoke(Method.java:597)
        at org.apache.velocity.runtime.parser.node.PropertyExecutor.execute(PropertyExecutor.java:154)
        at org.apache.velocity.runtime.parser.node.ASTIdentifier.execute(ASTIdentifier.java:226)
        at org.apache.velocity.runtime.parser.node.ASTReference.execute(ASTReference.java:207)
        at org.apache.velocity.runtime.parser.node.ASTReference.evaluate(ASTReference.java:337)
        at org.apache.velocity.runtime.parser.node.ASTExpression.evaluate(ASTExpression.java:84)
        at org.apache.velocity.runtime.parser.node.ASTIfStatement.render(ASTIfStatement.java:107)
        at org.apache.velocity.runtime.parser.node.SimpleNode.render(SimpleNode.java:271)
        at org.apache.velocity.runtime.directive.Parse.render(Parse.java:232)
        at org.apache.velocity.runtime.parser.node.ASTDirective.render(ASTDirective.java:153)
        at org.apache.velocity.runtime.parser.node.ASTBlock.render(ASTBlock.java:94)
        at org.apache.velocity.runtime.parser.node.ASTIfStatement.render(ASTIfStatement.java:109)
        at org.apache.velocity.runtime.parser.node.SimpleNode.render(SimpleNode.java:271)
        at org.apache.velocity.Template.merge(Template.java:296)
        at com.atlassian.confluence.util.velocity.VelocityUtils.getRenderedTemplateWithoutSwallowingErrors(VelocityUtils.java:53)
        at com.atlassian.confluence.setup.velocity.ApplyDecoratorDirective.render(ApplyDecoratorDirective.java:229)
        at org.apache.velocity.runtime.parser.node.ASTDirective.render(ASTDirective.java:153)
        at org.apache.velocity.runtime.parser.node.SimpleNode.render(SimpleNode.java:271)
        at org.apache.velocity.runtime.directive.VelocimacroProxy.render(VelocimacroProxy.java:215)
        at org.apache.velocity.runtime.parser.node.ASTDirective.render(ASTDirective.java:153)
        at org.apache.velocity.runtime.parser.node.ASTBlock.render(ASTBlock.java:94)
        at org.apache.velocity.runtime.parser.node.ASTIfStatement.render(ASTIfStatement.java:109)
        at org.apache.velocity.runtime.parser.node.SimpleNode.render(SimpleNode.java:271)
        at org.apache.velocity.Template.merge(Template.java:296)
        at com.atlassian.confluence.util.velocity.VelocityUtils.getRenderedTemplateWithoutSwallowingErrors(VelocityUtils.java:53)
        at com.atlassian.confluence.setup.velocity.ApplyDecoratorDirective.render(ApplyDecoratorDirective.java:229)
        at org.apache.velocity.runtime.parser.node.ASTDirective.render(ASTDirective.java:153)
        at org.apache.velocity.runtime.parser.node.SimpleNode.render(SimpleNode.java:271)
        at org.apache.velocity.Template.merge(Template.java:296)
        at com.opensymphony.webwork.dispatcher.VelocityResult.doExecute(VelocityResult.java:91)
        at com.atlassian.xwork.results.ProfiledVelocityResult.doExecute(ProfiledVelocityResult.java:21)
        at com.opensymphony.webwork.dispatcher.WebWorkResultSupport.execute(WebWorkResultSupport.java:116)
        at com.opensymphony.xwork.DefaultActionInvocation.executeResult(DefaultActionInvocation.java:263)
        at com.opensymphony.xwork.DefaultActionInvocation.invoke(DefaultActionInvocation.java:187)
        at com.opensymphony.xwork.interceptor.AroundInterceptor.intercept(AroundInterceptor.java:35)
        at com.opensymphony.xwork.DefaultActionInvocation.invoke(DefaultActionInvocation.java:165)
        at com.atlassian.confluence.util.LoggingContextInterceptor.intercept(LoggingContextInterceptor.java:48)
        at com.opensymphony.xwork.DefaultActionInvocation.invoke(DefaultActionInvocation.java:165)
        at com.atlassian.confluence.security.actions.PermissionCheckInterceptor.intercept(PermissionCheckInterceptor.java:54)
        at com.opensymphony.xwork.DefaultActionInvocation.invoke(DefaultActionInvocation.java:165)
        at com.atlassian.confluence.pages.actions.PageAwareInterceptor.intercept(PageAwareInterceptor.java:114)
        at com.opensymphony.xwork.DefaultActionInvocation.invoke(DefaultActionInvocation.java:165)
        at com.atlassian.confluence.spaces.actions.SpaceAwareInterceptor.intercept(SpaceAwareInterceptor.java:67)
        at com.opensymphony.xwork.DefaultActionInvocation.invoke(DefaultActionInvocation.java:165)
        at com.opensymphony.xwork.interceptor.AroundInterceptor.intercept(AroundInterceptor.java:35)
        at com.opensymphony.xwork.DefaultActionInvocation.invoke(DefaultActionInvocation.java:165)
        at com.atlassian.confluence.core.actions.LastModifiedInterceptor.intercept(LastModifiedInterceptor.java:39)
        at com.opensymphony.xwork.DefaultActionInvocation.invoke(DefaultActionInvocation.java:165)
        at com.atlassian.confluence.core.ConfluenceAutowireInterceptor.intercept(ConfluenceAutowireInterceptor.java:25)
        at com.opensymphony.xwork.DefaultActionInvocation.invoke(DefaultActionInvocation.java:165)
        at com.opensymphony.xwork.interceptor.AroundInterceptor.intercept(AroundInterceptor.java:35)
        at com.opensymphony.xwork.DefaultActionInvocation.invoke(DefaultActionInvocation.java:165)
        at com.opensymphony.xwork.interceptor.AroundInterceptor.intercept(AroundInterceptor.java:35)
        at com.opensymphony.xwork.DefaultActionInvocation.invoke(DefaultActionInvocation.java:165)
        at com.atlassian.xwork.interceptors.XWorkTransactionInterceptor.intercept(XWorkTransactionInterceptor.java:98)
        at com.opensymphony.xwork.DefaultActionInvocation.invoke(DefaultActionInvocation.java:165)
        at com.opensymphony.xwork.interceptor.AroundInterceptor.intercept(AroundInterceptor.java:35)
        at com.opensymphony.xwork.DefaultActionInvocation.invoke(DefaultActionInvocation.java:165)
        at com.opensymphony.xwork.DefaultActionProxy.execute(DefaultActionProxy.java:115)
        at com.opensymphony.webwork.dispatcher.ServletDispatcher.serviceAction(ServletDispatcher.java:229)
        at com.opensymphony.webwork.dispatcher.ServletDispatcher.service(ServletDispatcher.java:199)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:803)

[ Permlink | « Hide ]
Andy Brook - 18/Mar/08 04:33 AM
found in 3.3.0

[ Permlink | « Hide ]
Andy Brook - 18/Mar/08 04:39 AM - edited
Happened to notice a stack at ... 22:25, 23:25, 00:25 , the next at 01:25, 02:30, wonder if its a scheduled job?

I have an idea how this happens, but not how to solve it. I have the following page content:

{cache:refresh=1h}{sql:datasource=java:/ConfluenceDS|table=false}
select count(*) as "current members of confluence-users" from external_entities
inner join external_members on
external_entities.id = external_members.extentityid
inner join groups on
external_members.groupid = groups.id
where groups.groupname='confluence-users'{sql}{cache}

That page is included into the Administration/General Configuration/Site Welcome Message, such that every time the Dash page loads, that is included. Great, it works fine.

I also happen to have a remote 'paranoia' script running which loads the Dashboard every 5 minutes to verify Confluence is still alive. Given the cache time of the included content is one hour, near enough every hour +5minutes, the Welcome page is loaded, the cache checked for content and the SQL executed on cache timeout.

So, This problem appears to happen when the cached value is timed out.....


[ Permlink | « Hide ]
Bob Swift - 18/Mar/08 08:36 AM
The result set and connection are closed, however, the statement is not closed explicitly. Connection close should have covered that, but I will add an explicit statement close as well.

[ Permlink | « Hide ]
Bob Swift - 18/Mar/08 08:40 AM
Please test if you can. Note that this includes changes for macro security that require another plugin, so if you are using macro security, revert back to released plugin after test.

[ Permlink | « Hide ]
Andy Brook - 18/Mar/08 09:40 AM
ok, will try to test and comment.

[ Permlink | « Hide ]
Andy Brook - 19/Mar/08 04:40 AM
Could you also check for any unclosed result sets? I'm getting similar stacks for those too. Cheers.

[ Permlink | « Hide ]
Bob Swift - 19/Mar/08 07:55 PM
Fix result set closure. While the normal result set is closed in the finally clause, in the case with multiple result sets, only the last one is handled by the finally clause. An existing result set needs to be closed before re-assigning the resultSet variable.

Andy, thanks for testing this. Hopefully b2 will work better.


[ Permlink | « Hide ]
Bob Swift - 26/Apr/08 12:15 AM
This is now officially released.


Powered by Atlassian JIRA the Professional Issue Tracker. (Enterprise Edition, Version: 3.12.2-#300) - Bug/feature request - Atlassian news - Contact Administrators