Migration

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 different versions.

Warning

The data in the target database will be overwritten. Ensure that the data is backed up before proceeding.

Prerequisites

  • Swivel 3.2 onwards.

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

  • Source and Target servers 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: 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. Allows 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. 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:

  • MySQL Database How To Guide

  • MS SQL Database How To Guide

  • 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, follow the steps below to migrate the data.

Warning

The data in the target database will be overwritten unless the option to Append data is used (available from Swivel version 3.9.2 onwards).

  1. From the Swivel Administration console select Migration > Data.

  2. Select the database type.

  3. Enter MIGRATE in the text box.

  4. Click Apply.

A message will indicate if the data has been successfully migrated.

Example: MySQL database Migration

MySQL Migration Successful Message

The logs will indicate the following messages (the below example shows logs for a MySQL migration):

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 an 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.

  1. From the Swivel Administration console select Database > General.

  2. Select the target database type.

  3. Click Apply.

  4. View the 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

Error: 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.

Resolution:

  1. Ensure that the Swivel data is backed up.

  2. To view the duplicate entries, run the following command from the mysql command line:

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

Example Output:

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 C are values used elsewhere. The duplicate entries must be removed. The simplest way to do this is to delete the entries and then re-enter one entry.

  1. 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;

  1. 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;

  1. 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>);

  1. 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;

  1. When all have been removed, try the MIGRATE command again.