Managing YugaByte Database Migrations with Flyway on Yugabyte | Flyway Logging and Debugging Tips

In the previous blog post, we learned about Flyway and its usage for database migrations. We discussed how Flyway can be used for both DDL and DML operations and also saw a basic example of how to use Flyway for database migrations.

In this blog post, we will discuss Flyway logging and debugging. Flyway provides several options to control the logging behavior during the migration process. Let’s dive into these options one by one.

Debug Logs

The -X option enables debug logging and provides detailed information about Flyway’s internal operations. Debug logging is useful for diagnosing complex migration issues and understanding the behavior of Flyway. You can use the following command to enable debug logs:

docker run --platform linux/amd64 \

-v $(pwd)/sql/dml:/flyway/sql/dml \
-v $(pwd)/sql/ddl:/flyway/sql/ddl:rw \
-v $(pwd)/output:/flyway/output \
--rm redgate/flyway:9.16.1 \
-licenseKey= \
-url=jdbc:postgresql://localhost:5433/yugabyte \
-schemas=yugabyte \
-user=yugabyte \
-password= \
-connectRetries=60 \
-outputFile=/flyway/output/output.sql \
-dryRunOutput=/flyway/output/dryrun.sql \
-skipExecutingMigrations="true" \
migrate -X

Quiet Logs

The -q or –quiet option disables Flyway’s normal logging and only displays error messages. This option is useful when developers want to focus on errors without being distracted by other log messages. You can use the following command to enable quiet logs:

docker run --platform linux/amd64 \
-v $(pwd)/sql/dml:/flyway/sql/dml \
-v $(pwd)/sql/ddl:/flyway/sql/ddl:rw \
-v $(pwd)/output:/flyway/output \
--rm redgate/flyway:9.16.1 \
-licenseKey= \
-url=jdbc:postgresql://localhost:5433/yugabyte \
-schemas=yugabyte \
-user=yugabyte \
-password= \
-connectRetries=60 \
-outputFile=/flyway/output/output.sql \
-dryRunOutput=/flyway/output/dryrun.sql \
-skipExecutingMigrations="true" \
migrate -q

Info Logs :

The -i or –info option displays Flyway’s normal logging and error messages. This is the default logging level for Flyway CLI. You can use the following command to enable info logs:

docker run --platform linux/amd64 \
-v $(pwd)/sql/dml:/flyway/sql/dml \
-v $(pwd)/sql/ddl:/flyway/sql/ddl:rw \
-v $(pwd)/output:/flyway/output \
--rm redgate/flyway:9.16.1 \
-licenseKey= \
-url=jdbc:postgresql://localhost:5433/yugabyte \
-schemas=yugabyte \
-user=yugabyte \
-password= \
-connectRetries=60 \
-outputFile=/flyway/output/output.sql \
-dryRunOutput=/flyway/output/dryrun.sql \
-skipExecutingMigrations="true" \
migrate -i

Warning Logs

The -W or –warnings option displays warnings in addition to Flyway’s normal logging and error messages. Warnings are typically non-fatal issues that may affect the migration process but do not prevent it from completing. You can use the following command to enable warning logs:

docker run --platform linux/amd64 \
-v $(pwd)/sql/dml:/flyway/sql/dml \
-v $(pwd)/sql/ddl:/flyway/sql/ddl:rw \
-v $(pwd)/output:/flyway/output \
--rm redgate/flyway:9.16.1 \
-licenseKey= \
-url=jdbc:postgresql://localhost:5433/yugabyte \
-schemas=yugabyte \
-user=yugabyte \
-password= \
-connectRetries=60 \
-outputFile=/flyway/output/output.sql \
-dryRunOutput=/flyway/output/dryrun.sql \
-skipExecutingMigrations="true" \
migrate -W

Error Logging:

The -E or –error option is used with Flyway to display only error messages and hide all other logging messages. This is useful when developers want to focus on errors without being distracted by warnings or informational messages. By using this option, developers can easily identify and resolve any issues related to migration scripts.

To enable error logging with Flyway, include the -E or –error option in the Flyway command.

docker run --platform linux/amd64 \
-v $(pwd)/sql/dml:/flyway/sql/dml \
-v $(pwd)/sql/ddl:/flyway/sql/ddl:rw \
-v $(pwd)/output:/flyway/output \
--rm redgate/flyway:9.16.1 \
-licenseKey= \
-url=jdbc:postgresql://localhost:5433/yugabyte \
-schemas=yugabyte \
-user=yugabyte \
-password= \
-connectRetries=60 \
-outputFile=/flyway/output/output.sql \
-dryRunOutput=/flyway/output/dryrun.sql \
-skipExecutingMigrations="true" \
migrate -E

6. How to get execution time for each migration script?

To get the execution time for each migration script, developers can use the Flyway command-line interface (CLI) or SQL query.

Using the CLI, developers can run the following command:

select * from yugabyte.flyway_schema_history;

The last column (execution_time) in the flyway_schema_history table contains the total execution time for each migration script.

7.How to get the number of affected rows while doing the DML operation?

The -X option is a flag in Flyway that enables debug logging. When it is included in the Flyway command, Flyway will output additional logging information during the migration process, including the SQL statements that are executed.

To get the number of affected rows while doing the DML operation, developers can use the -X option with the migrate command:

docker run --platform linux/amd64
-v $(pwd)/sql/dml:/flyway/sql/dml
-v $(pwd)/sql/ddl:/flyway/sql/ddl:rw
-v $(pwd)/output:/flyway/output
--name flyway
--rm redgate/flyway:9.16.1
-licenseKey=
-url=jdbc:postgresql://localhost:5433/yugabyte
-user=yugabyte
-password=
-connectRetries=60
-enterprise
-cleanDisabled="false"
-X
migrate

When used together as -X migrate, the command enables debug logging and applies all available migrations to the database while outputting additional information about the migration process, including the number of affected rows. This can be useful to troubleshoot any issues that may occur during the migration process and to gain a deeper understanding of what is happening behind the scenes.

Conclusion:

In conclusion, proper logging and debugging are essential when working with Flyway, as they help to diagnose and troubleshoot issues that may arise during the migration process. By setting the appropriate logging levels, developers can focus on the information that is most relevant to their needs, whether it be error messages, warnings, or detailed debug information.

Additionally, Flyway provides various tools to help developers track the execution time of each migration script and the number of affected rows during DML operations. These features are invaluable when optimizing the performance of a database migration and ensuring that it completes successfully.

Overall, Flyway’s logging and debugging capabilities make it an indispensable tool for developers working with databases, providing them with the insight and control needed to ensure smooth and efficient migrations.

Reference: