Failed to execute SQL: DROP INDEX IF EXISTS ofMucConvLog_msg_id;

Openfire
1

Hi experts,

I was upgrading the embedded Openfire in our product from 3.9.3 to 4.3.1 (DB: MS SQLServer) and found the upgrade stopped at db version 25.

The error log:
…

2019.02.28 13:59:09 org.jivesoftware.database.SchemaManager - SchemaManager: Failed to execute SQL:

DROP INDEX IF EXISTS ofMucConvLog_msg_id;

2019.02.28 13:59:09 org.jivesoftware.database.SchemaManager - Must specify the table name and index name for the DROP INDEX statement.

java.sql.SQLException: Must specify the table name and index name for the DROP INDEX statement.

at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:372) ~[jtds-1.3.1.jar:1.3.1]
…

The SQL should be changed to:
DROP INDEX IF EXISTS ofMucConvLog_msg_id ON ofMucConversationLog;

I’m not sure if the upgrade of the official Openfire will also fail in this case, or if there are some other logic to prevent it from happening.

Thanks!

1 Like
2

Thank you for the bug report; even more appreciated it has the fix! I’ve raised https://issues.igniterealtime.org/browse/OF-1698 to track this.

Greg

1 Like
3

Greg’s fix has been merged. The new update script will be part of 4.3.3 as well as 4.4.0. I’d appreciate if someone could test the fix using a nightly build.

4

The solution had been verified on SQL server before I posted this topic.

BTW, Oracle has no such IF EXISTS option. Need use plsql block to achieve this:
[
DECLARE
index_count INTEGER;
BEGIN
SELECT COUNT(*) INTO index_count
FROM USER_INDEXES
WHERE INDEX_NAME IN (‘ofMucConvLog_msg_id’, UPPER(‘ofMucConvLog_msg_id’));
IF index_count > 0 THEN
EXECUTE IMMEDIATE ‘DROP INDEX ofMucConvLog_msg_id’;
END IF;
END;
]

Note that by default, all text tokens are converted to uppercase and then indexed in Oracle, so should also compare upper cased index name.