How to get Service Builder to ALTER my tables?

[Marcus Hjortzén，修改在9 年前。]

Hi!

I've generated a model with the help of service builder. Now in release 2 I would like to add a new attribute by defining a new column in service.xml.
When installing the plugin the portal correctly identifies that the build number is greater than before and an upgrade is necessary. However, it then goes on with trying to execute the create table-statement, which of course fails since the table already exists (but without the new column) in the database.

Is there a way to get the Service Builder to generate ALTER-statements that are executed upon plugin installation?

I should add: We're using maven and not executing all goals (i.e. we're doing a "mvn clean liferay:build-service package liferay:deploy")

Kind regards,
Marcus

[Krzysztof Gołębiowski，修改在9 年前。]

Hello Marcus,
Service Builder do not ALTER database tables ever. Service plugin upgrade (in case if model has been chaneged) behaves in the following way:

• Dumps all service-related tables to temporary folder
• Drops all service-related tables
• Creates tables with newly-generated sql file
• Loads dumped data into new tables

If it tries to create new table on the top of existing one, then I suppose something went wrong :/ This mechanism is far from being ideal, so if you have many database changes then sometimes it may be even simpler to write custom ALTERing SQL within some portlet startupAction or UpgradeProcess.

What version of maven plugin do you use? I remember in 6.1.X there were few service-generation related bugs (like loosing model-hints data).

Regards,
KG

[Marcus Hjortzén，修改在9 年前。]

Hello KG and thank you for a swift reply!

I was certain that altering the tables was something Liferay had done previously but as you described the process it seems so much more reliable!
Anyway, we're using maven plugin version 6.2-ee-sp1, if that says anything.
This is what happens when deploying the plugin:

[ServiceComponentLocalServiceImpl:322] Upgrading UUFeatureToggle database to build number 30
[ServiceComponentLocalServiceImpl:329] Upgrading database with tables.sql
[BaseDB:457] Table 'uufeaturetoggle_feature' already exists: create table UUFeatureToggle_Feature (uuid_ varchar(75) null,featureId bigint not null primary key,userId bigint,userName varchar(75) null,createDate datetime null,modifiedDate datetime null,groupId bigint,key_ varchar(75) null,state_ integer,description varchar(500) null,blajj varchar(75) null) engine InnoDB;

Anyway, I can manage this small change with a manual alter-statement, however for the future it would be nice to have the support from service builder.

[Krzysztof Gołębiowski，修改在9 年前。]

I haven't user Service Builder for 6.2 yet so there may be some differences. But as I remember from 6.1, this message is a standard warning which appears when database is being updated (it just says that ang goes on ) - if there are no errors in console then everything should be fine. Did you check your database after upgrade? Are the new columns present there?

KG

[Marcus Hjortzén，修改在9 年前。]

The new column wasn't present, regretfully. So at next access of the plugin the log just vomits with stack traces!
Anyway, I'll have to settle to handle the db-change manually!

//Marcus

[Abhed Dekavadiya，修改在9 年前。]

Hello Marcus, Krzysztof,

I am facing a similar issue on Liferay 6.1-CE version.
We have two different hooks for different set of services for the project. One hook is mainly for our mobile application and contains many tables required for mobile app. Another hook is for all common and web related services, it also has many tables. Both are using Liferay's service builder.

Scenario-1: After any changes to service.xml file, those changes are getting reflected in database for mobile web services hook. But same doesn't work with common services hook. When I investigated further I found that the common service.xml has a few tables with master data, which had around 100K records and it takes a lot of time while backup and restore and sometimes it breaks also. I think that due to this backup and restore itself it doesn't apply remaining changes of restoring tables with new sql changes.

Scenario-2: We faced an issue of some data and schema lost on our QA server. There were random data lost from some of the tables, some has missing indexes. Which all was there earlier and also in DEV server. We come to a conclusion that this could have happened when it fails to completely restore the backup table data while deployment of the hook. We will have many other tables which will grow with million records in production and this service builder upgrade process is a risk. Hence we have disabled the service builder upgrade process by setting the flag 'build.auto.upgrade' to false in service.properties file.

Query-1: Is there any other way/mechanism to upgrade database for changes ? After disabling service builder upgrade, we are now applying changes through sql scripts.

Query-2: We have investigated Liferay code to perform the backup & restore. And it's worst. It applies indexes after restoring data, and indexes on a table with lot of data takes a lot of time. That sequence need to be changed. Is there an easy way to tell service.xml that don't perform backup & restore for a specific table (entity) ? By the way, I couldn't found any such flag in dtd of service.xml. But I might have missed something.

I wonder, this problem must have been faced by many other projects which deals with a lot of data. How does other handle this scenario ?
Marcus, are you still using the custom sql scripts to handle changes in database ?

Any kind of guidance/pointers will be really helpful.

Thanks in advance !

Kind Regards,
Abhed

[Krzysztof Gołębiowski，修改在9 年前。]

Hello Abhed,
To be honest... by many people Service Builder is not considered to be an ORM at all :/ For projects with large amount of data it is really not a good solution. We had once a project with over 200 tables, 4GB database and frequent model changes (our test environment had lot's of migration data). Standard SB upgrade procedure used to take few hours (and sometimes failed forcing us to load the database from backup and start the process all over again)! Finally, one of my friends wrote a python script which was automatically altering SB generated tables and raising service build number in database.

Only advice I can give is that you should not to make manual changes to database on all environments, but rather embed SQL scripts in UpgradeProcess and install it with every new version is released.

Maybe other experts will provide better solution.

KG

[Abhed Dekavadiya，修改在9 年前。]

Hello Krzystof,

Thank you for your feedback. Infact we have also concluded that the db upgrade process by LR Service builder is not good for a large project. And hence disabled the db upgrade from service builder.

Yes, manual db upgrade is not the right way, that's why I am looking for an existing way that can also make the deployment process easy and also provide log of db upgrade script execution. I don't want to re-write something, which could be already available. The liferay's db upgrade (used when upgrading liferay version), could be my last option.

Again, thanks for you inputs.

regards,
Abhed

[David H Nebinger，修改在9 年前。]

Wow, all of this irrelevant stuff aside...

So first Liferay has a release table which you are free to add your own records to. It's available as a service so you can use it for tracking service versions.

Next, upgrades are typically handled using startup hooks. It is your first point to do some functionality before the portlet is up and running. So if you use the release table, you can check your release and do a nice upgrade to apply new DDL to get to the correct version. If you don't want to do the release step, you can get to the DB metadata and check if the table(s) in question have necessary stuff (i.e. a newly added column, a column data type change, whatever) and if it's missing then apply the changes.

I believe after looking at those two alternatives you'll see that the release table option is going to be a much cleaner way to handle the DB upgrade process...

[Jack Thinkin，修改在7 年前。]

Is this situation changed in Liferay 7? Is there any simple guidence what to do for right updating-creating of tables?
There is no update and no creating of new added tables in service.xml (sure after build, deploy and new start of server) despite of build.auto.upgrade=true in services.properties and incremented build.number after each build.
Removing of datarecord in Release table creates absent tables after new start of sever.

[David H Nebinger，修改在7 年前。]

I usually recommend not letting SB manage tables unless building a MarketPlace portlet. Projects often have a DBA assigned and they can handle the DB changes.

If I had to do it in code, I'd use a StartupAction as part of my SB provider portlet to check the release table and update accordingly.

Except for a MarketPlace plugin where you have no DBA, I find it hard justifying spending development cycles writing code to update tables that a DBA could have done with a few lines of script.

[Jack Thinkin，修改在7 年前。]

Thanks. Is there any code that compares DB and DB files in deployed application and updates DB in case of changes during startup (if i right follow)? is it Your own implementation?

[David H Nebinger，修改在7 年前。]

Own implementation. You know when you are adding a column to an entity in service.xml, so pair that with an alter table statement in the startup action to do the right thing. Follow the KISS principle to keep things as simple as possible.

Liferay does have some table update support that you can build off of, but it will likely depend on the complexities of the change that you're making if they will work or not.

[Jack Thinkin，修改在7 年前。]

thanks. it would be good to know solution of liferay that works too for any case.

[Jack Thinkin，修改在7 年前。]

Removing of datarecord in Release table doesn't create anymore absent tables in service.xml after new start of sever, new deployment(in previous message). Other instance of Liferay has created absent tables.
Does anybody know possible reasons? What can be wrong?

[Fabio Pezzutto，修改在7 年前。]

Hi,

I've experienced the same behavior, using Liferay 7, my service builder bundle doesn't ALTER the tables.
Looking to the code, this seems to be due to this lines 55, 60, 65.
I've tried also running the upgrade of tables using upgrade process described here but it doesn't works
Which is the right way to run the custom bundle db upgrade?

[Jack Thinkin，修改在7 年前。]

There is seems big difference in cases. First case: the application is deployed at first time than no problems till now. and second case the application is redeployed. second case brings no stable fruits: sometimes the tables are updated and sometimes no/ or no more at all. Some peoples have implemented own portlets for update according many discussions. May be one stable solution could be publicised?

p.s. the own solution with KISS DCM (in previos messages) is sure reliable solution in case of many updates, if it can automatically find difference in Liferay scripts for old and new version and generate update scripts.

[Fabio Pezzutto，修改在7 年前。]

An LPS has been opened for the second case: LPS-67688