July 27th, 2011, 09:20 AM
-
Key size too big for index
Hello everybody!!
I was trying to define the index for my table PRODUCTS using theese fields:
COMPANY_CODE VARCHAR (2)
REF_NUMBER VARCHAR (256)
COMPANY_CODE VARCHAR (2)
and i can't because this error
SEVERE 27/07/11 15:36:liquibase: Error executing SQL ALTER TABLE PRODUCTS ADD CONSTRAINT PRODUCTS_PK PRIMARY KEY (COMPANY_CODE, REF_NUMBER)
org.firebirdsql.jdbc.FBSQLException: GDS Exception. 335544351. unsuccessful metadata update
My database has utf8 charset and has a page size of 16384
According to volny index calculator:
i could create this index:
Number of columns 2
Total key size 1292
Remains 2800
¿Why can't i do it?
Regards
July 27th, 2011, 10:17 AM
-
Originally Posted by DaleCooper
Hello everybody!!
I was trying to define the index for my table PRODUCTS using theese fields:
COMPANY_CODE VARCHAR (2)
REF_NUMBER VARCHAR (256)
COMPANY_CODE VARCHAR (2)
and i can't because this error
SEVERE 27/07/11 15:36:liquibase: Error executing SQL ALTER TABLE PRODUCTS ADD CONSTRAINT PRODUCTS_PK PRIMARY KEY (COMPANY_CODE, REF_NUMBER)
org.firebirdsql.jdbc.FBSQLException: GDS Exception. 335544351. unsuccessful metadata update
My database has utf8 charset and has a page size of 16384
According to volny index calculator:
i could create this index:
Number of columns 2
Total key size 1292
Remains 2800
¿Why can't i do it?
Regards
I think you need to post the current DDL for the table as reported by Firebird
July 28th, 2011, 01:44 AM
-
The isql with the sql output is this:
isql
connect INVENT.FDB user 'XXX' password 'XXX';
SQL> ALTER TABLE PRODUCTS ADD CONSTRAINT PRODUCTS_PK PRIMARY KEY (COMPANY_CODE, REF_NUMBER)
Statement failed, SQLCODE = -607
unsuccessful metadata update
-key size too big for index PRODUCTS
SQL>
July 28th, 2011, 02:55 AM
-
Originally Posted by DaleCooper
The isql with the sql output is this:
isql
connect INVENT.FDB user 'XXX' password 'XXX';
SQL> ALTER TABLE PRODUCTS ADD CONSTRAINT PRODUCTS_PK PRIMARY KEY (COMPANY_CODE, REF_NUMBER)
Statement failed, SQLCODE = -607
unsuccessful metadata update
-key size too big for index PRODUCTS
SQL>
Not how you connect to the database - @clivew wants the table structure- i.e CREATE TABLE PRODUCTS ( .....
July 28th, 2011, 02:56 AM
-
Originally Posted by DaleCooper
The isql with the sql output is this:
isql
connect INVENT.FDB user 'XXX' password 'XXX';
SQL> ALTER TABLE PRODUCTS ADD CONSTRAINT PRODUCTS_PK PRIMARY KEY (COMPANY_CODE, REF_NUMBER)
Statement failed, SQLCODE = -607
unsuccessful metadata update
-key size too big for index PRODUCTS
SQL>
Not how you connect to the database - @clivew wants the table structure- i.e CREATE TABLE PRODUCTS (Fields, data types etc)
July 28th, 2011, 03:16 AM
-
The create table sql is t:
create table PRODUCTS
(
COMPANY_CODE VARCHAR(2) not NULL,
REF_NUMBER VARCHAR(256) not NULL,
DESC VARCHAR(100),
);
July 28th, 2011, 03:53 PM
-
You are still not posting the FULL DDL for the table (with all its constraints etc.) as REPORTED BY Firebird.
I can tell this because:
1. What you posted would not compile due to the comma before the closing paren.
2. There is no mention of the PRODUCTS index referenced in your error message.
Trust me, we are not just being picky to make your life difficult.
We give of our time freely and we need to know what Firebird itself reports NOT what you think you instructed it to create in order to use our time wisely.
Clive
July 29th, 2011, 02:08 AM
-
Sorry , i have made the two mistakes writing the ddl sentences due to have been copied by hand.
The complete ddl sentences are theese:
create table PRODUCTS
(
COMPANY_CODE VARCHAR(2) not NULL,
REF_NUMBER VARCHAR(256) not NULL,
DESC VARCHAR(100)
);
ALTER TABLE PRODUCTS ADD CONSTRAINT PRODUCTS_PK PRIMARY KEY (COMPANY_CODE, REF_NUMBER)
Statement failed, SQLCODE = -607
unsuccessful metadata update
-key size too big for index PRODUCTS_PK
The table has not more constraints, only this pk.
Regards.
July 29th, 2011, 03:07 AM
-
Sorry , i have made the two mistakes writing the ddl sentences due to have been copied by hand.
The complete ddl sentences are theese:
create table PRODUCTS
(
COMPANY_CODE VARCHAR(2) not NULL,
REF_NUMBER VARCHAR(256) not NULL,
DESC VARCHAR(100)
);
ALTER TABLE PRODUCTS ADD CONSTRAINT PRODUCTS_PK PRIMARY KEY (COMPANY_CODE, REF_NUMBER)
Statement failed, SQLCODE = -607
unsuccessful metadata update
-key size too big for index PRODUCTS_PK
The table has not more constraints, only this pk.
Regards.
July 29th, 2011, 04:15 AM
-
tested and works
Originally Posted by DaleCooper
Sorry , i have made the two mistakes writing the ddl sentences due to have been copied by hand.
The complete ddl sentences are theese:
create table PRODUCTS
(
COMPANY_CODE VARCHAR(2) not NULL,
REF_NUMBER VARCHAR(256) not NULL,
DESC VARCHAR(100)
);
ALTER TABLE PRODUCTS ADD CONSTRAINT PRODUCTS_PK PRIMARY KEY (COMPANY_CODE, REF_NUMBER)
Statement failed, SQLCODE = -607
unsuccessful metadata update
-key size too big for index PRODUCTS_PK
The table has not more constraints, only this pk.
Regards.
I have tested and it works on firebird 2.5 page size 16384
with db UTF-8
ODS Version 11.2
Page Size 16384
Pages 153
Size on Disk 2.39MB
SQL Dialect 3
Default Character Set UTF8
https://gist.github.com/1113493
Please do a backup restore and check if it works after that
ps: also according to index calculator should be enough space for the index
http://www.volny.cz/iprenosil/interb...calculator.htm
July 29th, 2011, 04:46 AM
-
I know what is happening:
I am using latest fyracle version which is based in firebird 1.5 version.
I though this fyracle version was based in latest firebird version (2.5) and it is not true.
Whith the 1.5 version and according to wolny index key size calculator the index is too big.
Thanks to all for the help and time used.
July 29th, 2011, 01:23 PM
-
I know what is happening:
Glad to hear you are up and running.
No doubt you will now understand the importance of giving us a complete picture when soliciting our help.
I don't believe you ever mentioned fyracle and your failure to cut/paste information we requested accurately
led us down some blind alleys.
This is not a complaint; just a lesson for anyone who reads this post about the importance of
giving us a complete context to get a rapid solution.