How to rollback stored procedure using SQL format

General Discussion
1

We are using primarily SQL-formatted changeset and scripting out the rollback statement.
When we rollback a script that has the create/replace support, it does not rollback the previous stored procedure.

The XML-formatted changeset seems to support a changesetid attribute. Is this supported in the SQL-format.
What are other alternatives can we use?

Thanks.

2

Rollback to a changeset ID is only supported in the XML format.

If you want to rollback to the previous version of the stored procedure, then you would include that prior code version in the --rollback for the changeset.

Otherwise you could use the following if rollback is not required for the code:
–rollback not required

3

@daryldoak thanks for the reply.

When you refer to including the prior code version, are you referring to a multi-line rollback like this?

– rollback create or replace storedprocedure spname
– rollback begin
– rollback
– rollback …
– rollback end;

If the stored procedure is large, then I will have all this lines of code for rollback purpose only.
This seems to be inefficient. I wonder if anyone has the same problem or do most Liquibase use the XML format?

I do need to have a rollback so it is not an option for us to not have a rollback.

4

Yes, a multi-line rollback like your example. If you think that is too inefficent then I would recommend using the XML format.

5

Thanks, @daryldoak.

Do you know if this is a feature that will be supported in the future?

6

hello,

you can do it in this way :
1- create an sql changelog:
DROP PROCEDURE nameofprocedure;
2- execute the changelog

best practice is to do rollback separatelly and create a changelog for each rollback.
you can do multiple rollback bu using xml one that includes different rollback changelogs.

i hope this may help you

7

@leovic0101 I do not know if rollback to changeset ID will be supported by the SQL format in the future, but I kind of doubt it. There are a several Liquibase features that are unique to the XML/YAML/JSON formats.

8

@ahmedabdeljelil
Thank you for your reply.

How is that actually going to work? If I understand correctly, creating a separate changelog for rollback looks like an added overhead to manage. Say for example, if I have I have 3 successful deployment for SP:

  1. SP v1.0
  2. SP v2.0
  3. SP v3.0

If I want to rollback v2.0, using a different changelog, I will have have to make sure that I don’t invoke the rollback for v1.0. Am I understanding it correctly?

Thanks.

9

Thanks for the info, @daryldoak .
From what I am understanding, I think a hybrid model would probably be an appropriate solution where, components such as SP would be best implemented using XML while those that can have discreet implementation like DMLs or Tables can use SQL format.

1 Like
10

hello @leovic0101 ,

i mean you can create for example 3 sql files dedicated for rollback.

then create a changelog xml file that will call the required files for rollback.

example: in this case i have 3 sql scripts one.sql, two.sql and three.sql

with this changelog file rollback.xml, i’m going to execute only one and two rollback scripts

<?xml version="1.0" encoding="UTF-8"?>

<databaseChangeLog
xmlns="http://www.liquibase.org/xml/ns/dbchangelog/1.9"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog/1.9
        http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-1.9.xsd">

    <include file="one.sql"/>
    <include file="two.sql"/>
</databaseChangeLog>
11

Thanks, @ahmedabdeljelil.

How do you filter out which script to run in the CLI?

I used labels but that is the only way I know.
Also, please keep in mind that I am implementing this via CI/CD pipeline during a rollback scenario.

12

@leovic0101
You can’t filter which script to run, but you can filter what changesets will run using a liquibase context.

https://docs.liquibase.com/concepts/advanced/contexts.html

13

Thanks, @daryldoak
Yes, contexts is also an option.

14

I have used the – rollback version below the new definition of a stored procedure in a changeset, but am still getting:

“Error: 1064-42000: You have an error in your SQL syntax;”

I haven’t found examples documenting how exactly to syntax rollbacks consisting of multiple multi-line commands, such as a procedure drop, followed by a procedure definition? ( None of these examples worked for me: Automatic and Custom Rollbacks)

Could you point me to a such example for a stored procedure, where a rollback contains the previous version of said stored procedure?

Thanks

15

Here is an example:

--changeset BOB:create_functionv3 endDelimiter:/ rollbackEndDelimiter:/ runOnChange:true stripComments:false
--comment: Create test_func function again
    create or replace function test_func return date is
      v_dummy1 date;
      v_dummy2 date;
      v_dummy3 date;
      v_dummy4 date;
    begin
      --This is select 1
      select sysdate into v_dummy1 from dual;
      --This is select 2
      select sysdate-1 into v_dummy2 from dual;
      --This is select 3
      select sysdate-2 into v_dummy3 from dual;
      --This is select 4
      select sysdate-3 into v_dummy4 from dual;

      return v_dummy4;

    end test_func;
    /  

/* liquibase rollback
    create or replace function test_func return date is
      v_dummy1 date;
    begin
      --This is select 1
      select sysdate into v_dummy1 from dual;
      return v_dummy1;
    end test_func;
    /  
*/
16

yes, thank you. these block rollback comments don’t work for me in Liquibase open source 4.25.1 , but I found that simply doing --rollback for each line of rollback commands will work as expected. Like this:

–rollback create or replace function test_func return date is
–rollback v_dummy1 date;
–rollback begin
–rollback --This is select 1
–rollback select sysdate into v_dummy1 from dual;
–rollback return v_dummy1;
–rollback end test_func;
–rollback /

1 Like