[Bug] Java | Spring Boot | Flyway | Falha ao executar migration

Oi!

Apagando o banco e criando novamente resolve:

drop database vollmed_api;
create database vollmed_api;

Pare o projeto e rode novamente que vai resolver.

Obs: certifique-se no application.properties que o banco de dados utilizado pela API é esse mesmo (veja na url de conexão)

Oi professor! Já realizei o procedimento de deletar e criar novamente o banco de dados e o erro persiste. O projeto também não está rodando, pois ele para no momento que eu recebo esse erro.

Apenas confirmando: a URL correta é a seguinte, certo?

jdbc:mysql://localhost/vollmed_api

Inseri em meu método main o seguinte método que encontrei no seguinte stackoverflow, o que realmente parou o erro; contudo, a tabela ainda não foi criada em meu banco de dados. Você sabe por que isso pode ter acontecido?

https://stackoverflow.com/questions/37462550/flyway-repair-with-spring-boot

    @Bean
    public static FlywayMigrationStrategy cleanMigrateStrategy() {
        return flyway -> {
            flyway.repair();
            flyway.migrate();
        };
    }

Oi Leonardo!

Ao deletar e criar o banco o problema resolve. O que acredito estar acontecendo é você estar apagando e recriando o banco de dados incorreto, que não é o mesmo sendo utilizado na aplicação.

Manda aqui um print dos comandos para deletar o banco que voce rodou no MySQL.

Oi professor! Verifiquei aqui e parece que é exatamente isso que está acontecendo. Embora eu esteja indicando um banco de dados no application.properties, parece que o Spring está usando outro banco totalmente diferente.

Envio aqui print do banco de dados mostrado no Spring, das configurações que coloquei no meu application.properties (o nome do banco de dados é diferente porque esse é um projeto pessoal em vez do usado nas aulas, mas estou usando as mesmas orientações para desenvolvê-lo =D) e dos comandos que estou usando para apagar e recriar o banco de dados.

Você sabe por qual motivo meu application.properties pode não estar funcionando?

Manda o log completo que sai ai rodar o projeto no intellij

Aqui está! (agora pouco ele começou a dar um erro de acesso negado também, com um usuário que também não reconheço).

> Task :FeaturesToggleApplication.main()

  .   ____          _            __ _ _
 /\\ / ___'_ __ _ _(_)_ __  __ _ \ \ \ \
( ( )\___ | '_ | '_| | '_ \/ _` | \ \ \ \
 \\/  ___)| |_)| | | | | || (_| |  ) ) ) )
  '  |____| .__|_| |_|_| |_\__, | / / / /
 =========|_|==============|___/=/_/_/_/
 :: Spring Boot ::             (v3.2.0-M1)

2023-08-30T18:10:36.810-03:00  INFO 76539 --- [  restartedMain] c.p.f.FeaturesToggleApplication          : Starting FeaturesToggleApplication using Java 17.0.8 with PID 76539 (/home/leonardo/personal-repos/features-toggle/build/classes/java/main started by leonardo in /home/leonardo/personal-repos/features-toggle)
2023-08-30T18:10:36.813-03:00  INFO 76539 --- [  restartedMain] c.p.f.FeaturesToggleApplication          : No active profile set, falling back to 1 default profile: "default"
2023-08-30T18:10:36.850-03:00  INFO 76539 --- [  restartedMain] .e.DevToolsPropertyDefaultsPostProcessor : Devtools property defaults active! Set 'spring.devtools.add-properties' to 'false' to disable
2023-08-30T18:10:36.850-03:00  INFO 76539 --- [  restartedMain] .e.DevToolsPropertyDefaultsPostProcessor : For additional web related logging consider setting the 'logging.level.web' property to 'DEBUG'
2023-08-30T18:10:36.871-03:00  INFO 76539 --- [  restartedMain] .s.b.d.c.l.DockerComposeLifecycleManager : Using Docker Compose file '/home/leonardo/personal-repos/features-toggle/compose.yaml'
2023-08-30T18:10:37.310-03:00  INFO 76539 --- [utReader-stderr] o.s.boot.docker.compose.core.DockerCli   :  Container features-toggle-mysql-1  Created
2023-08-30T18:10:37.310-03:00  INFO 76539 --- [utReader-stderr] o.s.boot.docker.compose.core.DockerCli   :  Container features-toggle-mysql-1  Starting
2023-08-30T18:10:37.658-03:00  INFO 76539 --- [utReader-stderr] o.s.boot.docker.compose.core.DockerCli   :  Container features-toggle-mysql-1  Started
2023-08-30T18:10:37.658-03:00  INFO 76539 --- [utReader-stderr] o.s.boot.docker.compose.core.DockerCli   :  Container features-toggle-mysql-1  Waiting
2023-08-30T18:10:38.160-03:00  INFO 76539 --- [utReader-stderr] o.s.boot.docker.compose.core.DockerCli   :  Container features-toggle-mysql-1  Healthy
2023-08-30T18:10:39.813-03:00  INFO 76539 --- [  restartedMain] .s.d.r.c.RepositoryConfigurationDelegate : Bootstrapping Spring Data JPA repositories in DEFAULT mode.
2023-08-30T18:10:39.831-03:00  INFO 76539 --- [  restartedMain] .s.d.r.c.RepositoryConfigurationDelegate : Finished Spring Data repository scanning in 12 ms. Found 0 JPA repository interfaces.
2023-08-30T18:10:40.249-03:00  INFO 76539 --- [  restartedMain] o.s.b.w.embedded.tomcat.TomcatWebServer  : Tomcat initialized with port 8080 (http)
2023-08-30T18:10:40.256-03:00  INFO 76539 --- [  restartedMain] o.apache.catalina.core.StandardService   : Starting service [Tomcat]
2023-08-30T18:10:40.256-03:00  INFO 76539 --- [  restartedMain] o.apache.catalina.core.StandardEngine    : Starting Servlet engine: [Apache Tomcat/10.1.11]
2023-08-30T18:10:40.294-03:00  INFO 76539 --- [  restartedMain] o.a.c.c.C.[Tomcat].[localhost].[/]       : Initializing Spring embedded WebApplicationContext
2023-08-30T18:10:40.296-03:00  INFO 76539 --- [  restartedMain] w.s.c.ServletWebServerApplicationContext : Root WebApplicationContext: initialization completed in 989 ms
2023-08-30T18:10:40.682-03:00  INFO 76539 --- [  restartedMain] o.f.c.internal.license.VersionPrinter    : Flyway Community Edition 9.20.1 by Redgate
2023-08-30T18:10:40.682-03:00  INFO 76539 --- [  restartedMain] o.f.c.internal.license.VersionPrinter    : See release notes here: https://rd.gt/416ObMi
2023-08-30T18:10:40.682-03:00  INFO 76539 --- [  restartedMain] o.f.c.internal.license.VersionPrinter    : 

2023-08-30T18:10:40.714-03:00  INFO 76539 --- [  restartedMain] o.f.c.i.database.base.BaseDatabaseType   : Database: jdbc:mysql://127.0.0.1:32777/mydatabase (MySQL 8.1)
2023-08-30T18:10:40.740-03:00  WARN 76539 --- [  restartedMain] o.f.c.internal.database.base.Database    : Flyway upgrade recommended: MySQL 8.1 is newer than this version of Flyway and support has not been tested. The latest supported version of MySQL is 8.0.
2023-08-30T18:10:40.800-03:00  INFO 76539 --- [  restartedMain] o.f.core.internal.command.DbRepair       : Successfully repaired schema history table `mydatabase`.`flyway_schema_history` (execution time 00:00.050s).
2023-08-30T18:10:40.801-03:00  INFO 76539 --- [  restartedMain] o.f.core.internal.command.DbRepair       : Manual cleanup of the remaining effects of the failed migration may still be required.
2023-08-30T18:10:40.835-03:00  WARN 76539 --- [  restartedMain] o.f.c.internal.database.base.Database    : Flyway upgrade recommended: MySQL 8.1 is newer than this version of Flyway and support has not been tested. The latest supported version of MySQL is 8.0.
2023-08-30T18:10:40.847-03:00  INFO 76539 --- [  restartedMain] o.f.core.internal.command.DbValidate     : Successfully validated 2 migrations (execution time 00:00.007s)
2023-08-30T18:10:40.862-03:00  INFO 76539 --- [  restartedMain] o.f.core.internal.command.DbMigrate      : Current version of schema `mydatabase`: << Empty Schema >>
2023-08-30T18:10:40.874-03:00  INFO 76539 --- [  restartedMain] o.f.core.internal.command.DbMigrate      : Migrating schema `mydatabase` to version "1 - create-features-toggle-users-table"
2023-08-30T18:10:40.883-03:00 ERROR 76539 --- [  restartedMain] o.f.core.internal.command.DbMigrate      : Migration of schema `mydatabase` to version "1 - create-features-toggle-users-table" failed! Please restore backups and roll back database and code!
2023-08-30T18:10:40.911-03:00  WARN 76539 --- [  restartedMain] ConfigServletWebServerApplicationContext : Exception encountered during context initialization - cancelling refresh attempt: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'flywayInitializer' defined in class path resource [org/springframework/boot/autoconfigure/flyway/FlywayAutoConfiguration$FlywayConfiguration.class]: Migration V1__create-features-toggle-users-table.sql failed
-----------------------------------------------------------
SQL State  : 42000
Error Code : 1142
Message    : CREATE command denied to user 'myuser'@'172.19.0.1' for table 'users'
Location   : db/migration/V1__create-features-toggle-users-table.sql (/home/leonardo/personal-repos/features-toggle/build/resources/main/db/migration/V1__create-features-toggle-users-table.sql)
Line       : 1
Statement  : CREATE TABLE features_toggle_api.users (
    id INT,
    name VARCHAR(255),
    email VARCHAR(255),
    password VARCHAR(255),
    is_active BOOLEAN,
    created_by INT,
    updated_by INT,

    primary key(id)
)

2023-08-30T18:10:40.914-03:00  INFO 76539 --- [  restartedMain] o.apache.catalina.core.StandardService   : Stopping service [Tomcat]
2023-08-30T18:10:40.932-03:00  INFO 76539 --- [  restartedMain] .s.b.a.l.ConditionEvaluationReportLogger : 

Error starting ApplicationContext. To display the condition evaluation report re-run your application with 'debug' enabled.
2023-08-30T18:10:40.945-03:00 ERROR 76539 --- [  restartedMain] o.s.boot.SpringApplication               : Application run failed

org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'flywayInitializer' defined in class path resource [org/springframework/boot/autoconfigure/flyway/FlywayAutoConfiguration$FlywayConfiguration.class]: Migration V1__create-features-toggle-users-table.sql failed
-----------------------------------------------------------

Pelo log parece que sua aplicação ta usando o docker e deve ser dele que vem o MySQL.

no log:

Using Docker Compose file '/home/leonardo/personal-repos/features-toggle/compose.yaml'

Sim sim, eu coloquei o Docker na aplicação para aprender a usá-lo, mas não sabia que o Docker possuía um MySQL próprio hehehe como faço para acessar o banco ou para direcioná-lo para o meu MySQL local então?

Problema resolvido. Eu só precisava montar o docker-compose.yml com a seguinte estrutura:

services:
  mysql:
    network_mode: "host"
    image: 'mysql:latest'
    environment:
      - 'MYSQL_DATABASE=features_toggle_api'
      - 'MYSQL_PASSWORD=...'
      - 'MYSQL_ROOT_PASSWORD=...'
      - 'MYSQL_USER=leonardo'
    ports:
      - '3306'

Obrigado professor! :)