You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@hive.apache.org by Min Zhou <co...@gmail.com> on 2009/03/02 12:32:52 UTC

mysql exception when running a metastore thrift server

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

Re: mysql exception when running a metastore thrift server

Posted by Prasad Chakka <pr...@facebook.com>.
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 <co...@gmail.com>
Reply-To: <hi...@hadoop.apache.org>
Date: Mon, 2 Mar 2009 04:24:55 -0800
To: <hi...@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 <co...@gmail.com> 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

Re: mysql exception when running a metastore thrift server

Posted by Min Zhou <co...@gmail.com>.
 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 <co...@gmail.com> 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