firebird-support - Re: [ib-support] Migrating to FB 1.0 (key size problem)

Subject	Re: [ib-support] Migrating to FB 1.0 (key size problem)
Author	Herbert.Augustiny@sptroth.com
Post date	2002-03-22T09:12:25Z

>--------------------------------------------------------------------------------------------------------------------------------------------------|

>--------------------------------------------------------------------------------------------------------------------------------------------------|

<Herbert.Augustiny@...> wrote in message
news:OFF6604CC3.0CC29354-ONC1256B83.0038E106-C1256B83.0039C5A5@....
.
.

> Hi all
>
> I have a table on FB 9.4 test like this:
>
> CREATE DOMAIN ID_DOM AS NUMERIC(18,0);
>
> ID ID_DOM NOT NULL,
> Application_ID ID_DOM not null,
> Resource_Name VARCHAR(192) Character set none not null,
> Constraint Java_Resources_Unique Unique (Application_ID,
> Resource_Name),
>
> When I try to create the same table on FB 1.0 it fails with this error
> message:
> -key size exceeds implementation restriction for index "RDB$99"
> Statement failed, SQLCODE = -607
> unsuccessful metadata update

Please, in the future, remember to say which platform was the old version
and same for the new version. I'm 100% sure this is a non-Win32 platform.
At
least your 0.94 should be Linux or another port, but not Windows.

> If I change Resource_Name from VARCHAR(192) VARCHAR(188) I can create the
> table.

Yes. The difference is exactly 4 bytes. You see, I'm a genius in math.
There
was a typical compile-time definition in the build process that was set
only
for some platforms. It was related to int64. The code was protected by C's
preprocessor #define directives. I discovered that only Windows had the
same
preprocessor symbol in the build scripts. This means the places protected
by
#ifdef with such symbol weren't compiled in other platform. One of the
int64
places protected by the symbol was the usage of int64 in indices!!! Since
the Linux build didn't have it, I asked the team to remove that symbol and
stop the conditional compilation. All platforms were compiled from that
point with the int64 code for indices.

What this means for you: in the past (0.94), Linux and other non-Win32
platforms calculated the size of an index for an int64 as the size for an
int32, a typical integer. This was wrong. We had a bug logged where two
int64 values would be rejected in a Unique or PK constraint if their low
part (the low DWORD) was the same. Obviously, the index wasn't of real use
for the int64 range. The index missed the other DWORD, that's 4 bytes more.
Now that's fixed, the index size is calculated correctly and the stored
values aren't truncated. Hence, your maximum varchar shrunk but you won't
get false duplicates.

> The problem now is, I've got a db on FB 9.4 that I need to port to 1.0
> including the data. How can I shrink the column size of Resource_Name?
> (Alter table ... doesn't work)

Alter table won't let you shink a field that probably would cause data
truncation. You should drop the unique constraint, create a new field with
the length=188 and use the built-in substring:
update tbl set newfld = substring(oldfld from 1 for 188);
commit and drop the oldfld. If you can't, disconnect and retry.
After dropping the oldfld, do a COMMIT. Do an explicit commit or you will
clash with a bug highlighted in another thread those days here.
Use alter table again to rename the new field.
Reestablish the constraint.

C.
--
Claudio Valderrama C. - http://www.cvalde.com - http://www.firebirdSql.org
Independent developer
Owner of the Interbase® WebRing

To unsubscribe from this group, send an email to:
ib-support-unsubscribe@egroups.com

Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/