Database Clustering and Primary Key Retrofit

Knowledge Base Article: MES Database Clustering and Primary Key Retrofit

Note

Version Specific:

Use these steps when adding primary keys to versions before 3.81.12 SP0. After this version, these steps are done automatically.

Support has been added for database clustering by retrofitting and adding primary keys to all MES tables. This process is handled via system properties in the gateway configuration.

Download a PDF file:

Knowledge Base_ MES Database Clustering and Primary Key Retrofit.pdf

Configuration

To enable the automatic primary key retrofit and addition, you must modify the ignition.conf file:

  • Set the system property com.sepasoft.db.addPrimaryKeys=true in the gateway's ignition.conf.
  • Restart the Ignition gateway to initiate the process. 

Monitoring and Verification

  • Logs: The progress screen is no longer used; instead, everything is visible via the logs. You should check the logs for a correct progress report regarding the retrofit process.
  • Database: Once the upgrade completes, verify that all tables have received the expected primary key column. 

Important Limitations and Best Practices

  • Oracle Database Incompatibility: This property is unsupported on Oracle databases and enabling it will cause the system to crash.
  • System Performance: Enabling this system property will slow down MES system startup, even after the database changes are complete.
  • Cleanup: It is recommended to remove the system property from ignition.conf after the process completes to avoid startup delays.

Advanced Configuration for Testing

For testing purposes—specifically regarding database initialization and upgrades—a secondary property is available:

  • Property: -Dcom.sepasoft.db.disableAutoIdColumn=true
  • Function: This forbids adding primary key columns during table creation.

Testing Workflow: To test the upgrade process, you can utilize the following workflow:

  • Enable disableAutoIdColumn and comment out addPrimaryKeys in ignition.conf.
  • Restart Ignition and recreate the database (tables will be created empty and without primary key columns).
  • Enable addPrimaryKeys and restart Ignition again.
  • Observe the logs to watch the tables being upgraded.