Total length of columns in constraint is too long
A user migrating from MySQL hit error 8082 ('total length of columns in constraint is too long') on Linux when creating a UNIQUE constraint on char(500), int8, int8 — it worked on Windows with 4K pages but not on Linux's default 2K pages. Replies explained the limit: roughly [(PAGESIZE-93)/5]-1 bytes (390 at 2K), and that each INT8 consumes 10 bytes internally, leaving ~370 for the char column. The poster added 4K and 8K buffer pools/dbspaces but still got the same error, and one key reply arrived as unreadable base64. No resolution is recorded in the thread.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi,
We recently migrated our application from MySQL to IDS. While the migration
(post DDL and few other code changes) worked just fine in a dev. environment
on windows, while executing DDL scripts on Linux, IDS throws this error.
Basically I have a unique constraint on 3 columns with data types char(500),
int8, int8. It is well documented in IDS SQL guide that constraint on things
such as # of columns, total size of the index, constraint depends on the page
size and by default on windows both the buffer pool and the root dbspace is
created with 4k page size and on linux it's 2k only.
I then created a buffer pool with 4k page size and also created a dbspace with
4k page size and modified the onconfig file to point to the new dbspace. I
then brought the server offline, brought it back up and executed the same DDL
without much luck. I still get the exact same error. I also tried bumping up
the page size all the way to 8k but that doesn't help either.
As per how i added the buffer pool and dbspace, I first added the buffer pool
using the onparams utility (and saw new entry being made in the onconfig file)
and created dbspace using the onmonitor utility.
I've already spent quite a bit of time investigating this issue and any help
would be much appreciated.
Ankur,
I am not sure what documentation you refer to, but the IBM Informix Guide
to SQL: Syntax for 9.x
found at the link listed below clearly states the limitations of multiple
column constraints( page 2-73).
One of these restrictions is: "The total length of the list of columns
cannot exceed 390 bytes.".
http://publib.boulder.ibm.com/epubs/pdf/ct1sqna.pdf
Please recheck your development instance to determine if the table
definition is indeed the same.
Using your example I was able (on RH Linux) to define a max column size for
the char column of
370. As per the extract from the IBM Informix Guide to SQL: Reference
(listed below), I found that
each of the int8 columns were using 10 bytes of storage, accounting for the
final 20 bytes that the
manual states is the max length for the columns listed in the constraint.
INT8
The INT8 data type stores whole numbers that can range in value from
–9,223,372,036,854,775,807 to 9,223,372,036,854,775,807 [or -(263-1) to
263-1], for
18 or 19 digits of precision. The number –9,223,372,036,854,775,808 is a
reserved value that cannot be used. The INT8 data type is typically used to
store large counts, quantities, and so on.
Dynamic Server stores INT8 data in internal format that can require up to
10
bytes of storage. Extended Parallel Server stores INT8 values as 8 bytes.
Arithmetic operations and sort comparisons are performed more efficiently
on
integer data than on floating-point or fixed-point decimal data, but INT8
cannot store data with absolute values beyond | 263-1 |. If a value exceeds
the numeric range of INT8, the database server does not store the value.
Thank you,
Roger Kee
"ANKUR SHAH"
<ankurdotshah@gma
il.com> To
Sent by: ids@iiug.org
ids-bounces@iiug. cc
org
Subject
Total length of columns in
01/03/2007 03:06 constraint is too long [8082]
PM
Please respond to
ids@iiug.org
Hi,
We recently migrated our application from MySQL to IDS. While the migration
(post DDL and few other code changes) worked just fine in a dev.
environment
on windows, while executing DDL scripts on Linux, IDS throws this error.
Basically I have a unique constraint on 3 columns with data types
char(500),
int8, int8. It is well documented in IDS SQL guide that constraint on
things
such as # of columns, total size of the index, constraint depends on the
page
size and by default on windows both the buffer pool and the root dbspace is
created with 4k page size and on linux it's 2k only.
I then created a buffer pool with 4k page size and also created a dbspace
with
4k page size and modified the onconfig file to point to the new dbspace. I
then brought the server offline, brought it back up and executed the same
DDL
without much luck. I still get the exact same error. I also tried bumping
up
the page size all the way to 8k but that doesn't help either.
As per how i added the buffer pool and dbspace, I first added the buffer
pool
using the onparams utility (and saw new entry being made in the onconfig
file)
and created dbspace using the onmonitor utility.
I've already spent quite a bit of time investigating this issue and any
help
would be much appreciated.
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
Ankur,
Sorry about the previous email response. Here is what I meant to send.
I am not sure what documentation you refer to, but the IBM Informix Guide
to SQL: Syntax for 9.x
found at the link listed below clearly states the limitations of multiple
column constraints( page 2-73).
One of these restrictions is: "The total length of the list of columns
cannot exceed 390 bytes.".
http://publib.boulder.ibm.com/epubs/pdf/ct1sqna.pdf
Please recheck your development instance to determine if the table
definition is indeed the same.
Using your example I was able (on RH Linux) to define a max column size for
the char column of
370. As per the extract from the IBM Informix Guide to SQL: Reference
(listed below), I found that
each of the int8 columns were using 10 bytes of storage, accounting for the
final 20 bytes that the
manual states is the max length for the columns listed in the constraint.
INT8
The INT8 data type stores whole numbers that can range in value from
–9,223,372,036,854,775,807 to 9,223,372,036,854,775,807 [or -(263-1) to
263-1], for
18 or 19 digits of precision. The number –9,223,372,036,854,775,808 is a
reserved value that cannot be used. The INT8 data type is typically used to
store large counts, quantities, and so on.
Dynamic Server stores INT8 data in internal format that can require up to
10
bytes of storage. Extended Parallel Server stores INT8 values as 8 bytes.
Arithmetic operations and sort comparisons are performed more efficiently
on
integer data than on floating-point or fixed-point decimal data, but INT8
cannot store data with absolute values beyond | 263-1 |. If a value exceeds
the numeric range of INT8, the database server does not store the value.
Thank you,
Roger Kee
ids-bounces@iiug.org wrote on 01/03/2007 03:06:09 PM:
>
> Hi,
>
> We recently migrated our application from MySQL to IDS. While the
migration
> (post DDL and few other code changes) worked just fine in a dev.
environment
> on windows, while executing DDL scripts on Linux, IDS throws this error.
> Basically I have a unique constraint on 3 columns with data types
char(500),
> int8, int8. It is well documented in IDS SQL guide that constraint on
things
> such as # of columns, total size of the index, constraint depends onthe
page
> size and by default on windows both the buffer pool and the root dbspace
is
> created with 4k page size and on linux it's 2k only.
>
> I then created a buffer pool with 4k page size and also created a
> dbspace with
> 4k page size and modified the onconfig file to point to the new dbspace.
I
> then brought the server offline, brought it back up and executed thesame
DDL
> without much luck. I still get the exact same error. I also tried bumping
up
> the page size all the way to 8k but that doesn't help either.
>
> As per how i added the buffer pool and dbspace, I first added the buffer
pool
> using the onparams utility (and saw new entry being made in the
> onconfig file)
> and created dbspace using the onmonitor utility.
>
> I've already spent quite a bit of time investigating this issue and any
help
> would be much appreciated.
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
I agree -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Roger Kee Sent: Thursday, 4 January 2007 10:37 To: ids@iiug.org Subject: Re: Total length of columns in constraint is t.... [8094] Ankur, I am not sure what documentation you refer to, but the IBM Informix Guide to SQL: Syntax for 9.x found at the link listed below clearly states the limitations of multiple column constraints( page 2-73). One ----8<------------------------------------------------------------------ ---- snip ----8<------------------------------------------------------------------ ---- bit of time investigating this issue and any help would be much appreciated. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum. ************************************************************************ ******* Forum Note: Use "Reply" to post a response in the discussion forum. ******************************************************************* Everything about this email and its attachments, including potentially confidential components, is only for the eyes and ears of the persons to whom it has been addressed (i.e. the persons whose names appear in the "To" section of the email; however, those listed in sections "CC" and "BCC" may also consider themselves included in the group whose eyes and ears this email is for.). In the event that no physical, emotional or spiritual resemblance can be made between you and the intended recipients, you have been mistakenly or deliberately omitted from the email, someone has given it to you, or you have nicked it. If you're not supposed to have access to this email, please delete it, destroy it and deprive others from it. Oh, and let us know when you are done. *******************************************************************
Must be a secret! Bob Roussey Unix / Informix Administration Spirit Airlines Robert.Roussey@SpiritAir.com -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Clifford Burton Sent: Wednesday, January 03, 2007 6:57 PM To: ids@iiug.org Subject: RE: Total length of columns in constraint is t.... [8097] I agree -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Roger Kee Sent: Thursday, 4 January 2007 10:37 To: ids@iiug.org Subject: Re: Total length of columns in constraint is t.... [8094] Ankur, I am not sure what documentation you refer to, but the IBM Informix Guide to SQL: Syntax for 9.x found at the link listed below clearly states the limitations of multiple column constraints( page 2-73). One ----8<------------------------------------------------------------------ ---- snip ----8<------------------------------------------------------------------ ---- bit of time investigating this issue and any help would be much appreciated. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum. ************************************************************************ ******* Forum Note: Use "Reply" to post a response in the discussion forum. ******************************************************************* Everything about this email and its attachments, including potentially confidential components, is only for the eyes and ears of the persons to whom it has been addressed (i.e. the persons whose names appear in the "To" section of the email; however, those listed in sections "CC" and "BCC" may also consider themselves included in the group whose eyes and ears this email is for.). In the event that no physical, emotional or spiritual resemblance can be made between you and the intended recipients, you have been mistakenly or deliberately omitted from the email, someone has given it to you, or you have nicked it. If you're not supposed to have access to this email, please delete it, destroy it and deprive others from it. Oh, and let us know when you are done. ******************************************************************* ************************************************************************ ******* Forum Note: Use "Reply" to post a response in the discussion forum.
Hi Ankur,
which error you are getting while creation of Tables.
Maximum Index key length can be calculated as follows:
[(PAGESIZE -93)/5] -1
like for 2k it is
[( 2048-93)/5] -1 =[1955/5] -1 =391-1=390
if PAGESIZE is 4K
it is [(4096-93)/5] -1 =4003/5-1=800-1 =799
Radhika.
On 1/4/07, ANKUR SHAH <ankurdotshah@gmail.com> wrote:
>
>
> Hi,
>
> We recently migrated our application from MySQL to IDS. While the
> migration
> (post DDL and few other code changes) worked just fine in a dev.
> environment
> on windows, while executing DDL scripts on Linux, IDS throws this error.
> Basically I have a unique constraint on 3 columns with data types
> char(500),
> int8, int8. It is well documented in IDS SQL guide that constraint on
> things
> such as # of columns, total size of the index, constraint depends on the
> page
> size and by default on windows both the buffer pool and the root dbspace
> is
> created with 4k page size and on linux it's 2k only.
>
> I then created a buffer pool with 4k page size and also created a dbspace
> with
> 4k page size and modified the onconfig file to point to the new dbspace. I
> then brought the server offline, brought it back up and executed the same
> DDL
> without much luck. I still get the exact same error. I also tried bumping
> up
> the page size all the way to 8k but that doesn't help either.
>
> As per how i added the buffer pool and dbspace, I first added the buffer
> pool
> using the onparams utility (and saw new entry being made in the onconfig
> file)
> and created dbspace using the onmonitor utility.
>
> I've already spent quite a bit of time investigating this issue and any
> help
> would be much appreciated.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
Boy, it's so big, Celso Coimbra -----Mensagem original----- De: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org]Em nome de Robert Roussey(MIS) Enviada em: quarta-feira, 3 de janeiro de 2007 22:25 Para: ids@iiug.org Assunto: RE: Total length of columns in constraint is t.... [8099] Must be a secret! Bob Roussey Unix / Informix Administration Spirit Airlines Robert.Roussey@SpiritAir.com -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Clifford Burton Sent: Wednesday, January 03, 2007 6:57 PM To: ids@iiug.org Subject: RE: Total length of columns in constraint is t.... [8097] I agree -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Roger Kee Sent: Thursday, 4 January 2007 10:37 To: ids@iiug.org Subject: Re: Total length of columns in constraint is t.... [8094] Ankur, I am not sure what documentation you refer to, but the IBM Informix Guide to SQL: Syntax for 9.x found at the link listed below clearly states the limitations of multiple column constraints( page 2-73). One ----8<------------------------------------------------------------------ ---- snip ----8<------------------------------------------------------------------ ---- bit of time investigating this issue and any help would be much appreciated. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum. ************************************************************************ ******* Forum Note: Use "Reply" to post a response in the discussion forum. ******************************************************************* Everything about this email and its attachments, including potentially confidential components, is only for the eyes and ears of the persons to whom it has been addressed (i.e. the persons whose names appear in the "To" section of the email; however, those listed in sections "CC" and "BCC" may also consider themselves included in the group whose eyes and ears this email is for.). In the event that no physical, emotional or spiritual resemblance can be made between you and the intended recipients, you have been mistakenly or deliberately omitted from the email, someone has given it to you, or you have nicked it. If you're not supposed to have access to this email, please delete it, destroy it and deprive others from it. Oh, and let us know when you are done. ******************************************************************* ************************************************************************ ******* Forum Note: Use "Reply" to post a response in the discussion forum. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.
Hi Radhika, The error was mentioned in the subject, it was "total length of the columns in constraint is too large". AS i had mentioned in the post, i bumped up the buffered pool and the dbspace all the way upto 8k without much luck. Not sure what i am doing wrong but it still doesn't work.
The response had nothing but some junk data. Am i missing something, was there an attachment or something that's rendered inline on the browser.