liquibase初始化sql
1.使用liquibase
集成依赖
<liquibase.version>4.1.1</liquibase.version>
<dependency>
<groupId>org.liquibase</groupId>
<artifactId>liquibase-core</artifactId>
<version>${liquibase.version}</version>
</dependency>
项目数据源配置文件中加入liquibase配置信息
@Bean(name = "dataSource")
@ConfigurationProperties(prefix="spring.datasource.mysql")
public DataSource dataSource(){
DataSource dataSource = DataSourceBuilder.create().build();
return dataSource;
}
@Bean
public SpringLiquibase liquibase(@Qualifier("dataSource") DataSource dataSource) {
SpringLiquibase liquibase = new SpringLiquibase();
liquibase.setChangeLog("classpath:mysql/master.xml");//liquibase xml路径
liquibase.setDataSource(dataSource);
liquibase.setShouldRun(true);
return liquibase;
}
配置信息
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<databaseChangeLog logicalFilePath="db.changelog-master.xml" xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.4.xsd">
<!--扫描sql脚本文件路径-->
<includeAll path="/mysql/*.sql" relativeToChangelogFile="true"/>
</databaseChangeLog>
2.常见错误
2021-08-25 10:15:13.476 INFO -[Slf4jLogger.java:42]- Waiting for changelog lock....
表被锁 项目启动不了 需要设置DATABASECHANGELOGLOCK表字段LOCKED为0
已运行的changeSet修改后,liquibase检查篡改报错。
org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'liquibase' defined in class path resource [Config.class]: Invocation of init method failed; nested exception is liquibase.exception.ValidationFailedException: Validation Failed:
1 change sets check sum ids.sql::123::author was: 8:db5395481a0664c2794b7ac2399b3f22 but is now: 8:21e277195677c9252df3cad3f4cc368d
已经运行的changeSet记录会存入DATABASECHANGELOG中
解决办法:
1.根据返回的最新(now)MD5SUM值修改对应的数据库MD5SUM的值。数据库手动添加改变的sql命令。
2.删除对应的sql操作,及删除databasechangelog表中对应的记录
(2)执行sql脚本常见信息
Index column size too large. The maximum column size is 767 bytes.
MySQL Innodb索引字段长度最大为767字节,如果索引大小超过767字节则会引发该错误。
解决办法:
如果mysql安装在本地 需要手动修改配置:
查看状态:show variables like 'innodb_large_prefix';
设置属性:set global innodb_large_prefix='on';
如果是云数据库 在配置里面设置
innodb_large_prefix :ON 默认为OFF