Flyaway upgrade

[MitchellShiell]

Summary

The SONG server fails to initialize the database schema during startup due to missing database types and tables.

Description

When starting the SONG server, the application logs indicate that several database types and tables do not exist. When flyaway is enabled, this issue prevents the server from properly setting up the database schema and causes the container to exit.

Here is a snippet of relevant logs, part of the warning might also point to an error in my or the songs flyway location:

2024-05-27 13:47:43,865 [main] INFO o.f.c.i.d.DatabaseFactory - Database: jdbc:postgresql://song-db:5432/songDb (PostgreSQL 16.3)
2024-05-27 13:47:43,888 [main] WARN o.f.c.i.d.b.Database - Flyway upgrade recommended: PostgreSQL 16.3 is newer than this version of Flyway and support has not been tested.
2024-05-27 13:47:44,029 [main] WARN o.f.c.i.s.c.ClassPathScanner - Unable to resolve location classpath
/sql

Steps to Reproduce

1. Run the following docker-compose.yml snippet and view the song container logs

version: "3"
services:
  # ==========================
  # Song Database
  # ==========================
  song-db:
    image: postgres:latest
    environment:
      POSTGRES_USER: admin
      POSTGRES_PASSWORD: admin123
      POSTGRES_DB: songDb
    volumes:
      - ./persistentStorage/data-song-db:/var/lib/postgresql/data
    networks:
      - db-network
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U admin -d songDb"]
      interval: 5s
      timeout: 5s
      retries: 5
      start_period: 5s

  # ==========================
  # Zookeeper
  # ==========================
  zookeeper:
    image: confluentinc/cp-zookeeper:latest
    environment:
      ZOOKEEPER_CLIENT_PORT: 2181
      ZOOKEEPER_TICK_TIME: 2000
    ports:
      - "22181:2181"
    healthcheck:
      test: ["CMD-SHELL", "echo srvr | nc 127.0.0.1 2181 | grep Mode"]
      interval: 10s
      timeout: 10s
      retries: 3
      start_period: 20s

  # ==========================
  # Kafka
  # ==========================
  kafka:
    image: confluentinc/cp-kafka:latest
    depends_on:
      zookeeper:
        condition: service_healthy
    ports:
      - "29092:29092"
      - "9092:9092"
    environment:
      KAFKA_BROKER_ID: 1
      KAFKA_ZOOKEEPER_CONNECT: zookeeper:2181
      KAFKA_ADVERTISED_LISTENERS: PLAINTEXT://host.docker.internal:9092,PLAINTEXT_HOST://localhost:29092
      KAFKA_LISTENER_SECURITY_PROTOCOL_MAP: PLAINTEXT:PLAINTEXT,PLAINTEXT_HOST:PLAINTEXT
      KAFKA_INTER_BROKER_LISTENER_NAME: PLAINTEXT
      KAFKA_OFFSETS_TOPIC_REPLICATION_FACTOR: 1
    healthcheck:
      test: ["CMD-SHELL", "kafka-topics --list --bootstrap-server localhost:9092"]
      interval: 10s
      timeout: 10s
      retries: 3
      start_period: 20s
  
  # ==========================
  # Song
  # ==========================
  song:
    image: ghcr.io/overture-stack/song-server:438c2c42
    depends_on:
      song-db:
        condition: service_healthy
      kafka:
        condition: service_healthy
      zookeeper:
        condition: service_healthy
    ports:
      - "8080:8080"
    environment:
      # PostgreSQL connection details
      SPRING_DATASOURCE_URL: jdbc:postgresql://song-db:5432/songDb
      SPRING_DATASOURCE_USERNAME: admin
      SPRING_DATASOURCE_PASSWORD: admin123
      # Flyway migration settings
      SPRING_FLYWAY_ENABLED: true
      SPRING_FLYWAY_LOCATIONS: classpath:flyway/sql,classpath:db/migration
      # Spring Run Profiles
      SPRING_PROFILES_ACTIVE: prod,secure,score-client-cred,kafka
      # ID Management configuration
      ID_USELOCAL: true
      # Schema Strictness Configuration
      SCHEMAS_ENFORCELATEST: true
      # Kafka Event Management Configuration
      SPRING_KAFKA_BOOTSTRAP-SERVERS: kafka:9092
      SPRING_KAFKA_TEMPLATE_DEFAULT-TOPIC: song-analysis
    networks:
      - db-network

# ==========================
# Networks
# ==========================
networks:
  db-network:

Expected Behavior

The SONG server should find and apply all necessary Flyway migration scripts to create the required database types and tables.

Actual Behavior

The SONG server logs indicate that it cannot find the expected database types and tables, causing the initialization process to exit out

Possible Solution

• Upgrade Flyway: Upgrade to the latest version of Flyway to ensure compatibility with PostgreSQL 16.3.
• Verify Migration Scripts Location: Ensure the migration scripts are correctly located in the src/main/resources/db/migration directory.

Environment

• Flyway Version: 5.2.4
• Database: PostgreSQL 16.3
• SONG Server Version: song-server:438c2c4