Openfire 4.2.0 Login Issues? Check your database schema

akrherz · December 5, 2017, 2:09pm

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

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

SpinWood · December 5, 2017, 5:10pm

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.

Geoff_Cummins · December 5, 2017, 5:29pm

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

akrherz · December 5, 2017, 5:34pm

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

Sam_Bastin · December 5, 2017, 10:49pm

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 ?

akrherz · December 6, 2017, 2:40am

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

Nuno · December 7, 2017, 4:42pm

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

akrherz · December 7, 2017, 4:52pm

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?

Nuno · December 7, 2017, 5:33pm

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

wroot · December 7, 2017, 5:57pm

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.

akrherz · December 7, 2017, 5:58pm

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

_ndres · December 8, 2017, 10:10pm

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.

_ndres · December 8, 2017, 11:01pm

Hi.

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

Thanx

akrherz · December 9, 2017, 2:09am

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?

_ndres · December 9, 2017, 2:13am

Hi akrherz.

Yeah. I always stop the service before upgrading.

Thank you.

akrherz · December 9, 2017, 2:25am

Are you able to access the Openfire Admin Console?

_ndres · December 9, 2017, 2:26am

Hi akrherz.

Yeah. I can access the Admin Console.

Thank you.

akrherz · December 9, 2017, 2:28am

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

_ndres · December 9, 2017, 2:32am

Hi akrherz

Done.

Thank you.