Problems with Razuna 1.9.1 and MS SQL Backend

Razuna v2 Legacy Self-Hosted
1

I’m having a bit of trouble connecting Razuna with a backend MS SQL instance. Basically what is happening is this: when I walk through the custom installation screens, I choose the option for MS SQL, plug in my server, port, db/schema info, username, password, etc, check connectivity to the DB (which passes), fill out the info on the remaining screens, and when prompted to Finalize the Setup, I click the button to do so.

At this point, the progress bar pretty much sits there doing nothing indefinitely. In SQL, I am seeing several tables being created, so something is working right, but the setup never in fact completes. If I switch to the embedded H2 DB, everything is golden and the finalize step completes super quick.

In the background, I’m seeing this in catalina.out:

±----------------±-
{ts ‘2017-04-04 21:20:20’} ---------------------- Creating new collections
{ts ‘2017-04-04 21:20:20’} — Executing Remove from Cron
{ts ‘2017-04-04 21:20:20’} ---------------------- Starting removal
{ts ‘2017-04-04 21:20:20’} ---------------------- Config Error. Aborting… !!!
±----------------±-
| type | Database
| detail | When connecting to the Database this error was reported: Feature not supported: "autoServerMode && (r
| message | General SQL Error
| errorcode | errorCode.sqlError
| extendedinfo |
| tagcontext | {CFML Type::array}
| sql | SELECT opt_id, opt_value
FROM options
WHERE opt_id LIKE ?
| nativeerrorcode | 50100
| sqlstate | HYC00
| queryError | Feature not supported: "autoServerMode && (readOnly || fileLockMethod == NO || fileLockMethod == SERI
| datasource | RAZUNA_SERVER
| tagname | CFQUERY
| template | /opt/razuna_tomcat_1_9_1/tomcat/webapps/razuna-searchserver/api/authentication.cfc
| line | 71
| column | 4
±----------------±-
{ts ‘2017-04-04 21:20:20’} — Executing Indexing from Cron
{ts ‘2017-04-04 21:20:20’} ---------------------- Config Error. Aborting… !!!
±----------------±-
| type | Database
| detail | When connecting to the Database this error was reported: Feature not supported: "autoServerMode && (r
| message | General SQL Error
| errorcode | errorCode.sqlError
| extendedinfo |
| tagcontext | {CFML Type::array}
| sql | SELECT opt_id, opt_value
FROM options
WHERE opt_id LIKE ?
| nativeerrorcode | 50100
| sqlstate | HYC00
| queryError | Feature not supported: "autoServerMode && (readOnly || fileLockMethod == NO || fileLockMethod == SERI
| datasource | RAZUNA_SERVER
| tagname | CFQUERY
| template | /opt/razuna_tomcat_1_9_1/tomcat/webapps/razuna-searchserver/api/authentication.cfc
| line | 71
| column | 4
±----------------±-

…over and over and over again until I back out of the process in the web UI and/or shutdown Tomcat. From what I can tell, it almost seems like it’s relying on H2 features despite choosing the option for MS SQL…I don’t know.

Everything so far has been relatively pain free and works as expected, but for some reason this one piece is really driving me crazy!

Platform is Ubuntu 16.04 LTS, Java 8

2

Hi dlofstrom ,

Please see this link for your references : http://wiki.razuna.com/display/ecp/Connecting+Razuna+to+a+database

http://wiki.razuna.com/display/ecp/Installation+Trouble-Shooting

Hope this helps.

Thanks

3

Hi Huy - I’ve followed the first link you sent (and the subsequent links specific to setting up the MS SQL database, user and schema) to the letter. The second link only stresses that TCP/IP be enabled for the instance of SQL, which it is. The fact that I am able to get past the database property and validation screens in the Razuna custom installation, along with the fact that some database tables are being written would seem to indicate that this part is at least set up right, it just seems to be something in the finalization step that is not completing, but I can’t determine exactly what that is.

Besides catalina.out, are there any other logs I should be looking at (install logs, etc)? Not sure where to look other than what’s in ./tomcat/logs…

Derek

4

More on this:

One problem would seem to be that the database setup process cannot create two tables: raz1_approval and raz1_approval_done. I tested against MySQL and it built all the tables just fine. Compared the list of tables here with the list of tables created on MS SQL and these were the only two missing.

Looking at ./global/cfc/update.cfc (which seems to contain the table creation queries), I’m seeing that several data types for the raz1_approval table are statically assigned “BOOLEAN”, which is an invalid MSSQL data type.

The data types for raz1_approval_done seem to be fine so I’m not sure what’s causing the table creation issue there, but certainly the one for “BOOLEAN” could cause problems.

5

Update: The problem is actually with ./global/cfc/db_mssql.cfc. In the ‘approval’ table definition, the “boolean” data types should be replaced with “tinyint” (I think, I’m no DBA…). This allows this table, and all remaining tables in the build process to be built, and the setup process to complete.

I don’t know how complete this solution is or what other pages would need to be updated. ./global/cfc/update.cfc still has invalid MSSQL data types defined for these tables, so I assume these would need to get corrected as well. Though I’m not entirely sure when the queries in this page get invoked.

6

Hi,

Just change the values in the db_mssql.cfc file for now. This will then conclude the setup. I will fix it on my end.

The update file is for updates. Not for new installations. So nothing there for you to change.

Thank you.

7

Looking at it, you should most like use “bit” instead of “boolean” for the data type.

8

Great, thanks for the follow up and clarification Nitai!