Code Excursion

Upgrading from Liferay 6.1 to 6.2 Part 1

A few years back I inherited a Liferay 6.1 installation which we are now in the process of upgrading. The road hasn’t been as smooth as I expected largely due to referential integrity being enforced more stringently in Liferay 6.2. Previous versions of Liferay also had referential integrity but it was more convention than enforcement. The Liferay system that I am working with has already gone through at least one other upgrade and has been worked on by a dozen different programmers. In other words it has been around a while and has accumulated quite a bit of cruft. The first sign that this was going to be issues were error messages in the log files complaining about non existant UserGroups. Excerpts of these errors are shown below.

Log File Errors

16:49:38,710 ERROR [http-bio-8080-exec-1][GroupImpl:392] com.liferay.portal.kernel.exception.SystemException: com.liferay.portal.NoSuchUserGroupException: No UserGroup exists with the primary key 123

16:49:42,054 ERROR [http-bio-8080-exec-2][IncludeTag:129] Current URL /group/usarec-home/usarec generates exception: An exception occurred processing JSP page /html/portlet/dockbar/view_my_sites.jspf at line 17__14:  */_15: --%>_16: _17: <c:if test="<%= user.hasMySites() %>">_18:  <aui:nav-item cssClass="my-sites" dropdown="<%= true %>" id="mySites" label="my-sites" wrapDropDownMenu="<%= false %>">_19:     <liferay-ui:my-sites classNames="<%= new String[] {Group.class.getName(), Organization.class.getName()} %>" cssClass="dropdown-menu my-sites-menu" />_20:   </aui:nav-item>___Stacktrace: [Sanitized]

These errors occurred during startup of the Liferay 6.2 server post upgrade. This upgrade was performed on a XFCE Fedora Core 23 workstation running Liferay 6.1 on Apache Tomcat 7 with a Mariadb database. Our solution to this problem was to run the following SQL query against the database. This action was more or less confirmed by Liferay.

Clean up database

Delete from Users_UserGroups where userGroupId not in (select usergroupid from UserGroup);

Unfortunately we didn’t even get this far when we tried upgrading our development environment with Was running Liferay 6.1 on Apache Tomcat 7 with a Sybase ASE 16 database. When upgrading this installation of liferay we immediately started seeing errors similar to the following.

Database errors in log file.

14:30:32,712 INFO  [localhost-startStop-1][BaseDB:484] Database does not support case sensitive queries
14:30:33,270 INFO  [localhost-startStop-1][UpgradeProcess:164] Upgrading com.liferay.portal.upgrade.UpgradeProcess_6_2_0
14:30:33,271 INFO  [localhost-startStop-1][UpgradeProcess:164] Upgrading com.liferay.portal.upgrade.v6_2_0.UpgradeSchema
14:30:33,429 WARN  [localhost-startStop-1][BaseDB:457] ALTER TABLE 'BookmarksEntry' failed. Default clause is required in order to add non-NULL column 'status'.: alter table BookmarksEntry a
dd status int;_ [Sanitized]
14:30:33,436 WARN  [localhost-startStop-1][BaseDB:457] ALTER TABLE 'BookmarksEntry' failed. Default clause is required in order to add non-NULL column 'statusByUserId'.: alter table Bookmark
sEntry add statusByUserId decimal(20,0)_go_ [Sanitized]

14:30:34,718 WARN  [localhost-startStop-1][BaseDB:457] Neither the 'select into' nor the 'full logging for alter table' database options are enabled for database 'MYAPP_DB1'. ALTER TABLE with data copy cannot be done.: alter table DLFileEntry drop versionUserId;_ [Sanitized]
14:30:34,736 WARN  [localhost-startStop-1][BaseDB:457] Neither the 'select into' nor the 'full logging for alter table' database options are enabled for database 'MYAPP_DB1'. ALTER TABLE with data copy cannot be done.: alter table DLFileEntry drop versionUserName;_ [Sanitized]

The upgrade was failing because our Sybase installation did not default to allow nulls when altering or creating tables.

Reconfigure Sybase

EXECUTE sp_dboption MYAPP_DB1,'allow nulls by default',  'TRUE'
EXECUTE sp_dboption MYAPP_DB1,'select into/bulkcopy/pllsort',  'TRUE'

After that things went more smoothly but there were still referential integrity issues, involving companyId, preventing the upgrade process from completing. The solution to this was to run a query to find all tables which have a companyId column and delete any records containing companyIds that were not in the Company table and not zero or null. You could also try updating the companyId to an existing Company. In my case the data appeared to be orphaned and I went with deletion.

List all tables with a companyId column. (Sybase)

select distinct name from sysobjects
where id in (select id from syscolumns where name = 'companyId')
order by name;

Taking the resulting list and manipulating it in Vim I created a query to give me a report on a list of tables that have mismatched companyIds. The next code snippet has an excerpt of such a query.

Finding Problems In Database

SELECT 'Account_',  count(*) FROM myapp_user.Account_ WHERE companyId NOT IN (select companyId from myapp_user.Company) and companyId IS NOT NULL and companyId <> 0
union
SELECT 'Address',  count(*) FROM myapp_user.Address WHERE companyId NOT IN (select companyId from myapp_user.Company) and companyId IS NOT NULL and companyId <> 0
union
SELECT 'AnnouncementsDelivery',  count(*) FROM myapp_user.AnnouncementsDelivery WHERE companyId NOT IN (select companyId from myapp_user.Company) and companyId IS NOT NULL and companyId <> 0
union
SELECT 'AnnouncementsEntry',  count(*) FROM myapp_user.AnnouncementsEntry WHERE companyId NOT IN (select companyId from myapp_user.Company) and companyId IS NOT NULL and companyId <> 0;

Once we identified those tables with mismatched companyIds we deleted the records. Once this issue was resolved we were able to successfully complete the upgrade. With one exception. Dropping and rebuilding the indexes failed to complete. We had to go back and manually drop the indexes and recreate them. This brought us up to the first error shown in this article and once again we deleted any records in the Users_UserGroups table which contained ids not in the User_ and UserGroup tables.

Now we are up and running and all our worries are over. Right? Unfortunately that was not the case. Referential integrity issues once again reared its ugly head several times during indexing. We encounter errors similar to the one below for the Contact_ table and a few others.

Indexing error in log file

20:07:52,222 ERROR [ajp-bio-8009-exec-5][LuceneIndexer:142] Error encountered while reindexing
com.liferay.portal.kernel.search.SearchException: com.liferay.portal.NoSuchUserException: No User exists with the key {contactId=11503}
		at com.liferay.portal.kernel.search.BaseIndexer.getDocument(BaseIndexer.java:181)

We deleted the records referencing non-existant foreign keys. Finally the log files had stopped producting referential integrity errors. This made us curious and we decided to create queries for UserGroups, Groups and Roles just like we did for Companies. We found several issues with non-existent foreign keys and will delete the offending records. We had considered doing the same with userIds but quickly became apparent as a bad idea. Users come and go but the relevance of the content they create often stays.