I have a spring boot application. And I have configured liquibase to create tables. I have few issues though. The tables are not getting created inside the schema and in the location provided in application.yml. Also, if i change the username from default sa to "root", the spring boot liquibase scripts fail with the error "wrong username or password".
Here is my application.yml file:
debug: false
server:
port: 9110
servlet:
context-path: '/v1/ss-db-service'
tomcat:
remoteip:
protocol-header: x-forwarded-proto
spring:
jpa:
properties:
hibernate:
dialect: org.hibernate.dialect.MySQL5Dialect
hibernate:
ddl-auto: none
show-sql: true
h2:
console:
enabled: true
path: /h2-console
settings:
web-allow-others: true
liquibase:
enabled: true
contexts: dev
user: sa
password:
database-change-log-table: 'db_changeset_log'
database-change-log-lock-table: 'db_changeset_lock'
change-log: classpath:liquibase/changesets/MASTER_changeset.xml
# default-schema: AGENCY_SS_REQUEST
datasource:
agency-ss-request:
url: jdbc:h2:mem:AGENCY_SS_REQUEST;MODE=MYSQL;DB_CLOSE_DELAY=-1;INIT=RUNSCRIPT FROM 'classpath:schema.sql';
username: sa
password:
logging:
level:
'org.hibernate.SQL': INFO
'org.hibernate.type.descriptor.sql': INFO
'liquibase.statement': WARN
'liquibase.resource': WARN
I tried setting the scehma with default-schema. But spring boot application fails to start with "Schema not found error".
This is my schema.sql file:
CREATE SCHEMA IF NOT EXISTS AGENCY_SS_REQUEST;
SET SCHEMA AGENCY_SS_REQUEST;
Here is liquibase configuration files:
Here is my liquibase.properties file: (Though, changing anything in this file does not seem to take effect:
### Maven Liqubase Config File
url=jdbc:h2:mem:AGENCY_SS_REQUEST;MODE=MYSQL;DB_CLOSE_DELAY=-1;INIT=RUNSCRIPT FROM
'classpath:schema.sql';
username=sa
password=
driver=org.h2.Driver
Master_changeset.xml:
<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog 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-4.0.xsd">
<property name="now" value="now()" dbms="h2"/>
<property name="now" value="now()" dbms="mysql"/>
<property name="floatType" value="float4" dbms="postgresql, h2"/>
<property name="floatType" value="float" dbms="mysql, oracle, mssql, mariadb"/>
<property name="clobType" value="clob" dbms="h2"/>
<property name="clobType" value="clob" dbms="mysql, oracle, mssql, mariadb, postgresql"/>
<property name="uuidType" value="varchar(36)" dbms="h2, mysql, mariadb"/>
<!-- Initial USER_PRODCER_INFO Schema -->
<include file="./REQUEST_TABLE_20230116_changeset.xml" relativeToChangelogFile="true"/>
</databaseChangeLog>
REQUEST_INFO_20210203_01.xml:
<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext"
xmlns:pro="http://www.liquibase.org/xml/ns/pro"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog-ext
http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd
http://www.liquibase.org/xml/ns/pro http://www.liquibase.org/xml/ns/pro/liquibase-pro-4.0.xsd
http://www.liquibase.org/xml/ns/dbchangelog
http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-4.0.xsd">
<changeSet id="REQUEST_INFO_20210203_01" author="snagesh">
<createTable tableName="ss_request">
<column name="ss_request_id" type="VARCHAR(36)">
<constraints nullable="false" primaryKey="true"/>
</column>
--- there are more fields
</createTable>
</changeSet>
</databaseChangeLog>
with the above configuration, if i run the application, i see in the spring boot console that liquibase has created tables in a random memory location. if i open that in h2 console i see the below things:
Here, tables are created outside the schema. also in some random memory location.
If I access the h2 console i have configured in application.yml, just the h2 tables are there. none of my tables are created in the location.
This is my entity file:
@Data
@Entity
@NoArgsConstructor
@AllArgsConstructor
@Table(name = "ss_request", schema = "AGENCY_SS_PORTAL")
public class SSRequest {
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
@Column(name = "ss_request_id", nullable = false)
private Integer ssRequestId;
--- more fields
My questions are:
- why can't i see my tables in jdbc:h2:mem:AGENCY-SS-PORTAL this location, but in a random location(I get this random location from spring boot application logs)?
- Why are not my tables getting created inside the schema. and why does the application fail to start if i enforce the schema creation with default-schema: AGENCY_SS_REQUEST
- why does the application fail with username/password error, if i change the password from sa to root. is liquibase looking at any other configuration?
Note: I am doing this project on intelliJ. so if any extra configuration is needed, let me know
