Hi,

We are building a data warehousing tool and a custom portlet to allow system to import data directly to the MySQL database every hour as well as user to add data through the UI. However, we found that new data that were imported directly didn't show up on Liferay portal (6.0.1). Not only that, whenever user try to add new data through the UI after the auto import, the portal would crash and complain about ID conflicts. We're guessing it's the Counter table cached in the portal and didn't aware that our auto import added new data and increased the Counter IDs. Whenever this error occurred, all we can do to fix it is to restart the Liferay service to force it pick up the changes.

We have been trying to find a solution including using calling CacheRegistryUtil.clear() and so on, but nothing work. We are only using a standalone Windows system for this project, so there is no cluster involved.

Can someone please guide me what we can do to clear the db cache, or even disable the db cache, so that the auto import can be ran without crashing the portal when user use the UI?

Thanks,
Brian

You shouldn't add data directly to the database. Should use UserLocalServiceImpl.add() method. This method will add/update other dependent tables too as well as the counter.

If you can read Java, following page and sample code may give you a clue on how to add users programmically.
http://www.liferay.com/community/wiki/-/wiki/Main/Web+Service+samples

Thanks Hitoshi for your quick response.

Actually, the custom portlet that we are building does not related to the Liferay user services. I didn't make it clear earlier, sorry. It is a custom forms + report type of projects, which don't interact with any Liferay services besides using it as a portal for authentication. At first, we tried to build it using Service Builder. However, as we have to allow user to do ad-hoc bulk data import and we couldn't find straight forward instruction on how to use custom SQL in Liferay, we decided to do the import part external through SQL. So now, everything is working besides the clear cache or refresh db part.

Thanks,
Brian

Not sure what you meant by Counter conflict in your original post. If you're not using service builder but using your own custom sql statement with jdbc connection, it's shouldn't be a liferay issue. Caching should also be about the application server you're using and not an liferay issue.

If the data is not being updated in your mysql, are you committing your data?

FYI, I have a similar portlet and it's working OK.

At first, the bulk import part wasn't the initial project requirement. So, we developed our custom portlet using the service builder . All the Java services, finders, and custom db tables were created and everything worked fine. However, when the bulk import requirement came in, things get a bit complicated.

We tried to follow http://arunkumarsrm.blogspot.ca/2011/01/custom-sql-in-liferay-plugin-portlet.html and hoped that we could execute INSERT, UPDATE, and DELETE SQL statements to avoid updating the db directly and clearing the cache, but we couldn't make it work.

You are right that we can use jdbc to add, and update our portlet. But, as we already built our portlet using service builder (DAO), I assume using jdbc just to add, and update would still need to do the clear cache thing, else DAO would not pick up the changes. So, it sounds like unless, we rewrite everything to use jdbc to read, add, and update, otherwise, we have no chance to just tell Liferay to pick up the changes after we update the db directly?

Thanks again.

Brian)

To do bulk import and stick with your SB code, you want to a) enable the remote SB interfaces and b) use a web service client to invoke the methods to create and populate your objects.

This ensures that your business logic gets executed, your counter object is updated appropriately, and you don't have issues w/ Liferay SB code having stale cached entities.

Merry Christmas !

Strange that CacheRegistryUtil.clear() is not working. I believe that is what is called when Control Panel : Server Administration : "Clear the database cache. " is clicked. I have systems where other subsystem apps update db directly and then human clicking Execute for "Clear the database cache" OR programmatically doing a per entity clear cache seems to do the trick ( xXXPersistence.clearCache() )

Is the problem that user db crud might be taking place during the bulk db crud and that incurs conflicts ?

Thanks David. This might be the proper way and directly importing to the database is probably a hack that shouldn't be done. However, as restarting Liferay after each import does resolve the stale cashed entities issue, and there are complex SQL JOIN statements that are hard to express in DAO way, we are hoping that there is a "Liferay, please pick up the new data from the database" kind of method to quickly resolve the issue.



Thanks Jack and Merry Christmas to you too! Actually, the funnies thing is that, even though we did "Clear the database cache", Liferay still didn't pickup the changes unless we restart the Liferay service. I also tried the following functions:



All these ran before the line CounterLocalServiceUtil.increment(xXX.class.getName()); But none of them resolve the issue.



Good guess, but I tested them locally by doing it one at a time (first import a bunch of new records, then add a new record through the UI), and it still failed at the CounterLocalServiceUtil.increment saying that the ID already exists.

Thanks,
Brian

Hi Brian. I am a bit confused wrt the facts surrounding your case and am hoping to clarify with the below questions:

Is it really Liferay CE v6.0.1 that you are using ? (the latest v6.0 Liferay CE is v6.0.6)

Is it the case that formerly you had a portlet and you used the servicebuilder and then because of the bulk import requirement you have switched to updating the database directly with sql ? If yes, are you attempting to update the lportal database directly where you aren't involving the counter table and CounterLocalServiceUtil but then after the direct to db changes you are attempting to clear cache and increment the counter(s) ?

Hi Jack, You are right, I'm on "Liferay Portal Community Edition 6.0.6 CE (Bunyan / Build 6006 / February 17, 2011)", not 6.0.1.

The case is almost exactly as you described, but when I import records by updating the database directly, I also update the Liferay Counter table directly:

UPDATE Counter SET currentId = <newId>

The import works well only if followed by a Liferay service restart.

We still have to allow users to input records through the UI, while we have the bulk import feature added as an additional feature.

Thanks,
Brian

Thanks Brian (in Richmond Hill, Ontario?), definition of symptoms of your challenge taking shape. I still need to set the environment and explore further.

Hi Brian (my adopted Cree brother first name actually)
haven't had time to review this, sorry ; has been a busy 2013 so far (including for BI with jasperreports and the like ; new OSGi in Action book arrived today too ; can't wait to read it...)

Beside the cache, liferay uses external indexing. By default, lucene is used. If index is not updated with the database, it data may not show up.