I am migrating a very old server configuration to AWS Linux 2. Here are the details of the old and new configuration:
Old Server
OS: Fedora 11
Openfire: 3.6.4
MySQL: 5.1
New Server
OS: AWS Linux 2
Openfire: 4.6.2
MariaDB: 5.5
The MySQL database was exported from the old server and successfully imported into the new server. After installing Openfire 4.6.2 on the new server and setting up the configuration I started Openfire and had difficulty connecting to MariaDB. Searched the forums and found that I needed to install the MariaDB connector (mariadb-java-client-1.8.0.jar). With that change Openfire started but I have errors in the logs as follows:
SchemaManager: Failed to execute SQL: ALTER TABLE ofMucConversationLog ADD COLUMN stanza CLOB
I was able to trace this down to the file resources/database/upgrade/24/openfire_db2.sql being executed during the upgrade, even though my openfire.xml uses the MySQL driver:
To prove this I saved off the original openfire_db2.sql file (in the resources/database/upgrade/24 directory) and created a symlink that pointed to the openfire_mysql.sql file. When I restarted Openfire I could see that the upgrade succeeded for 24 but then failed on another upgrade.
I’ve since found documentation that recommends doing a more incremental upgrade when there is this kind of disparity between versions but I’m not sure that would help in this situation. Does anyone have any idea why the openfire_db2.sql file would be used instead of the openfire_mysql.sql file during the upgrade processing? Is there another place in the configuration that I should be specifying that this is a MySQL database?
Wow, that is puzzling. I believe the code at play here is in DbConnectionManager.java, why it is picking up db2 is beyond me. Does openfire.xml have any other database info in it that would think db2 is being used?
There’s nothing related to db2 that I know of. Here’s the database configuration (taken from the original configuration):
<database>
<defaultProvider>
<driver>com.mysql.jdbc.Driver</driver>
<serverURL>jdbc:mysql://localhost:3306/openfire</serverURL>
<username encrypted=“true”>encrypted user name</username>
<password encrypted=“true”>encrypted password</password>
<testSQL>select 1</testSQL>
<testBeforeUse>true</testBeforeUse>
<testAfterUse>true</testAfterUse>
<minConnections>5</minConnections>
<maxConnections>25</maxConnections>
<connectionTimeout>1.0</connectionTimeout>
</defaultProvider>
</database>
Can you tell me what the trigger is for Openfire to perform the upgrade? Is it simply reading the ofVersion and, if it doesn’t match the latest, start going through the sql files after the current version? If so, then I have further evidence of this. The ofVersion in the original database was at 20. For 21 and 22 the db2.sql and mysql.sql files are identical so there would have been no failures. However 23 adds a column of allowpm to the ofMucRoom table but the db2 file uses INTEGER as the datatype whereas the mysql version uses the TINYINT datatype. When I checked the DB this morning, sure enough, the allowpm column is INTEGER. So, while it was valid syntax for MySQL, it wasn’t what the mysql version of the file would have specified.
There may be an additional clue. While the upgrade clearly fails that doesn’t cause Openfire to fail…it keeps running. As it runs the following errors appear in the log:
2021.04.02 05:23:12 org.jivesoftware.openfire.pubsub.DefaultPubSubPersistenceProvider - (conn=89) You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'LEFT JOIN (SELECT id FROM ofPubsubItem WHERE serviceID=‘pubsub’ AND nodeID=‘demo’ at line 1
java.sql.BatchUpdateException: (conn=89) You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'LEFT JOIN (SELECT id FROM ofPubsubItem WHERE serviceID=‘pubsub’ AND nodeID=‘demo’ at line 1
I did some additional troubleshooting on this. I’m not a Java developer but was able to following the build instructions to download and build the Openfire source. I instrumented the setMetaData() function in DbConnectionManager.java so that I could have the dbName and driverName printed to the log. The dbName is used to determine the database type.
I found two problems:
the dbName returned is mariadb. This is not supported in the if/else-if clause and so the database type would never be set properly.
I believe the last else-if is a bug. The code shows the following:
else if (dbName.indexOf(“db2”) != 1) {
…and, as a result of being used for comparison 1 (rather than -1), db2 is chosen as the database type which then explains why my upgrade is failing and why subsequent DB queries are using db2 syntax.
Can someone please confirm my findings? I will be make these changes locally for my testing to see if I can move forward but I would rather use an official build for this implementation.
Hi Dan. Thanks for getting to the bottom of this. I can’t argue with your logic. Unless its reported name corresponds with a very specific pattern, I think this also means that everyone (?) using an actual DB2 database never used the DB2 scripts…
Hi Guus: Thanks for the quick reply and for both confirming and fixing them. I’ll do a pull to get your changes. And yeah…I hadn’t looked at it from the perspective of db2 users but you’re right…that would have impacted them as well. Glad we could get this taken care of.