Vista combinada Visión Plana Vista de árbol
Discusiones [ Anterior | Siguiente ]
toggle
Brian C
Data imported directly to the MySQL db don't show up on Liferay
24 de diciembre de 2012 12:02
Respuesta

Brian C

Ranking: New Member

Mensajes: 6

Fecha de incorporación: 6 de julio de 2010

Mensajes recientes

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
24 de diciembre de 2012 14:17
Respuesta

Hitoshi Ozawa

Ranking: Liferay Legend

Mensajes: 7952

Fecha de incorporación: 23 de marzo de 2010

Mensajes recientes

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
24 de diciembre de 2012 15:09
Respuesta

Brian C

Ranking: New Member

Mensajes: 6

Fecha de incorporación: 6 de julio de 2010

Mensajes recientes

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
24 de diciembre de 2012 15:25
Respuesta

Hitoshi Ozawa

Ranking: Liferay Legend

Mensajes: 7952

Fecha de incorporación: 23 de marzo de 2010

Mensajes recientes

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
24 de diciembre de 2012 16:22
Respuesta

Brian C

Ranking: New Member

Mensajes: 6

Fecha de incorporación: 6 de julio de 2010

Mensajes recientes

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
25 de diciembre de 2012 7:36
Respuesta

David H Nebinger

Community Moderator

Ranking: Liferay Legend

Mensajes: 8971

Fecha de incorporación: 1 de septiembre de 2006

Mensajes recientes

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
25 de diciembre de 2012 8:48
Respuesta

Jack Bakker

Ranking: Liferay Master

Mensajes: 787

Fecha de incorporación: 3 de enero de 2010

Mensajes recientes

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
25 de diciembre de 2012 10:07
Respuesta

Brian C

Ranking: New Member

Mensajes: 6

Fecha de incorporación: 6 de julio de 2010

Mensajes recientes

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
26 de diciembre de 2012 17:37
Respuesta

Jack Bakker

Ranking: Liferay Master

Mensajes: 787

Fecha de incorporación: 3 de enero de 2010

Mensajes recientes

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
27 de diciembre de 2012 9:46
Respuesta

Brian C

Ranking: New Member

Mensajes: 6

Fecha de incorporación: 6 de julio de 2010

Mensajes recientes

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
31 de diciembre de 2012 10:52
Respuesta

Jack Bakker

Ranking: Liferay Master

Mensajes: 787

Fecha de incorporación: 3 de enero de 2010

Mensajes recientes

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
1 de marzo de 2013 13:54
Respuesta

Jack Bakker

Ranking: Liferay Master

Mensajes: 787

Fecha de incorporación: 3 de enero de 2010

Mensajes recientes

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
2 de marzo de 2013 3:06
Respuesta

Hitoshi Ozawa

Ranking: Liferay Legend

Mensajes: 7952

Fecha de incorporación: 23 de marzo de 2010

Mensajes recientes

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.