Nuxeo cluster and SQL Server shared instance problem

[arubiom00_]

We have a Nuxeo-5.9.3 cluster consisting in two Tomcat server and a SQL Server geo-cluster. The SQL instance is shared among many applications.

We have observed that the cleanup of the table cluster_invals and cluster_nodes was not working properly, and Nuxeo performance plummeted (due to 30 rows in cluster_nodes and millions in cluster_invals). After doing some research, we found that the way Nuxeo handles the cleanup of those tables only works if you have a dedicated SQL Server instance.

We were able to work-around the problem by:

• Adding a column to cluster_nodes (hostname nvarchar(128)).

• Modify the file sqlserver.sql.txt included in nuxeo-core-storage-sql-5.9.3.jar. Now nodes are deleted and inserted this way:

    -- DB Collation is CI but Nuxeo DB is CS
    DELETE FROM [cluster_nodes] WHERE hostname =
      (SELECT host_name COLLATE DATABASE_DEFAULT FROM sys.dm_exec_sessions S
       WHERE S.session_id = @@SPID)
  
    INSERT INTO [cluster_nodes] (nodeid, created, hostname)
      SELECT @@SPID, CURRENT_TIMESTAMP, host_name FROM sys.dm_exec_sessions S
      WHERE S.session_id = @@SPID
  

The work-around can be easily improved (it doesn't currently work for many nuxeo instances on the same node).

Do you think there is a better approach for this problem? Should it be reported as a bug?

Thanks in advance.

[arubiom00_]

I've tested both versions (5.9.3 and 5.9.4) and no delete was executed during shutdown. I tested in the lab with H2 database and I got the same result.

[arubiom00_]

I've tested versions 5.9.3 and 5.9.4 with H2 databases using the virtual machines availables at

[Florent_Guillau · Accepted solution]

I can confirm that 5.9.4 indeed has this problem, but it's fixed for the next 5.9.5 (release in around 2 weeks). I don't have an exact ticket to pinpoint when the problem was introduced or when the fix occurred, but we've done a number of changes in the repository shutdown mechanism and one of them has fixed it.

You can try a 5.9.5-SNAPSHOT from http://qa.nuxeo.org/jenkins/job/IT-nuxeo-master-build/ if you want to confirm.

[Florent_Guillau]

And thanks for the report.

[arubiom00_]

Great!