Conversion from version 1.13.0 to 1.13.2

[mlohbihler]

Version 1.13.2 of Mango has not yet been released, but some users have already expressed concern with regard to how long the database update may take. The update this time is particularly onerous, making some significant changes to the pointValues and pointValueAnnotations tables. If your instance uses MySQL and contains upwards of millions of records (a configuration that was specifically tested), you might expect a conversion time of hours.

If this downtime is unacceptable, below is an alternative conversion method. This procedure is intended for MySQL, but can be adapted for other databases. Note that it includes two restarts of Mango. During the uptime between restarts, the accumulated history will not be available.

1. Shut down Mango, and apply the 1.13.2 upgrade.

2. Access your MySQL database using the mysql console, or a tool like the MySQL Workbench, and execute the following statements.

rename table pointValues to pointValuesSave;
rename table pointValueAnnotations to pointValueAnnotationsSave;

create table pointValues (
  id bigint not null auto_increment,
  dataPointId int not null,
  dataType int not null,
  pointValue double,
  ts bigint not null,
  primary key (id)
) engine=MyISAM;
create index pointValuesTempIdx1 on pointValues (dataPointId, ts);

create table pointValueAnnotations (
  pointValueId bigint not null,
  textPointValueShort varchar(128),
  textPointValueLong longtext,
  sourceType smallint,
  sourceId int,
  primary key (pointValueId)
) engine=MyISAM;

update systemSettings set settingValue='1.13.1' where settingName='databaseSchemaVersion';

This will copy your pointValues tables to new names, and create proxies in their place. These statements should execute quickly.

3. Restart Mango. All history will appear to be gone, but new samples will be stored.

4. Run the following statements:

alter table pointValueAnnotationsSave drop foreign key pointValueAnnotationsFk1;
alter table pointValueAnnotationsSave ENGINE = MyISAM;
alter table pointValueAnnotationsSave add primary key (pointValueId);
alter table pointValuesSave drop foreign key pointValuesFk1;

drop index pointValuesIdx1 on pointValuesSave;
drop index pointValuesIdx2 on pointValuesSave;
alter table pointValuesSave ENGINE = MyISAM;
create index pointValuesIdx1 on pointValuesSave (dataPointId, ts);

These statements will convert the pointValues tables to MyISAM, remove FKs, and keep only necessary indices. They may take a very long time to run.

5. Shut down Mango, and execute the following statements:

insert into pointValuesSave (id,dataPointId,dataType,pointValue,ts) select id,dataPointId,dataType,pointValue,ts from pointValues;
insert into pointValueAnnotationsSave (pointValueId,textPointValueShort,textPointValueLong,sourceType,sourceId) select pointValueId,textPointValueShort,textPointValueLong,sourceType,sourceId from pointValueAnnotations;
drop table pointValues;
drop table pointValueAnnotations;

rename table pointValuesSave to pointValues;
rename table pointValueAnnotationsSave to pointValueAnnotations;

These statements will copy all of the newly collected data into the old tables, and then rename the tables back to their original names. These should run quickly, depending on how much new data has been collected into the proxy tables.

6. Restart Mango. The database upgrade is now complete. All history should again be available.

Note that application features that depend upon history data should be disabled, such as the PTCP publisher data sync.