Update 8 Dec 2017: Openfire 4.2.1 has been released, which should contain a fix that allows Openfire to correctly update its schema at first launch. The workarounds below should no longer be necessary. Please open new discussion threads with any issues you have.
We have noticed a number of users report login issues after upgrading to Openfire 4.2.0. Please review this message to see if you are hitting this bug.
OF-1254 Openfire uses a version value set within the
ofVersion table to track its database schema. A bug existed with the database upgrade scripts for version “25” that incorrectly left the version within the database at version “24”. Unfortunately, if your installation ran this database upgrade script, you got the
ofMucConvLog_msg_id index properly created, but the schema version was still stuck at 24. When Openfire starts, it would see your database schema at 24 and attempt to run this script again, creating the index would fail, but nothing would break as there were no subsequent schema upgrades to run.
So the issue now is that we do need schema update 26 to run, so some manual intervention is needed.
How to tell if I am affected by this bug?
If you review your Openfire logs, you should see something like this in your error.log and all.log files.
2017.12.05 12:45:20 INFO [main]: org.jivesoftware.database.SchemaManager - Found old database version 24 for openfire. Upgrading to version 26... 2017.12.05 12:45:20 ERROR [main]: org.jivesoftware.database.SchemaManager - SchemaManager: Failed to execute SQL: CREATE INDEX ofMucConvLog_msg_id ON ofMucConversationLog (messageID); 2017.12.05 12:45:20 ERROR [main]: org.jivesoftware.database.SchemaManager - object name already exists: OFMUCCONVLOG_MSG_ID in statement [ CREATE INDEX ofMucConvLog_msg_id ON ofMucConversationLog (messageID)] java.sql.SQLSyntaxErrorException: object name already exists: OFMUCCONVLOG_MSG_ID in statement [ CREATE INDEX ofMucConvLog_msg_id ON ofMucConversationLog (messageID)]
How to fix
You can install the
DB Access Openfire Plugin, then navigate on the Openfire admin console to ‘Server’ -> ‘DB Access’ and enter the following SQL statements
select version from ofVersion where name = 'openfire';
You should get 24 reported back. Don’t run the following command if you see something other than 24!
UPDATE ofVersion SET version = 25 WHERE name = 'openfire';
You should get a response that 1 row was modified. Try restarting Openfire / upgrading again to version 4.2.0 and see if users can log in again!
Alternative Approach if you can’t access admin console
- Stop Openfire
resources/database/upgrade/25/openfire_<insert your db here>.sql
- Remove the create index statement in that script
- Start Openfire