Manual DB data changes are not reflected immediately via API

[cecilia-donnelly]

When data is changed directly in the database (e.g. when doing a bulk insert or deletion), those changes are not immediately shown to apps who are accessing the data via the API. We may just need to document that db owners should run mvn tomcat7:redeploy after manually changing their data.

To reproduce while testing, add some client data via a MySQL command line interface, and then check localhost:8080/openhmis/services/clients. The new data will not appear in the browser until you run mvn tomcat7:redeploy.

@slifty suggested that Hibernate is doing some sort of caching and so doesn't immediately pass on changes to the underlying data.

I'm not sure how often this will be an issue for production use, but we should document it, at least.

Note that this is not an issue for developers working only with the API. Any data changed via the API will be shown correctly by future API calls. The only issue comes when the data is updated manually instead of through API calls.

[ashaar364]

@cecilia-donnelly This is not a big issue.

set the following property in hibernate.cfg.xml file <property name="hibernate.connection.autocommit">true</property>

after setting this property, restart your tomcat. i.e, mvn tomcat7:redeploy

Now add the changes to the database in bulk and see the changes on the browser. You will be able to see the latest data on the browser.

You can get the same result if you are using the hibernate.properties file in that case you need to add the following in the properties file hibernate.connection.autocommit=true and restart your tomcat

[cecilia-donnelly]

@ashaar364 thanks for the tip!

By the way, I edited your response so that the "property" tags above would show up as code -- they were hidden by GitHub Markdown, which thought they were html. Wrapping text in backticks (``) makes it show up as monospaced literal text.

I'll test this and then close the issue. Thank you!

[cecilia-donnelly]

@ashaar364 this absolutely did the trick. I was going to commit a change to hibernate.cfg.xml.example setting that property equal to true, but I saw that Hibernate recommends not doing so. See this Stack Overflow question for more on why. In short, we may have multiple transactions going on and want to maintain database state for each transaction.

Can we have a bit of a discussion in the issue about what we want to recommend, here? I'm guessing that we want "autocommit = true" for development but not production.

@steveconners @slifty @kfogel

[kfogel]

A couple of things:

I think we should follow the Hibernate recommendation here. Transactions are a way to ensure that application-specific data interdependencies remain in a consistent state (as far as the world outside the transaction is concerned) at all times. So I don't think we want "autocommit = true" by default.

However, I'm confused about something: Why does setting the Hibernate autocommit parameter have any effect on when manually made changes to the DB become visible to Hibernate or, for that matter, to anyone else? That is, if one is making manual changes in the database, then the question of when one's changes become visible to other users of the database (e.g., to Hibernate) has to do with whether one's manual changes are wrapped in a transaction or not, right? (Or am I missing something?) In other words, I would have assumed that what's going on here is that the manual changes were taking place inside a transaction -- perhaps because some DB-level configation automatically wraps every session in a transaction -- and that the changes would not become visible to Hibernate until that transaction is committed. But the committing of the transaction is not done by Hibernate, because these changes were not made through Hibernate!

Unless I'm misunderstanding what is meant by the phrase "data is changed directly in the database (e.g. when doing a bulk insert or deletion)" in the original report... If those changes were also somehow going through Hibernate (even if not through the application itself), then yes, Hibernate's autocommit setting could affect this.

[slifty]

Sorry all, talked to cod nelly about this in Orc then had a baby before posting on github

Baby is sleeping In my arms now actually...

Kfogel the short story as I understand is that hibernate maintains an in memory data layer which only syncs with the database as needed.

The solution I suggest here is to update game DAOs to flush that layer before issuing the select queries to ensue those are working with the latest.

Best, Dan

On Thursday, August 13, 2015, Karl Fogel notifications@github.com wrote:

A couple of things:

I think we should follow the Hibernate recommendation here. Transactions are a way to ensure that application-specific data interdependencies remain in a consistent state (as far as the world outside the transaction is concerned) at all times. So I don't think we want "autocommit = true" by default.

However, I'm confused about something: Why does setting the Hibernate autocommit parameter have any effect on when manually made changes to the DB become visible to Hibernate or, for that matter, to anyone else? That is, if one is making manual changes in the database, then the question of when one's changes become visible to other users of the database (e.g., to Hibernate) has to do with whether one's manual changes are wrapped in a transaction or not, right? (Or am I missing something?) In other words, I would have assumed that what's going on here is that the manual changes were taking place inside a transaction -- perhaps because some DB-level configation automatically wraps every session in a transaction -- and that the changes would not become visible to Hibernate until that transaction is committed. But the committing of the transaction is not done by Hibernate, because these changes were not made through Hibernate!

Unless I'm misunderstanding what is meant by the phrase "data is changed directly in the database (e.g. when doing a bulk insert or deletion)" in the original report... If those changes were also somehow going through Hibernate (even if not through the application itself), then yes, Hibernate's autocommit setting could affect this.

— Reply to this email directly or view it on GitHub https://github.com/PCNI/OpenHMIS/issues/30#issuecomment-130826836.

Semt feom a mobil dvice. Please fotgive any typps.

[kfogel]

HOLY MOLY CONGRATULATIONS DAN WHOA!!!

WELCOME IDA!

HAPPY HIBERNATING!

[ashaar364]

Why we want to execute the sQL query to bring something into the database. For me everything should come into the system by using API. If API doesn't provide this facility we should update the API, make the changes in it and stop using manual queries

[kfogel]

Hi, @ashaar364. I suspect the direct SQL queries were manual changes made during development/testing, for example to see if a certain API call delivers the right results when a certain change is made in the database. IOW one has to make some manual DB changes when testing an API, because otherwise you're relying on the thing you're testing working correctly in order to test it :-).

[slifty]

What Karl is saying is true, but in addition the Api absolutely must be able to support the idea that other systems and applications might modify the data outside of the Api - I know that this will at least be the short to mid term case for the pathways implementation and suspect it will be true in most other real world cases as well.

On Mon, Aug 17, 2015 at 1:31 PM Karl Fogel notifications@github.com wrote:

Hi, @ashaar364 https://github.com/ashaar364. I suspect the direct SQL queries were manual changes made during development/testing, for example to see if a certain API call delivers the right results when a certain change is made in the database. IOW one has to make some manual DB changes when testing an API, because otherwise you're relying on the thing you're testing working correctly in order to test it :-).

— Reply to this email directly or view it on GitHub https://github.com/PCNI/OpenHMIS/issues/30#issuecomment-131897910.

[ashaar364]

@kfogel for testing purpose executing the queries and verifying the data in the API is fine. For that purpose we can set the autocommit flag to true in the development environment.

[ashaar364]

@slifty if we take the idea that API must be able to support the idea that other systems and application might modify the data out the API. We develop the API, add the authorization and authentication, add the logging and some one break all of these and enter the data into the system. Then why we have API in first place. Any one who want to access or modify the data has to use the API and pass through the authentication and system should log each activity.

[slifty]

Just to be clear @ashaar There are plenty of places where there is both an App and an API (Twitter, Facebook, instagram, etc) and I bet there are examples of tools they provide that don't use their API -- it is still a useful thing to have!

That said, I agree that the vendors should aspire to convert all of their apps to use the API since there are a lot of benefits from doing so. The issue is that most won't be able to convert everything to use the API on day one and we need to support a transition period in order for the tool to ever be realistically adopted.

[ashaar364]

@slifty The question is not about API or App. The question is about the connection to the production database using any tool and updating it.Twitter, Facebook instagram etc can provide the tools that don't use API or use anything other than APi. Do they provide any of their production database information and asking the user they can direct connect to it and create a new user.

And for the second part, if the vendor is not be able to user our system for any reason and their is a transition period to adopt it then we should provide the alternate to the vendor so they can use our system. We can ask to the user to provide the data in the csv format and we update the API to handle the csv file and bring the data into the system.

I hope that will clear what i am talking about.

[steveconners]

Excellent discussion. I admire your ideals @ashaar364. I think I single access point would provide a crisp, clean system.

However, this system is currently envisioned as a method for existing vendors to implement an api alongside their existing application infrastructures. We do not have a current expected implementation without the design requirement that the API be tolerant of non-exclusive access to the database.

If we envision that HMIS Vendors will implement this API against their database alongside their existing application suite, we are not expecting they open their database to users for consumption, they are opening their database to their own applications.

You mention providing an alternative (i.e. a csv import) however, creating a synchronization engine is a non-trivial task. We can investigate this as we move forward, but I fear this would consume much effort needed for the other aspects of the system.

[ashaar364]

@steveconners I have only one concern that if we are bringing any data into the system, we should know who updated that data and authorized person has updated it, we should have a log history and if we want to track back that change(s) we can do it.How we are bringing that data, it depends upon the requirement(s) and environment.

What i mean about the csv import is that we should think about the alternative solutions that we can provide to our vendor(s) depending upon their needs, requirements and our capacity and capability.

[kfogel]

The issue here isn't about API vs direct access, I think. It's really about ensuring that any tool that does use direct access is making sure to maintain internal consistency in the database. For example if there are foreign-key relatiionships that need to be preserved, or if there are other cross-table synchronizations that need to be maintained in order that the semantics of the data be properly preserved, then any tool -- including the API layer itself -- that accesses the data needs to obey those semantics.

Using the API is one way to be sure one is obeying the semantics, but it doesn't have to be the only way. The data layer needs to document what those semantics are, though, in order for implementers to be able to maintain such consistency.

The issue of the API layer using Hibernate and thus having transaction skew with respect to other database accessors is relatively minor and can be solved in various ways -- not necessarily by turning on full autocommit, perhaps, but maybe by doing what Dan suggested in his earlier comment.