Migrate How to guide

Overview

Migrate allows user data to be copied from one Swivel Data Store (database) to another, including to and from the Swivel internal data store, it is useful for copying data between different database types or even diferent versions.

CAUTION: The data in target database will be overwritten. Ensure that the data is backed up.

Prerequisites

Swivel 3.2 onwards.

Backup data and ensure a valid data backup set is available.

Source and Target serevrs should be set to use the same Timezone.

How to Migrate

The following options are available on the Swivel Administration console under Migration/Data

Target database: default: Appliance Database, options (version 3.9.4): Appliance database, JDBC, MS SQL Server, MySQL 5, Oracle 10g, PostgreSQL 8.2. This is where the data will be copied to.

Append to existing data: default: No, Options: Yes/No, allow the data to append to the target database or to overwrite it.

Enter 'MIGRATE': Enter the word MIGRATE to confirm that the data will be migrated. The target database will be overwritten, unless the Append option is used, where the data will be added to the target database.

Data Source

Ensure that the Swivel system is working and there are no errors in the logs.

Data Destination

Tables are created on the target database as part of the Migration process. The target Swivel database configuration needs to be set up on each swivel instance but the Migration is only required on one Swivel instance and in a replicated environment such as the Swivel MySQL appliances the data is replicated to the other instances. Follow the database guides for setting up the databases, the PINsafe configuration and upload any required drivers, see MySQL Database How To Guide, see MS SQL Database How To Guide, see Oracle Database How To Guide. The database should not be selected on the PINsafe Administration console/Database/General until after the Migration.

Once the database has been configured, to migrate the data follow the below steps. CAUTION: The data in target database will be overwritten unless the option to Append data is used from Swivel version 3.9.2 onwards.

From the Swivel Administration console select Migration/Data then select the database type and enter MIGRATE, click on apply. A message indicates the data has been successfully migrated.

Example: MySQL database Migration.

The logs will indicate the following messages, the below show the logs for a MySQL example:

Database at jdbc:mysql://localhost/pinsafe_rep

Database com.mysql.jdbc.Driver loaded successfully.

The length of time for Migration will vary between install sites, but 500 users will approximately take 2 minutes.

Length of Time for Migration

This will vary with the amount of data and number of attributes that each user has, but for a internal Swivel database to a MySQL Swivel appliance database, it will take roughly 2-3 minutes per thousand users. Allow the Sync to complete fully.

Testing

Migrate the data and check the logs.

From the Swivel Administration console select Database/General select the target database type then click apply. View user data and status page to ensure correct users and numbers of users have been migrated.

Known Issues

IMPORTANT: you MUST use the migrate option when changing between databases of different types. Trying to use SQL export scripts from one database type in a different type will almost certainly cause user credentials to become invalid. This is because Swivel uses the user creation time (among other things) to encrypt the credentials, and different databases store timestamps in different formats, to different accuracies. The migrate function recalculates the credential encryption for the target database. You can use export scripts (such as mysqldump) when moving a database to another server of the SAME type (except internal), although using Migrate is still the preferred option.

Troubleshooting

Check the PINsafe logs

For database issues, refer to the relevant database guides.

Duplicate Entries

PINsafe data migration failed! com.swiveltechnologies.pinsafe.server.user.database.DatabaseException: SQL Exception: The statement was aborted because it would have caused a duplicate key value in a unique or primary key constraint or unique index identified by 'PINSAFENB' defined on 'PINSAFEN'.

Duplicate entries in the MySQL database may cause migration to a swivel internal database to fail with the above error message:

Ensure that the Swivel data is backed up.

To view the duplicate entries, the following command can be run from within the mysql command line:

SELECT A, C, COUNT( D ) AS N FROM PINSAFEN GROUP BY A, C HAVING N > 1;

Example:

 mysql> SELECT A, C, COUNT( D ) AS N FROM PINSAFEN GROUP BY A, C HAVING N > 1;
 +------+----+---+
 | A    | C  | N |
 +------+----+---+
 |  151 | 14 | 2 |
 |  181 | 14 | 2 |
 | 3781 | 14 | 2 |
 | 4171 | 14 | 2 |
 | 4191 |  0 | 2 |
 | 4191 | 14 | 2 |
 | 4571 | 14 | 2 |
 | 5121 |  0 | 2 |
 | 5121 | 14 | 2 |
 | 5301 |  0 | 2 |
 | 5301 | 14 | 2 |
 | 5501 | 14 | 2 |
 | 5811 | 14 | 2 |
 | 5941 |  0 | 2 |
 +------+----+---+
 14 rows in set (0.01 sec)

The N column shows all the duplicate entries, A and N are values used elsewhere.

The duplicate entries must be removed. The simplest way to do this, is to delete the entries hen re-enter one entry

run the following command to find the existing date entry:

SELECT * FROM PINSAFEN WHERE A=<value A from table> AND C=<value C from table>

Example:

 SELECT * FROM PINSAFEN WHERE A=151 AND C=14;

Then delete the entry

DELETE FROM PINSAFEN WHERE A=<value A from table> AND C=<value C from table>;

Example:

 DELETE FROM PINSAFEN WHERE A=151 AND C=14;

Then insert the value using the date given above from the Select command

INSERT INTO PINSAFEN (A, C, D) VALUES (<value A from table>, <value C from table>, '<yyyy-mm hh:mm:ss>');

Example:

 INSERT INTO PINSAFEN (A, C, D) VALUES (151, 14, '2012-01 12:00:00');

If the date field is empty then use:

INSERT INTO PINSAFEN (A, C) VALUES (<value A from table>, <value C from table>);

Example:

 INSERT INTO PINSAFEN (A, C, D) VALUES (151, 14, '2012-01 12:00:00');

Run the count command again to see additional duplicates.

SELECT A, C, COUNT( D ) AS N FROM PINSAFEN GROUP BY A, C HAVING N > 1;

When all have been removed then try the MIGRATE command again.