First migration not running in existing database after initial baselining.

mrashid

I have an existing MySQL 5.7 database and I want to run migration scripts targeting it as well as keep track of the applied migrations. Redgate's flyway (v7.15 since latest version does not work with MySQL 5.7)) seems like the perfect tool for it. After the easy install by unzipping and updating my flyway.conf in exactly three lines (flyway.url, flyway.user and flyway.password), When I ran 'flyway migrate' first time, I got following error. Note that at this point I had my first migration script (v1_add_createdAt_column_in_build_table.sql) in  flyway's default location, which is the 'sql' sub-folder in the installation folder.

C:\flyway-7.15.0>flyway migrate
Successfully validated 0 migrations (execution time 00:00.005s)
WARNING: No migrations found. Are your locations set up correctly?
ERROR: Found non-empty schema(s) `testdb1` but no schema history table. Use baseline() or set baselineOnMigrate to true to initialize the schema history table.

After setting flyway.baselineOnMigrate=true as suggested, I ran 'flyway migrate' again and the error seemed to have been fixed and I saw flyway_schema_history table was created in the target database (specified in flyway.url in flyway.conf) with one record specific to this initial baselining of the database.

C:\flyway-7.15.0>flyway migrate
Successfully validated 0 migrations (execution time 00:00.005s)
WARNING: No migrations found. Are your locations set up correctly?
Creating Schema History table `testdb1`.`flyway_schema_history` with baseline ...
Successfully baselined schema with version: 1
Current version of schema `testdb1`: 1
Schema `testdb1` is up to date. No migration necessary.

Here's the first record .in the schema history table

installed_rank - 1
version - 1
description - << Flyway Baseline >>
type - BASELINE
script - << Flyway Baseline >>
checksum =
installed_by - root
installed_on - 2023-10-11 15:16:12
execution_time - 0
success - 1


But my issue happened when I ran 'flyway migrate' to apply the change in v1_add_createdAt_column_in_build_table.sql as I see flyway ignore the script and not run the migration (see output below).

C:\flyway-7.15.0>flyway migrate
Successfully validated 1 migration (execution time 00:00.010s)
Current version of schema `testdb1`: 1
Schema `testdb1` is up to date. No migration necessary.

Unsure about naming of my first migration sql script, I tried bumping version up to 2 and also separating version and description parts of the migration sql script name by two underscores (v2__add_createdAt_column_in_build_table.sql). But this did not work. What am I doing wrong here in terms of setting up flyway and running the first migration in an already existing database?

Jon_Kirkwood

Hi @mrashid

Thank you for reaching out on the Redgate forums regarding your Flyway inquiry.

 It seems like you've made a good start with Flyway, but there may be an issue with the naming and location of your migration scripts. To address this, you should ensure that your migration scripts are correctly named and located in the right folder.

 Here are some areas to look that may be causing the 'No migrations found' warning.

•  Naming Conventions: Your migration script names should follow a specific naming convention, which includes the version number and description separated by double underscores. For example:

  <strong>V1__add_createdAt_column_in_build_table.sql</strong>.

• Location of Migration Scripts: By default, Flyway looks for migration scripts in the "sql" sub-folder of its installation folder. However, it's recommended to keep your migration scripts separate from the Flyway installation directory. You can specify the location explicitly in your Flyway configuration file (flyway.conf) using the flyway.locations property.
  For example, if you have your migration scripts in a folder named "db/migration," your configuration might look like this:
  flyway.locations=filesystem:./db/migration

• Update Version in Migration Script: Ensure that the version in your migration script matches the version specified in the filename. For example, the script V1__add_createdAt_column_in_build_table.sql should contain:

-- db/migration/V1__add_createdAt_column_in_build_table.sql 
ALTER TABLE build 
ADD created_at TIMESTAMP;

• Migration Metadata Table: Flyway tracks the applied migrations in a metadata table. Ensure that this table exists in your database. In your case, you have already initialized the schema history table with the baseline. It's named flyway_schema_history, and it should be in the same schema specified in your database URL.
  Make sure this table exists and is in the correct schema (in your case, testdb1).

Hopefully these areas help to solve your migration and get Flyway up and running.

VHK

Jon Kirkwood - Thanks for your suggestions, I tried all above steps and still have same problem. 

Here is my ticket# - https://forum.red-gate.com/discussion/90648/flyway-schema-public-is-up-to-date-no-migration-necessary/p1?new=1