We have a small openfire instance now running on RHEL8 that has been around for years - it has been upgraded, moved, etc as the base system has also been upgraded and changed. We’ve used the embedded database all this time because we don’t have many users - we have Openfire tied to our internal LDAP for accounts.
Our openfire.lobs file is now 28G, openfire.script is 388M. What can we do to shrink the lob file ?
Below are some notes from a successful clean-up performed six months ago. Back up your files beforehand and remember the database schema change, as it might cause trouble during future Openfire upgrades.
1. Stop Openfire service 2. Open HSQLDB Manager Add path to JRE in cmd: set path=%path%;C:\Programs\Openfire\jre\bin Run the following batch file: C:\Programs\Openfire\bin\extra\embedded-db-viewer.bat 3. Execute the following commands in HSQLDB Manager ALTER TABLE ofPubsubItem ALTER COLUMN payload SET DATA TYPE LONGVARCHAR; SHUTDOWN; 4. Change openfire.script (your existing IDs will be different) - ALTER SEQUENCE SYSTEM_LOBS.LOB_ID RESTART WITH 2777 + ALTER SEQUENCE SYSTEM_LOBS.LOB_ID RESTART WITH 1 - SET SCHEMA SYSTEM_LOBS - INSERT INTO BLOCKS VALUES(2776,2147480871,0) - INSERT INTO LOBS VALUES(0,1,0,1) - [...] - INSERT INTO LOB_IDS VALUES(20,53,1,40) - SET SCHEMA PUBLIC + SET SCHEMA SYSTEM_LOBS + INSERT INTO BLOCKS VALUES(0,2147483647,0) + SET SCHEMA PUBLIC 5. Delete openfire.lobs 6. Start Openfire service
I finally got a chance to run through these steps and it worked - thanks!
Thanks for sharing this. It seems that it is generally a good idea to use LONGVARCHAR instead of CLOB for this database column. I’ve created a new issue in our bugtracker for this to be changed: [OF-2436] - Ignite Realtime Jira