How to clean a very large H2 Database

[phildunlap]

That looks like it would be caused by MySQL not supporting NaN, Infinity, or -Infinity as valid values in a column described as a double. If one has Mango already running on MySQL and attempts to set the value of a point to NaN, you get 0, if you do Infinity, you get Double.MAX_VALUE

But for converting, it doesn't look like there's any handling for the differences in what values are acceptable in one database or another. You could fix this by removing or changing these values before doing the conversion, by running this either through the Mango/bin/h2-web-console script or on the /sqlConsole.shtm page of Mango running on the old database:

-- for use to remove values from the H2 pointValues table that cannot be brought into MySQL
delete from pointValues where pointValue=CAST('NaN' AS DOUBLE PRECISION)
delete from pointValues where pointValue=CAST('Infinity' AS DOUBLE PRECISION);
delete from pointValues where pointValue=CAST('-Infinity' AS DOUBLE PRECISION)

Most commercial users use the NoSQL database to avoid problems with larger point values tables (since in Mango 3 all licensed cores can use the NoSQL database, unlike in 2.x where it is a separate license). That's probably why the issue has not been significant in the past. If you want to use SQL to store your point values, you definitely have to keep that table size under control or Mango will begin to slow down.

[etantonio]

The db convertion from H2 to mysql is running (10 hours just to convert table pointValues) but I think there's something strange, at the moment I have only data from last 80 days concerning about 100 variables stored at 1 sample/minute, so I think 80x100x60x24=11,5Million rows in table pointValues, instead the table is around 40Million points, corresponding to an H2 database of 3.5GB corresponding also to a mysql pointValues table using 3.5GB.
To delete data before last 80 days (originally 5 months) I used the datasource purge for each used datasource. The purge from admin settings seems not to work on a big H2 database or in any case it tooks a very long time.

[etantonio]

I'm searching for the reason why my H2 database is growing so fast,
I think that this "Logging TYpe = All Data" could be an error:

but, looking at the data, it seems that it takes a new value each minute

according to Data Source logging

so, it seems, it is not a problem, is it right?

[phildunlap]

If you are not using the NoSQL database, yes pointvalues are a contributing factor to the database expansion (and in some people's cases probably the only factor). But, the issue is here: https://github.com/infiniteautomation/ma-core-public/issues/1344

The likelihood is that sometime (probably sooner than later, maybe 3.6?) we will have a DB upgrade that does the necessary side work to upgrade an PageStore H2 database created in 196 to 197, as referenced in the issues referenced in that issue. In version 197 of H2 the problem of saving a LOB not reusing space has been resolved.

But, if something like your pointValues pointValueAnnotations or events tables are just very full, the next version of the database may not help in your case.

[etantonio]

This post is deleted!

[etantonio]

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

[phildunlap]

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.

[etantonio]

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

[phildunlap]

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.

[etantonio]

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:

1. 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?

2. 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

[phildunlap]

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.