9 Ответы Последний ответ: 05.12.2019 13:16, автор: Piotr Wrzosek

    Liquibase issues in SQLcl 19.2

    Piotr Wrzosek

      Just started playing with Liquibase extension (love the idea btw, thank you!) and noted a few things:

      1. Documentation states "The DATABASECHANGELOG_EXPORT table is created internally to gather object details and sort them correctly. The created object is automatically excluded from the capture process and destroyed upon capture completion."

      In my experience the table is not dropped after performing genschema. It is also included in further diff/capture which can be seen even in provided examples.

      2. updatesql command always output schema name, no matter the provided {include schema} parameter value.

      3. Main issue for me: upon trying to sync with changelogsync using succesfully generated changelog with genschema earlier I received following error:

      Liquibase was unable to process the request at this time (generic error).

      liquibase.exception.SetupException: Error Reading Migration File: Invalid byte 1 of 1-byte UTF-8 sequence.

      After this changelogsync_controller.xml is created with 0 bytes and held open (no error handling?).

      I believe this is caused by the fact the specified encoding of generated .xml files is UTF-8 (<?xml version="1.0" encoding="UTF-8"?>), but the actual captured code content is not.

      I thought it respects my NLS_LANG setting, which by default is not UTF-8 (NLS_LANG=POLISH_POLAND.EE8MSWIN1250), but even changing it (show nls shows NLS_LANG POLISH_POLAND.AL32UTF8 and NLS_CHARACTERSET AL32UTF8) doesn't help.

      Shouldn't it force UTF-8 output when it has such encoding fixed, or set it's definition correctly in files?

      4. Example "Capture and Deploy a Schema and then Upgrade it and Redeploy" in documentation - it doesn't look right. Running twice genschema and then twice update on these full schema exports instead of generating diff changesets for the second time won't do any good....

      5. While running update on generated changesets I encountered a lot of errors:

       

      We were unable to process the request at this time (sql error).

       

      ORA-06502: PL/SQL: numeric or value errorstring

      LPX-00409: incorrect token encountered while parsing

      ORA-06512: at "SYS.DBMS_METADATA", line 11160

      ORA-06512: at line 22

       

      Since there is no output of what changeset is being processed I don't know what they relate to (existing tables probably). Some output on what's going on (what changeset is being applied) would be helpful too.

       

      6. There's DATABASECHANGELOG_ACTIONS table created on update action, which is not dropped and not documented. Is it needed/permanent?

        • 1. Re: Liquibase issues in SQLcl 19.2
          skutz-Oracle

          I'm the developer you have to blame for this, so wanted to give you some answers.

           

          1. This was a bug DATABASECHANGELOG_EXPORT should not be sticking around.

           

          2. I could not reproduce this:

           

          SQL> lb updatesql foo_table.xml true

          lb updatesql foo_table.xml true

          CREATE TABLE "HR"."FOO"

             (    "ID" NUMBER,

                  "NAME" VARCHAR2(200)

             ) SEGMENT CREATION DEFERRED

            PCTFREE 10 PCTUSED 40 INITRANS 1 NOCOMPRESS LOGGING

            TABLESPACE "SYSTEM";

           

          SQL> lb updatesql foo_table.xml false

          lb updatesql foo_table.xml false

          CREATE TABLE "FOO"

             (    "ID" NUMBER,

                  "NAME" VARCHAR2(200)

             ) SEGMENT CREATION DEFERRED

            PCTFREE 10 PCTUSED 40 INITRANS 1 NOCOMPRESS LOGGING

            TABLESPACE

           

          3. Changelogsync doesn't look like an encoding issue to me, I have to dig deeper into the Liquibase code to figure out what's going on. However, I was holding the file open and I fixed that.

           

          4. Try it. This is where I deviated from standard lb behavior. If you :

          a. run genschema on DEV

          b. update on TEST

          c. change DEV

          d. run genschema on DEV again

          e. update on TEST again.

          It will perform alters to bring test from state 1 to state 2 automatically.  You can see what it will do by running updatesql not update. This is why the Question 6 table is there...

           

          5 I really need to see more of your log to better understand where I'm failing and why your no getting this information. Is there enough/correct information when it doesn't fail?

          6 DATABASECHANGELOG_ACTIONS is needed and permanent. It's how I capture the state of objects, and is used for alter and rollback generation.

           

          I appreciate the feedback and will work with you to make this a better offering. If there is other feature/functionality you want please let know and I'll see what I can do!

           

          Thanks,

          Syme

          • 2. Re: Liquibase issues in SQLcl 19.2
            Piotr Wrzosek

            Thank you for responding and sorry for the delayed reply - somehow I didn't get notification of the update.

             

            ad 2 - Now I cannot reproduce it neither - I'll have to go back to what I was exactly doing initially...

            ad 3 - It is for sure caused by wrong encoding, because when I convert the content to UTF-8 it works properly.

            The root issue is with genschema when generated code is saved - but not with UTF-8 encoding - in my case it's Windows-1250. I've tried changing NLS_LANG and encoding in sqlcl to UTF-8 but both don't affect the result.

            ad 4 - I have tested it and now I understand more how it works.

            I didn't expect diff action taking place when doing the actual update - I expected the changesets to already contain required changes.

            There is however an important warning with this approach - not all changes can be properly recognized and implemented automatically.

            Typical example which is often mentioned is renaming column in table - sqlcl's lb update will generate drop <old column name> and create <new column name> statements, which if auto-run will result in data loss.

            That is why the diffs should be generated to changesets first to check them and make eventual corrections.

            Relying on what gets done during diff is way too risky to use it - at least for me, in the current state.

            ad 5 - These error messages are displayed only in sqlcl console window, they are not logged like output (errors or not) of executed DDL's.

            Whatever happens during parsing of XML's does not gets in the logs.

            I encounter similar errors when running lb update multiple times on different changelogs in the same session - like some variables are session-persistant causing this.

            Other example stack (from 19.4):

             

            ORA-06502: PL/SQL: numeric or value errorstring

            LPX-00409: incorrect token encountered while parsing

            ORA-06512: at "SYS.DBMS_METADATA", line 12266

            ORA-06512: at "SYS.UTL_XML", line 462

            ORA-06512: at "SYS.DBMS_METADATA_INT", line 8249

            ORA-06512: at "SYS.DBMS_METADATA_INT", line 8396

            ORA-06512: at "SYS.DBMS_METADATA_INT", line 12637

            ORA-06512: at "SYS.DBMS_METADATA_INT", line 12847

            ORA-06512: at "SYS.DBMS_METADATA", line 6196

            ORA-06512: at "SYS.DBMS_METADATA", line 12253

            ORA-06512: at line 22

             

            Like I've mentioned there is not enough information presented what is currently going on during update operation (which file/changeset is being processed).

            Together with the above logging issue it makes it harder to identify the exact place the issue occurred.

             

            After my recent testing I have some more issues/thoughts to share:

            7. Running lb clearchecksums some_exact_changelog.xml resulted in clearing checksums of all registered changelogs in DATABASECHANGELOG.

            8. Changelogs are always marked as executed after performing update, no matter if the executed actions succeedeed or failed.

               They are not considered for subsequent updates. This is the major issue I see for now.

            9. There is no control on what should happen if error occurred - should the changelogs processing continue or not.

            10. Enhancement proposition: ability to remap schemas (when different ones will be supported in the future?)/tablespaces, similar to data pump import utility.

             

            Best regards,

            Piotr

            • 3. Re: Liquibase issues in SQLcl 19.2
              Piotr Wrzosek

              Forgot to mention one more error I encountered when testing on HR schema: DDL for all tables except COUNTRIES fail on specifying tablespace for indexes - it's just missing (using the same named "HR"). Tested on 12.1 and 19.4:

               

              CREATE TABLE "DEPARTMENTS"

                 ( "DEPARTMENT_ID" NUMBER(4,0),

              "DEPARTMENT_NAME" VARCHAR2(30 CHAR) CONSTRAINT "DEPT_NAME_NN" NOT NULL ENABLE,

              "MANAGER_ID" NUMBER(6,0),

              "LOCATION_ID" NUMBER(4,0),

              CONSTRAINT "DEPT_ID_PK" PRIMARY KEY ("DEPARTMENT_ID")

                USING INDEX

                PCTFREE 10 INITRANS 2 MAXTRANS 255 LOGGING

                STORAGE(INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

                PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

                TABLESPACE  ENABLE

                 ) SEGMENT CREATION IMMEDIATE

                PCTFREE 10 PCTUSED 40 INITRANS 1 NOCOMPRESS LOGGING

                STORAGE( INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

                PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1

                BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

                TABLESPACE "HR2"

                NO INMEMORY ;

              Error starting at line : 1 in command -

              CREATE TABLE "DEPARTMENTS"

                 ( "DEPARTMENT_ID" NUMBER(4,0),

              "DEPARTMENT_NAME" VARCHAR2(30 CHAR) CONSTRAINT "DEPT_NAME_NN" NOT NULL ENABLE,

              "MANAGER_ID" NUMBER(6,0),

              "LOCATION_ID" NUMBER(4,0),

              CONSTRAINT "DEPT_ID_PK" PRIMARY KEY ("DEPARTMENT_ID")

                USING INDEX

                PCTFREE 10 INITRANS 2 MAXTRANS 255 LOGGING

                STORAGE(INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

                PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

                TABLESPACE  ENABLE

                 ) SEGMENT CREATION IMMEDIATE

                PCTFREE 10 PCTUSED 40 INITRANS 1 NOCOMPRESS LOGGING

                STORAGE( INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

                PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1

                BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

                TABLESPACE "HR2"

                NO INMEMORY

              Error report -

              ORA-00959: tablespace 'ENABLE' does not exist

              00959. 00000 -  "tablespace '%s' does not exist"

              • 4. Re: Liquibase issues in SQLcl 19.2
                thatJeffSmith-Oracle

                Weird, it's working for me

                 

                SQLcl: Release 19.2 Production on Thu Sep 26 16:00:11 2019

                Copyright (c) 1982, 2019, Oracle.  All rights reserved.

                 

                Last Successful login time: Thu Sep 26 2019 16:00:12 -04:00

                 

                Connected to:

                Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

                 

                SQL> ddl departments

                  CREATE TABLE "HR"."DEPARTMENTS"

                   (    "DEPARTMENT_ID" NUMBER(4,0),

                        "DEPARTMENT_NAME" VARCHAR2(30) CONSTRAINT "DEPT_NAME_NN" NOT NULL ENABLE,

                        "MANAGER_ID" NUMBER(6,0),

                        "LOCATION_ID" NUMBER(4,0),

                        "EXTRA_COLUMN" VARCHAR2(25),

                         CONSTRAINT "DEPT_ID_PK" PRIMARY KEY ("DEPARTMENT_ID")

                  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS

                  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

                  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1

                  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

                TABLESPACE "USERS"  ENABLE,

                         CONSTRAINT "DEPT_LOC_FK" FOREIGN KEY ("LOCATION_ID")

                          REFERENCES "HR"."LOCATIONS" ("LOCATION_ID") ENABLE,

                         CONSTRAINT "DEPT_MGR_FK" FOREIGN KEY ("MANAGER_ID")

                          REFERENCES "HR"."EMPLOYEES" ("EMPLOYEE_ID") ENABLE

                   ) SEGMENT CREATION IMMEDIATE

                  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

                NOCOMPRESS LOGGING

                  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

                  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1

                  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

                  TABLESPACE "USERS" ;

                 

                 

                   COMMENT ON COLUMN "HR"."DEPARTMENTS"."DEPARTMENT_ID" IS 'Primary key column of departments table.';

                   COMMENT ON COLUMN "HR"."DEPARTMENTS"."DEPARTMENT_NAME" IS 'A not null column that shows name of a department. Administration,

                Marketing, Purchasing, Human Resources, Shipping, IT, Executive, Public

                Relations, Sales, Finance, and Accounting. ';

                   COMMENT ON COLUMN "HR"."DEPARTMENTS"."MANAGER_ID" IS 'Manager_id of a department. Foreign key to employee_id column of employees table. The manager_id column of the employee table references this column.';

                   COMMENT ON COLUMN "HR"."DEPARTMENTS"."LOCATION_ID" IS 'Location id where a department is located. Foreign key to location_id column of locations table.';

                   COMMENT ON TABLE "HR"."DEPARTMENTS"  IS 'Departments table that shows details of departments where employees

                work. Contains 27 rows; references with locations, employees, and job_history tables.';

                 

                 

                  CREATE INDEX "HR"."DEPT_LOCATION_IX" ON "HR"."DEPARTMENTS" ("LOCATION_ID")

                  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS

                  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

                  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1

                  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

                  TABLESPACE "USERS" ;

                SQL>

                • 5. Re: Liquibase issues in SQLcl 19.2
                  4071929

                  Hi,

                   

                  I have experienced problems described in point 5 .

                   

                  However now in the version September 12, 2019 - Update 19.2.1.246.1707 of SQLcl this diagnostic is shown:

                   

                  "No SQL produced as the object is the same as defined in the change log."

                   

                  And it happens despite the fact I have schema changes in XMLs, for example column added in a table.

                   

                  It works fine for CREATE (like adding table to a schema), but fails to produce ALTER queries from XML.

                   

                  Kind regards,

                  Bart

                  • 6. Re: Liquibase issues in SQLcl 19.2
                    Piotr Wrzosek

                    Hi Jeff,

                     

                    I meant the actual DLL that is generated and executed by lb update command, not the ddl command of sqlcl. Tested it on several DB's including 19.4 EE, 19XE, and it's all the same. SQLcl version 19.2.1 (19.2.1.246.1707).

                    • 7. Re: Liquibase issues in SQLcl 19.2
                      Piotr Wrzosek

                      Update of the reported issues, using SQLcl version 19.2.1 (19.2.1.246.1707).

                       

                      ad 2 - Not reproduced.

                      ad 3 - Spent lot of time on it, and finally solved by forcing SQLcl to run in UTF-8 by adding following env variable: JAVA_TOOL_OPTIONS: --Dfile.encoding=UTF-8 , as mentioned by Ales on Jeff's blog post comments 3 years ago: https://www.thatjeffsmith.com/archive/2016/08/unicode-and-oracle-sqlcl-on-windows

                         I see 2 issues here:

                         a - Liquibase functionality in SQLcl works properly only when client is using UTF-8.

                         b - SQLcl on Windows doesn't properly set encoding to UTF-8 by itself, even when I set NLS_LANG=POLISH_POLAND.AL32UTF8 and codepage to 65001 ('chcp 65001') - 'show encoding' still returns "Encoding:windows-1250", and "set encoding UTF-8" does _not_ work (even for not sqlcl lb related-functionality, despite 'show encoding' returning "Encoding:UTF-8" afterwards). The only solution is as mentioned (JAVA_TOOL_OPTIONS).

                      ad 4 - Still think this is dangerous approach.

                      ad 5 - Solved in new release as Bart mentioned.

                      ad 7 - Still stands true.

                      ad 8 - Still stands true.

                      ad 9 - Still stands true.

                      ad 10 - ER - nothing to add.

                      11 - Missing tablespace for generated DDL by lb update command - still stands true.

                      New observation:

                      12 - lb changelogsync does not close generated file, holds it open.

                       

                      Regards,

                      Piotr

                      • 8. Re: Liquibase issues in SQLcl 19.2
                        Piotr Wrzosek

                        ad 5 - I confirm Bart finding - no errors, but object changes are not detected properly.

                        ad 8 - stands true also for sql scripts (runOracleScript ChangeSet type) - commit is always executed on finish.

                        13 - log files are not closed. I would check every file operation, as this issue seems to be common.

                        14 - lb diff can generate drop DATABASECHANGELOG_ACTIONS if it not exists on reference schema, which is dangerous.

                        • 9. Re: Liquibase issues in SQLcl 19.2
                          Piotr Wrzosek

                          ad 5 - I've found the root cause. It's the error in underlying package SQLcl uses for generating diffs - DBMS_METADATA_DIFF.

                          For example if I execute

                          select dbms_metadata_diff.compare_alter('TABLE', 'COUNTRIES', 'COUNTRIES', 'HR', 'HR2') from dual;

                          it errors with

                            Error report -

                            ORA-06502: PL/SQL: numeric or value error

                            LPX-00409: incorrect token encountered while parsing

                            ORA-06512: at "SYS.DBMS_METADATA_DIFF", line 769

                            ORA-06512: at "SYS.DBMS_METADATA", line 12266

                            ORA-06512: at "SYS.UTL_XML", line 462

                            ORA-06512: at "SYS.DBMS_METADATA_INT", line 8249

                            ORA-06512: at "SYS.DBMS_METADATA_INT", line 8396

                            ORA-06512: at "SYS.DBMS_METADATA_INT", line 12637

                            ORA-06512: at "SYS.DBMS_METADATA_INT", line 12847

                            ORA-06512: at "SYS.DBMS_METADATA", line 6196

                            ORA-06512: at "SYS.DBMS_METADATA", line 12253

                            ORA-06512: at "SYS.DBMS_METADATA_DIFF", line 733

                            ORA-06512: at line 1

                          This happens only when the NLS settings for the session are different than American (or possibly English in general).

                          The workaround is to set NLS_LANG=AMERICAN_AMERICA.AL32UTF8 before SQLcl is run. I've tried to track down the exact NLS setting which triggers the error but couldn't - changed all relevant NLS_* to the same values as they are with AMERICAN_AMERICA setting in session, but the error still persisted. This one I'm going to report on MOS, as it's PL/SQL code error in database.

                           

                          The other side of this issue is however with SQLcl - it does not properly propagate SQL errors and hides them, which makes the diagnosing of the issue impossible. It's like doing 'WHEN OTHERS THEN NULL' in PL/SQL error handling. There are places in SQLcl lb extension where SQL exceptions are not catched; in this case even when they are the output is sent to context which is not initialized (null). So this needs some attention.

                           

                          New issues discovered:

                          15 - DATABASECHANGELOG_EXPORT is dropped without PURGE, so RECYCLEBIN is filled very quickly with thousands of old versions of DATABASECHANGELOG_EXPORT (together with it's dependant objects).

                          16 - lb diff does not detect all changes or detect changes which are not existing. Specifically when new procedure is added it is not detected - diffResult.txt even doesn't mention procedure as compared object type. Looking at Liquibase diff/diffChangeLog commands documentation procedure is listed in processed categories of objects in Community Edition. This raises another very important question - is liquibase functionality in SQLcl limited only to Community edition or does it handle functionality of Pro edition?

                          As for the changes which are not existing - based on HR schema this includes executing again comments (not changed), dropping and recreating foreign key constraints (again not touched), recreating again procedures and triggers which code was not changed, and unnecessary rebuilding indexes due to changed INITIAL storage parameter (changed by database itself on original deployment).

                          17 - lb update does not respect liquibase changeset attribute failOnError=true, which states migration should fail if error occurs.

                          18 - documentation remarks: "2.3 Supported Types" is missing some object types for SXML list, like INDEX. "2.2 Requirements for Using Liquibase" is missing requirement of create procedure privilege in addition to create table. "2.5 ChangeSets" description for CreateSxmlObject is wrong ("Creates a function from SQL.").

                           

                          I have also another question regarding the lb diff functionality - generated changeSets in diffResult.xml are strict Liquibase type ones, which means they are not registered in DATABASECHANGELOG_ACTIONS (the current object state is not saved for the purpose of rollback). For some type of changes (like changing datatype, even simple one like VARCHAR2(30)->VARCHAR2(40)) it means no automatic rollback is possible without manually defining action for it by adding <rollback> to changeSet in diffResult.xml. Would it be possible to save the current state of the object for them so the rollback could be executed automatically when needed (by doing diff)?

                           

                          Thanks,

                          Piotr