Merge two Mango data bases

[moinmoin]

Hello Everyone,

in the past, when our Mango data base got too big, we dumped its contents and created a new data base for operation. This was a quick "fix" because the big data base caused a heap space exception in some jdbc function. This is now worked around by setting a higher maximum memory usage for the tomcat server.

The old data bases (dumps) still exist and hold valuable data though. The data bases are MySQL ones. The goal is to merge these into one big data base and use that one in the future (again with higher JVM memory). The Mango configuration did not change from data base to data base, so the layouts are the same. The problem lies with unique IDs for the measurements in the 'pointvalues' table. MySQL consequently issues a "duplicate entry for key 'Primary'" error.

So my actual question: What is the preferred way to merge Mango data bases? Is there any best practice for it?

Thanks and Best Regards,
Michael

[mlohbihler]

Hi Michael,

There are no set practices for merging Mango databases together. That said, we are moving the system (slowly) away from a dependence upon auto-increment keys, instead using the point id / timestamp as the key. In the meantime, there are two uses of the point value id:

1. matching with the annotation in the point value annotations table
2. matching with image file names (for the http image data source)

If you need help merging your databases, contact infinite automation for details on support.

[moinmoin]

Thanks for your comment Matthew.

A compound key, as you mentioned, would make things way easier merging-wise. Sadly, the paid support is no option due to lack of budget (sound familiar?).

For now I will have to do my analysis on the distributed old data bases and the running one.

In the meantime I would appreciate any hints or ideas for a solution.

Thanks and Best Regards,
Michael

[moinmoin]

Just thinking aloud whether the following could work:

Turn off auto-increment and delete the ids on a destination db pointvalues table. Then import the old db data. Delete the ID column from the destination db. Then re-create the ID column with auto-increment and primary key.

The thing that breaks of course are the pointvalues annotations (what is their use?) and the image file names.

Is this correct? I would greatly appreciate some advice/help.

Thanks,
Michael