[Liquibase-user] Problem creating databasechangelog table in Firebird

[DATACOM - Diego]

Hi all,

I am trying to use Liquibase 1.1.1 on Firebird 1.5 using the Ant task.
When I try to migrate the database for the first time, the build fails
with this error:


updateDatabase:
[migrateDatabase] 01/08/2007 12:14:35
liquibase.database.DatabaseFactory findCorrectDatabaseImplementation
[migrateDatabase] WARNING: Unknown database: Firebird
[migrateDatabase] 01/08/2007 12:14:36
liquibase.database.AbstractDatabase checkDatabaseChangeLogTable
[migrateDatabase] INFO: Creating database history table with name:
DATABASECHANGELOG

BUILD FAILED
E:\workspace3.2\liquibase_test\build.xml:41:
liquibase.migrator.exception.MigrationFailedException:
liquibase.migrator.exception.JDBCException:
org.firebirdsql.jdbc.FBSQLException: GDS Exception. 335544351.
unsuccessful metadata update
key size too big for index RDB$PRIMARY446


Firebird 1.5 has a restriction that an index cannot exceed 252 bytes
(this seems to have increased in Firebird 2). To overcome this, I am
creating the table manually using smaller column sizes (the CREATE
used by Liquibase can be found on class AbstractDatabase):


CREATE TABLE databasechangelog(
id VARCHAR(30) NOT NULL,
author VARCHAR(50) NOT NULL,
filename VARCHAR(255) NOT NULL,
dateexecuted TIMESTAMP NOT NULL,
md5sum VARCHAR(32),
description VARCHAR(255),
comments VARCHAR(255),
tag VARCHAR(255),
liquibase VARCHAR(10)
);
ALTER TABLE databasechangelog ADD CONSTRAINT databasechangelog_pk
PRIMARY KEY (id, author);


This is not the better solution, of course. I wonder if there is any
possibility that these sizes be changed in Liquibase. Maybe avoiding
such a big primary key would be an even better solution. I thought the
CREATE could become something like this:


CREATE TABLE databasechangelog (
id NUMERIC(10) NOT NULL,
name VARCHAR(255) NOT NULL,
author VARCHAR(255) NOT NULL,
filename VARCHAR(255) NOT NULL,
dateexecuted TIMESTAMP NOT NULL,
md5sum VARCHAR(32),
description VARCHAR(255),
comments VARCHAR(255),
tag VARCHAR(255),
liquibase VARCHAR(10)
);
ALTER TABLE databasechangelog ADD CONSTRAINT databasechangelog_pk
PRIMARY KEY (id);


Here the previous "id" column was renamed to "name" and a new numeric
"id" column was created to serve as primary key. I think this is more
elegant and can be more efficient too.

What do you think?

[Nathan Voxland]

I ran into a similar problem when working on support for Sybase recently. The create table statement in trunk (upcoming 1.2 release) is currently:

CREATE TABLE DATABASECHANGELOG (
id varchar(150) not null,
author varchar(150) not null,
filename varchar(255) not null,
dateExecuted " + getDateTimeType() + " not null,
md5sum varchar(32),
description varchar(255),
comments varchar(255),
tag varchar(255),
liquibase varchar(10),
primary key(id, author, filename))

which is similar to what you were doing, although with larger id and author columns.

I think we need to have the primary key on id, author and filename because the uniqueness of a change set it determined by all three of these. Having a PK on them will make lookups by all three faster, and will guarantee that uniqueness. We could create a surrogate primary key like you suggested, but I think we would still want to have a unique constraint on name/author/filename so I don't think we would be loosing much.

I'm torn on the best way to handle this problem. I feel that if I cut down id, author, and filename to be any smaller to take firebird's limitations into account, it will be to restricting on other people. I'm thinking the best thing to do is document the create table statement on the site and tell people that if their database doesn't support keys as large to lower id, author, and filename as they see fit.

Does that seem best?

Nathan


-----Original Message-----
From: liquibase-user-***@lists.sourceforge.net [mailto:liquibase-user-***@lists.sourceforge.net] On Behalf Of DATACOM - Diego
Sent: Wednesday, August 01, 2007 12:37 PM
To: liquibase-***@lists.sourceforge.net
Subject: [Liquibase-user] Problem creating databasechangelog table inFirebird

Hi all,

I am trying to use Liquibase 1.1.1 on Firebird 1.5 using the Ant task.
When I try to migrate the database for the first time, the build fails
with this error:


updateDatabase:
[migrateDatabase] 01/08/2007 12:14:35
liquibase.database.DatabaseFactory findCorrectDatabaseImplementation
[migrateDatabase] WARNING: Unknown database: Firebird
[migrateDatabase] 01/08/2007 12:14:36
liquibase.database.AbstractDatabase checkDatabaseChangeLogTable
[migrateDatabase] INFO: Creating database history table with name:
DATABASECHANGELOG

BUILD FAILED
E:\workspace3.2\liquibase_test\build.xml:41:
liquibase.migrator.exception.MigrationFailedException:
liquibase.migrator.exception.JDBCException:
org.firebirdsql.jdbc.FBSQLException: GDS Exception. 335544351.
unsuccessful metadata update
key size too big for index RDB$PRIMARY446


Firebird 1.5 has a restriction that an index cannot exceed 252 bytes
(this seems to have increased in Firebird 2). To overcome this, I am
creating the table manually using smaller column sizes (the CREATE
used by Liquibase can be found on class AbstractDatabase):


CREATE TABLE databasechangelog(
id VARCHAR(30) NOT NULL,
author VARCHAR(50) NOT NULL,
filename VARCHAR(255) NOT NULL,
dateexecuted TIMESTAMP NOT NULL,
md5sum VARCHAR(32),
description VARCHAR(255),
comments VARCHAR(255),
tag VARCHAR(255),
liquibase VARCHAR(10)
);
ALTER TABLE databasechangelog ADD CONSTRAINT databasechangelog_pk
PRIMARY KEY (id, author);


This is not the better solution, of course. I wonder if there is any
possibility that these sizes be changed in Liquibase. Maybe avoiding
such a big primary key would be an even better solution. I thought the
CREATE could become something like this:


CREATE TABLE databasechangelog (
id NUMERIC(10) NOT NULL,
name VARCHAR(255) NOT NULL,
author VARCHAR(255) NOT NULL,
filename VARCHAR(255) NOT NULL,
dateexecuted TIMESTAMP NOT NULL,
md5sum VARCHAR(32),
description VARCHAR(255),
comments VARCHAR(255),
tag VARCHAR(255),
liquibase VARCHAR(10)
);
ALTER TABLE databasechangelog ADD CONSTRAINT databasechangelog_pk
PRIMARY KEY (id);


Here the previous "id" column was renamed to "name" and a new numeric
"id" column was created to serve as primary key. I think this is more
elegant and can be more efficient too.

What do you think?
--
DIEGO Moreira da Rosa

DATACOM
Av França, 735 - Porto Alegre, RS - 90230-220
DDR: [phone]
Fax: [phone]
site: www.datacom.ind.br

-------------------------------------------------------------------------
This SF.net email is sponsored by: Splunk Inc.
Still grepping through log files to find problems? Stop.
Now Search log events and configuration files using AJAX and a browser.
Download your FREE copy of Splunk now >> http://get.splunk.com/

[DATACOM - Diego]

Yes, it seems to be a recurrent problem. I took a look at dbdeploy to
see how they cope with this. Since dbdeploy does not update the database
automatically, they include a directory in their distribution containing
a SQL script for each supported DBMS.

Post by Nathan Voxland
which is similar to what you were doing, although with larger id and author columns.

By the way, what is the reason to have "filename" in the primary key?

Post by Nathan Voxland
I think we need to have the primary key on id, author and filename because the uniqueness of a change set it determined by all three of these. Having a PK on them will make lookups by all three faster, and will guarantee that uniqueness. We could create a surrogate primary key like you suggested, but I think we would still want to have a unique constraint on name/author/filename so I don't think we would be loosing much.

Yes, you have a point. Obviously Firebird 1.5 will not allow a unique
index either.

Post by Nathan Voxland
I'm torn on the best way to handle this problem. I feel that if I cut down id, author, and filename to be any smaller to take firebird's limitations into account, it will be to restricting on other people. I'm thinking the best thing to do is document the create table statement on the site and tell people that if their database doesn't support keys as large to lower id, author, and filename as they see fit.
Does that seem best?

Like you said, one option is to document the CREATE and warn people that
they may be required to run it manually. In this case, the script(s)
could be included in the distribution, so that people can modify and run
it using Ant (to look for the script inside the source code is not cool).

Another option can be to make the size of the columns depend on the DBMS
implementation (just like the "getDateTimeType()" in the middle of the
CREATE). This will not solve my problem with Firebird 1.5 (since it is
not supported yet), but can solve your problem with Sybase.

Post by Nathan Voxland
Nathan

[Nathan Voxland]

We include filename as a primary key column because it is part of what uniquely identifies the change. You can use the <include> tag to break your changeLog up into multiple files, and by using the filename as part of the identifier, you don't have to worry about duplicating your id's between files.

Moving the create table SQL into it's own method is a good idea. I did something similar with the createChangeLogLockSQL method, but hadn't gotten around to it with the databasechangelog table.

I think I'm going to leave the column sizes as they are because I decided that 150 is just fine for an author and id column and it's more likely to work on other databases that have similar limitations.

For now, I will document the create table statement on the site plus check databaseMetaData.getMaxIndexLength() to make sure we can create the index. If we can't we will output the error and the starting create table statement.

Nathan

-----Original Message-----
From: liquibase-user-***@lists.sourceforge.net [mailto:liquibase-user-***@lists.sourceforge.net] On Behalf Of DATACOM - Diego
Sent: Thursday, August 02, 2007 8:57 AM
To: liquibase-***@lists.sourceforge.net
Subject: Re: [Liquibase-user] Problem creating databasechangelog tableinFirebird
Yes, it seems to be a recurrent problem. I took a look at dbdeploy to
see how they cope with this. Since dbdeploy does not update the database
automatically, they include a directory in their distribution containing
a SQL script for each supported DBMS.

Post by Nathan Voxland
which is similar to what you were doing, although with larger id and author columns.

By the way, what is the reason to have "filename" in the primary key?

Post by Nathan Voxland
I think we need to have the primary key on id, author and filename because the uniqueness of a change set it determined by all three of these. Having a PK on them will make lookups by all three faster, and will guarantee that uniqueness. We could create a surrogate primary key like you suggested, but I think we would still want to have a unique constraint on name/author/filename so I don't think we would be loosing much.

Yes, you have a point. Obviously Firebird 1.5 will not allow a unique
index either.

Post by Nathan Voxland
I'm torn on the best way to handle this problem. I feel that if I cut down id, author, and filename to be any smaller to take firebird's limitations into account, it will be to restricting on other people. I'm thinking the best thing to do is document the create table statement on the site and tell people that if their database doesn't support keys as large to lower id, author, and filename as they see fit.
Does that seem best?

Like you said, one option is to document the CREATE and warn people that
they may be required to run it manually. In this case, the script(s)
could be included in the distribution, so that people can modify and run
it using Ant (to look for the script inside the source code is not cool).

Another option can be to make the size of the columns depend on the DBMS
implementation (just like the "getDateTimeType()" in the middle of the
CREATE). This will not solve my problem with Firebird 1.5 (since it is
not supported yet), but can solve your problem with Sybase.

Post by Nathan Voxland
Nathan

--
DIEGO Moreira da Rosa

DATACOM
Av França, 735 - Porto Alegre, RS - 90230-220
DDR: [phone]
Fax: [phone]
site: www.datacom.ind.br

-------------------------------------------------------------------------
This SF.net email is sponsored by: Splunk Inc.
Still grepping through log files to find problems? Stop.
Now Search log events and configuration files using AJAX and a browser.
Download your FREE copy of Splunk now >> http://get.splunk.com/

[DATACOM - Diego]

Post by Nathan Voxland
For now, I will document the create table statement on the site plus check databaseMetaData.getMaxIndexLength() to make sure we can create the index. If we can't we will output the error and the starting create table statement.

That is fine. I just do not know whether you can trust on
databaseMetaData.getMaxIndexLength() or not. I tried it against Firebird
1.5 and Oracle 10g and it returned 0 (no limit or not known) for both.

Also the limit for Firebird 1.5 is variable depending if it is a
compound index. To see more about Firebird indexes, see here:

http://www.ibphoenix.com/main.nfs?a=ibphoenix&page=ibp_expert1

Post by Nathan Voxland
Nathan

[Nathan Voxland]

Well that's no help then :) Lazy JDBC driver writers...

I'll probably just have to rely on documenting it on the supported databases page.

Thanks for checking what Firebird returns.

Nathan

-----Original Message-----
From: liquibase-user-***@lists.sourceforge.net [mailto:liquibase-user-***@lists.sourceforge.net] On Behalf Of DATACOM - Diego
Sent: Thursday, August 02, 2007 11:44 AM
To: liquibase-***@lists.sourceforge.net
Subject: Re: [Liquibase-user] Problem creating databasechangelogtableinFirebird

Post by Nathan Voxland
For now, I will document the create table statement on the site plus check databaseMetaData.getMaxIndexLength() to make sure we can create the index. If we can't we will output the error and the starting create table statement.

That is fine. I just do not know whether you can trust on
databaseMetaData.getMaxIndexLength() or not. I tried it against Firebird
1.5 and Oracle 10g and it returned 0 (no limit or not known) for both.

Also the limit for Firebird 1.5 is variable depending if it is a
compound index. To see more about Firebird indexes, see here:

http://www.ibphoenix.com/main.nfs?a=ibphoenix&page=ibp_expert1

Post by Nathan Voxland
Nathan

--
DIEGO Moreira da Rosa

DATACOM
Av França, 735 - Porto Alegre, RS - 90230-220
DDR: [phone]
Fax: [phone]
site: www.datacom.ind.br

-------------------------------------------------------------------------
This SF.net email is sponsored by: Splunk Inc.
Still grepping through log files to find problems? Stop.
Now Search log events and configuration files using AJAX and a browser.
Download your FREE copy of Splunk now >> http://get.splunk.com/