MS SQL Server Schema Name Problems

General Discussion
1

Hi,

Another problem.  I am trying to run liquibase 2.0RC1 on a MS SQL Server database.  I set the default schema name to ‘alana’.  When I attempt to run liquibase, i get an error that I do not have permission to use the dbo schema.  Stepping through the source code, it looks like the create for databasechangeloglock is created against dbo and not alana. 

Here is the sql it tries to run:
CREATE TABLE [dbo].[DATABASECHANGELOGLOCK] ([ID] INT NOT NULL, [LOCKED] BIT NOT NULL, [LOCKGRANTED] DATETIME, [LOCKEDBY] VARCHAR(255), CONSTRAINT [PK_DATABASECHANGELOGLOCK] PRIMARY KEY ([ID]))

Don’t think that most sites would want to give DBO privs to regular users.

Thanks,
Alana

2

You are right. The newest build at liquibase.org/ci/latest should use the specified schema for the databasechangelog table too.

Nathan

3

Thanks.

Will try it right now.

Alana

4

Hi,

I tried build 191 and I am still having the same problems.  In the updateDatabase ant task, I specify my default schema name.
My database change log is:

                                       

       
           
               
           
         

And the resultant SQL is

    -- ********************************************************************* -- Update Database Script -- ********************************************************************* -- Change Log: C:/workspace/sas5.0/env-config/testChangeLog.xml -- Ran at: 4/12/10 12:28 PM -- Against: test1user@jdbc:sqlserver://1.2.3.4:1433;selectMethod=cursor;lastUpdateCount=true;databaseName=test1db;responseBuffering=full; -- LiquiBase version: 2.0-rc2-SNAPSHOT -- *********************************************************************

    – Create Database Lock Table
    CREATE TABLE [dbo].[DATABASECHANGELOGLOCK] ([ID] INT NOT NULL, [LOCKED] BIT NOT NULL, [LOCKGRANTED] DATETIME, [LOCKEDBY] VARCHAR(255), CONSTRAINT [PK_DATABASECHANGELOGLOCK] PRIMARY KEY ([ID]))
    GO

    INSERT INTO [dbo].[DATABASECHANGELOGLOCK] ([ID], [LOCKED]) VALUES (1, 0)
    GO

    – Lock Database
    – Create Database Change Log Table
    CREATE TABLE [dbo].[DATABASECHANGELOG] ([ID] VARCHAR(63) NOT NULL, [AUTHOR] VARCHAR(63) NOT NULL, [FILENAME] VARCHAR(200) NOT NULL, [DATEEXECUTED] DATETIME NOT NULL, [ORDEREXECUTED] INT NOT NULL, [EXECTYPE] VARCHAR(10) NOT NULL, [MD5SUM] VARCHAR(35), [DESCRIPTION] VARCHAR(255), [COMMENTS] VARCHAR(255), [TAG] VARCHAR(255), [LIQUIBASE] VARCHAR(20), CONSTRAINT [PK_DATABASECHANGELOG] PRIMARY KEY ([ID], [AUTHOR], [FILENAME]), CONSTRAINT [UQ_DBCL_ORDEREXEC] UNIQUE ([ORDEREXECUTED]))
    GO

    – Changeset testChangeLog.xml::1::unittest::(Checksum: 2:c32f08916f3df4a8e23652bc9873e4ff)
    CREATE TABLE [dbo].[A_Table] ([id] BIGINT CONSTRAINT DF_A_Table_id DEFAULT NULL)
    GO

    INSERT INTO [dbo].[DATABASECHANGELOG] ([AUTHOR], [COMMENTS], [DATEEXECUTED], [DESCRIPTION], [EXECTYPE], [FILENAME], [ID], [LIQUIBASE], [MD5SUM], [ORDEREXECUTED]) VALUES (‘unittest’, ‘’, GETDATE(), ‘Create Table’, ‘EXECUTED’, ‘testChangeLog.xml’, ‘1’, ‘2.0-rc2-SNP’, ‘2:c32f08916f3df4a8e23652bc9873e4ff’, 1)
    GO

    – Changeset testChangeLog.xml::2::unittest::(Checksum: 2:c22a5acdbc4b24b0e1e151495e06e7bc)
    CREATE INDEX AN_INDEX ON [dbo].A_Table
    GO

    INSERT INTO [dbo].[DATABASECHANGELOG] ([AUTHOR], [COMMENTS], [DATEEXECUTED], [DESCRIPTION], [EXECTYPE], [FILENAME], [ID], [LIQUIBASE], [MD5SUM], [ORDEREXECUTED]) VALUES (‘unittest’, ‘’, GETDATE(), ‘Create Index’, ‘EXECUTED’, ‘testChangeLog.xml’, ‘2’, ‘2.0-rc2-SNP’, ‘2:c22a5acdbc4b24b0e1e151495e06e7bc’, 2)
    GO

All the tables, both liquibase tables and my table are created against DBO.  If I specify the schema in the change set, then A_TABLE would be created against the correct schema.  But DatabaseChangeLock and DatabaseChangeLogLock would still be part of dbo.

Since we have to support many databases, I am hesitant to set the schema name in the actual change log file.  Would prefer to do it in ant via  defaultSchemaName, for databases where this is relevant.  What would happen if we set the schema name in “createTable” but it is not relevant for a certain db vendor. Would that field be ignored?

Thanks,
Alana

p.s. The index creation sql is now correct.  Thanks.

5

To answer part of my own question
Lets say you have a parameter for schemaName called “databaseSchema”.

                                       

If you want to run it against a DB, but not specify the default schema as a change log property, the resultant SQL is

    CREATE TABLE ${databaseSchema}.A_Table (id NUMBER(38,0) DEFAULT NULL);

I would have hoped the resultant sql was “CREATE TABLE A_TABLE(…)”.  If you pass in an empty string for the databaseSchema change log property, then you do get the expected sql.  I think it should be treated the same either way.

Thanks,
Alana

6

I think keeping the string as “${databaseSchema}” if the parameter is not set is the right approach.  It is how Ant works, so it is more familiar, plus you can see the parameter that was unset so you would know which one to change.

You can get around it by setting the databaseSchema property at the top of the changelog file itself in a block to an empty string.  Like Ant, it will only set the property if it is currently unset.

Nathan 

7

Hi,

I have tried build 198 and I am still having the same issue I originally reported.  When liquibase attempts to create the DATABASECHANGELOG* tables, it does it in the dbo schema.  It should be using the default schema name specified.
Normally we do not have permission to write in dbo.
I have included the stack trace below. 

Thanks,
Alana

251307: Unable to update database schema.

Caused by: liquibase.exception.LockException: liquibase.exception.DatabaseException: Error executing SQL CREATE TABLE [dbo].[DATABASECHANGELOGLOCK] ([ID] INT NOT NULL, [LOCKED] BIT NOT NULL, [LOCKGRANTED] DATETIME, [LOCKEDBY] VARCHAR(255), CONSTRAINT [PK_DATABASECHANGELOGLOCK] PRIMARY KEY ([ID])); INSERT INTO [dbo].[DATABASECHANGELOGLOCK] ([ID], [LOCKED]) VALUES (1, 0)
at liquibase.lockservice.LockService.acquireLock(LockService.java:116)
at liquibase.lockservice.LockService.waitForLock(LockService.java:61)
at liquibase.Liquibase.update(Liquibase.java:89)
at com.gemalto.protiva.setup.gp.liquibase.ProtivaSpringLiquibase.update(ProtivaSpringLiquibase.java:46)
… 27 more

8

More informtion:

Tracing through liquibase.  On line 22 of AbstractExecutor.applyVisitors, the databaseObject (MSSQLDatabase) has a the default schema set to my schema.  Yet the toString reports the default schema as,

user @ jdbc:sqlserver://localhost:1433;selectMethod=cursor;lastUpdateCount=true;databaseName=db;responseBuffering=full; (Default Schema: dbo)

Looking at the code for MSSQLDatabase, the method getDefaultSchemaName on line 209:
@Override
public String getDefaultSchemaName() {
        return “dbo”;
}
no wonder the schema i set is never used for the databasechangelog* tables. :(    Why is this hard coded to be dbo?

9

Hi,

Below is my fix for MSSQLDatabase?

    @Override public String getDefaultSchemaName() {     String schemaName = super.getDefaultSchemaName();     return ( schemaName == null ) ? "dbo" : schemaName; }
Can something like this make it into the official code base? 

We had been using 2.0-rc1.  When we started to test against MSSQL, thats when we noticed problems with rc1. 
We really need MSSQL functionality, but are uncomfortable using a snapshot for a production system.  Any idea when rc2 will be ready?

Thanks,
Alana

10

That is the right change, I had just committed something similar to trunk. 

I’m hoping rc2 will be done soon, there are just a few bugs left in assigned to 2.0.  I would like to get all those cleaned up before the 2.0 rc2 release.  Hopefully next week or the week after.

Nathan