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.

Radix IoT Website Mango 3 Documentation Website Mango 4 Documentation Website

  • Hy,
    I've a very big H2 database, about 4GB, nw finally I can delete data older than 2 month, I tried to purge from purge settings
    0_1537903348085_Purgesettings.PNG

    no result after "Purge now using purge settings".
    So I delete data older than 2 month on each datasource, and this seems to be ok but db size is not changed after such deletes.
    Where Is my error?
    Thanks,
    Antonio


  • Hi Antonio,

    Purging point values will only clean up tables in the H2 if you are not using NoSQL. But, merely deleting from tables in H2 is insufficient to get it to shrink itself. There is some amount of 'compaction' that it does when Mango shuts down, but in my experience the real way to shrink your H2 database is to,

    1. Use the SQL backup section of the system settings to create a core-database-H2-date.zip file in your Mango/backup (or as configured) directory.
    2. Stop Mango
    3. Move your existing Mango/databases/mah2.h2.db file elsewhere for safe keeping
    4. Start Mango
    5. Use the SQL backup section of the system settings section to restore the backup from part 1

    You may need to restart Mango again after that if you notice your data sources are not polling.

    I would definitely expect your database to shrink significantly by going through this backup / restore process.


  • at the moment there are sql errors during database startup after restoring, and in any case the size is always more than 2.5GByte for 2 mounths data.
    I've to delete audit? How?
    We can't start with a new empty database


  • You should share the SQL errors if you are going to refer to them. It is not supported to restore an older Mango database version into a newer Mango. To do that you should restore the database first at the command line, then start Mango, which will work even if the backup is from an older version. I have provided instructions on doing that here: https://forum.infiniteautomation.com/topic/2748/how-to-restore-a-database-backup

    I've to delete audit? How?

    Where is that coming from? You can use the Mango/bin/h2-web-console.sh or .bat script to get a direct connection to the database if you need to change anything there directly, but you shouldn't need to.

    We can't start with a new empty database

    As in, "It is not acceptable" rather than unable to, right? Were you using the NoSQL module? Do you have a Mango/databases/mangoTSDB directory?


  • After restore of Database the size is still 2.5GByte starting from 3GByte,
    in any case after restore I choose to restart Mango, this is the error:

    0_1538418882904_ErBOtto.PNG

    Seems that Events table is no more available.
    There's no way to restore this database, this is fourth attempt failed.


  • Hmm. That's definitely why I always say,

    Move your existing Mango/databases/mah2.h2.db file elsewhere for safe keeping

    You should be able to start up on that database. If your events table is missing after a restore, it would strike that either the backup or restore process was not allowed to complete. You could purge your events and userEvents tables if they're a source of some of the size or slowdown using the 'Purge all events' button in the screenshot of purge settings you posted.


  • I don't know where is the problem, really often I purge all events, expecially before a backup. In any case I still turn back to original database becoming each day bigger.


  • You could try converting this instance over to MySQL. There was an issue with H2 databases growing that didn't get a true resolution until the 3.4 series with the persistent publisher, for instance. MySQL does not have the same issue with binary data sections not getting reused or freed when updated.


  • I'm trying to convert it to mysql,
    so I've installed MySQL 8, created a schema named "mango"

    then in my env.properties I've now:

    db.type=mysql
    db.url=jdbc:mysql://localhost/mango
    db.username=solergy
    db.password=secret
    db.mysqldump=
    db.mysql=

    convert.db.type=h2
    convert.db.url=jdbc:h2:${ma.home}/databases/mah2
    convert.db.username=
    convert.db.password=

    but the result is that:

    INFO 2018-10-03 18:22:00,037 (com.serotonin.m2m2.db.BasePooledProxy.initializeI
    mpl:36) - Initializing pooled connection manager
    FATAL 2018-10-03 18:22:00,792 (com.serotonin.m2m2.db.DatabaseProxy.initialize:19
    0) - Unable to connect to database of type MYSQL
    org.springframework.jdbc.CannotGetJdbcConnectionException: Could not get JDBC Co
    nnection; nested exception is org.apache.commons.dbcp.SQLNestedException: Cannot
    create PoolableConnectionFactory (Could not create connection to database serve
    r.)
    at org.springframework.jdbc.datasource.DataSourceUtils.getConnection(Dat
    aSourceUtils.java:80)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:
    615)

    Thanks for your help
    Antonio


  • I would it'd be the db.url that would be going wrong. Two things could be at issue,

    1. It's not interpreting the absence of port as the default port, resolve this by explicitly adding a :3306 like jdbc:mysql://localhost:3306/mango
    2. localhost is not resolving to an address that comports with the bind address in your MySQL configuration (like ::1 if it's not enabling IPv6). Resolve this by adjusting the bind address in the MySQL configuration, or by trying the explicit local address instead of the hostname, like jdbc:mysql://127.0.0.1:3306/mango

    But a lot of error output is missing in your post, such as the 'caused by' section in the stack track. It could be that you do not have the users credentials or privileges correct. Consult this thread for instructions on creating the MySQL database, a user, and granting privileges on that database to that user: https://forum.infiniteautomation.com/topic/3559/convert-from-h2-to-mysql-database-guidelines


  • according to your suggestion now the env.properties is the following:

    db.type=mysql
    db.url=jdbc:mysql://127.0.0.1:3306/mango
    db.username=solergy
    db.password=secret
    db.mysqldump=
    db.mysql=

    convert.db.type=h2
    convert.db.url=jdbc:h2:${ma.home}/databases/mah2
    convert.db.username=
    convert.db.password=

    the mango database for user solergy is created, granted and up and running:

    0_1538642627946_MangoDatabase.JPG

    0_1538643566987_MySQL Running.JPG

    but still mango doesn't start:

    INFO  2018-10-04 10:27:16,771 (com.serotonin.m2m2.db.BasePooledProxy.initializeI
    mpl:36) - Initializing pooled connection manager
    FATAL 2018-10-04 10:27:18,016 (com.serotonin.m2m2.db.DatabaseProxy.initialize:19
    0) - Unable to connect to database of type MYSQL
    org.springframework.jdbc.CannotGetJdbcConnectionException: Could not get JDBC Co
    nnection; nested exception is org.apache.commons.dbcp.SQLNestedException: Cannot
     create PoolableConnectionFactory (Could not create connection to database serve
    r.)
            at org.springframework.jdbc.datasource.DataSourceUtils.getConnection(Dat
    aSourceUtils.java:80)
            at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:
    615)
            at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:68
    0)
            at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:71
    2)
            at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:72
    2)
            at com.serotonin.db.spring.ExtendedJdbcTemplate.queryForObject(ExtendedJ
    dbcTemplate.java:39)
            at com.serotonin.db.spring.ExtendedJdbcTemplate.queryForObject(ExtendedJ
    dbcTemplate.java:63)
            at com.serotonin.db.DaoUtils.queryForObject(DaoUtils.java:336)
            at com.serotonin.m2m2.db.dao.SystemSettingsDao.getValue(SystemSettingsDa
    o.java:158)
            at com.serotonin.m2m2.db.dao.SystemSettingsDao.getIntValue(SystemSetting
    sDao.java:178)
            at com.serotonin.m2m2.db.upgrade.DBUpgrade.checkUpgrade(DBUpgrade.java:5
    1)
            at com.serotonin.m2m2.db.upgrade.DBUpgrade.checkUpgrade(DBUpgrade.java:3
    4)
            at com.serotonin.m2m2.db.DatabaseProxy.initialize(DatabaseProxy.java:180
    )
            at com.serotonin.m2m2.Lifecycle.databaseInitialize(Lifecycle.java:609)
            at com.serotonin.m2m2.Lifecycle.initialize(Lifecycle.java:287)
            at com.serotonin.m2m2.Main.main(Main.java:115)
    Caused by: org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableCon
    nectionFactory (Could not create connection to database server.)
            at org.apache.commons.dbcp.BasicDataSource.createPoolableConnectionFacto
    ry(BasicDataSource.java:1549)
            at org.apache.commons.dbcp.BasicDataSource.createDataSource(BasicDataSou
    rce.java:1388)
            at org.apache.commons.dbcp.BasicDataSource.getConnection(BasicDataSource
    .java:1044)
            at org.springframework.jdbc.datasource.DataSourceUtils.doGetConnection(D
    ataSourceUtils.java:111)
            at org.springframework.jdbc.datasource.DataSourceUtils.getConnection(Dat
    aSourceUtils.java:77)
            ... 15 more
    Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException:
     Could not create connection to database server.
            at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    
            at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
    
            at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Sou
    rce)
            at java.lang.reflect.Constructor.newInstance(Unknown Source)
            at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
            at com.mysql.jdbc.Util.getInstance(Util.java:386)
            at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1015)
            at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:989)
            at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:975)
            at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:920)
            at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2
    568)
            at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2304)
            at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:834)
            at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:47)
            at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    
            at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
    
            at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Sou
    rce)
            at java.lang.reflect.Constructor.newInstance(Unknown Source)
            at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
            at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:416)
            at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java
    :346)
            at org.apache.commons.dbcp.DriverConnectionFactory.createConnection(Driv
    erConnectionFactory.java:38)
            at org.apache.commons.dbcp.PoolableConnectionFactory.makeObject(Poolable
    ConnectionFactory.java:582)
            at org.apache.commons.dbcp.BasicDataSource.validateConnectionFactory(Bas
    icDataSource.java:1556)
            at org.apache.commons.dbcp.BasicDataSource.createPoolableConnectionFacto
    ry(BasicDataSource.java:1545)
            ... 19 more
    Caused by: java.lang.NullPointerException
            at com.mysql.jdbc.ConnectionImpl.getServerCharacterEncoding(ConnectionIm
    pl.java:3279)
            at com.mysql.jdbc.MysqlIO.sendConnectionAttributes(MysqlIO.java:1940)
            at com.mysql.jdbc.MysqlIO.proceedHandshakeWithPluggableAuthentication(My
    sqlIO.java:1866)
            at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1252)
            at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2486)
            at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2
    519)
            ... 33 more
    ERROR 2018-10-04 10:27:18,400 (com.serotonin.m2m2.Main.main:119) - Error during
    initialization
    org.springframework.jdbc.CannotGetJdbcConnectionException: Could not get JDBC Co
    nnection; nested exception is org.apache.commons.dbcp.SQLNestedException: Cannot
     create PoolableConnectionFactory (Could not create connection to database serve
    r.)
            at org.springframework.jdbc.datasource.DataSourceUtils.getConnection(Dat
    aSourceUtils.java:80)
            at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:
    615)
            at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:68
    0)
            at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:71
    2)
            at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:72
    2)
            at com.serotonin.db.spring.ExtendedJdbcTemplate.queryForObject(ExtendedJ
    dbcTemplate.java:39)
            at com.serotonin.db.spring.ExtendedJdbcTemplate.queryForObject(ExtendedJ
    dbcTemplate.java:63)
            at com.serotonin.db.DaoUtils.queryForObject(DaoUtils.java:336)
            at com.serotonin.m2m2.db.dao.SystemSettingsDao.getValue(SystemSettingsDa
    o.java:158)
            at com.serotonin.m2m2.db.dao.SystemSettingsDao.getIntValue(SystemSetting
    sDao.java:178)
            at com.serotonin.m2m2.db.upgrade.DBUpgrade.checkUpgrade(DBUpgrade.java:5
    1)
            at com.serotonin.m2m2.db.upgrade.DBUpgrade.checkUpgrade(DBUpgrade.java:3
    4)
            at com.serotonin.m2m2.db.DatabaseProxy.initialize(DatabaseProxy.java:180
    )
            at com.serotonin.m2m2.Lifecycle.databaseInitialize(Lifecycle.java:609)
            at com.serotonin.m2m2.Lifecycle.initialize(Lifecycle.java:287)
            at com.serotonin.m2m2.Main.main(Main.java:115)
    Caused by: org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableCon
    nectionFactory (Could not create connection to database server.)
            at org.apache.commons.dbcp.BasicDataSource.createPoolableConnectionFacto
    ry(BasicDataSource.java:1549)
            at org.apache.commons.dbcp.BasicDataSource.createDataSource(BasicDataSou
    rce.java:1388)
            at org.apache.commons.dbcp.BasicDataSource.getConnection(BasicDataSource
    .java:1044)
            at org.springframework.jdbc.datasource.DataSourceUtils.doGetConnection(D
    ataSourceUtils.java:111)
            at org.springframework.jdbc.datasource.DataSourceUtils.getConnection(Dat
    aSourceUtils.java:77)
            ... 15 more
    Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException:
     Could not create connection to database server.
            at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    
            at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
    
            at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Sou
    rce)
            at java.lang.reflect.Constructor.newInstance(Unknown Source)
            at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
            at com.mysql.jdbc.Util.getInstance(Util.java:386)
            at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1015)
            at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:989)
            at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:975)
            at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:920)
            at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2
    568)
            at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2304)
            at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:834)
            at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:47)
            at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    
            at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
    
            at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Sou
    rce)
            at java.lang.reflect.Constructor.newInstance(Unknown Source)
            at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
            at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:416)
            at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java
    :346)
            at org.apache.commons.dbcp.DriverConnectionFactory.createConnection(Driv
    erConnectionFactory.java:38)
            at org.apache.commons.dbcp.PoolableConnectionFactory.makeObject(Poolable
    ConnectionFactory.java:582)
            at org.apache.commons.dbcp.BasicDataSource.validateConnectionFactory(Bas
    icDataSource.java:1556)
            at org.apache.commons.dbcp.BasicDataSource.createPoolableConnectionFacto
    ry(BasicDataSource.java:1545)
            ... 19 more
    Caused by: java.lang.NullPointerException
            at com.mysql.jdbc.ConnectionImpl.getServerCharacterEncoding(ConnectionIm
    pl.java:3279)
            at com.mysql.jdbc.MysqlIO.sendConnectionAttributes(MysqlIO.java:1940)
            at com.mysql.jdbc.MysqlIO.proceedHandshakeWithPluggableAuthentication(My
    sqlIO.java:1866)
            at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1252)
            at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2486)
            at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2
    519)
            ... 33 more
    INFO  2018-10-04 10:27:18,448 (com.serotonin.m2m2.Lifecycle.terminate:353) - Man
    go Lifecycle terminating...
    Exception in thread "high-pool-1-thread-1" java.lang.NullPointerException
            at com.serotonin.m2m2.CoreLicenseDefinition$2.run(CoreLicenseDefinition.
    java:149)
            at com.serotonin.timer.sync.Synchronizer$TaskWrapper.run(Synchronizer.ja
    va:155)
            at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
            at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
            at java.lang.Thread.run(Unknown Source)
    INFO  2018-10-04 10:27:18,625 (com.serotonin.m2m2.db.BasePooledProxy.terminateIm
    pl:110) - Stopping database
    INFO  2018-10-04 10:27:18,634 (com.serotonin.m2m2.Lifecycle.terminate:453) - Man
    go Lifecycle terminated.
    

  • Did you perchance change the db.stream property to true ?

    I don't believe we've done much testing against MySQL 8 (thanks for posting the version information!), so it's possible there are issues related to that. You could try and older version and see if that works out of the box. I know 5.5 has been used a great deal.


  • Hy,
    db.stream is unchanged, this is remaining properties for db on env.properties:

    db.forceUseIndex=true
    db.fetchSize=-1
    db.stream=false

    maybe I've to choose a certain character encoding in mysql?

    Caused by: java.lang.NullPointerException
    at com.mysql.jdbc.ConnectionImpl.getServerCharacterEncoding(ConnectionIm
    pl.java:3279)


  • No, I wouldn't think so. It's never come up in the past, but I think few people were using the MySQL server version released last April and they may be new issues. I did search the error a little and find something in the code that may have something to do with it, but I do intend to look into this more and try to run on MySQL 8.


  • I installed MySQL server 8 on my Windows machine and found I could convert an H2 database without issue. I also tried telling Java that it was in Italy (in case locale was affecting the getServerCharacterEncoding method) but it still converted without issue.

    maybe I've to choose a certain character encoding in mysql?

    This is done for you in the MySQLProxy class, it modifies the db.url for UTF-8:
    https://github.com/infiniteautomation/ma-core-public/blob/main/Core/src/com/serotonin/m2m2/db/MySQLProxy.java#L28

    A suggestion I saw is to try the latest JDBC. This is a 2.8 system? I would guess you have a very outdated Mango/lib/mysql-connector-java-x.y.z.jar file. You could try the version bundled with the latest Mango 3, or you could download the latest MySQL jdbc jar and replace the outdated with the new. You can download the latest here: https://dev.mysql.com/downloads/connector/j/5.1.html


  • Ok,
    after driver change, installation is started but after a long while during migration I had this error,
    can I do something to avoid it and continue migration?

    Using Java at java
    INFO  2018-10-05 08:57:59,936 (com.serotonin.m2m2.Main.main:82) - Starting Mango
     2.8.4 with Schema Version: 13
    INFO  2018-10-05 08:58:01,596 (com.serotonin.m2m2.Main.loadModules:416) - Loadin
    g module 'asciiFile', v1.3.0 by Infinite Automation
    INFO  2018-10-05 08:58:01,656 (com.serotonin.m2m2.Main.loadModules:416) - Loadin
    g module 'BACnet', v2.3.0 by Infinite Automation Software
    INFO  2018-10-05 08:58:01,666 (com.serotonin.m2m2.Main.loadModules:416) - Loadin
    g module 'controlcore', v1.2.0 by Infinite Automation
    INFO  2018-10-05 08:58:01,676 (com.serotonin.m2m2.Main.loadModules:416) - Loadin
    g module 'dashboards', v3.3.0 by Infinite Automation Systems Inc.
    INFO  2018-10-05 08:58:01,826 (com.serotonin.m2m2.Main.loadModules:416) - Loadin
    g module 'dataFile', v1.2.0 by Infinite Automation
    INFO  2018-10-05 08:58:01,846 (com.serotonin.m2m2.Main.loadModules:416) - Loadin
    g module 'dataImport', v1.5.0 by Infinite Automation Software
    INFO  2018-10-05 08:58:01,856 (com.serotonin.m2m2.Main.loadModules:416) - Loadin
    g module 'dataPointDetailsView', v1.1.0 by Infinite Automation
    INFO  2018-10-05 08:58:01,866 (com.serotonin.m2m2.Main.loadModules:416) - Loadin
    g module 'dnp3', v1.7.0 by Infinite Automation Software
    INFO  2018-10-05 08:58:01,876 (com.serotonin.m2m2.Main.loadModules:416) - Loadin
    g module 'egauge', v1.2.0 by Infinite Automation
    INFO  2018-10-05 08:58:01,886 (com.serotonin.m2m2.Main.loadModules:416) - Loadin
    g module 'excelReports', v1.1.1 by Infinite Automation Software
    INFO  2018-10-05 08:58:02,006 (com.serotonin.m2m2.Main.loadModules:416) - Loadin
    g module 'graphicalViews', v1.7.0 by Infinite Automation Software
    INFO  2018-10-05 08:58:02,316 (com.serotonin.m2m2.Main.loadModules:416) - Loadin
    g module 'http', v1.6.0 by Infinite Automation Software
    INFO  2018-10-05 08:58:02,366 (com.serotonin.m2m2.Main.loadModules:416) - Loadin
    g module 'internal', v1.6.1 by Infinite Automation Software
    INFO  2018-10-05 08:58:02,386 (com.serotonin.m2m2.Main.loadModules:416) - Loadin
    g module 'log4jDS', v1.2.0 by Infinite Automation Software
    INFO  2018-10-05 08:58:02,396 (com.serotonin.m2m2.Main.loadModules:416) - Loadin
    g module 'log4JReset', v1.2.0 by Infinite Automation Software
    INFO  2018-10-05 08:58:02,406 (com.serotonin.m2m2.Main.loadModules:416) - Loadin
    g module 'loggingConsole', v1.2.0 by Infinite Automation
    INFO  2018-10-05 08:58:02,426 (com.serotonin.m2m2.Main.loadModules:416) - Loadin
    g module 'maintenanceEvents', v1.6.0 by Infinite Automation Software
    INFO  2018-10-05 08:58:02,436 (com.serotonin.m2m2.Main.loadModules:416) - Loadin
    g module 'mangoApi', v1.2.0 by Infinite Automation
    INFO  2018-10-05 08:58:02,446 (com.serotonin.m2m2.Main.loadModules:416) - Loadin
    g module 'mbus', v1.2.0 by Infinite Automation Software
    INFO  2018-10-05 08:58:02,456 (com.serotonin.m2m2.Main.loadModules:416) - Loadin
    g module 'meta', v2.3.0 by Infinite Automation Software
    INFO  2018-10-05 08:58:02,476 (com.serotonin.m2m2.Main.loadModules:416) - Loadin
    g module 'modbus', v1.7.0 by Infinite Automation Software
    INFO  2018-10-05 08:58:02,536 (com.serotonin.m2m2.Main.loadModules:416) - Loadin
    g module 'onewire', v1.6.0 by Infinite Automation Software
    INFO  2018-10-05 08:58:02,606 (com.serotonin.m2m2.Main.loadModules:416) - Loadin
    g module 'opcda', v1.7.0 by Infinite Automation Software
    INFO  2018-10-05 08:58:02,616 (com.serotonin.m2m2.Main.loadModules:416) - Loadin
    g module 'pakbus', v1.1.0 by Infinite Automation Software
    INFO  2018-10-05 08:58:02,676 (com.serotonin.m2m2.Main.loadModules:416) - Loadin
    g module 'persistent', v1.7.0 by Infinite Automation Software
    INFO  2018-10-05 08:58:02,706 (com.serotonin.m2m2.Main.loadModules:416) - Loadin
    g module 'pointLinks', v1.6.0 by Infinite Automation Software
    INFO  2018-10-05 08:58:02,726 (com.serotonin.m2m2.Main.loadModules:416) - Loadin
    g module 'pop3', v1.6.0 by Infinite Automation Software
    INFO  2018-10-05 08:58:02,736 (com.serotonin.m2m2.Main.loadModules:416) - Loadin
    g module 'reports', v2.3.0 by Infinite Automation Software
    INFO  2018-10-05 08:58:02,796 (com.serotonin.m2m2.Main.loadModules:416) - Loadin
    g module 'scheduledEvents', v1.6.0 by Infinite Automation
    INFO  2018-10-05 08:58:02,886 (com.serotonin.m2m2.Main.loadModules:416) - Loadin
    g module 'scripting', v1.2.0 by Infinite Automation Software
    INFO  2018-10-05 08:58:02,906 (com.serotonin.m2m2.Main.loadModules:416) - Loadin
    g module 'serial', v1.3.0 by Infinite Automation
    INFO  2018-10-05 08:58:02,926 (com.serotonin.m2m2.Main.loadModules:416) - Loadin
    g module 'snmp', v1.6.0 by Infinite Automation Software
    INFO  2018-10-05 08:58:02,966 (com.serotonin.m2m2.Main.loadModules:416) - Loadin
    g module 'sqlConsole', v1.6.0 by Infinite Automation Software
    INFO  2018-10-05 08:58:02,976 (com.serotonin.m2m2.Main.loadModules:416) - Loadin
    g module 'sqlds', v1.7.0 by Infinite Automation Software
    INFO  2018-10-05 08:58:02,996 (com.serotonin.m2m2.Main.loadModules:416) - Loadin
    g module 'ssh', v1.2.0 by Infinite Automation
    INFO  2018-10-05 08:58:03,006 (com.serotonin.m2m2.Main.loadModules:416) - Loadin
    g module 'sstGlobalScripts', v1.6.0 by Infinite Automation Software
    INFO  2018-10-05 08:58:03,016 (com.serotonin.m2m2.Main.loadModules:416) - Loadin
    g module 'sstGraphics', v1.2.0 by Infinite Automation Software
    INFO  2018-10-05 08:58:03,056 (com.serotonin.m2m2.Main.loadModules:416) - Loadin
    g module 'sstTheme', v1.2.0 by Infinite Automation Software
    INFO  2018-10-05 08:58:03,066 (com.serotonin.m2m2.Main.loadModules:416) - Loadin
    g module 'TCPIP', v1.2.0 by Infinite Automation
    INFO  2018-10-05 08:58:03,076 (com.serotonin.m2m2.Main.loadModules:416) - Loadin
    g module 'templateConfig', v1.1.0 by Infinite Automation Software
    INFO  2018-10-05 08:58:03,106 (com.serotonin.m2m2.Main.loadModules:416) - Loadin
    g module 'twilio', v1.2.0 by Infinite Automation
    INFO  2018-10-05 08:58:03,116 (com.serotonin.m2m2.Main.loadModules:416) - Loadin
    g module 'virtualDS', v1.6.0 by Infinite Automation Software
    INFO  2018-10-05 08:58:03,126 (com.serotonin.m2m2.Main.loadModules:416) - Loadin
    g module 'vmstat', v1.6.0 by Infinite Automation Software
    INFO  2018-10-05 08:58:03,136 (com.serotonin.m2m2.Main.loadModules:416) - Loadin
    g module 'watchlists', v1.8.0 by Infinite Automation Software
    WARN  2018-10-05 08:58:05,118 (com.serotonin.m2m2.Main.loadModules:623) - Unused
     classes entry: com.infiniteautomation.mango.pakbus.dwr.PakBusEditDwr
    INFO  2018-10-05 08:58:05,368 (com.serotonin.m2m2.Lifecycle.initialize:216) - In
    itializing Spring Object Mapper
    INFO  2018-10-05 08:58:08,428 (com.serotonin.m2m2.Lifecycle.loadLic:471) - Check
    ing license...
    INFO  2018-10-05 08:58:09,298 (com.serotonin.m2m2.db.BasePooledProxy.initializeI
    mpl:36) - Initializing pooled connection manager
    Fri Oct 05 08:58:10 CEST 2018 WARN: Establishing SSL connection without server's
     identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ a
    nd 5.7.6+ requirements SSL connection must be established by default if explicit
     option isn't set. For compliance with existing applications not using SSL the v
    erifyServerCertificate property is set to 'false'. You need either to explicitly
     disable SSL by setting useSSL=false, or set useSSL=true and provide truststore
    for server certificate verification.
    Fri Oct 05 08:58:11 CEST 2018 WARN: Establishing SSL connection without server's
     identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ a
    nd 5.7.6+ requirements SSL connection must be established by default if explicit
     option isn't set. For compliance with existing applications not using SSL the v
    erifyServerCertificate property is set to 'false'. You need either to explicitly
     disable SSL by setting useSSL=false, or set useSSL=true and provide truststore
    for server certificate verification.
    INFO  2018-10-05 09:02:03,183 (com.serotonin.m2m2.db.H2Proxy.initializeImpl:42)
    - Initializing H2 connection manager
    WARN  2018-10-05 09:02:03,718 (com.serotonin.m2m2.db.DBConvert.execute:41) - Run
    ning database conversion from H2 to MYSQL
    WARN  2018-10-05 09:02:08,243 (com.serotonin.m2m2.db.DBConvert.copyTable:87) -
    --> Converting table systemSettings...
    WARN  2018-10-05 09:02:08,843 (com.serotonin.m2m2.db.DBConvert.copyTable:136) -
     --> Finished converting table systemSettings. 78 records copied.
    WARN  2018-10-05 09:02:08,844 (com.serotonin.m2m2.db.DBConvert.copyTable:87) -
    --> Converting table users...
    WARN  2018-10-05 09:02:09,074 (com.serotonin.m2m2.db.DBConvert.copyTable:136) -
     --> Finished converting table users. 5 records copied.
    WARN  2018-10-05 09:02:09,080 (com.serotonin.m2m2.db.DBConvert.copyTable:87) -
    --> Converting table userComments...
    WARN  2018-10-05 09:02:09,089 (com.serotonin.m2m2.db.DBConvert.copyTable:136) -
     --> Finished converting table userComments. 0 records copied.
    WARN  2018-10-05 09:02:09,095 (com.serotonin.m2m2.db.DBConvert.copyTable:87) -
    --> Converting table mailingLists...
    WARN  2018-10-05 09:02:09,427 (com.serotonin.m2m2.db.DBConvert.copyTable:136) -
     --> Finished converting table mailingLists. 1 records copied.
    WARN  2018-10-05 09:02:09,434 (com.serotonin.m2m2.db.DBConvert.copyTable:87) -
    --> Converting table mailingListInactive...
    WARN  2018-10-05 09:02:09,442 (com.serotonin.m2m2.db.DBConvert.copyTable:136) -
     --> Finished converting table mailingListInactive. 0 records copied.
    WARN  2018-10-05 09:02:09,445 (com.serotonin.m2m2.db.DBConvert.copyTable:87) -
    --> Converting table mailingListMembers...
    WARN  2018-10-05 09:02:09,770 (com.serotonin.m2m2.db.DBConvert.copyTable:136) -
     --> Finished converting table mailingListMembers. 1 records copied.
    WARN  2018-10-05 09:02:09,776 (com.serotonin.m2m2.db.DBConvert.copyTable:87) -
    --> Converting table templates...
    WARN  2018-10-05 09:02:10,642 (com.serotonin.m2m2.db.DBConvert.copyTable:136) -
     --> Finished converting table templates. 4 records copied.
    WARN  2018-10-05 09:02:10,648 (com.serotonin.m2m2.db.DBConvert.copyTable:87) -
    --> Converting table dataSources...
    WARN  2018-10-05 09:02:11,113 (com.serotonin.m2m2.db.DBConvert.copyTable:136) -
     --> Finished converting table dataSources. 12 records copied.
    WARN  2018-10-05 09:02:11,117 (com.serotonin.m2m2.db.DBConvert.copyTable:87) -
    --> Converting table dataPoints...
    WARN  2018-10-05 09:02:38,963 (com.serotonin.m2m2.db.DBConvert.copyTable:136) -
     --> Finished converting table dataPoints. 201 records copied.
    WARN  2018-10-05 09:02:38,972 (com.serotonin.m2m2.db.DBConvert.copyTable:87) -
    --> Converting table pointValues...
    ERROR 2018-10-05 10:07:39,219 (com.serotonin.m2m2.Main.main:119) - Error during
    initialization
    com.serotonin.ShouldNeverHappenException: java.sql.SQLException: 'NaN' is not a
    valid numeric or approximate numeric value
            at com.serotonin.m2m2.db.DatabaseProxy.initialize(DatabaseProxy.java:134
    )
            at com.serotonin.m2m2.Lifecycle.databaseInitialize(Lifecycle.java:609)
            at com.serotonin.m2m2.Lifecycle.initialize(Lifecycle.java:287)
            at com.serotonin.m2m2.Main.main(Main.java:115)
    Caused by: java.sql.SQLException: 'NaN' is not a valid numeric or approximate nu
    meric value
            at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:965)
            at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:898)
            at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:887)
            at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:861)
            at com.mysql.jdbc.PreparedStatement.setDouble(PreparedStatement.java:330
    0)
            at com.mysql.jdbc.PreparedStatement.setNumericObject(PreparedStatement.j
    ava:3531)
            at com.mysql.jdbc.PreparedStatement.setObject(PreparedStatement.java:369
    5)
            at com.mysql.jdbc.JDBC42PreparedStatement.setObject(JDBC42PreparedStatem
    ent.java:99)
            at com.mysql.jdbc.PreparedStatement.setObject(PreparedStatement.java:362
    0)
            at com.mysql.jdbc.JDBC42PreparedStatement.setObject(JDBC42PreparedStatem
    ent.java:83)
            at org.apache.commons.dbcp.DelegatingPreparedStatement.setObject(Delegat
    ingPreparedStatement.java:166)
            at org.apache.commons.dbcp.DelegatingPreparedStatement.setObject(Delegat
    ingPreparedStatement.java:166)
            at com.serotonin.m2m2.db.DBConvert.copyTable(DBConvert.java:119)
            at com.serotonin.m2m2.db.DBConvert.execute(DBConvert.java:54)
            at com.serotonin.m2m2.db.DatabaseProxy.initialize(DatabaseProxy.java:131
    )
            ... 3 more
    INFO  2018-10-05 10:07:39,224 (com.serotonin.m2m2.Lifecycle.terminate:353) - Man
    go Lifecycle terminating...
    Exception in thread "high-pool-1-thread-1" java.lang.NullPointerException
            at com.serotonin.m2m2.CoreLicenseDefinition$2.run(CoreLicenseDefinition.
    java:149)
            at com.serotonin.timer.sync.Synchronizer$TaskWrapper.run(Synchronizer.ja
    va:155)
            at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
            at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
            at java.lang.Thread.run(Unknown Source)
    INFO  2018-10-05 10:07:39,279 (com.serotonin.m2m2.db.BasePooledProxy.terminateIm
    pl:110) - Stopping database
    INFO  2018-10-05 10:07:39,281 (com.serotonin.m2m2.Lifecycle.terminate:453) - Man
    go Lifecycle terminated.
    

    I think the problem could be that pointvalue is nullable

    0_1538733423521_PointValues.JPG

    you suggest me to clean values with an sql like
    "delete from pointvalues where pointvalue is null;"
    ?


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


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


  • 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:

    0_1540911037597_4c7aaf62-9c9e-42cb-b899-46a732b463d2-immagine.png

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

    0_1540911699158_fd273f3b-9581-4fd4-9d7d-c6dc71f626ca-immagine.png

    according to Data Source logging

    0_1540911832921_2c348e4c-937b-4992-a809-89c0df8ef98b-immagine.png

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


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