Skip to content

Instantly share code, notes, and snippets.

@ihoneymon

ihoneymon/20171103_flyway_intro_script.asc

Last active January 8, 2023 07:14
Show Gist options
  • Save ihoneymon/dc6cf72805b64e093cab8988f0e78782 to your computer and use it in GitHub Desktop.
Save ihoneymon/dc6cf72805b64e093cab8988f0e78782 to your computer and use it in GitHub Desktop.
Download ZIP
Flyway 소개영상을 위한 스크립트
Raw
20171103_flyway_intro_script.asc

Flyway 기능소개

준비

  • 스프링 부트에서 다음 의존성을 추가한다.

build.gradle
buildscript {
	ext {
		springBootVersion = '1.5.8.RELEASE'
	}
	repositories {
		mavenCentral()
	}
	dependencies {
		classpath("org.springframework.boot:spring-boot-gradle-plugin:${springBootVersion}")
	}
}

apply plugin: 'java'
apply plugin: 'eclipse'
apply plugin: 'org.springframework.boot'

group = 'io.honeymon'
version = '0.0.1-SNAPSHOT'
sourceCompatibility = 1.8

repositories {
	mavenCentral()
}


dependencies {
	compile('org.springframework.boot:spring-boot-starter-data-jpa')
	compile('org.flywaydb:flyway-core')
	compile('org.springframework.boot:spring-boot-starter-web')
	runtime('com.h2database:h2')
	compileOnly('org.projectlombok:lombok')
	testCompile('org.springframework.boot:spring-boot-starter-test')
}
application.yml
spring:
  datasource:
    url: jdbc:h2:file:~/demo;DB_CLOSE_ON_EXIT=FALSE;
    username: sa
    password:
  jpa:
    hibernate:
      ddl-auto: validate  // (1)
  h2:
    console:
      enabled: true  // (2)
flyway:
  enabled: true // (3)

  1. ddl 실행옵션으로 (none, update, validate, create, create-drop) 이 있다.

  2. h2-console 실행조건으로 기본적으로는 `false`로 되어 있다.

  3. flyway 활성화 설정. true 가 기본

Flyway가 활성화되면, 애플리케이션의 DataSource 를 이용해서 지정된 DB 내에 schema_version 이라고 테이블을 생성한다.

spring:
  datasource:
    url: jdbc:h2:file:~/demo;DB_CLOSE_ON_EXIT=FALSE;
    username: sa
    password:
  jpa:
    hibernate:
      ddl-auto: validate
flyway:
  enabled: true
version_rank  	installed_rank  	version  	description  	type  	script  	checksum  	installed_by  	installed_on  	execution_time  	success

spring.jpa.hibername.ddl-auto: validate

@Getter
@Setter
@NoArgsConstructor(access = AccessLevel.PROTECTED)
@Entity
public class User extends AbstractPersistable<Long> {
    @Column(unique = true)
    private String email;
    private String name;
    private String desc;

    public User(String email) {
        Assert.hasText(email, "Email is required.");
        this.email = email;
    }
}
flyway V1__init.sql
--
-- V1: Create USER TABLE
--
--
CREATE TABLE `user` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `email` varchar(255) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `desc` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `UK_demo_email` (`email`),
) DEFAULT CHARSET=utf8;

User 엔티티 필드만 변경됐을 때

public class User extends AbstractPersistable<Long> {
	// 생략
    private String descr; // desc -> descr
    // 생략
}

spring.jpa.hibername.ddl-auto: validate 상태에서는 엔티티와 DB 엔티티 구조가 달라서 다음과 같이 컬럼을 찾을 수 없다고 에러가 발생

Caused by: javax.persistence.PersistenceException: [PersistenceUnit: default] Unable to build Hibernate SessionFactory
	at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl.persistenceException(EntityManagerFactoryBuilderImpl.java:954)
	// 생략
Caused by: org.hibernate.tool.schema.spi.SchemaManagementException: Schema-validation: missing column [descr] in table [user]
	at org.hibernate.tool.schema.internal.SchemaValidatorImpl.validateTable(SchemaValidatorImpl.java:85) ~[hibernate-core-5.0.12.Final.jar:5.0.12.Final]
	at org.hibernate.tool.schema.internal.SchemaValidatorImpl.doValidation(SchemaValidatorImpl.java:50) ~[hibernate-core-5.0.12.Final.jar:5.0.12.Final]
	at org.hibernate.tool.hbm2ddl.SchemaValidator.validate(SchemaValidator.java:91) ~[hibernate-core-5.0.12.Final.jar:5.0.12.Final]
	at org.hibernate.internal.SessionFactoryImpl.<init>(SessionFactoryImpl.java:475) ~[hibernate-core-5.0.12.Final.jar:5.0.12.Final]
	at org.hibernate.boot.internal.SessionFactoryBuilderImpl.build(SessionFactoryBuilderImpl.java:444) ~[hibernate-core-5.0.12.Final.jar:5.0.12.Final]
	at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl.build(EntityManagerFactoryBuilderImpl.java:879) ~[hibernate-entitymanager-5.0.12.Final.jar:5.0.12.Final]
	... 27 common frames omitted

기존 스크립트를 변경했을 때

위에서 변경했던 descr 필드를 desc 필드로 변경한 후에

flyway V1__init.sql
--
-- V1: Create USER TABLE
--
--
CREATE TABLE `user` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `email` varchar(255) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `descr` varchar(255) DEFAULT NULL,	// (1)
  PRIMARY KEY (`id`),
  UNIQUE KEY `UK_demo_email` (`email`),
) DEFAULT CHARSET=utf8;

  1. desc → descr 로 변경

다음과 같은 에러가 발생한다.

org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'flywayInitializer' defined in class path resource [org/springframework/boot/autoconfigure/flyway/FlywayAutoConfiguration$FlywayConfiguration.class]: Invocation of init method failed; nested exception is org.flywaydb.core.api.FlywayException: Validate failed. Migration Checksum mismatch for migration 1
-> Applied to database : -1341614813
-> Resolved locally    : 158332526
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1628)
	// 생략
Caused by: org.flywaydb.core.api.FlywayException: Validate failed. Migration Checksum mismatch for migration 1
-> Applied to database : -1341614813
-> Resolved locally    : 158332526
	at org.flywaydb.core.Flyway.doValidate(Flyway.java:1108) ~[flyway-core-3.2.1.jar:na]
	at org.flywaydb.core.Flyway.access$300(Flyway.java:62) ~[flyway-core-3.2.1.jar:na]
	at org.flywaydb.core.Flyway$1.execute(Flyway.java:1012) ~[flyway-core-3.2.1.jar:na]
	at org.flywaydb.core.Flyway$1.execute(Flyway.java:1006) ~[flyway-core-3.2.1.jar:na]
	at org.flywaydb.core.Flyway.execute(Flyway.java:1418) ~[flyway-core-3.2.1.jar:na]
	at org.flywaydb.core.Flyway.migrate(Flyway.java:1006) ~[flyway-core-3.2.1.jar:na]
	at org.springframework.boot.autoconfigure.flyway.FlywayMigrationInitializer.afterPropertiesSet(FlywayMigrationInitializer.java:66) ~[spring-boot-autoconfigure-1.5.8.RELEASE.jar:1.5.8.RELEASE]
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1687) ~[spring-beans-4.3.12.RELEASE.jar:4.3.12.RELEASE]
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1624) ~[spring-beans-4.3.12.RELEASE.jar:4.3.12.RELEASE]
	... 23 common frames omitted

flyway 에서 DB에 생성한 schema_version`에 등록되어 있는 해시값과 달라지면 다음과 같이 오류가 발생한다. 이 때에는 `V1init.sql 을 원래대로 복원하고 새로운 파일(예: V2modify_column.sql)을 생성하여 변경사항을 반영해야한다.

번호를 건너뛰는 경우

V1에서 V3의 스크립트를 실행하면 정상적으로 실행이 된다. .V3__add_job_at_user.sql

ALTER TABLE user ADD COLUMN `job` VARCHAR(50) DEFAULT NULL;

그 후 V2 파일을 생성하면 다음과 같은 오류가 발생한다. .V2__add_company_at_user.sql

ALTER TABLE user ADD COLUMN `company` VARCHAR(50) DEFAULT NULL;
org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'flywayInitializer' defined in class path resource [org/springframework/boot/autoconfigure/flyway/FlywayAutoConfiguration$FlywayConfiguration.class]: Invocation of init method failed; nested exception is org.flywaydb.core.api.FlywayException: Validate failed. Detected resolved migration not applied to database: 2
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1628)
	// 생략
Caused by: org.flywaydb.core.api.FlywayException: Validate failed. Detected resolved migration not applied to database: 2
	at org.flywaydb.core.Flyway.doValidate(Flyway.java:1108) ~[flyway-core-3.2.1.jar:na]
	at org.flywaydb.core.Flyway.access$300(Flyway.java:62) ~[flyway-core-3.2.1.jar:na]
	at org.flywaydb.core.Flyway$1.execute(Flyway.java:1012) ~[flyway-core-3.2.1.jar:na]
	at org.flywaydb.core.Flyway$1.execute(Flyway.java:1006) ~[flyway-core-3.2.1.jar:na]
	at org.flywaydb.core.Flyway.execute(Flyway.java:1418) ~[flyway-core-3.2.1.jar:na]
	at org.flywaydb.core.Flyway.migrate(Flyway.java:1006) ~[flyway-core-3.2.1.jar:na]
	at org.springframework.boot.autoconfigure.flyway.FlywayMigrationInitializer.afterPropertiesSet(FlywayMigrationInitializer.java:66) ~[spring-boot-autoconfigure-1.5.8.RELEASE.jar:1.5.8.RELEASE]
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1687) ~[spring-beans-4.3.12.RELEASE.jar:4.3.12.RELEASE]
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1624) ~[spring-beans-4.3.12.RELEASE.jar:4.3.12.RELEASE]
	... 23 common frames omitted

V2를 V4로 변경하면 정상적으로 수행된다.

스크립트에 오류가 있는 경우

ALTER TABLE user ADD COLUMNS `note` VARCHAR(2000) DEFAULT NULL;	// (1)

  1. COLUMN를 COLUMNS로 변경하여 오류가 발생하도록 했다.

org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'flywayInitializer' defined in class path resource [org/springframework/boot/autoconfigure/flyway/FlywayAutoConfiguration$FlywayConfiguration.class]: Invocation of init method failed; nested exception is org.flywaydb.core.internal.dbsupport.FlywaySqlScriptException:
Migration V5__add_note_at_user.sql failed
-----------------------------------------
SQL State  : HY004
Error Code : 50004
Message    : Unknown data type: "NOTE"; SQL statement:
ALTER TABLE user ADD COLUMNS `note` VARCHAR(2000) DEFAULT NULL [50004-196]
Location   : db/migration/V5__add_note_at_user.sql (/Users/jake/workspace/demo/out/production/resources/db/migration/V5__add_note_at_user.sql)
Line       : 1
Statement  : ALTER TABLE user ADD COLUMNS `note` VARCHAR(2000) DEFAULT NULL

다시 실행하면 메시지는 다음과 같이 변경된다.

org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'flywayInitializer' defined in class path resource [org/springframework/boot/autoconfigure/flyway/FlywayAutoConfiguration$FlywayConfiguration.class]: Invocation of init method failed; nested exception is org.flywaydb.core.api.FlywayException: Schema "PUBLIC" contains a failed migration to version 5 !
	// 생략
Caused by: org.flywaydb.core.api.FlywayException: Schema "PUBLIC" contains a failed migration to version 5 !
	at org.flywaydb.core.internal.command.DbMigrate$2.doInTransaction(DbMigrate.java:196) ~[flyway-core-3.2.1.jar:na]
	at org.flywaydb.core.internal.command.DbMigrate$2.doInTransaction(DbMigrate.java:156) ~[flyway-core-3.2.1.jar:na]
	at org.flywaydb.core.internal.util.jdbc.TransactionTemplate.execute(TransactionTemplate.java:72) ~[flyway-core-3.2.1.jar:na]
	at org.flywaydb.core.internal.command.DbMigrate.migrate(DbMigrate.java:156) ~[flyway-core-3.2.1.jar:na]
	at org.flywaydb.core.Flyway$1.execute(Flyway.java:1059) ~[flyway-core-3.2.1.jar:na]
	at org.flywaydb.core.Flyway$1.execute(Flyway.java:1006) ~[flyway-core-3.2.1.jar:na]
	at org.flywaydb.core.Flyway.execute(Flyway.java:1418) ~[flyway-core-3.2.1.jar:na]
	at org.flywaydb.core.Flyway.migrate(Flyway.java:1006) ~[flyway-core-3.2.1.jar:na]
	at org.springframework.boot.autoconfigure.flyway.FlywayMigrationInitializer.afterPropertiesSet(FlywayMigrationInitializer.java:66) ~[spring-boot-autoconfigure-1.5.8.RELEASE.jar:1.5.8.RELEASE]
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1687) ~[spring-beans-4.3.12.RELEASE.jar:4.3.12.RELEASE]
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1624) ~[spring-beans-4.3.12.RELEASE.jar:4.3.12.RELEASE]
	... 23 common frames omitted

데이터베이스를 보면 다음과 같이 되어 있는 것을 볼 수 있다. V5__add_note_at_user.sql의 success가 FALSE로 되어 있는 것을 확인할 수 있다. 스크립트를 실행 중에 실패했다는 것을 의미한데. V6 버전을 올려도 V5가 실패하면 V6가 실행되지 않기 때문에 V5를 정상적으로 동작하도록 수정을 한 후에 schema_version 테이블에서 실패한 컬럼을 지우고 다시 실행해야 한다.

version_rank  	installed_rank  	version  	description  	type  	script  	checksum  	installed_by  	installed_on  	execution_time  	success
1	1	1	init	SQL	V1__init.sql	-1341614813	SA	2017-11-03 14:47:55.508	6	TRUE
2	2	3	add job at user	SQL	V3__add_job_at_user.sql	-1714421583	SA	2017-11-03 14:47:55.529	13	TRUE
3	3	4	add company at user	SQL	V4__add_company_at_user.sql	-297101317	SA	2017-11-03 14:47:55.544	7	TRUE
4	4	5	add note at user	SQL	V5__add_note_at_user.sql	-1599470887	SA	2017-11-03 14:47:55.556	5	FALSE

Flyway 에서 변경이력을 관리하기 위한 발급번호는 다음에서 확인이 가능하다.

DELETE FROM "schema_version" WHERE "version_rank" = 4;

실패한 스크립트를 제거하고 스크립트의 오류를 수정하자.

ALTER TABLE user ADD COLUMN `note` VARCHAR(2000) DEFAULT NULL;

애플리케이션을 다시 실행하면 schema_version 테이블의 내용은 다음과 같이 변경될 것이다.

version_rank  	installed_rank  	version  	description  	type  	script  	checksum  	installed_by  	installed_on  	execution_time  	success
1	1	1	init	SQL	V1__init.sql	-1341614813	SA	2017-11-03 14:47:55.508	6	TRUE
2	2	3	add job at user	SQL	V3__add_job_at_user.sql	-1714421583	SA	2017-11-03 14:47:55.529	13	TRUE
3	3	4	add company at user	SQL	V4__add_company_at_user.sql	-297101317	SA	2017-11-03 14:47:55.544	7	TRUE
4	4	5	add note at user	SQL	V5__add_note_at_user.sql	-1402017088	SA	2017-11-03 15:25:04.175	22	TRUE
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment