Sql file not actually executed on database but entry made to databasechnagelog table

Issue :

  • sql file not actually executed on database but entry made to databasechnagelog table.
  • this is a kind of silent failure of liquibase, with such failure we can’t rely.
  • same script succeeds randomly on database without any issue.
  • the script is to create view in one schema.

example script : view_booking_entity_rules_setup.sql
DECLARE
k_template_company CONSTANT VARCHAR2(30) := ‘AE9_TEMPLATE_COMPANY’;

v_company_id  ae9_common_data.companies.company_id%TYPE;
v_schema      VARCHAR2(30) := sys_context('USERENV','CURRENT_SCHEMA');

CURSOR get_company_id_cur( c_company_short_id companies.company_short_id%TYPE)
    IS SELECT company_id
	     FROM v_company_list
	    WHERE company_short_id = c_company_short_id;

BEGIN

IF v_schema = k_template_company THEN
	v_company_id := '000';
ELSE
	OPEN get_company_id_cur(c_company_short_id => SUBSTR(v_schema,-3));
	FETCH get_company_id_cur INTO v_company_id;
	CLOSE get_company_id_cur;
END IF;

BEGIN

EXECUTE IMMEDIATE 'CREATE OR REPLACE FORCE VIEW booking_entity_rules_setup
					AS 
					SELECT 
					 RULE_ID         
					,BOOKED_UNDER    
					,SETUP_TYPE      
					,LEGAL_ENTITY    
					,BOOKING_ENTITY  
					,COMPANY_ID
					,ACTIVITY      
					,OBJECT_TIMESTAMP
					,CRE_DATE        
					,CRE_USER        
					,UPD_DATE        
					,UPD_USER        
					,CRE_ORACLE_USER 
					,CRE_OS_USER     
					,UPD_ORACLE_USER 
					,UPD_OS_USER     
					,STATUS 
                    ,PLAN_TYPE         
					 FROM (SELECT    RULE_ID         
									,BOOKED_UNDER    
									,SETUP_TYPE      
									,LEGAL_ENTITY    
									,BOOKING_ENTITY  
									,COMPANY_ID
									,ACTIVITY      
									,OBJECT_TIMESTAMP
									,CRE_DATE        
									,CRE_USER        
									,UPD_DATE        
									,UPD_USER        
									,CRE_ORACLE_USER 
									,CRE_OS_USER     
									,UPD_ORACLE_USER 
									,UPD_OS_USER     
									,STATUS
                                    ,PLAN_TYPE     
									,DENSE_RANK()
								   OVER (
									  PARTITION BY bers.booked_under,bers.setup_type,bers.legal_entity
									  ORDER BY
										 CASE
											WHEN bers.company_id = '''||v_company_id||''' THEN 1
											ELSE 2
										 END)
									  rnk
							  FROM ae9_common_data.booking_entity_rules_setup bers
							 WHERE bers.company_id IN ('''||v_company_id||''', ''ALL_COMPANIES''))
					WHERE rnk=1';


EXCEPTION
  WHEN OTHERS THEN 
	DBMS_OUTPUT.PUT_LINE(SUBSTR(sqlerrm,1,255));
    app_dba_install_logs.insert_liquibase_error_log('booking_entity_rules_setup','ERROR = '||SQLERRM,sys_context( 'USERENV','CURRENT_SCHEMA'));

END;

EXCEPTION
WHEN OTHERS THEN NULL;
END;
/

COMMIT
/

Hi @AnuragEQX!

Sorry for taking so long to get to your question. Could you share with me how you defined your changeset in your changelog?

I am wondering if there is a failure and it is handled by the exception, I wonder if liquibase sees it succeeding no matter what.

Also, must have complete liquibase output (–logLevel=debug should be set in your liquibase.properties).

Hope that helps!

Ronak

Thanks Ronak for the response.

Here is the database changelog file with changeset.

atePlus/company/ae9_template_company/changesets/ [rdbms11204] cat db_eqpcom_ae9_template_company_mvl.xml
<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext" xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.1.xsd     http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd">
  <changeSet author="GujarKu" id="MVL" logicalFilePath="view_alloc_payroll_tax_info.sql" runOnChange="true" runAlways="false" failOnError="false">
    <comment>335497-EPD38346DDL request for EPD35926  Alter table ALLOCPAYROLLTAXINFODT</comment>
    <sqlFile path="../materialized_views/view_alloc_payroll_tax_info.sql" relativeToChangelogFile="true" splitStatements="true" endDelimiter="\n/" encoding="utf8"/>
  </changeSet>
  <changeSet author="RathiRo" id="MVL" logicalFilePath="view_booking_entity_rules_setup.sql" runOnChange="true" runAlways="false" failOnError="false">
    <comment>339488-EPD41370  update view for newly added column</comment>
    <sqlFile path="../materialized_views/view_booking_entity_rules_setup.sql" relativeToChangelogFile="true" splitStatements="true" endDelimiter="\n/" encoding="utf8"/>
  </changeSet>
</databaseChangeLog>

And here is the execution log.

11:11:17.572 [main] INFO liquibase.executor.jvm.JdbcExecutor - [ EQX ]1 row(s) affected
11:11:17.627 [main] INFO liquibase.executor.jvm.JdbcExecutor - --REM /***************************************************
*****************
--REM
--REM  Copyright (c) 2006 EES, UBS AG
--REM
--REM  File Name     : view_booking_entity_rules_setup.sql
--REM
--REM  Version Date       Who             Comments
--REM *********************************************************************
--REM  Release 37.5
--REM  37.5.1  15.03.2018 ShaikhDi        EPD-16295 : Creation
--REM  Release 42.5
--REM  42.5.1  26.02.2019 GargAy          EPD-23993 : Column ADDED
--REM  Release 48.5
--REM  48.5.1  25.05.2020 RathiRo         EPD-41370 : Column added

BEGIN
        EXECUTE IMMEDIATE 'DROP SYNONYM booking_entity_rules_setup';
EXCEPTION
        WHEN OTHERS THEN
                NULL;
END;
11:11:17.630 [main] INFO liquibase.executor.jvm.JdbcExecutor - [ EQX ]-1 row(s) affected
11:11:17.631 [main] INFO liquibase.executor.jvm.JdbcExecutor - DECLARE
        k_template_company  CONSTANT VARCHAR2(30) := 'AE9_TEMPLATE_COMPANY';

        v_company_id  ae9_common_data.companies.company_id%TYPE;
        v_schema      VARCHAR2(30) := sys_context('USERENV','CURRENT_SCHEMA');

        CURSOR get_company_id_cur( c_company_short_id companies.company_short_id%TYPE)
            IS SELECT company_id
                     FROM v_company_list
                    WHERE company_short_id = c_company_short_id;

BEGIN

        IF v_schema = k_template_company THEN
                v_company_id := '000';
        ELSE
                OPEN get_company_id_cur(c_company_short_id => SUBSTR(v_schema,-3));
                FETCH get_company_id_cur INTO v_company_id;
                CLOSE get_company_id_cur;
        END IF;

  BEGIN

    EXECUTE IMMEDIATE 'CREATE OR REPLACE FORCE VIEW booking_entity_rules_setup
                                                AS
                                                SELECT
                                                 RULE_ID
                                                ,BOOKED_UNDER
                                                ,SETUP_TYPE
                                                ,LEGAL_ENTITY
                                                ,BOOKING_ENTITY
                                                ,COMPANY_ID
                                                ,ACTIVITY
                                                ,OBJECT_TIMESTAMP
                                                ,CRE_DATE
                                                ,CRE_USER
                                                ,UPD_DATE
                                                ,UPD_USER
                                                ,CRE_ORACLE_USER
                                                ,CRE_OS_USER
                                                ,UPD_ORACLE_USER
                                                ,UPD_OS_USER
                                                ,STATUS
                        ,PLAN_TYPE
                                                 FROM (SELECT    RULE_ID
                                                                                ,BOOKED_UNDER
                                                                                ,SETUP_TYPE
                                                                                ,LEGAL_ENTITY
                                                                                ,BOOKING_ENTITY
                                                                                ,COMPANY_ID
                                                                                ,ACTIVITY
                                                                                ,OBJECT_TIMESTAMP
                                                                                ,CRE_DATE
                                                                                ,CRE_USER
                                                                                ,UPD_DATE
                                                                                ,UPD_USER
                                                                                ,CRE_ORACLE_USER
                                                                                ,CRE_OS_USER
                                                                                ,UPD_ORACLE_USER
                                                                                ,UPD_OS_USER
                                                                                ,STATUS
                                        ,PLAN_TYPE
                                                                                ,DENSE_RANK()
                                                                           OVER (
                                                                                  PARTITION BY bers.booked_under,bers.set
up_type,bers.legal_entity
                                                                                  ORDER BY
                                                                                         CASE
                                                                                                WHEN bers.company_id = ''
'||v_company_id||''' THEN 1
                                                                                                ELSE 2
                                                                                         END)
                                                                                  rnk
                                                                  FROM ae9_common_data.booking_entity_rules_setup bers
                                                                 WHERE bers.company_id IN ('''||v_company_id||''', ''ALL_
COMPANIES''))
                                                WHERE rnk=1';


    EXCEPTION
      WHEN OTHERS THEN
                DBMS_OUTPUT.PUT_LINE(SUBSTR(sqlerrm,1,255));
        app_dba_install_logs.insert_liquibase_error_log('booking_entity_rules_setup','ERROR = '||SQLERRM,sys_context( 'US
ERENV','CURRENT_SCHEMA'));
  END;

EXCEPTION
  WHEN OTHERS THEN NULL;
END;
11:11:17.759 [main] INFO liquibase.executor.jvm.JdbcExecutor - [ EQX ]-1 row(s) affected
11:11:17.760 [main] INFO liquibase.executor.jvm.JdbcExecutor - COMMIT
11:11:17.761 [main] INFO liquibase.executor.jvm.JdbcExecutor - [ EQX ]0 row(s) affected
11:11:17.761 [main] INFO liquibase.changelog.ChangeSet - SQL in file ../materialized_views/view_booking_entity_rules_setu
p.sql executed
11:11:17.761 [main] INFO liquibase.changelog.ChangeSet - ChangeSet view_booking_entity_rules_setup.sql::MVL::RathiRo ran
successfully in 141ms
11:11:17.763 [main] INFO liquibase.executor.jvm.JdbcExecutor - INSERT INTO AE9_COMPANY_UBS.DATABASECHANGELOG (ID, AUTHOR,
 FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_
ID) VALUES ('MVL', 'RathiRo', 'view_booking_entity_rules_setup.sql', SYSTIMESTAMP, 1240, '8:2d43e08147cf31b98229b204f2d0d
bba', 'sqlFile', '339488-EPD41370  update view for newly added column', 'EXECUTED', NULL, NULL, '3.6.2.2', '3249075780')
11:11:17.765 [main] INFO liquibase.executor.jvm.JdbcExecutor - [ EQX ]1 row(s) affected

Hi @AnuragEQX, could I also see the row data 's in the databasechangelog table?

Here is the sample raw data for reference.
The time might not match but data was similar when executed for schema

SQL> select * from ae9_company_ubs.databasechangelog where filename='view_booking_entity_rules_setup.sql' and tag='REL48_5_ORACLE_079_000'

ID         AUTHOR          FILENAME                                           DATEEXECUTED                                       ORDEREXECUTED
---------- --------------- -------------------------------------------------- -------------------------------------------------- -------------
EXECTYPE   MD5SUM                              DESCRIPTION                                       
---------- ----------------------------------- --------------------------------------------------
COMMENTS                                                                                                                                              
------------------------------------------------------------------------------------------------------------------------------------------------------
TAG                                                                                                                                                   
------------------------------------------------------------------------------------------------------------------------------------------------------
LIQUIBASE           
--------------------
CONTEXTS                                                                                                                                              
------------------------------------------------------------------------------------------------------------------------------------------------------
LABELS                                                                                                                                                
------------------------------------------------------------------------------------------------------------------------------------------------------
DEPLOYMENT_ID
-------------
MVL        RathiRo         view_booking_entity_rules_setup.sql                29.05.2020 06:46:30.817034                                  2420
EXECUTED   8:2d43e08147cf31b98229b204f2d0dbba  sqlFile                                           
339488-EPD41370  update view for newly added column                                                                                                   
REL48_5_ORACLE_079_000                                                                                                                                
3.6.2.2             
                                                                                                                                                      
                                                                                                                                                      
0727589885   
                                                                                                                                                      
1 row selected.

@AnuragEQX sorry, I should have asked what is the exact command you used to run liquibase.
ex.
liquibase update

As I understand here is the process:

  • you run liquibase command (assuming liquibase update)
  • using the following changeset
<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext" xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.1.xsd     http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd">
 <changeSet author="GujarKu" id="MVL" logicalFilePath="view_alloc_payroll_tax_info.sql" runOnChange="true" runAlways="false" failOnError="false">
   <comment>335497-EPD38346DDL request for EPD35926  Alter table ALLOCPAYROLLTAXINFODT</comment>
   <sqlFile path="../materialized_views/view_alloc_payroll_tax_info.sql" relativeToChangelogFile="true" splitStatements="true" endDelimiter="\n/" encoding="utf8"/>
 </changeSet>
 <changeSet author="RathiRo" id="MVL" logicalFilePath="view_booking_entity_rules_setup.sql" runOnChange="true" runAlways="false" failOnError="false">
   <comment>339488-EPD41370  update view for newly added column</comment>
   <sqlFile path="../materialized_views/view_booking_entity_rules_setup.sql" relativeToChangelogFile="true" splitStatements="true" endDelimiter="\n/" encoding="utf8"/>
 </changeSet>
</databaseChangeLog>
  • which produces the below output
11:11:17.572 [main] INFO liquibase.executor.jvm.JdbcExecutor - [ EQX ]1 row(s) affected
11:11:17.627 [main] INFO liquibase.executor.jvm.JdbcExecutor - --REM /***************************************************
*****************
--REM
--REM  Copyright (c) 2006 EES, UBS AG
--REM
--REM  File Name     : view_booking_entity_rules_setup.sql
--REM
--REM  Version Date       Who             Comments
--REM *********************************************************************
--REM  Release 37.5
--REM  37.5.1  15.03.2018 ShaikhDi        EPD-16295 : Creation
--REM  Release 42.5
--REM  42.5.1  26.02.2019 GargAy          EPD-23993 : Column ADDED
--REM  Release 48.5
--REM  48.5.1  25.05.2020 RathiRo         EPD-41370 : Column added

BEGIN
        EXECUTE IMMEDIATE 'DROP SYNONYM booking_entity_rules_setup';
EXCEPTION
        WHEN OTHERS THEN
                NULL;
END;
11:11:17.630 [main] INFO liquibase.executor.jvm.JdbcExecutor - [ EQX ]-1 row(s) affected
11:11:17.631 [main] INFO liquibase.executor.jvm.JdbcExecutor - DECLARE
        k_template_company  CONSTANT VARCHAR2(30) := 'AE9_TEMPLATE_COMPANY';

        v_company_id  ae9_common_data.companies.company_id%TYPE;
        v_schema      VARCHAR2(30) := sys_context('USERENV','CURRENT_SCHEMA');

        CURSOR get_company_id_cur( c_company_short_id companies.company_short_id%TYPE)
            IS SELECT company_id
                     FROM v_company_list
                    WHERE company_short_id = c_company_short_id;

BEGIN

        IF v_schema = k_template_company THEN
                v_company_id := '000';
        ELSE
                OPEN get_company_id_cur(c_company_short_id => SUBSTR(v_schema,-3));
                FETCH get_company_id_cur INTO v_company_id;
                CLOSE get_company_id_cur;
        END IF;

  BEGIN

    EXECUTE IMMEDIATE 'CREATE OR REPLACE FORCE VIEW booking_entity_rules_setup
                                                AS
                                                SELECT
                                                 RULE_ID
                                                ,BOOKED_UNDER
                                                ,SETUP_TYPE
                                                ,LEGAL_ENTITY
                                                ,BOOKING_ENTITY
                                                ,COMPANY_ID
                                                ,ACTIVITY
                                                ,OBJECT_TIMESTAMP
                                                ,CRE_DATE
                                                ,CRE_USER
                                                ,UPD_DATE
                                                ,UPD_USER
                                                ,CRE_ORACLE_USER
                                                ,CRE_OS_USER
                                                ,UPD_ORACLE_USER
                                                ,UPD_OS_USER
                                                ,STATUS
                        ,PLAN_TYPE
                                                 FROM (SELECT    RULE_ID
                                                                                ,BOOKED_UNDER
                                                                                ,SETUP_TYPE
                                                                                ,LEGAL_ENTITY
                                                                                ,BOOKING_ENTITY
                                                                                ,COMPANY_ID
                                                                                ,ACTIVITY
                                                                                ,OBJECT_TIMESTAMP
                                                                                ,CRE_DATE
                                                                                ,CRE_USER
                                                                                ,UPD_DATE
                                                                                ,UPD_USER
                                                                                ,CRE_ORACLE_USER
                                                                                ,CRE_OS_USER
                                                                                ,UPD_ORACLE_USER
                                                                                ,UPD_OS_USER
                                                                                ,STATUS
                                        ,PLAN_TYPE
                                                                                ,DENSE_RANK()
                                                                           OVER (
                                                                                  PARTITION BY bers.booked_under,bers.set
up_type,bers.legal_entity
                                                                                  ORDER BY
                                                                                         CASE
                                                                                                WHEN bers.company_id = ''
'||v_company_id||''' THEN 1
                                                                                                ELSE 2
                                                                                         END)
                                                                                  rnk
                                                                  FROM ae9_common_data.booking_entity_rules_setup bers
                                                                 WHERE bers.company_id IN ('''||v_company_id||''', ''ALL_
COMPANIES''))
                                                WHERE rnk=1';


    EXCEPTION
      WHEN OTHERS THEN
                DBMS_OUTPUT.PUT_LINE(SUBSTR(sqlerrm,1,255));
        app_dba_install_logs.insert_liquibase_error_log('booking_entity_rules_setup','ERROR = '||SQLERRM,sys_context( 'US
ERENV','CURRENT_SCHEMA'));
  END;

EXCEPTION
  WHEN OTHERS THEN NULL;
END;
11:11:17.759 [main] INFO liquibase.executor.jvm.JdbcExecutor - [ EQX ]-1 row(s) affected
11:11:17.760 [main] INFO liquibase.executor.jvm.JdbcExecutor - COMMIT
11:11:17.761 [main] INFO liquibase.executor.jvm.JdbcExecutor - [ EQX ]0 row(s) affected
11:11:17.761 [main] INFO liquibase.changelog.ChangeSet - SQL in file ../materialized_views/view_booking_entity_rules_setu
p.sql executed
11:11:17.761 [main] INFO liquibase.changelog.ChangeSet - ChangeSet view_booking_entity_rules_setup.sql::MVL::RathiRo ran
successfully in 141ms
11:11:17.763 [main] INFO liquibase.executor.jvm.JdbcExecutor - INSERT INTO AE9_COMPANY_UBS.DATABASECHANGELOG (ID, AUTHOR,
 FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_
ID) VALUES ('MVL', 'RathiRo', 'view_booking_entity_rules_setup.sql', SYSTIMESTAMP, 1240, '8:2d43e08147cf31b98229b204f2d0d
bba', 'sqlFile', '339488-EPD41370  update view for newly added column', 'EXECUTED', NULL, NULL, '3.6.2.2', '3249075780')
11:11:17.765 [main] INFO liquibase.executor.jvm.JdbcExecutor - [ EQX ]1 row(s) affected
  • after this run, your dbchangelog table looks like this:
SQL> select * from ae9_company_ubs.databasechangelog where filename='view_booking_entity_rules_setup.sql' and tag='REL48_5_ORACLE_079_000'

ID         AUTHOR          FILENAME                                           DATEEXECUTED                                       ORDEREXECUTED
---------- --------------- -------------------------------------------------- -------------------------------------------------- -------------
EXECTYPE   MD5SUM                              DESCRIPTION                                       
---------- ----------------------------------- --------------------------------------------------
COMMENTS                                                                                                                                              
------------------------------------------------------------------------------------------------------------------------------------------------------
TAG                                                                                                                                                   
------------------------------------------------------------------------------------------------------------------------------------------------------
LIQUIBASE           
--------------------
CONTEXTS                                                                                                                                              
------------------------------------------------------------------------------------------------------------------------------------------------------
LABELS                                                                                                                                                
------------------------------------------------------------------------------------------------------------------------------------------------------
DEPLOYMENT_ID
-------------
MVL        RathiRo         view_booking_entity_rules_setup.sql                29.05.2020 06:46:30.817034                                  2420
EXECUTED   8:2d43e08147cf31b98229b204f2d0dbba  sqlFile                                           
339488-EPD41370  update view for newly added column                                                                                                   
REL48_5_ORACLE_079_000                                                                                                                                
3.6.2.2    

From your output, I have cut the following out:

11:11:17.761 [main] INFO liquibase.changelog.ChangeSet - ChangeSet view_booking_entity_rules_setup.sql::MVL::RathiRo ran
successfully in 141ms

So it looks like the changeset ran, and so it should have an entry in the dbchangelog table, and that is intended behavior. Am I missing something?

Thanks,

Ronak

Thanks Ronak,

The issue is, entry goes to databasechangelog table but the actual DDL(view) was not changed.

the expection is when entry is made to databasechangelog table then all the DDL/DML should execute on database.

Hello Ronak,

The situation that we are in is not really good.
Liquibase says eveything is successful with No Error. but actually no actual object changes are done on DB.

The expectation is when nothing is changed on DB then it should report error…

:frowning:

Hi @AnuragEQX, this has never happened that I have seen before. Could I see your liquibase.propeties file?

I still have not seen a response to the question of the exact command you run? like

liquibase upadate

If you want we can chat on discord too:
https://discord.gg/9yBwMtj

Thanks,

Ronak

Hello Ronak,

here the command and properties file.

cat liquibase.properties_SERGGG2G

driver: oracle.jdbc.OracleDriver
classpath: ../../liquibase/lib/ojdbc7.jar
url: jdbc:oracle:thin:@servername.equateplus.net:1521:SERGGG2G
username:********
password:**********
logLevel=info
logFile=log/abc.txt

liquibase --defaultSchemaName=AE9_COMPANY_XXX --defaultsFile=/app/AE9/dyn/ora_backup/cefs_db_archive/liquibase_workspace/liq_properties/liquibase.properties_SERGGG2G --changeLogFile=/app/AE9/dyn/ora_backup/cefs_db_archive/liquibase_workspace/AE9/tags/${RELEASE_ID_NAME}/Ae9OracleEquatePlus/company/ae9_template_company/changesets/db_eqpcom_ae9_template_company_mvl.xml update

Hi @AnuragEQX,

Thanks for sending your properties file and command that you ran. I believe your view object is being created but not where you are expecting it.

Could you check for the view in the following schemas:

  • user you are connecting as (so there should be a schema name that matches your username)
  • SERGGG2G
  • AE9_COMPANY_XXX

Thanks,

Ronak

Hello Ronak,

we run liquibase command with user say “liquibase_user”

and by using --defaultSchemaName=AE9_COMPANY_XXX we control the current_schema where the actual changes should be done by Liquibase command.

already verifued its not the issue.

Hi @AnuragEQX,

I understand you are using -defaultSchemaName=AE9_COMPANY_XXX, but I am not sure if the schema name is changed somewhere else in the process (I didn’t take a close look at the view code as I am not a sql expert). So, just for diagnosing sake, I am wondering if you can see the view in any of the other schemas. Default schema in oracle is the user schema name you are connecting with. That would be a great place to search for the view. Even if you are sure, it would help my understanding and allow me to cross that possibility off the list. For example, perhaps the --defaultSchemaName is not being honored in the command line.

Also, what version of liquibase are you using? I was testing with 3.9.0.

Thanks,

Ronak

Hello Ronak,

No where else we are switching schema.
and that *.xml file contains many more views script, which gets created in the schema but not this one.

  • in past same view was created in same schema.
  • we made change in view definition hence expected the changed script to be picked up by Liquibase.

Version used = version 3.6.2

@AnuragEQX,

Thanks for your last reply, this is a very important piece of new information you just provided:

If that is true, then I expect the issue to be happening on database systems that have had the old view deployed before you updated the changeset. Is that correct?

I see you have:
CREATE OR REPLACE FORCE VIEW booking_entity_rules_setup
but, from this documenation, there is a section below that states:

When Liquibase reaches a changeset, it computes a check sum and stores it in the DATABASECHANGELOG. The value of storing the checksum is so that Liquibase can know if someone changed the changes in the changeset since it was run. If the changeset was changed since it was run, Liquibase will exit the migration with an error message like Validation failed: change set check sums <changeset identifer> was: <old checksum> but is now: <newchecksum> .

Since you are not getting any errors there, I suspect that on the databases where the view does not get deployed, the following may be true:

  • There is already an entry in the DATABASECHANGELOG table for the view
  • Liquibase ignores the view as it thinks the view is already deployed. (I see some output that indicates it ran but, I am not sure if that output was from a succcessful run or a run in which liquibase is not behaving as you expected, so this is still in my mind left to investigate)

I would love it if @NathanVoxland, @SteveZ, or @MikeOlivas would weigh in here.

If that doesn’t happen, I would say we should schedule a call and you can walk us through the issue.

Thanks,

Ronak

I just discussed this with my collegue on the pro side (@SteveZ). This issue is not clear, and I think it would be helpful to have a support call.

If you are open to it, a low risk and easy way to get a support call would be just to sign up for a free 30-day trial here:

Otherwise we can keep trying on the forum but we will need a more thourough walkthru of your environment and how the issue is happening. I really suspect that either liquibase thinks it is already deployed (b/c the script changed but not the xml in the changeset) or the object got created somewhere outside of the defaultschema specified in the commandline.

Have you been able to find any pattern to why this is true:

I would be curious if the databases that liquibase works just fine are already had the view existing or not?

Thanks,

Ronak

Thanks Roank,

I an assure you that we are using the current_schemas properly.

Please note that same *.xml has around 200+ .sql files which gets executed if there is any change in *.sql file in the same schema.
barring the one problematic view script view_booking_entity_rules_setup.sql (which had change as we added additional column in view), remaining all *.sql files were executed on the right current schema.

Will subscribe for the free trial and let you know for call schedule.

1 Like

Perfect @AnuragEQX! I look forward to learning of the root cause of this as well, confident that Liquibase support will be able to help you resolve.

Hello Ronak,
I have registered liquibase pro. Don’t see a option for call setup.

Do i need to send mail to support@liquibase.com for support call?

1 Like