Mango Service Not Running (Switching from H2 DB to MySQL)

[JAST]

I am using Mango v. 2.8.8
It was running on an H2 database which appears to be out of memory

Looking at other Forum Posts, I saw one proposed solution was to switch to a MySQl DB. I stopped the mango service, installed the MySQL server, went ahead and created a DB for Mango and updated my env.properties and commented out the H2 db settings. When I start the Mango service it seems to initialize the service, runs for 5 seconds, then stops. I noticed if I revert back to H2 it works again, however to prevent any more memory issues I've disabled my datasource.

In summary:

• What is meant by h2.jdbc Out of Memory? Are there any steps I can do to fix this first?
• Is there anything else other than updating the env.properties in order to use MySQL?
• Once running with MySQl how could I transfer my historical data from the H2 database to MySQL?

[phildunlap]

Hi gquiroz,

What is meant by h2.jdbc Out of Memory? Are there any steps I can do to fix this first?

The h2.jdbc part of that means that the java.lang.OutOfMemoryError was somewhere inside the H2 Java Database Connector's code, and that it was rethrown at that spot in the code.

You may be able to shrink your H2 database. Have you checked the size of the H2 database file at Mango/databases/mah2.h2.db? This thread gives two ways to backup/restore so as to shrink the H2 database: https://forum.infiniteautomation.com/topic/2748/how-to-restore-a-database-backup

Is there anything else other than updating the env.properties in order to use MySQL?

This thread gives example SQL for creating a database user and granting them privileges: https://forum.infiniteautomation.com/topic/3559/convert-from-h2-to-mysql-database-guidelines/7

Once running with MySQl how could I transfer my historical data from the H2 database to MySQL?

Are you using NoSQL? In which case, it does not need to be transferred since it is in the Mango/databases/mangoTSDB database. If not, then you would have converted it while doing the database conversion. So, no.

[JAST]

Hi phildunlap,

my mah2.h2.db file size is 443,876 KB

I've configured my env.properties file as shown in this thread: https://forum.infiniteautomation.com/topic/3559/convert-from-h2-to-mysql-database-guidelines/7

I see I am getting a couple of errors/exceptions:

• Unable to connect to database of type MYSQl
• Could not get JDBC connection
• Exception n thread "high-pool-1-thread-1"
  
  

is there some sort of overide I should place or remove any existing data sources along with datapoints?

[phildunlap]

Unable to connect to database of type MYSQl

1. Check that MySQL is running, pidof mysqld
2. Check the bind address in the /etc/mysql/my.cnf to see if it's something like 127.0.0.1 and so you need to use the explicit IPv4 in the connection string in the env.properties instead of localhost (which is maybe is resolving to ::1).
3. You say it is as that referenced thread is, so can you confirm that you've created the user and have put the port in the connection string? Did you change the names of the database you created at the MySQL command line, or did you use 'mango' as I did in that thread?

Did the cannot get JDBC connection errors provide any details? I believe the image you posted is of an error that's caused by trying to start without a DB.

[JAST]

I am a using windows 10 system

I found what the issue was. MySQL v 8 was not working due to a security setting. i downgraded to MySQL v 5 and it worked.
I am having trouble with the BACnet discovery. Not sure what is meant by Property: characterSetNotSupporte '29'
com.serotonin.bacnet4j.type.constructed.BACnetError cannot be cast to com.serotonin.bac4j.type.constructed.ServicesSupported

[phildunlap]

I found what the issue was. MySQL v 8 was not working due to a security setting. i downgraded to MySQL v 5 and it worked.

Do you know what the setting was?

I am having trouble with the BACnet discovery. Not sure what is meant by Property: characterSetNotSupporte '29'

I'm not sure either. There is an enumeration of supported encodings in the com.serotonin.bacnet4j.type.primitive.CharacterString class, none of which have that number. I would suspect that exception is getting thrown by the "validateEncoding" method of character string. I tried to do a little searching to see if the BACnet spec has a character encoding 29, which I'm not sure it does. This leads me to wonder if some other sort of configuration error could be going on. It is just doing an object list from a single device or are all devices behaving oddly?

Also, it's good practice to make a new topic if it's a new topic :)

[JAST]

Hi phildunlap, apparently MYSQL v8 came with a security update that prevents many third party applications from accessing the server.
Authentication plugin 'caching_sha2_password' cannot be loaded
I could not find a fix for this, other than just downgrading to an older version.
I will create a new post regarding the BACnet question: https://forum.infiniteautomation.com/topic/4042/adding-a-bacnet-router-as-a-foreign-device