#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2011
    Posts
    6
    Rep Power
    0

    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
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2006
    Location
    Carlsbad, CA
    Posts
    2,057
    Rep Power
    383
    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
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2011
    Posts
    6
    Rep Power
    0
    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>
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2006
    Location
    Plovdiv. Bulgaria
    Posts
    226
    Rep Power
    13
    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 ( .....
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2006
    Location
    Plovdiv. Bulgaria
    Posts
    226
    Rep Power
    13
    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)
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2011
    Posts
    6
    Rep Power
    0
    The create table sql is t:

    create table PRODUCTS
    (
    COMPANY_CODE VARCHAR(2) not NULL,
    REF_NUMBER VARCHAR(256) not NULL,
    DESC VARCHAR(100),
    );
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2006
    Location
    Carlsbad, CA
    Posts
    2,057
    Rep Power
    383
    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
  14. #8
  15. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2011
    Posts
    6
    Rep Power
    0
    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.
  16. #9
  17. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2011
    Posts
    6
    Rep Power
    0
    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.
  18. #10
  19. Bug Hunter
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2003
    Location
    Transylvania (Romania)
    Posts
    309
    Rep Power
    24

    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
    My home page: http://www.firebirdsql.org and work place :http://www.reea.net
  20. #11
  21. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2011
    Posts
    6
    Rep Power
    0
    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.
  22. #12
  23. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2006
    Location
    Carlsbad, CA
    Posts
    2,057
    Rep Power
    383
    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.

IMN logo majestic logo threadwatch logo seochat tools logo