Blogs

Platform

Liferay Portal 7.x

Two Liferay Rules in an Example

An example why violating Liferay's database rules can get you into deep trouble.

David H Nebinger

February 01, 2021

3 Minute Read

A community member posted the following plea on the Liferay Community Slack channel. I feel that it makes a great example, so I'm including it here.

Hi
I am migrating from Liferay 7.0 to Liferay 7.3 GA6
So I followed the below steps for DB migration

1 ./opt/liferay736/bundle/tools/portal-tools-db-upgrade-client
Inside the directory, I have updated the following files as per the document

1. app-server.properties
2. portal-upgrade-database.properties
3. portal-upgrade-ext.properties

2. I executed following command in terminal ./db_upgrade.sh -j "-Dfile.encoding=UTF-8 -Duser.country=US -Duser.language=en -Duser.timezone=GMT -Xmx8000m"

3. Command started execution and after some period of time, I got the following error

com.liferay.portal.kernel.upgrade.UpgradeException: java.sql.SQLException: Error on rename of './vmeeting/#sql-799_13e3' to './vmeeting/organization_' (errno: 150 - Foreign key constraint is incorrectly formed)

I compared Liferay 7.0 DB organization_ table with Liferay 7.3 GA6 table, in Liferay 7.3 GA6 we have few new columns with composite primary keys

I came to know, the upgrade tool is trying to alter the table by adding a new primary along with an old primary key (composite primary keys)

Since MySQL doesn't allow to change primary key we are getting that error

Here are log messages

Caused by: java.sql.SQLException: Error on rename of './vmeeting/#sql-799_13e3' to ./vmeeting/organization_' (errno: 150 - Foreign key constraint is incorrectly formed)
  at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129)
  at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
  at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
  at com.mysql.cj.jdbc.StatementImpl.executeUpdateInternal(StatementImpl.java:1355)
  at com.mysql.cj.jdbc.StatementImpl.executeLargeUpdate(StatementImpl.java:2128)
  at com.mysql.cj.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1264)
  at com.zaxxer.hikari.pool.ProxyStatement.executeUpdate(ProxyStatement.java:117)
  at com.zaxxer.hikari.pool.HikariProxyStatement.executeUpdate(HikariProxyStatement.java)
  at com.liferay.portal.dao.db.BaseDB.runSQL(BaseDB.java:337)
  at com.liferay.portal.dao.db.BaseDB.runSQL(BaseDB.java:303)
  at com.liferay.portal.kernel.dao.db.BaseDBProcess.runSQL(BaseDBProcess.java:71)
  at com.liferay.portal.kernel.upgrade.UpgradeProcess.removePrimaryKey(UpgradeProcess.java:642)
  at com.liferay.portal.kernel.upgrade.UpgradeCTModel._upgradeCTModel(UpgradeCTModel.java:112)
  at com.liferay.portal.kernel.upgrade.UpgradeCTModel.doUpgrade(UpgradeCTModel.java:47)
  at com.liferay.portal.kernel.upgrade.UpgradeProcess.upgrade(UpgradeProcess.java:104)
  ... 15 more

Please guide me
Thanks in advance

So Rule #1, as Olaf and I are both fond of saying, is Never look inside the Liferay database.

Rule #2 often goes unsaid, but is really much more important: Never, ever, under any circumstances, make changes to any Liferay tables, columns, indexes, etc.

And if there's a Rule #3, it is probably Go back and re-read rules 1 and 2.

As soon as someone decided it was a good idea to change the Liferay database, this team was doomed.

They didn't just add a column to Liferay, they created their own fork of Liferay. As soon as they did that, they were completely on their own.

And the thing is, Liferay provides facilities that the team could have used instead of adding columns and keys to the Liferay database...

If they had additional fields, they could have used custom fields. If they had a lot of them or they wanted to build some sort of compound key mechanism, they could have used a separate ServiceBuilder entity to hold the key. I can't tell you how many times I've had a CustomUser entity with a single primary key column named userId, where the Liferay table had its thing and my CustomUser table had its own thing, but they both shared the same userId key value.

Liferay never uses foreign keys at all. ServiceBuilder does not even have a facility to define or maintain foreign keys. They are shunned by the platform.

Why? Well, as in this case, they get in the way when Liferay needs to make table changes because of new capabilities introduced in later versions. Liferay long ago learned that the value from defining a foreign key was quickly lost when trying to apply data model changes during upgrades. Additionally, since Liferay uses a search index in conjunction with a database, cascading delete capabilities of foreign keys would not be reflected in corresponding index updates, leading the database and the index to be out of sync.

As a new developer to Liferay, you're not expected to understand why Liferay doesn't use foreign keys and why they don't use cascading deletes. In fact you're not expected to understand why Liferay has made any of the decisions they have made during the implementation of the product. And there will come a time when you do understand why Liferay has made certain decisions and you may disagree, sometimes vehemently.

In either case, you are expected to understand that Liferay puts a great deal of thought and analysis into the design and architecture of every piece of Liferay and that all decisions were made for a reason.

But that's okay, the platform is extensible so you can tweak it in ways to fall in line with your requirements. But when you take on this extensive of customization, realize that you are effectively forking Liferay and will be taking on all of the responsibilities of maintaining and updating the code.

Unfortunately the community member is left in a bad predicament. They can't move forward with the upgrade because Liferay can't handle the changes they've made. If I were on their team, I'd want to start from "fixing" their 7.0 environment to undo these changes and customizations that were introduced. Create an SB project to hold the additional organization details and then build an upgrade process that populates the table from the changed organization_ table, and it ends by dropping whatever table changes that were made to the table(s). Add a service wrapper or model listener to the Organization entity to handle cascade delete concerns to the new entity. Combine that with the coding effort to look for these values from the new SB entities, and basically get to a working 7.0 environment that is standard Liferay+extensions, no more heavy customizations.

Once that is done, then approach the 7.0 -> 7.3 upgrade. Since the 7.0 would be standard, the db upgrade tool will be able to handle it.

This is really the only solution. I mean, could you manually figure out how to work around this by temporarily dropping foreign keys, reducing the custom compound key back to a regular PK, then do the upgrade and re-apply all of this stuff? Sure, that might be possible. But do you really want to jump through all of those hoops again when 7.4 comes out? And again for 7.5? or 8.0? Or whatever Liferay wants to call it? Unless you truly fix the environment, you're looking at a hard road.

6 Comments

Please sign in to comment.

Come on! As Liferay was a bug-free product!

I could understand if you say.. "do it at your own risk" or whatever... but as an user of Liferay CE since Liferay 4.2 I had to dive into de DB sooo many times to solve bugs (Malfunction, stuck upgrade processes ... ). And that's OK!!

And if you change something you are on your own.

I have learnt a lot diving into DB and I would suggest just the opposite to "Never look inside the Liferay database".

Always look into DB!! YO will learn a lot.

I thint that's not the way to build and challenge a community at all. An open source community.

At least in this article you don't say "our db is not ours"

Please sign in to reply.

Hi, Aritz. When I post these blogs, they're not really for you, my friend. After working with Liferay for so long I'm sure you recognize the patterns, can predict where something is going sideways and yes, have gone into the database successfully and made changes. I don't begrudge you that level of detail because I know that with so much experience, you understand the implications of everything you are about to do.

I do write the blogs though for new people coming to the platform, the ones without the experience of you or I. To those that don't know the implications that can come from adding a column to a Liferay table or deleting rows directly w/o updating the index or even changing data without knowing how Liferay handles code-enforced row relationships.

For those folks, they really can get themselves in trouble. Like the poster of the message I was writing about, his team is in a real pickle now with no simple way to move forward. At the time when they started the project, had they followed these two rules, they wouldn't be in the shape they are now.

That's who this blog is for, the ones that don't know what they don't know, the ones that need some guide rails otherwise they could drive themselves off the edge of the road.

Please sign in to reply.

Well. Then I agree. :) Thanks David

Please sign in to reply.

I agree, looking into the database will help a lot, especially when dealing with newer features.

It's okay to look into the database and make assumptions based on what you learned. But always find a way to verify those assumptions both now and in future versions.

E.g., run some smoke tests after a database upgrade for every little thing of yours that needs knowledge of the database.

If you have no way of continously checking your assumptions, they're worth nothing.

Please sign in to reply.

Thanks David, that's the correct approach.

Please sign in to reply.

I will add that many years ago, when I was in Support, I helped a customer with an upgrade that was failing? The reason? They had added custom columns into an existing Liferay table. The upgrade worked fine once the tables were removed, so the best way for them moving forward would be to use Custom Fields or Service Builder.

Please sign in to reply.