Grokbase Groups Hive user March 2009
FAQ
Hi list,
I've successfully started a metastore server running with a connection to
a remote mysql db.
but when I execute a DDL script show tables, the metastore server throws
an exception like below:

ERROR JPOX.Datastore: Error thrown executing CREATE TABLE `SD_PARAMS`
(
`SD_ID` BIGINT NOT NULL,
`PARAM_KEY` VARCHAR(256) BINARY NOT NULL,
`PARAM_VALUE` VARCHAR(767) BINARY NULL,
PRIMARY KEY (`SD_ID`,`PARAM_KEY`)
) ENGINE=INNODB : Specified key was too long; max key length is 767 bytes
com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: Specified key was too
long; max key length is 767 bytes


I guess it's not a bug of Hive, but a limitation of Mysql InnoDb Engine when
creating tables. How do you solve this problem?

Thanks,
Min
--
My research interests are distributed systems, parallel computing and
bytecode based virtual machine.

http://coderplay.javaeye.com

Search Discussions

  • Min Zhou at Mar 2, 2009 at 12:25 pm
    It's okay now when I decrease the length of PARAM_KEY.

    CREATE TABLE `SD_PARAMS`
    (
    `SD_ID` BIGINT NOT NULL,
    `PARAM_KEY` VARCHAR(255) BINARY NOT NULL,
    `PARAM_VALUE` VARCHAR(767) BINARY NULL,
    PRIMARY KEY (`SD_ID`,`PARAM_KEY`)
    ) ENGINE=INNODB : Specified key was too long; max key length is 767 bytes

    On Mon, Mar 2, 2009 at 7:32 PM, Min Zhou wrote:

    Hi list,
    I've successfully started a metastore server running with a connection to
    a remote mysql db.
    but when I execute a DDL script show tables, the metastore server throws
    an exception like below:

    ERROR JPOX.Datastore: Error thrown executing CREATE TABLE `SD_PARAMS`
    (
    `SD_ID` BIGINT NOT NULL,
    `PARAM_KEY` VARCHAR(256) BINARY NOT NULL,
    `PARAM_VALUE` VARCHAR(767) BINARY NULL,
    PRIMARY KEY (`SD_ID`,`PARAM_KEY`)
    ) ENGINE=INNODB : Specified key was too long; max key length is 767 bytes
    com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: Specified key was too
    long; max key length is 767 bytes


    I guess it's not a bug of Hive, but a limitation of Mysql InnoDb Engine
    when creating tables. How do you solve this problem?

    Thanks,
    Min
    --
    My research interests are distributed systems, parallel computing and
    bytecode based virtual machine.

    http://coderplay.javaeye.com


    --
    My research interests are distributed systems, parallel computing and
    bytecode based virtual machine.

    http://coderplay.javaeye.com
  • Prasad Chakka at Mar 2, 2009 at 6:28 pm
    Most probably you have an utf-8 database and PARAM_KEY size is multiplied by 3 which is 768 and exceeds the mysql's prescribed max length.

    Reducing this to 255 may cause problems to users with existing tables. We should certainly change this next time we do an upgrade of schema.

    ________________________________
    From: Min Zhou <coderplay@gmail.com>
    Reply-To: <hive-user@hadoop.apache.org>
    Date: Mon, 2 Mar 2009 04:24:55 -0800
    To: <hive-user@hadoop.apache.org>
    Subject: Re: mysql exception when running a metastore thrift server

    It's okay now when I decrease the length of PARAM_KEY.

    CREATE TABLE `SD_PARAMS`
    (
    `SD_ID` BIGINT NOT NULL,
    `PARAM_KEY` VARCHAR(255) BINARY NOT NULL,
    `PARAM_VALUE` VARCHAR(767) BINARY NULL,
    PRIMARY KEY (`SD_ID`,`PARAM_KEY`)
    ) ENGINE=INNODB : Specified key was too long; max key length is 767 bytes


    On Mon, Mar 2, 2009 at 7:32 PM, Min Zhou wrote:
    Hi list,
    I've successfully started a metastore server running with a connection to a remote mysql db.
    but when I execute a DDL script show tables, the metastore server throws an exception like below:

    ERROR JPOX.Datastore: Error thrown executing CREATE TABLE `SD_PARAMS`
    (
    `SD_ID` BIGINT NOT NULL,
    `PARAM_KEY` VARCHAR(256) BINARY NOT NULL,
    `PARAM_VALUE` VARCHAR(767) BINARY NULL,
    PRIMARY KEY (`SD_ID`,`PARAM_KEY`)
    ) ENGINE=INNODB : Specified key was too long; max key length is 767 bytes
    com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: Specified key was too long; max key length is 767 bytes


    I guess it's not a bug of Hive, but a limitation of Mysql InnoDb Engine when creating tables. How do you solve this problem?

    Thanks,
    Min
    --
    My research interests are distributed systems, parallel computing and bytecode based virtual machine.

    http://coderplay.javaeye.com



    --
    My research interests are distributed systems, parallel computing and bytecode based virtual machine.

    http://coderplay.javaeye.com

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
groupuser @
categorieshive, hadoop
postedMar 2, '09 at 11:33a
activeMar 2, '09 at 6:28p
posts3
users2
websitehive.apache.org

2 users in discussion

Min Zhou: 2 posts Prasad Chakka: 1 post

People

Translate

site design / logo © 2021 Grokbase