Forums

Home » Liferay Portal » English » 3. Development

Combination View Flat View Tree View
Threads [ Previous | Next ]
toggle
Brian C
Data imported directly to the MySQL db don't show up on Liferay
December 24, 2012 12:02 PM
Answer

Brian C

Rank: New Member

Posts: 6

Join Date: July 6, 2010

Recent Posts

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
Hitoshi Ozawa
RE: Data imported directly to the MySQL db don't show up on Liferay
December 24, 2012 2:17 PM
Answer

Hitoshi Ozawa

Rank: Liferay Legend

Posts: 7990

Join Date: March 23, 2010

Recent Posts

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
Brian C
RE: Data imported directly to the MySQL db don't show up on Liferay
December 24, 2012 3:09 PM
Answer

Brian C

Rank: New Member

Posts: 6

Join Date: July 6, 2010

Recent Posts

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
Hitoshi Ozawa
RE: Data imported directly to the MySQL db don't show up on Liferay
December 24, 2012 3:25 PM
Answer

Hitoshi Ozawa

Rank: Liferay Legend

Posts: 7990

Join Date: March 23, 2010

Recent Posts

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.
Brian C
RE: Data imported directly to the MySQL db don't show up on Liferay
December 24, 2012 4:22 PM
Answer

Brian C

Rank: New Member

Posts: 6

Join Date: July 6, 2010

Recent Posts

At first, the bulk import part wasn't the initial project requirement. So, we developed our custom portlet using the service builder emoticon. 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)
David H Nebinger
RE: Data imported directly to the MySQL db don't show up on Liferay
December 25, 2012 7:36 AM
Answer

David H Nebinger

Rank: Liferay Legend

Posts: 6715

Join Date: September 1, 2006

Recent Posts

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.
Jack Bakker
RE: Data imported directly to the MySQL db don't show up on Liferay
December 25, 2012 8:48 AM
Answer

Jack Bakker

Rank: Liferay Master

Posts: 565

Join Date: January 3, 2010

Recent Posts

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 ?
Brian C
RE: Data imported directly to the MySQL db don't show up on Liferay
December 25, 2012 10:07 AM
Answer

Brian C

Rank: New Member

Posts: 6

Join Date: July 6, 2010

Recent Posts

David H Nebinger:
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.


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.

Jack Bakker:
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() )


Thanks Jack and Merry Christmas to you too! emoticon 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:

1        xXXUtil.clearCache();
2        CacheRegistryUtil.clear();
3        MultiVMPoolUtil.clear();
4        WebCachePoolUtil.clear();
5        EhcachePortalCacheManager portalCacheManager = new EhcachePortalCacheManager();
6        portalCacheManager.clearAll();


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

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


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
Jack Bakker
RE: Data imported directly to the MySQL db don't show up on Liferay
December 26, 2012 5:37 PM
Answer

Jack Bakker

Rank: Liferay Master

Posts: 565

Join Date: January 3, 2010

Recent Posts

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) ?
Brian C
RE: Data imported directly to the MySQL db don't show up on Liferay
December 27, 2012 9:46 AM
Answer

Brian C

Rank: New Member

Posts: 6

Join Date: July 6, 2010

Recent Posts

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
Jack Bakker
RE: Data imported directly to the MySQL db don't show up on Liferay
December 31, 2012 10:52 AM
Answer

Jack Bakker

Rank: Liferay Master

Posts: 565

Join Date: January 3, 2010

Recent Posts

Thanks Brian (in Richmond Hill, Ontario?), definition of symptoms of your challenge taking shape. I still need to set the environment and explore further.
Jack Bakker
RE: Data imported directly to the MySQL db don't show up on Liferay
March 1, 2013 1:54 PM
Answer

Jack Bakker

Rank: Liferay Master

Posts: 565

Join Date: January 3, 2010

Recent Posts

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...)
Hitoshi Ozawa
RE: Data imported directly to the MySQL db don't show up on Liferay
March 2, 2013 3:06 AM
Answer

Hitoshi Ozawa

Rank: Liferay Legend

Posts: 7990

Join Date: March 23, 2010

Recent Posts

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.