Deploying users

[Matt Jackson, modifié il y a 14 années.]

Hi,

I'm currently developing with Liferay, and I have a few different instances running. Porting portlets and content across these instances with WAR and LAR files has been very easy, and I'm hoping to find a way of making it this easy to transfer users, groups, organisations, communities and roles across instances in a similar way.

After some investigation, its become obvious that theres no in-built way of doing this with Liferay, and that all of these details are stored in the Liferay database. However, most of my Liferay instances at present use the default hsql for their databases, and all the data for these databases are stored in convenient SQL form in the lportal.script file.

What I'd like some help with is identifying which tables in the Liferay database contain all the linked data regarding users, groups, organisations, communities and roles. With this information, it should be easy enough to write a script to filter the lportal.script file for INSERTs and UPDATEs to the relevant tables.

Obviously, tables like user_, group_, community_, organisation_ and role_ will be required, but what other tables contain link data essential to transferring? If possible, I would also like to preserve all the permissions data associated with roles.

Of course, if someone can think of a better way of achieving what I'm asking, I'm open to suggestions :-)


Thanks!

[Matt Jackson, modifié il y a 14 années.]

Update:

I have attempted to do this by copying over the contents of the user_, usergrouprole, roles_permissions, permission_, organization_, group_, contact_, users_groups, users_orgs, users_roles, groups_orgs, groups_usergroups, role_ and usergroup tables.

I have done this with the following bash script, executed from {liferay folder}/data/hsql/ on the server I'm exporting from:

#!/bin/sh

grep -i "INSERT INTO user_" ./lportal.script > ~/portal_users_data.sql
grep -i "INSERT INTO usergrouprole" ./lportal.script >> ~/portal_users_data.sql
grep -i "INSERT INTO rolespermissions" ./lportal.script >> ~/portal_users_data.sql
grep -i "INSERT INTO permissions_" ./lportal.script >> ~/portal_users_data.sql
grep -i "INSERT INTO organization_" ./lportal.script >> ~/portal_users_data.sql
grep -i "INSERT INTO group_" ./lportal.script >> ~/portal_users_data.sql
grep -i "INSERT INTO contact_" ./lportal.script >> ~/portal_users_data.sql
grep -i "INSERT INTO users_groups" ./lportal.script >> ~/portal_users_data.sql
grep -i "INSERT INTO users_orgs" ./lportal.script >> ~/portal_users_data.sql
grep -i "INSERT INTO users_roles" ./lportal.script >> ~/portal_users_data.sql
grep -i "INSERT INTO groups_orgs" ./lportal.script >> ~/portal_users_data.sql
grep -i "INSERT INTO groups_usergroups" ./lportal.script >> ~/portal_users_data.sql
grep -i "INSERT INTO role_" ./lportal.script >> ~/portal_users_data.sql
grep -i "INSERT INTO usergroup" ./lportal.script >> ~/portal_users_data.sql

#append semicolons to lines
sed -e 's/$/;/' ~/portal_users_data.sql > temp.txt
cat temp.txt > ~/portal_users_data.sql
rm temp.txt

When I run the resultant database script on the server I'm importing to, I get many successful inserts, and a few failures due to duplicate keys (probably the default users). I can see that I now have many more users in my user_ table, but when I launch my liferay control panel, I don't see *any* of these users, nor any of the orgs, communities, roles or groups which I meant to import.

Can anyone tell me where I'm going wrong?

[Amos Fong, modifié il y a 14 années.]

Hi Matt,

Have you tried reindexing or clearing cache in Control Panel > Server Administration?

[Matt Jackson, modifié il y a 14 années.]

Hi Amos,

Thanks for the suggestion - unfortunately this seems to have no effect!

I find it hard to believe no-one has ever wanted to do this before. Is there no generally accepted way of porting over users from one installation to another?

[Amos Fong, modifié il y a 14 années.]

Your companyId fields probably don't match if you migrated them from the other database. So they won't show up as users in your company/portal instance. Check the companyId of a user you can see and all the ones you can't to see if they match.

Otherwise, are there any exceptions in the logs?

[Matt Jackson, modifié il y a 14 années.]

Amos Fong:

Your companyId fields probably don't match if you migrated them from the other database.

This was the problem, thank you very much. I'm currently working on a script to automate the whole procedure, which I'll post here when done.

Thanks again!

[Matt Jackson, modifié il y a 14 années.]

OK, I attempted to write a script to automate the procedure of copying *just the relevant tables*, then ran into numerous problems to do with cross-table relationships.

Ultimately, I decided to take a blank postgres database, and run the statements held in the lportal.script file. A few modifications had to be made to the lportal.script SQL to make it work with my database (postgresql).

Firstly, I ran the following on it:

$ cat lportal.script | grep -v '^SET' | grep -v '^CREATE USER' | grep -v '^GRANT' | sed -e 's/CREATE MEMORY TABLE/CREATE TABLE/g'|awk '{printf("%s;\n", $0); }'

Then I did a few additional substitutions on datatypes to make them work with postgres:

longvarchar -> text
binary -> bytea
double -> numeric

I also commented out the first line of lportal.script ('CREATE SCHEMA PUBLIC AUTHORIZATION DBA')

Running the resultant script on my postgresql database was successful, with all the data being ported across.

I then told my liferay installation to look at the postgres database by changing my portal-ext.properties:

jdbc.default.driverClassName=org.postgresql.Driver
jdbc.default.url=jdbc:postgresql://localhost:5432/lportal
jdbc.default.username=lportal
jdbc.default.password=xxx

Unforutnately, when firing up the liferay server with these settings, I get the following in my log:

20-Aug-2009 08:27:17 org.apache.catalina.core.StandardHostValve custom
SEVERE: Exception Processing ErrorPage[errorCode=404, location=/errors/404.jsp]
org.apache.jasper.JasperException: java.lang.NullPointerException
	at org.apache.jasper.servlet.JspServletWrapper.handleJspException(JspServletWrapper.java:522)
	at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:416)
	at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:342)
	at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:267)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
	at org.apache.catalina.core.ApplicationDispatcher.invoke(ApplicationDispatcher.java:630)
	at org.apache.catalina.core.ApplicationDispatcher.processRequest(ApplicationDispatcher.java:438)
	at org.apache.catalina.core.ApplicationDispatcher.doForward(ApplicationDispatcher.java:374)
	at org.apache.catalina.core.ApplicationDispatcher.forward(ApplicationDispatcher.java:302)
	at org.apache.catalina.core.StandardHostValve.custom(StandardHostValve.java:424)
	at org.apache.catalina.core.StandardHostValve.status(StandardHostValve.java:343)
	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:144)
	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:286)
	at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:845)
	at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:583)
	at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:447)
	at java.lang.Thread.run(Thread.java:619)
Caused by: java.lang.NullPointerException
	at com.liferay.portal.util.PortalImpl.getHomeURL(PortalImpl.java:937)
	at com.liferay.portal.util.PortalUtil.getHomeURL(PortalUtil.java:297)
	at org.apache.jsp.errors._404_jsp._jspService(Unknown Source)
	at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
	at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:374)
	... 19 more

I can't understand why this is happening, as the new postgresql database has exactly the same structure and content as the hsql database.

Can anyone offer any suggestions why this is not working correctly?

[Matt Jackson, modifié il y a 14 années.]

OK, slightly embarassing but the solution to my above problem was that the database user assigned to my installation didn't have the necessary permissions. After correcting this, restarting the server brought the liferay server online - but caused some strange problems.

Firstly, postgresql gave some strange error messages (key constraint violations). Liferay itself worked, but many of my communities had missing or muddled-up content. Some content also appeared in the wrong community. Restoring the content from .lar files fixed this issue, and now the server runs fine, with all the same data that was previously coming from hsql, now coming from Postgresql.

I am curious to know what caused the strange problems when I first brought the server online (if anyone knows please post here), but essentially my problems are solved. Thanks for the help.

[Lisa Simpson, modifié il y a 14 années.]

Postgres works differently than hipersonic. Your better bet is to point Liferay to a completely empty Postgres database where the database user has GRANT ALL rights on that database. Restart Liferay. At that point, it will create the tables & relationships. The key constraints are what are causing your missing and muddled up content

[Matt Jackson, modifié il y a 14 années.]

Thank you, but what I specifically want to do (and have now achieved) is to port the content already entered into my hsql database over to my postgresql database.

I understand that if I wanted to start my liferay instance from scratch, the best thing to do would just be to give Liferay the database details and let it populate the data itself, but it is important in my case to preserve the data already entered into the hsql db.

Interestingly, the only problem I am having at the moment with my newly-ported data is that when trying to edit a web content display portlet with the 'edit' button on the portlet, I receive the error

15:19:22,408 WARN  [PortalImpl:2948] Current URL /web/guest/home?p_p_id=15&p_p_lifecycle=0&p_p_state=maximized&p_p_mode=view&p_p_col_id=header&p_p_col_count=1&_15_struts_action=%2Fjournal%2Fedit_article&_15_redirect=%2Fweb%2Fguest%2Fhome&_15_groupId=16&_15_articleId=12046&_15_version=1.0 generates exception: com.liferay.portal.kernel.xml.DocumentException: Error on line 1 of document  : Content is not allowed in prolog. Nested exception: Content is not allowed in prolog.

in my browser and in the <liferay dir>/tomcat/logs/catalina.out log. I am able to modify the web content portlets normally from the control panel

Any ideas why this might be happening?