Please Note This forum exists for community support for the Mango product family and the Radix IoT Platform. Although Radix IoT employees participate in this forum from time to time, there is no guarantee of a response to anything posted here, nor can Radix IoT, LLC guarantee the accuracy of any information expressed or conveyed. Specific project questions from customers with active support contracts are asked to send requests to support@radixiot.com.
How to clean a very large H2 Database
-
This post is deleted! -
I used this query to search for datapoints to reduce:
SELECT count(*) as totale, dataPointId, d.name FROM pointValues p, dataPoints d where p.dataPointID = d.id group by p.dataPointId , d.name order by totale desc;I will tune the results of this query,
I also founded 8500000 records in PointValueAnnotations all like the following row
TEXTPOINTVALUESHORT | TEXTPOINTVALUELONG | SOURCEMESSAGE --------------------+--------------------+---------------------- | | scripting.annotation|are these rows useful for something or I can delete it?
Who produce these rows?Thanks, Antonio
-
That is a consequence of a Scripting data source setting points that belong to other data sources, so that those values can be seen to have come from elsewhere after the fact. Yes you could delete those rows, you may possibly have to do a backup and a restore to reclaim the disk space.
-
Thanks, I will delete such rows but I think other similar rows will be created in the future, there's a way to avoid this?
Antonio -
Our solution was to code up the NoSQL module so that people wouldn't have trouble with their pointValues tables. That's the surefire way to avoid it.
-
At the end tuning datapoints purge I'm arrived to a reasonable size for H2 database, about 1.5GB, now I'm testing the mysql porting of this database. I've 2 troubles:
- backupMysqlWithOutDatabase:442) - java.io.IOException: Cannot run program "mysqldump"
the mysql setting on env.properties is the following:
# MySQL database settings. Your MySQL instance must already be running and configured before this can be used. db.type=mysql db.url=jdbc:mysql://127.0.0.1:3306/mango db.username=solergy db.password=secret db.mysqldump= db.mysql=what is required in "db.mysqldump" and "db.mysql" fileds?
- When using excel reports purge I've the following exception
what's the problem and how to solve it?
WARN 2018-11-06 10:23:35,344 (com.infiniteautomation.mango.web.mvc.rest.v1.exce lreports.ExcelReportRestController.purge:547) - java.lang.NullPointerException at java.io.File.<init>(Unknown Source) at com.infiniteautomation.mango.excelreports.ExcelReportsCommon.getRepor t(ExcelReportsCommon.java:172) at com.infiniteautomation.mango.excelreports.dao.ExcelReportDao.purgeRep ortsBefore(ExcelReportDao.java:190) at com.infiniteautomation.mango.web.mvc.rest.v1.excelreports.ExcelReport RestController.purge(ExcelReportRestController.java:543) at com.infiniteautomation.mango.web.mvc.rest.v1.excelreports.ExcelReport RestController.purgeUsingSettings(ExcelReportRestController.java:509) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) at java.lang.reflect.Method.invoke(Unknown Source) at org.springframework.web.method.support.InvocableHandlerMethod.doInvok e(InvocableHandlerMethod.java:221) at org.springframework.web.method.support.InvocableHandlerMethod.invokeF orRequest(InvocableHandlerMethod.java:137) at org.springframework.web.servlet.mvc.method.annotation.ServletInvocabl eHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:110) at org.springframework.web.servlet.mvc.method.annotation.RequestMappingH andlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:806) at org.springframework.web.servlet.mvc.method.annotation.RequestMappingH andlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:729) at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapt er.handle(AbstractHandlerMethodAdapter.java:85) at org.springframework.web.servlet.DispatcherServlet.doDispatch(Dispatch erServlet.java:959) at org.springframework.web.servlet.DispatcherServlet.doService(Dispatche rServlet.java:893) at org.springframework.web.servlet.FrameworkServlet.processRequest(Frame workServlet.java:970) at org.springframework.web.servlet.FrameworkServlet.doDelete(FrameworkSe rvlet.java:894) at javax.servlet.http.HttpServlet.service(HttpServlet.java:713) at org.springframework.web.servlet.FrameworkServlet.service(FrameworkSer vlet.java:846) at javax.servlet.http.HttpServlet.service(HttpServlet.java:790) at org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:808 ) at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(Servlet Handler.java:1669) at org.eclipse.jetty.servlets.UserAgentFilter.doFilter(UserAgentFilter.j ava:83) at org.eclipse.jetty.servlets.GzipFilter.doFilter(GzipFilter.java:300) at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(Servlet Handler.java:1652) at org.springframework.web.filter.CharacterEncodingFilter.doFilterIntern al(CharacterEncodingFilter.java:85) at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerR equestFilter.java:107) at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(Servlet Handler.java:1652) at org.springframework.web.filter.ShallowEtagHeaderFilter.doFilterIntern al(ShallowEtagHeaderFilter.java:87) at com.serotonin.m2m2.web.filter.MangoShallowEtagHeaderFilter.doFilterIn ternal(MangoShallowEtagHeaderFilter.java:47) at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerR equestFilter.java:107) at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(Servlet Handler.java:1652) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain. doFilter(FilterChainProxy.java:317) at org.springframework.security.web.access.intercept.FilterSecurityInter ceptor.invoke(FilterSecurityInterceptor.java:127) at org.springframework.security.web.access.intercept.FilterSecurityInter ceptor.doFilter(FilterSecurityInterceptor.java:91) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain. doFilter(FilterChainProxy.java:331) at org.springframework.security.web.access.ExceptionTranslationFilter.do Filter(ExceptionTranslationFilter.java:115) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain. doFilter(FilterChainProxy.java:331) at org.springframework.security.web.session.SessionManagementFilter.doFi lter(SessionManagementFilter.java:137) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain. doFilter(FilterChainProxy.java:331) at org.springframework.security.web.authentication.AnonymousAuthenticati onFilter.doFilter(AnonymousAuthenticationFilter.java:111) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain. doFilter(FilterChainProxy.java:331) at org.springframework.security.web.servletapi.SecurityContextHolderAwar eRequestFilter.doFilter(SecurityContextHolderAwareRequestFilter.java:169) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain. doFilter(FilterChainProxy.java:331) at org.springframework.security.web.savedrequest.RequestCacheAwareFilter .doFilter(RequestCacheAwareFilter.java:63) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain. doFilter(FilterChainProxy.java:331) at org.springframework.security.web.authentication.logout.LogoutFilter.d oFilter(LogoutFilter.java:121) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain. doFilter(FilterChainProxy.java:331) at com.serotonin.m2m2.web.mvc.spring.security.CsrfHeaderFilter.doFilterI nternal(CsrfHeaderFilter.java:41) at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerR equestFilter.java:107) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain. doFilter(FilterChainProxy.java:331) at org.springframework.security.web.csrf.CsrfFilter.doFilterInternal(Csr fFilter.java:124) at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerR equestFilter.java:107) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain. doFilter(FilterChainProxy.java:331) at org.springframework.security.web.header.HeaderWriterFilter.doFilterIn ternal(HeaderWriterFilter.java:66) at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerR equestFilter.java:107) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain. doFilter(FilterChainProxy.java:331) at org.springframework.security.web.context.SecurityContextPersistenceFi lter.doFilter(SecurityContextPersistenceFilter.java:105) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain. doFilter(FilterChainProxy.java:331) at org.springframework.security.web.context.request.async.WebAsyncManage rIntegrationFilter.doFilterInternal(WebAsyncManagerIntegrationFilter.java:56) at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerR equestFilter.java:107) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain. doFilter(FilterChainProxy.java:331) at org.springframework.security.web.FilterChainProxy.doFilterInternal(Fi lterChainProxy.java:214) at org.springframework.security.web.FilterChainProxy.doFilter(FilterChai nProxy.java:177) at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(D elegatingFilterProxy.java:346) at org.springframework.web.filter.DelegatingFilterProxy.doFilter(Delegat ingFilterProxy.java:262) at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(Servlet Handler.java:1652) at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java :585) at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.j ava:143) at org.eclipse.jetty.security.SecurityHandler.handle(SecurityHandler.jav a:577) at org.eclipse.jetty.server.session.SessionHandler.doHandle(SessionHandl er.java:223) at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandl er.java:1127) at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java: 515) at org.eclipse.jetty.server.session.SessionHandler.doScope(SessionHandle r.java:185) at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandle r.java:1061) at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.j ava:141) at org.eclipse.jetty.server.handler.ContextHandlerCollection.handle(Cont extHandlerCollection.java:215) at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper .java:97) at org.eclipse.jetty.server.Server.handle(Server.java:499) at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:310) at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.jav a:257) at org.eclipse.jetty.io.AbstractConnection$2.run(AbstractConnection.java :540) at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPoo l.java:635) at org.eclipse.jetty.util.thread.QueuedThreadPool$3.run(QueuedThreadPool .java:555) at java.lang.Thread.run(Unknown Source)Thanks, Antonio
-
what is required in "db.mysqldump" and "db.mysql" fileds?
The path to invoke the mysqldump or mysql programs (or just the executables if they're on the Mango user's $Path). The error,
backupMysqlWithOutDatabase:442) - java.io.IOException: Cannot run program "mysqldump"
Indicates mysqldump is not on the path.
what's the problem and how to solve it?
That issue appears to have been solved in version 3.1.3 of the excel reports module. You will probably needs to work around it by manually deleting from the excelReports table and deleting from Mango/web/modules/excelReports/web/report-data/ as appropriate. It may only be one report with a null filename, you could perhaps investigate the excelReports SQL table to see.
