提问人：David 提问时间：11/7/2023 最后编辑：Rick JamesDavid 更新时间：11/13/2023 访问量：175
当查询中的值为 $ 时，通过 flyway 插入数据时引发错误
Error throwing while inserting data through flyway when the value is $ in the query
问：
当查询中的值为 $ 时，通过 flyway 插入数据时引发错误
每当它在值字段中找到“$”时，我在将数据插入数据库时都会收到错误。我正在使用飞行路线。但是当我手动插入时，该值插入正常。
这是我在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'
);
要求：
我正在通过属性文件使用此数据
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;
}
现在我已经转移到 spring cloud config，加载正常。
所以我的 db.config 变成
@Configuration
@Data
public class DBConfig {
@Value("${database.master.url}")
private String url;
}
错误：
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
现在的问题是我需要手动插入此查询，这在批量文件中很棘手。我想做飞行道。如何通过飞行方式插入此值。
答：
4赞
Anish B.
11/10/2023
#1
默认情况下，flyway 在执行查询时会查找以查询中存在的前缀开头的占位符。${
如果flyway在查询中找到placholder，则它会查找该占位符的值（如果存在）。
在您的情况下，错误是有效的，因为此占位符不存在任何值。MYSQL_HOST_NAME:localhost
溶液：
有两种方法可以接近：
第一种方法：
v2_InsertData.sql更新到：
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'));注意：我在函数的帮助下对查询进行了一些更改，以绕过占位符验证。
CONCAT()第二种方法：
告诉flyway跳过通过application.properties替换查询中找到的占位符：
spring.flyway.placeholder-replacement=false
屏幕截图以证明它适用于这两种方法：
注意：我会建议你选择第一种方法，因为这是一个好而安全的选择。此外，除非需要，否则您不需要修改飞行路线的默认行为。
1赞
Rick James
11/11/2023
#2
飞行路线是否“插值”$ 事物？
如果PHP是在幕后使用的，那么这将是一个问题：
'...${MYSQL_PORT:3306}...'
但这不会：
"...${MYSQL_PORT:3306}..."
这是因为插值发生在双引号内，而不是单引号内。
查看双引号是否能解决问题。
评论