My company are looking at replacing a bespoke db change control process with Liquibase and as part of that I’m looking the impact of what would be different if we do this. We use Oracle and our DB change scripts (changesets in Liquibase terms) cover DML as well as DDL operations and generally include a lot of DBMS_OUTPUT statements in them to say what the scripts are doing as they excute e.g : - ‘Inserting row for id X in table Y.’ etc,etc.
We would ideally like to preserve this capability if we move to a liquibase solution (that is to see the messages appear in the Liquibase output) as we need to be able to have an idea of what changesets are doing as they are applied to a schema. This is generally helpful when debugging any issues arising from the application of a changeset .
From what I can see there seems to be no way in Liquibase of making it DBMS_OUTPUT aware . Is this true ? Does anyone have any ideas of a workaround for this . My one idea is to write the dbms_output messages produced during execution of that change to a database table and , then have have a final changeset which always runs which selects the relevant data from this table - but I’m stuck on how to write Liquibase XML to do this .
Just curious - if there was ever any discussion or consideration on this topic? We are considering using Liquibase, but the operations folks are accustomed to seeing the execution output… Looking through the code, it does not seem like this is supported (at least not in an obvious fashion!)
It is also interesting / disappointing - the limited amount of logging control that is provided – it seems like there is only one (1) logger, for all of liquibase. Ideally, but not perfect - I might just like to log all of the SQL that is executed.
Changing the logging level dumps everything… there is no way to say just log
<logger
name=“liquibase.executor.jvm.JdbcExecutor”
level=“debug” />
There is simply too much “stuff” to deal with at this level…
What the operations team would like, is the actual output of the SQL… Such that if I did an “update …where …” and they could see that it updated 10 rows -perfect — If the update updated 1000000 rows - we are in trouble! I know it should be tested, but I believe that we don’t always have an exact dump of production data to ensure that the script will do the exact correct thing… Anyway - it will still be great to see exactly what happened 1 row inserted, 4 rows update, etc.
Is this an issue for anyone else? Thank in advance. Phil
Improvements to the logging system is on my list of things to look at, possibly with 3.1 but there hasn’t been a lot of discussion yet around what would be most helpful to people.
I haven’t used the DBMS_OUTPUT package before and I could see where it would be helpful, although since it is oracle specific it would belong in an extension rather than in liquibase core itself. ChangeExecListener was added in 3.0.5 which is an interface you can implement that is called after a changeSet is ran which could very easily call DBMS_OUTPUT with information about the changeSet.
Data about the number of rows affected and other forecasting style analytics gets beyond what Liquibase is trying to do, but is functionality available in DaticalDB, a commercial product that builds on top of Liquibase (http://datical.com/Liquibase)
This forum topic seems to specifically address a question I have been asked by management. Our project adopted Liquibase release 2.0.5 about 6 months ago and we have been successfully using it since. One aspect of Liquibase that has been puzzling is the lack of feedback that Liquibase provides when it runs SQL. Our project is running against Oracle 11.2.0.4. I did a test today of what was in log file after running Liquibase UPDATE with loglevel of DEBUG. The SQL in files listed in changelogs is displayed in log file several times, but there is no feedback provided for number of rows updated, number of rows deleted, number of rows inserted, etc. That feedback would seem to be very basic information to provide in logfiles that are supposed to have detailed information.
Had a few questions:
1. What is general reason that SQL execution result feedback is not normally provided ? Should be available at DEBUG level of logging for sure. 2. SQL commands get executed and feedback is provided initially as to what happened. Where is the feedback getting stripped away since it is not provided ? 3. Are there any changes planned for future Liquibase releases that would display SQL execution result feedback ? If not can there be a new loglevel for FEEDBACK that will list only the SQL statements actually run and the results of them running ? 4. What is meant by NVoxland comment “Data about the number of rows affected and other forecasting style analytics gets beyond what Liquibase is trying to do…” ? Does this comment mean that providing SQL execution result feedback is out of scope for Liquibase ?
Any replies to any questions above would be helpful for me to explain to others the topic.
Implementation of CORE-1802 will likely solve my situation. That will likely be justification I need to push for upgrade to Liquibase 3.2 when it is available. Thanks for considering the topic of providing results of execution in log output.
I created https://liquibase.jira.com/browse/CORE-1802 to track improvements to the logging. Liquibase should be providing feedback on what it is executing. We’ve not logged the return value of SQL but that would be worth logging as well.
There is updateSql mode that you can use to list the SQL that WILL run, but the change in 1802 would improve the logging during execution.
Liquibase would be able to log the rows updated according to the JDBC driver, but that is after-the-fact “X rows were changed” information. The forecasting support I mentioned is more around discovering what WILL be changed by a liquibase update.
Yes, 1802 is currently set for 3.4 although I’m still looking at 3.4 vs. 4.0 work and timelines. Currently if you run with logLevel=DEBUG it will output all the SQL but it is mixed in with other log messages.
Thanks for the information Nathan, I can see the detailed log with logging level set to as DEBUG, however I was trying to capture the actual output of statements that are being executed. Some thing along these lines, if I run select statements, capturing its actual output which is the content of the table:
select * from ascii;
1
test
2 !@#$%^&* jain
3 hello world
At present DEBUG log just shows me that the custom sql has been executed:
select statement was just for example, what would really be great is liquibase capturing stdout of each and every statement that is being executed. We can simply provide the log to stakeholders for verification thus saving some time further which we/stakeholders spent at present
in making sure that changes are upto the mark or not.
I added a comment to CORE-2165 about logging the result of statements. Most statements managed by liquibase are DDL statements which tend to not have much for a printable result, but I’ll look at what we can do.
