DB changes in Openfire upgrade

Openfire Plugins
1

As part of Openfire upgrade, DB scripts(Openfire->resources->database->upgrade) inside the Openfire is executed automatically or not?

2

Yes. Each version of Openfire has a build-in database-version identifier. Every time Openfire starts up, it checks the currently installed version of your database. If the database is not up-to-date, update scripts will be executed one by one.

3

Thanks, Guss. We had used Openfire in our project, but DB values are not updated properly once Openfire service starts.D we need to manually run those?

4

They will always be executed, but execution has probably not been successful. Analyze the log files to find out why. You’ll then have to correct for that ‘manually’. If you find an issue with one of our update scripts, then we’d appreciate to learn about your findings.

5

Thanks Guus. In our project script execution is not happen properly due to failure in scripts. Please see the attached logs
openfire.log (58.7 KB)

6

That is helpful, thanks.

Have a look at the first few lines of the log file:

2024.05.15 10:36:18.267 e[32mINFO e[m [main]: org.jivesoftware.database.SchemaManager - Found old database version 21 for openfire. Upgrading to version 34...
2024.05.15 10:36:18.283 e[1;31mERRORe[m [main]: org.jivesoftware.database.SchemaManager - SchemaManager: Failed to execute SQL:
 ALTER TABLE ofUser ADD storedKey VARCHAR(32);

The installed version of the database is 21, so the first script that will be used in the update is 22. Sure enough, script 22 includes the failed SQL:

// add columns for SASL SCRAM-SHA-1
ALTER TABLE ofUser ADD storedKey VARCHAR(32);
ALTER TABLE ofUser ADD serverKey VARCHAR(32);
ALTER TABLE ofUser ADD salt VARCHAR(32);
ALTER TABLE ofUser ADD iterations INTEGER;

UPDATE ofVersion SET version = 22 WHERE name = 'openfire';

I do not think that the log file that you provided shows the cause of the original problem. I assume that this shows an error that is the result of a partial execution of a script. The first time that you tried to update, the command that now fails was already executed. This causes the retry to fail (as the column that’s added now already exists.

It’s probably one of the later statements that fails. You can look at your database to see what fails, and why. Hopefully, you can manually adjust for the failure. After doing that, and manually updating the version to 22 (which is the last line of the SQL update script), you can try starting Openfire again, for it to automatically install script 23 and later.