Question 283541 in Mysql Question posted in
The official MySQL documentation can be found here.

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

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.

Tags:

2

Answers


  1. 0

    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:

    enter image description here

    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.

    Login or Signup to reply.
  2. 0

    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. If not, please provide more details on where the string comes from and what flyway is.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search