SCRIPT PARTIALLY EXCUTED, After fixing error script complete script not executing

[linuxerpro]

Hi Team
In my script alter script executed create script not executed by liquibase, after fix create script alter script already compiled error getting. please suggest how to handle it

[daryldoak]

I’m not sure I fully understand the question, and I don’t know the DBMS platform, but hopefully this will help…

Scenario:
You have a changeset that contains multiple DDL statements, something like this:

--changeset author:id
ALTER TABLE test1 ADD (col1 varchar2(20));

CREATE TABLE test2 (col1 varchar2(20));

The ALTER was successful, but the CREATE failed, leaving your database half-changed, but Liquibase failed the changeset execution so the changeset is still “pending”.

You fix the syntax error in the CREATE and try to run again, but get a DBMS error that the ALTER has already been complete. Now you are stuck.

Solution:
You should only have 1 DDL statement per changeset to prevent the inconsistency between Liquibase transaction and the DBMS transaction.

--changeset author:id1
ALTER TABLE test1 ADD (col1 varchar2(20));

--changeset author:id2
CREATE TABLE test2 (col1 varchar2(20));

Now each Liquibase transaction (changeset) matches the DBMS platform’s transaction (DDL statement), and this is full restartable if the CREATE gets an error.

This is especially important for DBMS platforms like Oracle, which do not have transactional DDL.

[linuxerpro]

Hi,

Yes This is my actual scenario.

**--changeset test:1**

** ALTER TABLE test1 ADD (col2 varchar2(20));**

** CREATE TABLE test2 (col1 varchar2(20));**

As you suggest I already tried,

In my first run ALTER executed CREATE table got error.
After fixed CREATE table error and triggered second run.

**--changeset test:2**

** ALTER TABLE test1 ADD (col2 varchar2(20));**
** --rollback ALTER TABLE test1 DROP COLUMN col1;**

** --changeset test:3**
** CREATE TABLE test3 (col1 varchar2(20));**

** --rollback DROP TABLE test2;**

In my second run i got error in ALTER script, (ERROR: column being added already exists in table)

Kindly assist me for this.

I likes to give suggestion,

Imagine you have an Nth changeset, consisting of N forward scripts and N rollback scripts.

The objective is that if an error occurs in any line of the scripts within this specific changeset, all executed scripts for that changeset will automatically roll back. This creates a loop where the changeset continues to be attempted until the issue is resolved, preventing progress until the problem is fixed.

Kindly correct me if I’m wrong.

[daryldoak]

Each changeset is an independent transaction. All completed changesets are committed in the database, and are not impacted by a subsequent changeset that fails.

Assume I have 3 changesets:

--changeset test:1
sql
--changeset test:2
sql
--changeset test:3
sql

When I run Liquibase the first time, 3 changesets are pending. This can be confirmed by using the “status --verbose” command:

3 changesets have not been applied to XYZ
     db-changelog-1.xml::1::test
     db-changelog-1.xml::2::test
     db-changelog-1.xml::3::test

Now let’s assume there is a syntax error in changeset 2. If I run Liquibase “update” (remember 3 changesets are pending), changeset 1 will be applied and committed in the database, changeset 2 will fail, changeset 3 will not be attempted.

Now I can see changeset 1 recorded in the databasechangelog table, and there are 2 changesets are pending, which can be confirmed by using the “status --verbose” command:

2 changesets have not been applied to XYZ
     db-changelog-1.xml::2::test
     db-changelog-1.xml::3::test

Now I can correct changeset 2 and run Liquibase “update” again which will apply changesets 2 and 3.

Liquibase cannot control how the DBMS rolls-back a failed transaction inside the database. So if you had multiple DML statements in the failed changeset, all of the DML statements would be rolled-back (DML is transactional). But if you had multiple DDL statements in the failed changeset, it will depend on how the DBMS handles DDL transactions (DDL may or may not be transactional).

For example:

In Oracle, DDL statements are implicitly committed and cannot be rolled-back. You cannot control this, Liquibase cannot control this.

In Postgres, DDL statements are transactional, so they can be rolled-back.

So you need to understand how your underlying DBMS platform handled transactions.

There is no loop created, the process will fail.

Liquibase best practice “specify one change per changeset”.

Best Practices