PUBLIC - Liferay Portal Community Edition

External portlet datasource doesn't auto create schema

Details

  • Fix Priority:
    3
  • Similar Issues:
    Show 5 results 

Description

Liferay Portal Community Edition 6.1.1 CE GA2 (Paton / Build 6101 / July 31, 2012)

I'm building a portlet for my project, the portlets has a service.xml with multiple entities, all entities us an alternative datasource. The biggest part is working fine, finders work when using only one column. When i use 2 column then liferay doesn't use the data-source of the entity but searches inside it's own database.

Example, i have a page with searchcontainer, the row are standard filter with 

workItems = WorkItemLocalServiceUtil.getWorkItemByProjectId(projectId, searchContainer
						.getStart(), searchContainer.getEnd(), searchContainer.getOrderByComparator());

That works fine, there is only one column called projectId which is defined as a finder column in the service.xml, this query is executed on the database "dashboard".

But when a user wants to search through the data, they enter a value in the search form and then an other finder is used: 

workItems = WorkItemLocalServiceUtil.getWorkItemBySearchName_ProjectId(searchByName,
						projectId, searchContainer.getStart(), searchContainer.getEnd(), searchContainer
								.getOrderByComparator());

Then i get an JDBCException:
09:33:07,297 ERROR [http-bio-8080-exec-107][JDBCExceptionReporter:76] Table 'dashboard_portal_nico.workitem' doesn't exist
com.liferay.portal.kernel.exception.SystemException: com.liferay.portal.kernel.dao.orm.ORMException: org.hibernate.exception.SQLGrammarException: could not execute query

Code:
// get items by search input or get all workitems

			if (searchByName != null && searchByName != "") {
				workItems = WorkItemLocalServiceUtil.getWorkItemBySearchName_ProjectId(searchByName,
						projectId, searchContainer.getStart(), searchContainer.getEnd(), searchContainer
								.getOrderByComparator());
				workItemTotal = WorkItemLocalServiceUtil.countBySearchName_ProjectId(searchByName, projectId);
			} else {
				workItems = WorkItemLocalServiceUtil.getWorkItemByProjectId(projectId, searchContainer
						.getStart(), searchContainer.getEnd(), searchContainer.getOrderByComparator());
				workItemTotal = WorkItemLocalServiceUtil.getWorkItemByProjectId(projectId).size();
			}

Service Builder workitem entity:

<entity name="WorkItem" local-service="true" remote-service="true" data-source="dashboardDataSource" cache-enabled="false">

		<!-- PK fields -->

		<column name="workItemId" type="long" primary="true" />

		<!-- Audit fields -->

		<column name="name" type="String" />
		<column name="description" type="String" />
		<column name="state" db-name="state" type="String" />
		<column name="deadline" type="Date" />
		<column name="dateStart" type="Date" />
		<column name="dateEnd" type="Date" />
		<column name="dateCreated" type="Date" />
		<column name="dateUpdated" type="Date" />
		<column name="companyId" type="long" />
		<column name="type" type="String" db-name="type" />
		<column name="projectId" type="long" />
		<column name="parentWorkItemId" type="long" db-name="parentWorkItem" />
		<column name="reporterId" type="long" />
		<column name="assigneeId" type="long" />
		<!-- Relationships -->

		<column name="persons" type="Collection" entity="Person" mapping-table="workitemhasperson" />
		
		<!-- Order -->

		<order by="desc" >
			<order-column name="dateUpdated" order-by="desc" />
		</order>

		<!-- Finder methods -->
		<finder name="ProjectId" return-type="Collection" >
			<finder-column name="projectId" />
		</finder>
		<finder name="ParentWorkItem" return-type="Collection">
			<finder-column name="parentWorkItemId" />
		</finder>
		<finder name="SearchName_ProjectId" return-type="Collection"  >
			<finder-column name="name" comparator="LIKE"/>
			<finder-column name="projectId"  comparator="="/>
		</finder>
	</entity>

Activity

Ascending order - Click to sort in descending order
Hide
Permalink
Nico Bijl added a comment -

Okay i found more specific problem, the get function works, but the generated count function doesn't, i'll use get().size as alternative.

// get items by search input or get all workitems
			if (searchByName != null && searchByName != "") {
				workItems = WorkItemLocalServiceUtil.getWorkItemBySearchName_ProjectId(searchByName,
						projectId, searchContainer.getStart(), searchContainer.getEnd(), searchContainer
								.getOrderByComparator());
				workItemTotal = WorkItemLocalServiceUtil.getWorkItemBySearchName_ProjectId(searchByName, projectId).size();
			} else {
				workItems = WorkItemLocalServiceUtil.getWorkItemByProjectId(projectId, searchContainer
						.getStart(), searchContainer.getEnd(), searchContainer.getOrderByComparator());
				workItemTotal = WorkItemLocalServiceUtil.getWorkItemByProjectId(projectId).size();
			}
Show
Nico Bijl added a comment - Okay i found more specific problem, the get function works, but the generated count function doesn't, i'll use get().size as alternative. // get items by search input or get all workitems if (searchByName != null && searchByName != "") { workItems = WorkItemLocalServiceUtil.getWorkItemBySearchName_ProjectId(searchByName, projectId, searchContainer.getStart(), searchContainer.getEnd(), searchContainer .getOrderByComparator()); workItemTotal = WorkItemLocalServiceUtil.getWorkItemBySearchName_ProjectId(searchByName, projectId).size(); } else { workItems = WorkItemLocalServiceUtil.getWorkItemByProjectId(projectId, searchContainer .getStart(), searchContainer.getEnd(), searchContainer.getOrderByComparator()); workItemTotal = WorkItemLocalServiceUtil.getWorkItemByProjectId(projectId).size(); }
Hide
Permalink
Nico Bijl added a comment -

Solved it by creating my own finderImpl. In there i create a custom query and in the query i used the database name. (I added the database name to a config file and then you can access it inside your finder.) the sql then looked like this: "SELECT * FROM "dbName".tablename".

Hope thats enough explaination, if not, comment and i'll post some code.

Show
Nico Bijl added a comment - Solved it by creating my own finderImpl. In there i create a custom query and in the query i used the database name. (I added the database name to a config file and then you can access it inside your finder.) the sql then looked like this: "SELECT * FROM " dbName ".tablename". Hope thats enough explaination, if not, comment and i'll post some code.
Hide
Permalink
Hervé Ménage added a comment -

Thank you Nico,

With your approach, I do not see a big value in using Liferay SDK. Is your finder invoked from the LocalImpl class instead of calling the generated persistence class?

Regards
Hervé

Show
Hervé Ménage added a comment - Thank you Nico, With your approach, I do not see a big value in using Liferay SDK. Is your finder invoked from the LocalImpl class instead of calling the generated persistence class? Regards Hervé
Hide
Permalink
Juan G added a comment -

Thank you for the report. As part of our community verifier program, I am taking a look at this issue. If it is no longer a concern or you have additional information that will help in troubleshooting it, please let me know.

Show
Juan G added a comment - Thank you for the report. As part of our community verifier program, I am taking a look at this issue. If it is no longer a concern or you have additional information that will help in troubleshooting it, please let me know.
Hide
Permalink
Juan G added a comment -

Ok, so here are my findings.

After testing with a portlet pointing to external database (will upload test portlet in few minutes and is using almost the same service.xml as yours), the SQL scripts weren't executed, so no tables were created at all, and an "Table xxxx doesn't exist" error was thrown.

But this error went away whenever I created the table. The portlet executed sucessfully.

So the error would be that tables aren't created when external database is used, even when

build.auto.upgrade=true

is set in service.properties.

This error is reproducible in 6.1.1 and 6.2.0 CE M4.

Show
Juan G added a comment - Ok, so here are my findings. After testing with a portlet pointing to external database (will upload test portlet in few minutes and is using almost the same service.xml as yours), the SQL scripts weren't executed, so no tables were created at all, and an "Table xxxx doesn't exist" error was thrown. But this error went away whenever I created the table. The portlet executed sucessfully. So the error would be that tables aren't created when external database is used, even when build.auto.upgrade=true is set in service.properties. This error is reproducible in 6.1.1 and 6.2.0 CE M4.
Hide
Permalink
Juan G added a comment -

Portlet used for testing

Show
Juan G added a comment - Portlet used for testing
Hide
Permalink
Juan G added a comment -

Reproducible in 6.1.1 CE and 6.2.0 CE M4

Show
Juan G added a comment - Reproducible in 6.1.1 CE and 6.2.0 CE M4
Hide
Permalink
Juan G added a comment -

For testing, simply create another database, and then add the datasource in <TOMCAT>/conf/context.xml like this:

 
<Resource
		name="jdbc/SamplePool"
		auth="Container"
		type="javax.sql.DataSource"
		driverClassName="com.mysql.jdbc.Driver"
		url="jdbc:mysql://localhost:3306/<YOUR_DATABASE>?useUnicode=true&amp;characterEncoding=UTF-8"
		username="<USER>"
		password="<PASSWORD>"
		maxActive="20"
	/>

Then startup tomcat (better not use Eclipse because context.xml would be overwritten) and deploy test portlet.

Show
Juan G added a comment - For testing, simply create another database, and then add the datasource in <TOMCAT>/conf/context.xml like this: <Resource name= "jdbc/SamplePool" auth= "Container" type= "javax.sql.DataSource" driverClassName= "com.mysql.jdbc.Driver" url= "jdbc:mysql://localhost:3306/ <YOUR_DATABASE> ?useUnicode=true&amp;characterEncoding=UTF-8" username= " <USER> " password= " <PASSWORD> " maxActive= "20" /> Then startup tomcat (better not use Eclipse because context.xml would be overwritten) and deploy test portlet.
Hide
Permalink
Miguel Pastor added a comment -

Hey guys,

This is not a real bug but an unsupported feature.

Service builder is not creating the corresponding SQL files for those entities which are using a different datasource; you should create it manually before deploying your portlet.

As a simple workaround you can point your entities to the default dataSource and Liferay will generate the corresponding SQL, then you can copy it and execute in your own database. Take care if you are running Liferay datasource and portlet datasource against to different BBDD (for example, MySQL and Oracle)

Show
Miguel Pastor added a comment - Hey guys, This is not a real bug but an unsupported feature. Service builder is not creating the corresponding SQL files for those entities which are using a different datasource; you should create it manually before deploying your portlet. As a simple workaround you can point your entities to the default dataSource and Liferay will generate the corresponding SQL, then you can copy it and execute in your own database. Take care if you are running Liferay datasource and portlet datasource against to different BBDD (for example, MySQL and Oracle)

People

Vote (0)
Watch (1)

Dates

  • Created:
    Updated:
    Resolved:
    Days since last comment:
    9 weeks, 6 days ago