Migrate How to guide
From Swivel Knowledgebase Wiki
Contents |
Overview
Migrate allows user data to be copied from one database to another, including to and from the PINsafe internal database.
CAUTION: The data in target database will be overwritten. Ensure that the data is backed up.
Prerequisites
PINsafe 3.2 onwards
How to Migrate
Data Source
Ensure that the PINsafe system is working and there are no errors
Data Destination
Tables are created on the target database as part of the Migration process. The PINsafe database configuration needs to be set up on each PINsafe instance but the Migration is only required on one PINsafe instance. 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.
From the PINsafe 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.
Testing
Migrate the data and check the logs.
From the PINsafe 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
Duplicate entries in the MySQL database may cause migration to a PINsafe internal database to fail with the following error messages:
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'.
Ensure that the PINsafe 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.
Troubleshooting
Check the PINsafe logs
For database issues, refer to the relevant database guides.
