Hi, When I run Liquibase 2.0 RC7 (revision 1786) against a database that has not been managed by Liquibase yet, it errors out early in the process (log snippet below). Subsequent runs error out in different ways, depending on whether the tables exist and what command is used, so I'll just focus on the simplest case.
My setup: - I've built a recent snapshot of 2.0 RC7 (revision 1786) using Maven 3.0 (command at end of post). - The database and server are case-sensitive (SQL Server 2008 SP1, database and server collation set to Latin1_General_BIN) - The database in this test does not have a DATABASECHANGELOG or DATABASECHANGELOGLOCK table. - When I run Liquibase with the "status" command against my testing XML files, it errors out with:
liquibase:INFO: Successfully acquired change log lock liquibase:INFO: Creating database history table with name: [dbo].[DATABASECHANGELOG] liquibase:INFO: There is already an object named 'DATABASECHANGELOGLOCK' in the database.
- Looking at a SQL trace, I see that the "DATABASECHANGELOGLOCK" table is created early on, but then a prepared SQL statement is run, using the sp_tables procedure to check for the existence of "databasechangeloglock". A subsequent attempt to create "DATABASECHANGELOGLOCK" again fails with the error. - Based on some ad-hoc testing, I believe the same situation will occur for the DATABASECHANGELOG table. - If I run modifications like the following, these errors go away and the app works as expected. I'm guessing the app consistently reads/writes with the capitalized table names but just checks for existence with the lower-case names.
select * into databasechangeloglock from DATABASECHANGELOGLOCK where 1 = 0 go
select * into databasechangelog from DATABASECHANGELOG where 1 = 0 go
delete DATABASECHANGELOGLOCK where 1 = 1 go
INSERT INTO [dbo].[DATABASECHANGELOGLOCK] ([ID], [LOCKED]) VALUES (1, 0) go
Thanks for your attention, I am looking forward to 2.0 :) Trevor
p.s. I built 2.0 RC7 using the command: mvn install -Dmaven.test.skip.exec=true ...which I found on one forum posting. Hope that was the correct way to do it and isn't causing this problem. I did receive some Maven warnings, although the process completed and Liquibase JAR runs.
I think it should be resolved in RC7. I've gotten some patches around case sensitivity and consistency, but I haven't gotten a chance to install a case sensitive sqlserver instance and test it personally yet.
Unfortunately the error is still occurring on RC7, but the symptoms have changed.
If the LB tables do not exist, then exactly one run of either the "status" or "update" command will work (finishes successfully as long as I do my change sets correctly, and both tables are created. "update" command will log to the DATABASECHANGELOG table correctly)
But a subsequent run of either the "status" or "update" command will fail with the same error:
Liquibase Update Failed: Error executing SQL CREATE TABLE [dbo].[DATABASECHANGELOGLOCK] ([ID] INT NOT NULL, [LOCKED] BIT NOT NULL, [LOCKGRANTED] DATETIME, [LOCK...
I'm facing a similar issue on Sybase ASE12.5 using Liquibase version 2.0.1. The first time I run Liquibase on my DB, it works fine and creates the DATABASECHANGELOG and DATABASECHANGELOGLOCK tables properly. The second time I run it, Liquibase attempts to re-create the lock table, thus failing with the below error:
liquibase.exception.LockException: liquibase.exception.DatabaseException: Error executing SQL CREATE TABLE [DATABASECHANGELOGLOCK] ([ID] INT NOT NULL, [LOCKED] BIT NOT NULL, [LOCKGRANTED] D ATETIME NULL, [LOCKEDBY] VARCHAR(255) NULL, CONSTRAINT [PK_DATABASECHANGELOGLOCK] PRIMARY KEY ([ID])); on  INSERT INTO [DATABASECHANGELOGLOCK] ([ID], [LOCKED]) VALUES (1, 0): There is already an object named 'DATABASECHANGELOGLOCK' in the database.
it is not definitely case-sensitives issue, it could also be the schema mess. Could you log in to your database using Sybase Central and check whether in the schema exists a table with some other user name? Usually the main schema in Sybase is dba. But it could variate if you use some kind of connection / user / role setting applied to server/database instance. Please try to investigate more in the your current environment.
You were right Oleg. It was indeed schema related.
After some investigation, I found a workaround which consists of passing the defaultSchemaName property. Pass here the name of the user role associated with your schema (a list of these is found in the table sysusers).