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
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
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.
The upgrade was failing because our Sybase installation did not default to allow nulls when altering or creating tables.
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)
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
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
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.tags: linux - sybase - 16 - ase - Liferay - EE - 6.1 - 6.2 - database - upgrade