Azure SQL Connection Reslience

Support
#1

I am evaluating the CUBA Platform as a replacement for Lightswitch and I have several clients that are using the Azure SQL database. When I started using Azure SQL, I remember that connection resilience was added to Lightswitch and the underlying Entity Framework given Azure SQL will time-out connections. Discussions here.aspx) and here.

I did a partial port one of my less complex apps and noticed that the jTDS jdbc driver’s socket is getting closed periodically (Either by a timed out or network error) and manifests itself when a tab is closed or at other times in an edit screen. Unfortunately, the behavior is severe and in most cases requires the user to log out and log back in causing data to be discarded. I have verified running both in a Azure VM and in development.

I am wondering if you have seen this behavior? Looking at the stack trace, there are a LOT of third party libraries and I am not sure if this is even something you can fix. For me this does represent a problem as this renders the use of Azure SQL as a not a viable option when using the CUBA Platform as it stands now. This is going to be an issue for you more and more with the advent of Cloud based databases or any scenario where the database is not on the same LAN segment.

This is an awesome product and is will likely be my replacement for Lightswitch … you guys have done a great job!

Thanks,
Ian

2 Likes
#2

I have attached a Stack Trace.

StackTrace.txt (10.8K)

#3

Hi Ian,

Thank you for reporting the problem. I see two ways of possible fix:

  • First we should adopt the Microsoft’s native JDBC driver for SQL Server. It was recently open sourced and should be available in Maven repository, so I don’t see any reason why not to use it. Actually, you can try it right now for your deployment - just place it to tomcat/lib and make appropriate changes in context.xml.

  • If it doesn’t help, we’ll try to implement connection resilience on some level - playing with JDBC driver options or on a higher level.

And thank you for your positive feedback on our product!

1 Like
#4

Konstantin,

I believe that there is a little more to this as the jdbc bits are buried as mbean dependencies + cuba-core config stuff.

  1. From the Azure management website, the jdbc connection string is suggested to look like:

 jdbc:sqlserver://${azuresqlserver}.database.windows.net:1433;database=${dbname};user=${user}@${azuresqlserver};password=${password};encrypt=true;trustServerCertificate=false;hostNameInCertificate=*.database.windows.net;loginTimeout=30;

The various configurable elements are ${azuresqlserver}, ${dbname}, ${user}, ${password}.

  1. If I try to change the context.xml on the dev system, Studio gives an error when running stating that the database is unknown.

  2. I cannot just copy the jar file for the mssql-jdbc-6.1.0.jre8.jar to the production site. The Tomcat keeps re-installing the war and overwrites the context.xml file + libs folder.

  3. I can add the string to production_context.xml but I need to add the dependency to mssql-jdbc-6.1.0.jre8.jar which given my newbie status, I am not sure how to in the CUBA project. This is causing a mbean registration error (partial stack below):


2017-01-16 19:18:19.420 ERROR [localhost-startStop-29] com.haulmont.cuba.core.sys.AppContextLoader - Error initializing application
org.springframework.jmx.export.UnableToRegisterMBeanException: Unable to register MBean [com.haulmont.cuba.core.jmx.PersistenceManager@aad7af0] with key 'app-core.cuba:type=PersistenceManager'; nested exception is javax.management.InstanceAlreadyExistsException: app-core.cuba:type=PersistenceManager
        at org.springframework.jmx.export.MBeanExporter.registerBeanNameOrInstance(MBeanExporter.java:625) ~[spring-context-4.3.1.RELEASE.jar:4.3.1.RELEASE]

If you can give me a little more direction, I will be able to test this and make sure there is a solution with the ms driver.

#5

I was able to add the ms jdbc dependency:


configure(globalModule) {

    dependencies {
        compile('com.microsoft.sqlserver:mssql-jdbc:6.1.0.jre8')
    }
    task enhance(type: CubaEnhancing)
}

Still getting the same error as indicated in the stack trace above.

This is my context.xml:


<Context>
    <!-- Database connection -->
    <Resource
      name="jdbc/CubaDS"
      type="javax.sql.DataSource"
      maxTotal="20"
      maxIdle="2"
      maxWaitMillis="5000"
      driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver"
      url="jdbc:sqlserver://***.database.windows.net:1433;database=cuba;user=***@***;password=***;encrypt=true;trustServerCertificate=false;hostNameInCertificate=*.database.windows.net;loginTimeout=30;"
      validationQuery="select 1"></Resource>
    <!-- Switch off session serialization -->
    <Manager pathname=""></Manager>
</Context>

Before I spend any more time, can you tell me if I am at going in the right direction?

Thanks

#6

You are certainly going in the right direction.

The InstanceAlreadyExistsException on MBean registration is actually not related to the database - it suggests that in the JVM an instance of MBean with the same name already exists. This can be caused by deploying several web apps with the same
cuba.webContextName parameter.

Could you try to clean up the server somehow and redeploy?

#7

The InstanceAlreadyExistsException was being caused by the ms jdbc driver jar file being copied to the tomcat/lib folder. Once I removed the jar file everything worked fine.

It will take me some time to verify if the problem is resolved.

#8

First problem is with DateTimeOffset conversions:


ConversionException: 
Exception Description: The object [2003-03-05 00:00:00 -08:00], of class [class microsoft.sql.DateTimeOffset], from mapping [org.eclipse.persistence.mappings.DirectToFieldMapping[dateEntered-->Registrations.DateEntered]] with descriptor [RelationalDescriptor(com.company.test.entity.Registrations --> [DatabaseTable(Registrations)])], could not be converted to [class java.util.Date].

Attached is full stack trace.

DateTimeOffsetConversionException.txt (11.7K)

#9

The connection resilience issue does not exist with the Microsoft JDBC driver. I can leave any editor open up until a session timeout and there are not JDBC timeouts. I have tried disconnecting and reconnecting the network interface and there are no disconnect exceptions.

The only issue is the DateTimeOffset SQL data type. I suspect that the MssqlDbTypeConverter needs to convert TIMESTAMP representation for the DateTimeOffset to a Types.DATE with the timezone set to the core servers timezone??? The DateTimeOffset conversion will be a requirement for any system working with an Azure SQL database.

As I am just evaluating CUBA as a replacement for Lightswitch and don’t have an immediate requirement, I consider my research on this issue complete.

Thanks for your help and hope to see the MS JDBC implemented in a future version.

#10

Ian,

Thank you for the research and for the hints about a possible solution. We’ll deal with the issue in the near future.

1 Like
#11

Haulmont advertises CUBA.platform as an lightswitch alternative:

So, shouldn’t the issue here have “critical” priority?

#12

This issue is a test from my perspective!

I will not adopt another platform quickly after the shit-show that was Lightswitch. I like the tenor of the of this forum, the quality of the product and in general find the support excellent but I am waiting to see how responsive the developers are to meeting customer needs. If the CUBA platform developers do not listen to their customers, then I will not be adopting the platform.

This issue is a show stopper for me.

#13

@IanE, the developers seem fine here @CUBA.platform. The problem is the leadership (Business Development, Product Management), which is essentially non-existent.

You can assess this by tiny issues:

Even a mediocre Project-Manager would insist on this functionality, without any discussion:

And of course, this functionality is a must requirement, too:

https://www.cuba-platform.com/discuss/t/packaging-a-ui-component-and-use-it-from-studio-palette

And then finally: advertising a platform as Lightswitch replacement, without covering the essential database support, indicates again very very bad leadership.

Essence: Haulmont must fire their executives. And as executives usually do not fire themselves, this will not happen.

This is my main showstopper:

CUBA.platform has a good development team, but a missing leadership.

#14

@IanE, can you please contact me via info@lazaridis.com?

I like to discuss about experiences with other tools, possibly we have common requirements.

#15

Thanks for implementing this feature Konstantin.

Are you able to handle the DateTimeOffset data type?

PS: I did not contact Lazaridis! I have 2 issues, this one + image handling and that’s it! Your product + support is awesome as far as I am concerned … at least from a lurker perspective :slight_smile:

#16

Hi Ian,

If you are talking about the recently closed issue about Microsoft JDBC driver - yes, it will be the default for SQL Server starting from platform 6.5.

As for the DateTimeOffset data type, it’s not easy because EclipseLink JPA does not support it without custom converters, and CUBA visual components like DateField work only with the standard java.util.Date which has no information about timezone.

As you may know, we support timezones in the following way: on each login, a user session gets an information about current user timezone from the user settings or dynamically from the web browser. Then all DateTime values automatically converted to/from this timezone when displayed or edited by the user. That is the datetime values are stored and processed in some reference timezone like GMT but displayed/edited in the user’s timezone.

So could please you start a separate topic and explain why this behavior is not enough and you need to store dates in DateTimeOffset? Examples from real life are always good incentives for us.

#17

I think you need to read this thread again and it may make more sense why the DateTimeOffset data type is an issue and why it is related to the MS JDBC driver implementation and NOT a feature!

  1. I am looking at CUBA as a replacement for LightSwitch applications.
  2. LightSwitch create auditing fields Created and Modified using DateTimeOffset data types.
  3. I took an existing LS application, copied the database and tried to create similar screens using CUBA.
  4. It all worked until I found the connection resilience issue with Azure.
  5. You suggested I try the MS JDBC driver which I was implemented successfully but ran into the DateTimeOffset issue so the CUBA screens created using the old driver do not work anymore.

In fact, try running the CUBA Vision demo with the MS JDBC Driver and you will see the problem for yourself. It’s a regression which is going to be a bug. Is this real life enough for you?

Thanks for your help but this was not the response I was expecting.

#18

Hi Ian,

Thank you for pointing out the problem. You have never mentioned that system fields in LightSwitch are created with this type, and we haven’t time to test our latest changes on LS databases yet, hence the confusion.

We have fixed the issue for platform 6.5. However, the same as for jTDS driver, DateTimeOffset columns are still mapped to regular java.util.Date type so the values are converted to simple moments in time and loose information about time zones.

#19

Thanks Konstatin!

Converting to Date type was exactly how I though the issue should be handled.

I just noticed that you guys were unable to reproduce this issue but for clarity, I am running a Linux VM locally accessing the Azure SQL database over the internet … not from a VM running in the Azure infrastructure. This means that the connection is made through the Load Balancer which is the component that times out the connection. If you run the app in the Azure infrastructure the resilience issue is much harder to reproduce.

Just FYI, I am evaluating CUBA as replacement for LightSwitch (LS) in 2 scenarios:

  1. Use CUBA with a LS database unchanged. This is my ultimate goal as it would allow me to run a LS and CUBA app simultaneously. This issue was a bit of a show stopper and now that it’s resolved I can move on with my evaluation. Image handling or the equivalent of the LS Image Editor/Viewer is another show stopper and I realize that I can write it myself but it would be real nice if CUBA handled images in the database natively. The last issue that I still need to prototype is the RowVersion datatype + concurrency. the RowVersion is just a timestamp and as I understand it JPA supports a timestamp with the @Version attribute. Right now you reverse engineering the database with the RowVersion as a byte array and it should really be a timestamp. After I have tested this, I will create a new topic to discuss the possibility of importing it as a timestamp and having the option of using it as the @Version field. From my perspective, CUBA is real close for me to be able to achieve the goal for this scenario! If I can do this scenario, I will go back to the LS community and recommend CUBA as an viable alternative.
  2. Writing a script to change the schema so that it is more inline with what CUBA conventions. In this case there would not be no ability use a CUBA and LS app simultaneously. This script for would for example, change the RowVersion and audit field names and datatypes to match CUBA convention’s for versioned, creatable and updateable etc. The image handling is still an issue for me with this scenario.

This is just FYI, I am not asking for anything … other than consideration for image handling for image editing/viewing in CUBA as a standard functionality :).

Thanks again … Ian

#20

Hi Ian,

Using RowVersion created by LightSwitch as a version attribute for CUBA is an interesting idea. In our experiments with LS we have ignored this field, so we couldn’t provide optimistic locking between CUBA and LS applications using the same database, and this is certainly a shortcoming. We’ll return to this problem a bit later when working on 6.6, this is the issue for reference: https://youtrack.cuba-platform.com/issue/PL-8896

However, leaving aside the issue with concurrent access, a LS database can be used in parallel by CUBA and LS application even now - just map existing system columns (except RowVersion) to CUBA system attributes, and you won’t have to modify the database schema.

As for managing images, we’ll let you know as soon as we formulate an opinion on how it should work to discuss.