PINsafe upgrade fails on MySQL appliance

Overview

After upgrading the Swivel server the Swivel admin console is not accessible.

Prerequisites

Swivel upgrade from 3.x to a higher version

Swivel Appliance

Symptoms

Swivel administration console cannot be accessed

error messages relating to MySQL tables PINSAFEM, PINSAFEO, PINSAFEK may be displayed

The following error message may be displayed:

  type Exception report
  
  message
  
  description The server encountered an internal error () that prevented it from fulfilling this request.
  
  exception
  
  java.lang.NullPointerException
  com.swiveltechnologies.pinsafe.server.session.AbstractSessionManager.createFakeSession(AbstractSessionManager.java:60)
  com.swiveltechnologies.pinsafe.server.session.SessionQueue.createFakeSession(SessionQueue.java:39)
  com.swiveltechnologies.pinsafe.server.user.LocalAuth.sessionStart(LocalAuth.java:840)
  com.swiveltechnologies.pinsafe.server.ui.AdminLogin.doPost(AdminLogin.java:192)
  javax.servlet.http.HttpServlet.service(HttpServlet.java:709)
  javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
  com.swiveltechnologies.pinsafe.server.filter.AdminConsoleFilter.doFilter(AdminConsoleFilter.java:135)
 
  note The full stack trace of the root cause is available in the Apache Tomcat/5.5.20 logs.

The following error may be seen on the upgrade first access:

  Exception occurred during database access, exception:
 com.swiveltechnologies.pinsafe.server.user.database.DatabaseException: com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: ALTER command denied to user 'pinsafe'@'localhost' for table 'PINSAFEM'

subsequent errors may show the following:

  com.swiveltechnologies.pinsafe.server.user.database.DatabaseException: com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: Table 'PINSAFEO' already exists

Verifying through Webmin

Login to Webmin and select Servers, MySQL Database server, click on the pinsafe_rep database, then th=e table PINSAFEK, tick Field name A then click on View Data and it will show the Swivel version.

Solution

Option 1: Reinstall the previous version of PINsafe.

Option 2: Edit the MySQL table to complete the upgrade.

Check that the user pinsafe has permissions set to all. Login to the Appliance webmin using https://IP:10000. Select Servers/MySQL Database, then from Global Options select User Permissions, if the pinsafe user does not have permissions All, then select the user pinsafe and then highlight all the permissions and click on Save. Check that the user pinsafe has permissions set to all.

Check to see if the pinsafe_rep database has not upgraded. Login to the Appliance webmin using https://IP:10000. Select Servers/MySQL then from MySQL databases select pinsafe_rep. Select the table PINSAFEK and then click on View Key. If the key has a value of the old PINsafe version (Example 3300 instead of 3500) then the database has not been upgraded correctly.

To complete the MySQL database upgrade carry out the following steps:

Login to the appliance through the command line

Enter MySQL command mode by typing mysql

then enter: use pinsafe_rep;

then enter (where A=New PINsafe version number): update PINSAFEK set A=3500;

(NOTE: check with Swivel what the correct version number should be for more recent versions, for Swivel 3.10.4 the version should be 31040).

To exit the MySQL command mode enter: exit;

Example:

 mysql
 Welcome to the MySQL monitor.  Commands end with ; or \g.
 Your MySQL connection id is 1661 to server version: 5.0.22-log
 
 Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
 
 mysql> use pinsafe_rep;
 Reading table information for completion of table and column names
 You can turn off this feature to get a quicker startup with -A
 
 Database changed
 mysql> update PINSAFEK set A=3500;
 Query OK, 1 row affected (0.00 sec)
 Rows matched: 1  Changed: 1  Warnings: 0
 
 mysql> exit;
 Bye

Check to see if the pinsafe_rep database upgraded. Login to the Appliance webmin using https://IP:10000. Select Servers/MySQL then from MySQL databases select pinsafe_rep. Select the table PINSAFEK and then click on View Key. If the key has a value of the new Swivel version then the database has been upgraded correctly.