powered by Jive Software

Openfire 4.3.2 contains wrong sqlserver script

The included script in Openfire 4.3.2 to create the database schema for Microsoft SQL Server, openfire_sqlserver.sql is wrong. Its code syntax is for MySQL instead of MSSQL, therefore one will not be able to create the database schema required. It will generate lots of errors when executing.

I was fortunate enough to have an older installation of Openfire 4.2.3 with the correct script to use. However, that script does not contain the update to add a column to one of the tables (I don’t remember which), but errors will show up in the Openfire error logs indicating which.

Sounds like this one https://issues.igniterealtime.org/browse/OF-1698

It’s more than that, the syntax is not MSSQL compatible. The script at …\Openfire\resources\database\openfire_sqlserver.sql contains the following syntax:

PRIMARY KEY (username, principal(200)) ==> the (200) is not compatible

MUC Tables # sign is not a compatible mssql syntax

There are a couple of handful more such instances.

What version of MS SQL are you using?

I’m thinking maybe this problem occurred with recent SQL version. Nobody on the team is using MS SQL. Aside @speedy, who uses some version of SQL Express (probably older one). So, such issue went unnoticed.

You mentioned that you used 4.2.3 script. I have compared them and they are very similar aside of a few new lines in the latest. So 4.2.3 would fail also.

For now i have filed https://issues.igniterealtime.org/browse/OF-1724
Would be great if you or somebody could point out every syntax error or ideally provide a patch via pull request.

I am using sql express on one instance and will use real sql 2017 on others. It appears that in Openfire 4.3.2, the included script for openfire_mssql.sql erroneously is an exact copy of script openfire_mysql.sql.

Please forgive me for sticking a big foot in my mouth. It is my error. I made a copy of openfire_mysql.sql then ran it in mssql hence the problems I see. The syntax in 4.3.2. is correct. I will go away in shame now :frowning:

Not a problem. Glad this is not a bug in Openfire :slight_smile: