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.
Import historical data from older mango version
-
I want to import all my historical data from my pld mange v1.12
this is important for me becaus i use mango for loggin my photovoltaik system and solar boiler.Is there a way to import the historical data ?
-
It depends on how many data points you want to convert. If it's just a few the easiest way would be to do it by hand using the csv export from the mango 1.12 point details page and then using the Data Import module in Mango Automation. Note that you will need to modify the format of the file to meet the new standard. http://infiniteautomation.com/wiki/doku.php?id=mango_features:dataimport
For large database historical migrations the only way to do this in one go is to use the NoSQL conversion tool: http://infiniteautomation.com/wiki/doku.php?id=system_admin:database_conversion#migrating_historical_data_to_the_nosql_database
But for this you would need to purchase an Enterprise license for Mango to use the NoSQL database
-
How do i make an export of the v1.12 data base with the historical data ?
it is about 43 datapoints. -
You should be able to export the data as CSV on the point details page in 1.12.
-
That will be a hard.
I migrated to the new mango because the old version is very slow, especially when i want to enter the point details page...
I pressed the bricklogo and now i am alrady 4 minuts waiting....... stil no point data.Can this be related to the size of the database ?
Will this be happeining eventualy at the new mango also ? -
How many point values are in the database? Is it MySQL or Derby? The other thing to check that can cause a slow interface is too many events in your events table.
In the SQL console you can run "select count() from events" and "select count() from pointValues" to find out.
If it's only 43 data points it's hard to believe that the pointValues table is too large. I have a feeling it will be your events table and you may need to manual purge it by "delete from events" which will delete all your events. If it's a MySQL database there are other ways to truncate a table or create a new table with the same structure but empty then rename the old one and rename the new one to takes it's place.
If it's that the pointValues table is too large for your server you could use the Mango Automation Enterprise demo and convert the database to the NoSQL database and then use the export tool on the Point Details page and then import it back into a system running on H2 or MySQL database. Note that the enterprise demo will only run for 8 hour and then will turn off.
-
I entered the sql syntax and get this for a result:
java.sql.SQLSyntaxErrorException: Syntax error: Encountered ")" at line 1, column 14.
it has to do with a java version, i encoutered this before, that was also a reason to migrate to the new version.
When i made some calculations with values from the database i got the same error.i dont know what database is used, the mangodb folder has 3 folders log,seg0 and temp
Where do i manualy purge the events ?
can i email you the mangodb folder so you can see for your self ?
-
We don't support version 1.12 any more. What is the total size of the mangodb folder. This would be a derby database. You could try converting it to MySQL so you can manage it better.
The syntax error you got might be to do with the right name of the table. Try "select count(*) from EVENTS" or use the view table list button to see how the names of the tables are presented. We don't use Derby databases anymore so it might be something else but you can look up the proper Derby syntax.
-
The mangodb folder is 3.5Gb.
I used you new syntax and it reurned 4400980 events.Now of course the next trick, how to convert to mysql ?
At the moment i am downloading the full version with the database migration tool in it.
And import the derby database and export to the nem mango database.what is the sql or derby syntax for deteleting the events manualy ?
-
While importing the historical data i encountered this message:
java.sql.SQLSyntaxErrorException: Column 'PVA.SOURCEMESSAGE' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'PVA.SOURCEMESSAGE' is not a column in the target table
the import stopped..
what is going wrong, this data base stuff is really abracadabra for me. -
I tried the database conversion method.
In the env.properties file i changed the database conversion paramaters.
While starting i saw th eupdate of the database taking place in the log screen, it where about 8 staps and it was finished.Mango started and i wanted to log in.
The log in failes, the admin was not regognized as a user.
User ID not found....How do i log in to this mango installation?
Is there a trick when the user admin is not found ? -
@sprokkie Yes as expected the events table is too large and is causing you performance issues. Assuming you don't need to keep any of the events you can try "delete FROM EVENTS" in the SQL Console. It might take quite a while.
If the database conversion to MySQL worked your admin user should be the same as before.
-
Ok new day new idea's :)
Made a copy of the mango 1.12 installation and installes that on a fresh virtualbox win7 machine.
Result is a much faster installation and now i can easily export the historical data from a datapoint in to a csv file.Now i have to import the csv file in the mango 2.5 any tips ?
The help file about importing on the website is not realy clear. -
finaly i got al the historic values in seprate csv file's
I found out that there is some kind of limit to import in one file.
The original amount of line's for the outside temperature in the excel file was 91000, i dived the file per year and that worked fine.
