禁用flyway
为了解决之前有很多小伙伴因为数据库的版本问题,存在一些sql脚本语法不支持,导致数据库初始化失败
配置文件bootstrap.yml,将spring.flyway.enabled改为false即可。
spring:
flyway:
enabled: true #是否开启flyway,默认true.
baseline-on-migrate: true
#数据库连接配置
url: ${spring.datasource.url}
user: ${spring.datasource.username}
password: ${spring.datasource.password}
placeholder-replacement: false
init-sqls:
- CREATE DATABASE IF NOT EXISTS `aj_report` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
在开启的状态下,首次导入,会执行spring.flyway.init-sqls创建数据库aj_report
创建数据库需要获取数据库连接,而spring.datasource.url中的数据库aj_report此时并未创建,导致启动报错
错误信息如下:
Parsed mapper file: 'file [D:\anji-code\gitee\aj-report-p\report-core\target\classes\mapper\ReportShareMapper.xml]'
_ _ |_ _ _|_. ___ _ | _
| | |\/|_)(_| | |_\ |_)||_|_\
/ |
3.3.2
09-26 10:34:36.078 | main |-INFO o.s.s.c.ThreadPoolTaskExecutor:181 - Initializing ExecutorService 'applicationTaskExecutor'
09-26 10:34:36.336 | main |-INFO o.f.c.i.license.VersionPrinter:49 - Flyway Community Edition 5.2.1 by Boxfuse
09-26 10:34:36.427 | main |-WARN o.s.b.w.s.c.AnnotationConfigServletWebServerApplicationContext:559 - 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]: Invocation of init method failed; nested exception is org.flywaydb.core.internal.exception.FlywaySqlException:
Unable to obtain connection from database (jdbc:mysql://127.0.0.1:3306/aj_report?characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&useSSL=false) for user 'root': Unknown database 'aj_report'
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL State : 42000
Error Code : 1049
Message : Unknown database 'aj_report'
09-26 10:34:36.428 | main |-INFO o.s.s.c.ThreadPoolTaskExecutor:218 - Shutting down ExecutorService 'applicationTaskExecutor'
09-26 10:34:36.430 | main |-INFO o.a.catalina.core.StandardService:173 - Stopping service [Tomcat]
09-26 10:34:36.434 | main |-WARN o.a.c.loader.WebappClassLoaderBase:173 - The web application [ROOT] appears to have started a thread named [__DEFAULT__] but has failed to stop it. This is very likely to create a memory leak. Stack trace of thread:
java.lang.Object.wait(Native Method)
java.lang.Object.wait(Object.java:502)
java.util.TimerThread.mainLoop(Timer.java:526)
java.util.TimerThread.run(Timer.java:505)
09-26 10:34:36.435 | main |-WARN o.a.c.loader.WebappClassLoaderBase:173 - The web application [ROOT] appears to have started a thread named [report%0043ache.data] but has failed to stop it. This is very likely to create a memory leak. Stack trace of thread:
sun.misc.Unsafe.park(Native Method)
java.util.concurrent.locks.LockSupport.parkNanos(LockSupport.java:215)
java.util.concurrent.locks.AbstractQueuedSynchronizer$ConditionObject.awaitNanos(AbstractQueuedSynchronizer.java:2078)
java.util.concurrent.ScheduledThreadPoolExecutor$DelayedWorkQueue.take(ScheduledThreadPoolExecutor.java:1093)
java.util.concurrent.ScheduledThreadPoolExecutor$DelayedWorkQueue.take(ScheduledThreadPoolExecutor.java:809)
java.util.concurrent.ThreadPoolExecutor.getTask(ThreadPoolExecutor.java:1074)
java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1134)
java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
java.lang.Thread.run(Thread.java:748)
09-26 10:34:36.435 | main |-WARN o.a.c.loader.WebappClassLoaderBase:173 - The web application [ROOT] appears to have started a thread named [Statistics Thread-__DEFAULT__-1] but has failed to stop it. This is very likely to create a memory leak. Stack trace of thread:
sun.misc.Unsafe.park(Native Method)
java.util.concurrent.locks.LockSupport.parkNanos(LockSupport.java:215)
java.util.concurrent.locks.AbstractQueuedSynchronizer$ConditionObject.awaitNanos(AbstractQueuedSynchronizer.java:2078)
java.util.concurrent.ScheduledThreadPoolExecutor$DelayedWorkQueue.take(ScheduledThreadPoolExecutor.java:1093)
java.util.concurrent.ScheduledThreadPoolExecutor$DelayedWorkQueue.take(ScheduledThreadPoolExecutor.java:809)
java.util.concurrent.ThreadPoolExecutor.getTask(ThreadPoolExecutor.java:1074)
java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1134)
java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
java.lang.Thread.run(Thread.java:748)
09-26 10:34:36.444 | main |-INFO o.s.b.a.l.ConditionEvaluationReportLoggingListener:136 -
解决方法,通过DatabaseInitializer类在加载数据源之间拼接出flyway所需要的数据源
@Slf4j
@Component
@AllArgsConstructor
@ConditionalOnProperty(value = {"spring.flyway.enabled"})
public class DatabaseInitializer {
private final FlywayProperties flywayProperties;
private final DataSourceProperties dataSourceProperties;
@PostConstruct
public void init() throws SQLException {
log.info("DatabaseInitializer uses flyway init-sqls to initiate database");
String url = dataSourceProperties.getUrl();
// jdbc url最后一个 '/' 用于分割具体 schema?参数
int lastSplitIndex = url.lastIndexOf('?');
// 获取spring.datasource.url具体数据库schema前的jdbc url
String addressUrl = url.substring(0, lastSplitIndex);
String addresslast = url.substring(lastSplitIndex);
addressUrl = addressUrl.substring(0, addressUrl.lastIndexOf("/"));
// 直连数据库地址:jdbc:mysql://yourIp:port
HikariDataSource dataSource = new HikariDataSource();
dataSource.setJdbcUrl(addressUrl.concat(addresslast));
dataSource.setUsername(dataSourceProperties.getUsername());
dataSource.setPassword(dataSourceProperties.getPassword());
Connection connection = dataSource.getConnection();
Statement statement = connection.createStatement();
for (String sql : flywayProperties.getInitSqls()) {
// 通过flyway的init-sqls配置进行建库与数据库配置
// executeUpdate:执行给定的SQL语句,该语句可以是INSERT,UPDATE或DELETE语句或不返回任何内容的SQL语句,例如SQL DDL语句。
statement.executeUpdate(sql);
}
statement.close();
connection.close();
dataSource.close();
log.info("DatabaseInitializer initialize completed");
}
}
更换底层数据库
如需更换底层数据源,如SqlServer等,只要关闭上方的flyway,再去Navicat等工具中将mysql数据库脚本导出为你所需要的数据源版本即可,可能启动后会存在一些sql兼容性问题,需要自行去测试,(目前只测试oracle,存在自增id问题,改动性过大,就没有后续了…),如遇问题,欢迎大家去提交issus,共同完善。