Permissionshistory table issue

[Taufiq 57]

Affects all platforms for Wurm Unlimited builds 828552 and earlier, and Wurm Unlimited Dedicated Server builds 828553 and earlier.

 

Issue

The server is unable to add entries to the PERMISSIONSHISTORY table in wurmplayers.db. In the server logs, this can be seen with exceptions similar to the following:

WARNING: Failed to add permissions history for object (3540598449154)

java.sql.SQLException: [SQLITE_CONSTRAINT]  Abort due to constraint violation (NOT NULL constraint failed: PERMISSIONSHISTORY.PK)

        at org.sqlite.core.DB.newSQLException(DB.java:890)

        at org.sqlite.core.DB.newSQLException(DB.java:901)

        at org.sqlite.core.DB.execute(DB.java:810)

        at org.sqlite.core.DB.executeUpdate(DB.java:847)

        at org.sqlite.jdbc3.JDBC3PreparedStatement.executeUpdate(JDBC3PreparedStatement.java:86)

        at com.wurmonline.server.players.PermissionsHistories.dbAddHistoryEvent(PermissionsHistories.java:180)

        at com.wurmonline.server.players.PermissionsHistories.addHistoryEntry(PermissionsHistories.java:162)

        at com.wurmonline.server.questions.ManagePermissions.answer(ManagePermissions.java:507)

        at com.wurmonline.server.creatures.Communicator.reallyHandle(Communicator.java:6330)

        at com.wurmonline.communication.SocketConnection.tick(SocketConnection.java:615)

        at com.wurmonline.communication.SocketServer.tick(SocketServer.java:172)

        at com.wurmonline.server.Server.run(Server.java:2418)

        at java.util.TimerThread.mainLoop(Timer.java:555)

        at java.util.TimerThread.run(Timer.java:505)

Cause

This problem is due to the wrong data type being used as the primary key (INT instead of INTEGER), which means that keys are not auto-created.

 

Fix

Warning: The following steps will involve dropping and recreating a database table, and may result in loss of data. It is recommended that a backup be made before attempting to modify the database.

It is possible to recreate the permissionshistory table by dropping and recreating it with the correct data type (Sqlite is not able to change data types on existing tables).

1. Make a backup of the world directory (eg. Creative), found in steamapps\common\Wurm Unlimited for the client/server, or steamapps\common\Wurm Unlimited Dedicated Server for the dedicated server.
   
2. Create a file named patch-20151029-12af395a-permissionshistory.sql in the sqlite directory of the world directory (eg. Creative\sqlite), with the following contents:
   

   
   
   BEGIN TRANSACTION;
   
   CREATE TABLE PERMISSIONSHISTORY_12af395a
   
   (
   
       PK                      INTEGER       NOT NULL PRIMARY KEY,
   
       OBJECTID                BIGINT        NOT NULL,
   
       EVENTDATE               BIGINT        NOT NULL,
   
       PLAYERID                BIGINT        NOT NULL,
   
       PERFORMER               VARCHAR(40)   NOT NULL,
   
       EVENT                   VARCHAR(255)  NOT NULL
   
   );
   
   INSERT INTO PERMISSIONSHISTORY_12af395a SELECT PK,  OBJECTID,  EVENTDATE, PLAYERID, PERFORMER, EVENT FROM PERMISSIONSHISTORY;
   
   DROP TABLE PERMISSIONSHISTORY;
   
   ALTER TABLE PERMISSIONSHISTORY_12af395a RENAME TO PERMISSIONSHISTORY;
   
   COMMIT;
   

   
   

   Note that the BEGIN TRANSACTION; and COMMIT; lines will cause the update to fail if executed in an Sqlite browser that performs transactions automatically.

   
   

    

   
   
   
3. 
   

   Open a command or terminal window and navigate to the directory

   
   
   
4. 
   

   Execute the following to update the table:

   
   

   
   
   sqlite3 wurmplayers.db < patch-20151029-12af395a-permissionshistory.sql
   

   
   
   

Released Fix

Should be fixed in build Wurm Unlimited build 836088 (in the publicly available beta branch) and Wurm Unlimited Dedicated Server build 836095, though this will only fix new servers, not existing ones. We are looking at options for a system to implement database schema updates.

 

Edit: Wrong database specified in application instructions, corrected wurmlogin.db to wurmplayers.db. This will not cause issues apart from having a redundant table, but the fix will need to be reapplied.

 

Edit 2: Correction on which servers are fixed