SQL Server createTable column with defaultValueSequenceNext cannot find sequence, unable to specify schema
Description
Environment
SQL Server 2019
Activity
Nikolai ADIUTANTOV January 5, 2022 at 6:23 PM
I have the same behavior in liquibase 4.6.2
defaultValueSequenceNext in column tag forget schema and uses public schema instead of schema defind in createTable tag
It’s weird to use additional changeset to add constraint after table creation.
Is it planned to fix it or not?
Workaround for me: using native DDL statement to create table with required constraints and sequences
<createTable tableName="affiliation" remarks="List of affiliations" schemaName="sciencejournal">
<column name="id" type="int" remarks="ID" defaultValueSequenceNext="affiliation_seq">
<constraints primaryKey="true" primaryKeyName="affiliation_id_pk"/>
</column>
<column name="name" type="varchar(${affiliation.name.size})" remarks="Name of affiliation">
<constraints nullable="true"/>
</column>
</createTable>
Output:
liquibase_db_migration_1 | Caused by: liquibase.exception.DatabaseException: ERROR: relation "public.affiliation_seq" does not exist
liquibase_db_migration_1 | Position: 69 [Failed SQL: (0) CREATE TABLE sciencejournal.affiliation (id INTEGER DEFAULT nextval('public.affiliation_seq') NOT NULL, name VARCHAR(300), CONSTRAINT affiliation_id_pk PRIMARY KEY (id))]
liquibase_db_migration_1 | at liquibase.executor.jvm.JdbcExecutor$ExecuteStatementCallback.doInStatement(JdbcExecutor.java:397)
liquibase_db_migration_1 | at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:83)
liquibase_db_migration_1 | at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:151)
liquibase_db_migration_1 | at liquibase.database.AbstractJdbcDatabase.execute(AbstractJdbcDatabase.java:1279)
liquibase_db_migration_1 | at liquibase.database.AbstractJdbcDatabase.executeStatements(AbstractJdbcDatabase.java:1261)
liquibase_db_migration_1 | at liquibase.changelog.ChangeSet.execute(ChangeSet.java:660)
liquibase_db_migration_1 | ... 61 more
liquibase_db_migration_1 | Caused by: org.postgresql.util.PSQLException: ERROR: relation "public.affiliation_seq" does not exist
liquibase_db_migration_1 | Position: 69
liquibase_db_migration_1 | at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2553)
liquibase_db_migration_1 | at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2285)
liquibase_db_migration_1 | at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:323)
liquibase_db_migration_1 | at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:473)
Manu Wan February 5, 2021 at 8:50 AM
Hi, I’ve got the same problem. The general default schema name written in the liquibase.properties is automatically used instead of the given schema name. The workaround is to use the fixed method in https://liquibase.jira.com/browse/CORE-2753 after the create table: <addDefaultValue schemaName="${schema-name}" tableName="{table-name}" columnName="id" defaultValueSequenceNext="{table-name}_seq"/>
This appears to be the same issue as CORE-2753, except that I am trying to specify
defaultValueNextSequence
when defining the table rather than in the alter table.databaseChangeLog: - changeSet: id: schema deadlines author: dannygeorge changes: - sql: "CREATE SCHEMA deadlines" - changeSet: id: sequence deadlineIds author: dannygeorge changes: - createSequence: schemaName: deadlines sequenceName: deadlineIds #dataType: bigint # dataType not supported for mssql, but bigint is default anyway - changeSet: id: table DailyDeadlines author: dannygeorge changes: - createTable: tableName: DailyDeadlines schemaName: deadlines columns: - column: { name: id, type: bigint, defaultValueSequenceNext: deadlineIds, constraints: { unique: true, nullable: false } } - column: { name: timeOfDay, type: time, constraints: { nullable: false } } - column: { name: timeZone, type: varchar(50), constraints: { nullable: false } }
The resulting error is:
[ERROR] Migration failed for change set dbchanges-1.0.yaml::table DailyDeadlines::dannygeorge: [ERROR] Reason: liquibase.exception.DatabaseException: Invalid object name 'deadlineIds'. [Failed SQL: (208) CREATE TABLE deadlines.DailyDeadlines (id bigint CONSTRAINT DF_DailyDeadlines_id DEFAULT NEXT VALUE FOR deadlineIds NOT NULL, acType char(2) NOT NULL, acId bigint NOT NULL, eventType char(10) NOT NULL, timeOfDay time NOT NULL, timeZone varchar(50) NOT NULL, UNIQUE (id))]
I've tried various combinations instead of just 'deadlineIds':
deadlines.deadlineIds -> gets quoted as [deadlines.deadlineIds]
[deadlines].[deadlineIds] -> gets quoted as [[deadlines].[deadlineIds]]
It also appears that
defaultValueSequenceNext
is not documented in https://www.liquibase.org/documentation/column.html