Openfire 4.2.0 Login Issues? Check your database schema

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

Openfire 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

  1. Stop Openfire
  2. Edit resources/database/upgrade/25/openfire_<insert your db here>.sql
  3. Remove the create index statement in that script
  4. Start Openfire
2 Likes

After installing openfire-4.2.0-1, none of my clients can login. I get “26” when running the select statement you suggested. Any help would be appreciated.

I believe my clients can still login( 4 of use are logged in), but I cannot login to the Admin portal.

Please start a new discussion thread and provide as many details as possible, like Openfire logs when the login failures happen

I am running openfire on debian and upgraded to 4.2 this morning - all my users and myself can log into the client - however I can not log onto the :9090 admin console with either the admin account or my account (password failure) / I reset the parameter, but when i get to the admin setup screen it will not accept the default of ‘admin’ or the password previously (pre upgrade) used. Suggestions ?

are you able to log in on https://server:9091 , you may be hitting OF-1331, otherwise, please start a new thread on this forum

With so many problems on 4.2, why don’t u guys release a hot fix or something like that?!

Unsure what to tell you, somebody has to fix the issues first. No magic happening here, volunteers doing actual work. Do you have some patches to share?

no, I don’t code, but if so many people has problems, at least halt the release…

If a patch is even doable. You will have to accommodate to every possible starting scenario (upgrading from version x, then do y, from version z, then do w, etc.). Such hotfix will become a messy hack.

I already have a PR for review that should resolve this issue. https://github.com/igniterealtime/Openfire/pull/951

1 Like

Hi.

After updating the server (from 4.1.6 to 4.2.0 and then to 4.2.1) the clients (Spark 2.8.3.960) can’t connect to the server.

Hi.

¿Can We go back to the OpenFire 4.1.6 without restoring a backup copy?

Thanx

should be able to, but I suspect we can fix your 4.2.1 issue and make it work. Did openfire fully stop prior to upgrading it?

Hi akrherz.

Yeah. I always stop the service before upgrading.

Thank you.

Are you able to access the Openfire Admin Console?

Hi akrherz.

Yeah. I can access the Admin Console.

Thank you.

if you want to zip up your openfire logs/ folder and email them to me, I can have a look to see what’s up. akrherz@iastate.edu

Hi akrherz

Done.

Thank you.