Error throwing while inserting data through flyway when the value is $ in the query

Question

Error throwing while inserting data through flyway when the value is $ in the query

I am getting an error while inserting data into database whenever It found “$” in the value field. I am using flyway. But when I am inserting manually the value is inserting fine.

This is my insertion query in flyway :

v2_InsertData.sql

INSERT INTO property
         (application, profile, label, property_key, property_value)
     VALUES
         (‘app’, 'dev', 'v1', 'database.master.url',
'jdbc:mysql://${MYSQL_HOST_NAME:localhost}:${MYSQL_PORT:3306}/master?useUnicode=true&characterEncoding=UTF-8&useSSL=false&allowPublicKeyRetrieval=true'
         );

Requirement :

I am using this data through properties file

Db.properties

database.master.url=jdbc:mysql://${MYSQL_HOST_NAME:localhost}:${MYSQL_PORT:3306}/master?useUnicode=true&characterEncoding=UTF-8&useSSL=false&allowPublicKeyRetrieval=true

Db.config

@Configuration
@PropertySource(value = { "classpath:properties/db.properties" })
@Data
public class DBConfig {
    @Value("${database.master.url}")
    private String url;
}

Now I have moved to spring cloud config and that is loading fine.

So my db.config becomes

@Configuration
@Data
public class DBConfig {
    @Value("${database.master.url}")
    private String url;
}

Error :

org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'flywayInitializer' defined in class path resource [org/springframework/boot/autoconfigure/flyway/FlywayAutoConfiguration$FlywayConfiguration.class]: Unable to parse statement in db/migration/V4__Insert_Property.sql at line 1 col 1. See https://rd.gt/3ipi7Pm for more information: No value provided for placeholder: ${MYSQL_HOST_NAME:localhost}.  Check your configuration!
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1770) ~[spring-beans-6.0.12.jar:6.0.12]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:598) ~[spring-beans-6.0.12.jar:6.0.12]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:520) ~[spring-beans-6.0.12.jar:6.0.12]
    at org.springframework.beans.factory.support.AbstractBeanFactory.lambda$doGetBean$0(AbstractBeanFactory.java:325) ~[spring-beans-6.0.12.jar:6.0.12]
    at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:234) ~[spring-beans-6.0.12.jar:6.0.12]
    at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:323) ~[spring-beans-6.0.12.jar:6.0.12]
    at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:199) ~[spring-beans-6.0.12.jar:6.0.12]
    at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:312) ~[spring-beans-6.0.12.jar:6.0.12]
    at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:199) ~[spring-beans-6.0.12.jar:6.0.12]
    at org.springframework.context.support.AbstractApplicationContext.getBean(AbstractApplicationContext.java:1158) ~[spring-context-6.0.12.jar:6.0.12]
    at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:932) ~[spring-context-6.0.12.jar:6.0.12]
    at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:608) ~[spring-context-6.0.12.jar:6.0.12]
    at org.springframework.boot.web.servlet.context.ServletWebServerApplicationContext.refresh(ServletWebServerApplicationContext.java:146) ~[spring-boot-3.1.4.jar:3.1.4]
    at org.springframework.boot.SpringApplication.refresh(SpringApplication.java:737) ~[spring-boot-3.1.4.jar:3.1.4]
    at org.springframework.boot.SpringApplication.refreshContext(SpringApplication.java:439) ~[spring-boot-3.1.4.jar:3.1.4]
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:315) ~[spring-boot-3.1.4.jar:3.1.4]
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:1309) ~[spring-boot-3.1.4.jar:3.1.4]
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:1298) ~[spring-boot-3.1.4.jar:3.1.4]
    at com.myApp.confingserver.ConfingServerApplication.main(ConfingServerApplication.java:12) ~[classes/:na]
Caused by: org.flywaydb.core.api.FlywayException: Unable to parse statement in db/migration/V4__Insert_Property.sql at line 1 col 1. See https://rd.gt/3ipi7Pm for more information: No value provided for placeholder: ${MYSQL_HOST_NAME:localhost}.  Check your configuration!
    at org.flywaydb.core.internal.parser.Parser.getNextStatement(Parser.java:306) ~[flyway-core-9.20.1.jar:na]
    at org.flywaydb.core.internal.parser.Parser$ParserSqlStatementIterator.<init>(Parser.java:715) ~[flyway-core-9.20.1.jar:na]
    at org.flywaydb.core.internal.parser.Parser.parse(Parser.java:130) ~[flyway-core-9.20.1.jar:na]
    at org.flywaydb.core.internal.sqlscript.ParserSqlScript.parse(ParserSqlScript.java:72) ~[flyway-core-9.20.1.jar:na]
    at org.flywaydb.core.internal.sqlscript.ParserSqlScript.validate(ParserSqlScript.java:120) ~[flyway-core-9.20.1.jar:na]
    at org.flywaydb.core.internal.sqlscript.ParserSqlScript.executeInTransaction(ParserSqlScript.java:187) ~[flyway-core-9.20.1.jar:na]
    at org.flywaydb.core.internal.resolver.sql.SqlMigrationExecutor.canExecuteInTransaction(SqlMigrationExecutor.java:73) ~[flyway-core-9.20.1.jar:na]
    at org.flywaydb.core.internal.command.DbMigrate.isExecuteGroupInTransaction(DbMigrate.java:302) ~[flyway-core-9.20.1.jar:na]
    at org.flywaydb.core.internal.command.DbMigrate.applyMigrations(DbMigrate.java:267) ~[flyway-core-9.20.1.jar:na]
    at org.flywaydb.core.internal.command.DbMigrate.migrateGroup(DbMigrate.java:244) ~[flyway-core-9.20.1.jar:na]
    at org.flywaydb.core.internal.command.DbMigrate.lambda$migrateAll$0(DbMigrate.java:139) ~[flyway-core-9.20.1.jar:na]
    at org.flywaydb.database.mysql.MySQLNamedLockTemplate.execute(MySQLNamedLockTemplate.java:58) ~[flyway-mysql-9.16.3.jar:na]
    at org.flywaydb.database.mysql.MySQLConnection.lock(MySQLConnection.java:152) ~[flyway-mysql-9.16.3.jar:na]
    at org.flywaydb.core.internal.schemahistory.JdbcTableSchemaHistory.lock(JdbcTableSchemaHistory.java:144) ~[flyway-core-9.20.1.jar:na]
    at org.flywaydb.core.internal.command.DbMigrate.migrateAll(DbMigrate.java:139) ~[flyway-core-9.20.1.jar:na]
    at org.flywaydb.core.internal.command.DbMigrate.migrate(DbMigrate.java:97) ~[flyway-core-9.20.1.jar:na]
    at org.flywaydb.core.Flyway.lambda$migrate$0(Flyway.java:188) ~[flyway-core-9.20.1.jar:na]
    at org.flywaydb.core.FlywayExecutor.execute(FlywayExecutor.java:196) ~[flyway-core-9.20.1.jar:na]
    at org.flywaydb.core.Flyway.migrate(Flyway.java:140) ~[flyway-core-9.20.1.jar:na]
    at org.springframework.boot.autoconfigure.flyway.FlywayMigrationInitializer.afterPropertiesSet(FlywayMigrationInitializer.java:66) ~[spring-boot-autoconfigure-3.1.4.jar:3.1.4]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1817) ~[spring-beans-6.0.12.jar:6.0.12]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1766) ~[spring-beans-6.0.12.jar:6.0.12]
    ... 18 common frames omitted
Caused by: org.flywaydb.core.api.FlywayException: No value provided for placeholder: ${MYSQL_HOST_NAME:localhost}.  Check your configuration!
    at org.flywaydb.core.internal.parser.PlaceholderReplacingReader.read(PlaceholderReplacingReader.java:165) ~[flyway-core-9.20.1.jar:na]
    at java.base/java.io.FilterReader.read(FilterReader.java:65) ~[na:na]
    at org.flywaydb.core.internal.parser.PositionTrackingReader.read(PositionTrackingReader.java:33) ~[flyway-core-9.20.1.jar:na]
    at java.base/java.io.FilterReader.read(FilterReader.java:65) ~[na:na]
    at org.flywaydb.core.internal.parser.RecordingReader.read(RecordingReader.java:33) ~[flyway-core-9.20.1.jar:na]
    at java.base/java.io.FilterReader.read(FilterReader.java:65) ~[na:na]
    at org.flywaydb.core.internal.parser.PeekingReader.refillPeekBuffer(PeekingReader.java:73) ~[flyway-core-9.20.1.jar:na]
    at org.flywaydb.core.internal.parser.PeekingReader.peek(PeekingReader.java:187) ~[flyway-core-9.20.1.jar:na]
    at org.flywaydb.core.internal.parser.PeekingReader.peek(PeekingReader.java:169) ~[flyway-core-9.20.1.jar:na]
    at org.flywaydb.core.internal.parser.Parser.readToken(Parser.java:477) ~[flyway-core-9.20.1.jar:na]
    at org.flywaydb.core.internal.parser.Parser.getNextStatement(Parser.java:175) ~[flyway-core-9.20.1.jar:na]
    ... 39 common frames omitted

Now the problem is I need to insert this query manually which is tricky in case bulk file. I want do with flyway. How to do insert this value though flyway.

Answer 1

By default, flyway looks for placeholders starting with the prefix ${ present in the query while executing it.

If flyway finds a placholder in the query, then it looks up for a value for that placeholder if present.

In your case, the error is valid as there is no value present for this placeholder MYSQL_HOST_NAME:localhost.

---

Solution:

There are two ways in which you can approach:

• First Approach:

  Update v2_InsertData.sql to this:

  INSERT INTO property (application, profile, label, property_key, 
  property_value) 
  VALUES (‘app’, 'dev', 'v1', 'database.master.url', 
  CONCAT('jdbc:mysql://$','{MYSQL_HOST_NAME:localhost}', ':$', '{MYSQL_PORT:3306}', 
  '/master?useUnicode=true&characterEncoding=UTF-8&useSSL=false&allowPublicKeyRetrieval=true'));
  

  Note: I have changed the query a little bit with the help of CONCAT() function to bypass the placeholder validation.

• Second Approach:

  Tell flyway to skip replacing the placeholders found in the query via application.properties:

  spring.flyway.placeholder-replacement=false
  

Screenshot to prove that it works for both approaches:

Note: I will recommend you to go for first approach because it's a good and safe option. Also, you don't need to modify the default behaviour of the flyway unless it's needed.

Answer 2

Does flyway "interpolate" $ things?

If PHP is used under the covers, then this will be a problem:

'...${MYSQL_PORT:3306}...'

but this will not:

"...${MYSQL_PORT:3306}..."

This is because interpolation occurs inside double quotes, but not inside single quotes.

See if double quotes fixes the problem.