You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@hive.apache.org by Timothy Garza <ti...@collinsongroup.com> on 2015/11/30 19:12:12 UTC

UPDATE RE: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Specified key was too long; max key length is 767 bytes (beeline - hive server 2)

We’ve been playing with the MySQL Global Settings: (Hive metastore)

mysql> set global innodb_large_prefix = ON;  (<-- this was set to OFF previously)

…and now the ERROR is thus:
Specified key was too long; max key length is 3072 bytes

So it’s still ‘failing’ (but the HDFS operation itself succeeds). This must be the problem area as the message has changed from:

Specified key was too long; max key length is 767 bytes
to
Specified key was too long; max key length is 3072 bytes

…simply by altering the MySQL Global settings. So is hiveserver2 trying to use a key larger than MySQL supports (v5.5.2, file format Antelope)?

NB. This only occurs when executing beeline INSERT, not CREATE nor SELECT statements on a Hive Table (in this case a Sequence File).

My colleague thinks this is SSL related (because of the use of the word ‘key’ in the error), is HiveServer2 connecting to the Metastore using SSL?

________________________________
Weirdly I’m experiencing exactly the same issue when trying to populate a Hive Table using INSERT OVERWRITE TABLE. We’re recently upgraded from Hive 0.13 to 1.2.1. NB. The Hive Table populates but the map-reduce returns an error code. I have run the hive Schema Tool:   schematool -dbType mysql -upgradeSchemaFrom 0.13

The only table I can see with 767 size column is “PART_COL_STATS” – implemented in one of the metastore upgrade scripts. Column Name: PARTITION_NAME         | varchar(767). <-- I changed this column to varchar(1000) but get the same message afterwards:

ERROR jdbc.JDBCStatsPublisher: Error during JDBC initialization.
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Specified key was too long; max key length is 767 bytes
        at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
        at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
        at java.lang.reflect.Constructor.newInstance(Constructor.java:526)
        at com.mysql.jdbc.Util.handleNewInstance(Util.java:409)
        at com.mysql.jdbc.Util.getInstance(Util.java:384)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1052)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4232)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4164)
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2615)
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2776)
        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2832)
        at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1755)
        at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1679)
        at org.apache.hadoop.hive.ql.stats.jdbc.JDBCStatsPublisher.init(JDBCStatsPublisher.java:292)
        at org.apache.hadoop.hive.ql.exec.mr.ExecDriver.execute(ExecDriver.java:411)
        at org.apache.hadoop.hive.ql.exec.mr.MapRedTask.execute(MapRedTask.java:137)
        at org.apache.hadoop.hive.ql.exec.Task.executeTask(Task.java:160)
        at org.apache.hadoop.hive.ql.exec.TaskRunner.runSequential(TaskRunner.java:88)
        at org.apache.hadoop.hive.ql.Driver.launchTask(Driver.java:1653)
        at org.apache.hadoop.hive.ql.Driver.execute(Driver.java:1412)
        at org.apache.hadoop.hive.ql.Driver.runInternal(Driver.java:1195)
        at org.apache.hadoop.hive.ql.Driver.run(Driver.java:1059)
        at org.apache.hadoop.hive.ql.Driver.run(Driver.java:1054)
        at org.apache.hive.service.cli.operation.SQLOperation.runQuery(SQLOperation.java:154)
        at org.apache.hive.service.cli.operation.SQLOperation.access$100(SQLOperation.java:71)
        at org.apache.hive.service.cli.operation.SQLOperation$1$1.run(SQLOperation.java:206)
        at java.security.AccessController.doPrivileged(Native Method)
        at javax.security.auth.Subject.doAs(Subject.java:415)
        at org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1548)
        at org.apache.hive.service.cli.operation.SQLOperation$1.run(SQLOperation.java:218)
        at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:471)
        at java.util.concurrent.FutureTask.run(FutureTask.java:262)
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
        at java.lang.Thread.run(Thread.java:745)



From: San Luo [mailto:luohui@damai.cn]
Sent: 30 November 2015 10:06
To: user@hive.apache.org<ma...@hive.apache.org>
Subject: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Specified key was too long; max key length is 767 bytes


hi guys,

     when I am trying to use mysql to manager hive metastore,I got a problem like below:

[root@master hive]# hive

Logging initialized using configuration in jar:file:/usr/lib/hive/lib/hive-common-1.1.1.jar!/hive-log4j.properties
SLF4J: Class path contains multiple SLF4J bindings.
SLF4J: Found binding in [jar:file:/usr/lib/hadoop/share/hadoop/common/lib/slf4j-log4j12-1.7.5.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: Found binding in [jar:file:/usr/lib/hive/lib/hive-jdbc-1.1.1-standalone.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation.
SLF4J: Actual binding is of type [org.slf4j.impl.Log4jLoggerFactory]
hive> show tables;
OK
invites
pokes
test
Time taken: 2.573 seconds, Fetched: 3 row(s)
hive> LOAD DATA LOCAL INPATH './examples/files/kv2.txt' OVERWRITE INTO TABLE invites PARTITION (ds='2008-08-15');
Loading data to table default.invites partition (ds=2008-08-15)
Interrupting... Be patient, this might take some time.
Press Ctrl+C again to kill JVM
Failed with exception MetaException(message:javax.jdo.JDODataStoreException: An exception was thrown while adding/validating class(es) : Specified key was too long; max key length is 767 bytes
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Specified key was too long; max key length is 767 bytes
        at sun.reflect.GeneratedConstructorAccessor32.newInstance(Unknown Source)
        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
        at java.lang.reflect.Constructor.newInstance(Constructor.java:422)
        at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
        at com.mysql.jdbc.Util.getInstance(Util.java:386)
…….


I tried hive 1.2.1 and hive 1.1.1, neither works fine. I also tried to modify mysql charcter set of hive database to latin1 and utf8 but it doesn’t work.

Any idea will be appreciated.



The Collinson Group Limited; Registered number: 2577557, Registered in England & Wales; Registered Office: Cutlers Exchange, 123 Houndsditch, London, EC3A 7BU.


This e-mail may contain privileged and confidential information and/or copyright material and is intended for the use of the addressee only. If you receive this e-mail by mistake please advise the sender immediately by using the reply facility in your e-mail software and delete this e-mail from your computer system. You may not deliver, copy or disclose its contents to anyone else. Any unauthorised use may be unlawful. Any views expressed in this e-mail are those of the individual sender and may not necessarily reflect the views of The Collinson Group Ltd and/or its subsidiaries or any other associated company (collectively “Collinson Group”).

As communications via the Internet are not secure Collinson Group cannot accept any liability if this e-mail is accessed by third parties during the course of transmission or is modified or amended in any way following despatch. Collinson Group cannot guarantee that any attachment to this email does not contain a virus, therefore it is strongly recommended that you carry out your own virus check before opening any attachment, as we cannot accept liability for any damage sustained as a result of software virus infection. Senders of messages shall be taken to consent to the monitoring and recording of e-mails addressed to members of the Company.

RE: UPDATE RE: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Specified key was too long; max key length is 767 bytes (beeline - hive server 2)

Posted by Timothy Garza <ti...@collinsongroup.com>.
The following JIRA<https://issues.apache.org/jira/browse/HIVE-12553> refers:

https://issues.apache.org/jira/browse/HIVE-12553



From: Timothy Garza [mailto:timothy.garza@collinsongroup.com]
Sent: 01 December 2015 12:44
To: user@hive.apache.org
Subject: RE: UPDATE RE: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Specified key was too long; max key length is 767 bytes (beeline - hive server 2)

Now leaning towards this being a bug in Hive v1.2.1. for the MySQL metastore classes…Let me show you why:

I’m running the following simple Hive QL:

INSERT OVERWRITE TABLE <tablename> SELECT <column1>,<column2>… FROM <otherTable>;

[HiveServer2-Background-Pool: Thread-20]: ERROR jdbc.JDBCStatsPublisher: Error during JDBC initialization.
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Specified key was too long; max key length is 767 bytes

…that MySQL error refers to the Index Key ‘prefix’ being too long (blah blah). So looking in the MySQL Log, I see the following entry (note the ID column):

6 Query     CREATE TABLE /*  */ PARTITION_STATS_V2 (TS TIMESTAMP DEFAULT CURRENT_TIMESTAMP, ID VARCHAR(4000) PRIMARY KEY , ROW_COUNT BIGINT , RAW_DATA_SIZE BIGINT )

…that DDL for MySQL is generated by Hive and it is this which is failing!!! If we create the table ‘manually’ (using the DDL captured from the MySQL Log) but instead using a Key-size lower than 767, eg. varchar(255):

mysql> CREATE TABLE PARTITION_STATS_V2 (TS TIMESTAMP DEFAULT CURRENT_TIMESTAMP, ID VARCHAR(255) PRIMARY KEY , ROW_COUNT BIGINT , RAW_DATA_SIZE BIGINT );

This succeeds in creating the table in the metastore, but then when I run the Map-Reduce jobs again I can see in the MySQL Log that Hive detects the ‘anomaly’ ID column ie. varchar(255) and tries to correct it by running some more DDL:

ALTER TABLE PARTITION_STATS_V2 ALTER COLUMN ID VARCHAR(4000);   <-- it doesn’t like varchar(255)!!!

Even this alteration by Hive fails due to Syntax error:

WARN jdbc.JDBCStatsPublisher: Failed to update ID (size 255)
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'VARCHAR(4000)' at line 1

…because you cannot have varchar(4000) in a Primary Key column (due to MySQL index key prefix limitations).

Conclusion:
               This can only be a bug in Hive which must be fixed by changing the requirement for a varchar(4000) in the Primary Key column.

References:
http://dev.mysql.com/doc/refman/5.7/en/innodb-restrictions.html

Workaround:
               Include the following beeline switch: --hiveconf hive.stats.autogather=false    …which sidesteps this particular problem stream in the code.

Q. Can anyone guide me how to log a bug for Hive?


From: San Luo [mailto:luohui@damai.cn]
Sent: 01 December 2015 10:03
To: user@hive.apache.org<ma...@hive.apache.org>
Subject: 答复: UPDATE RE: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Specified key was too long; max key length is 767 bytes (beeline - hive server 2)

Thanks for response,

To Timothy
        I tried to set this global setting ,however message comes with “ERROR 1193 (HY000): Unknown system variable 'innodb_large_prefix' ”.

Here are mysql version and so on:
[root@master hive]# rpm -qa | grep mysql
mysql-5.1.73-5.el6_6.x86_64
mysql-libs-5.1.73-5.el6_6.x86_64
mysql-server-5.1.73-5.el6_6.x86_64

I didn’t start hive thrift server nor hiveserver2. Just simplely try to start hive and do some easy test with mysql metastore management, but got stucked by this character problem.

To Daniel:
         Yes, I tried to update mysql driver connector jar in $hive_home/lib, from mysql-connector-java-5.1.21-bin.jar to mysql-connector-java-5.1.37-bin.jar. unfortunately this does not bring improvement.


Thank you all again for sharing your time on my problem.


San.Luo

发件人: Daniel Haviv [mailto:daniel.haviv@veracity-group.com]
发送时间: 2015年12月1日 2:38
收件人: user@hive.apache.org<ma...@hive.apache.org>
主题: Re: UPDATE RE: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Specified key was too long; max key length is 767 bytes (beeline - hive server 2)

Hi,
I remember encountering a similar problem that was caused by an old mysql client driver.
You can try and upgrade your mysql connector.

Daniel

On Mon, Nov 30, 2015 at 8:12 PM, Timothy Garza <ti...@collinsongroup.com>> wrote:
We’ve been playing with the MySQL Global Settings: (Hive metastore)

mysql> set global innodb_large_prefix = ON;  (<-- this was set to OFF previously)

…and now the ERROR is thus:
Specified key was too long; max key length is 3072 bytes

So it’s still ‘failing’ (but the HDFS operation itself succeeds). This must be the problem area as the message has changed from:

Specified key was too long; max key length is 767 bytes
to
Specified key was too long; max key length is 3072 bytes

…simply by altering the MySQL Global settings. So is hiveserver2 trying to use a key larger than MySQL supports (v5.5.2, file format Antelope)?

NB. This only occurs when executing beeline INSERT, not CREATE nor SELECT statements on a Hive Table (in this case a Sequence File).

My colleague thinks this is SSL related (because of the use of the word ‘key’ in the error), is HiveServer2 connecting to the Metastore using SSL?

________________________________
Weirdly I’m experiencing exactly the same issue when trying to populate a Hive Table using INSERT OVERWRITE TABLE. We’re recently upgraded from Hive 0.13 to 1.2.1. NB. The Hive Table populates but the map-reduce returns an error code. I have run the hive Schema Tool:   schematool -dbType mysql -upgradeSchemaFrom 0.13

The only table I can see with 767 size column is “PART_COL_STATS” – implemented in one of the metastore upgrade scripts. Column Name: PARTITION_NAME         | varchar(767). <-- I changed this column to varchar(1000) but get the same message afterwards:

ERROR jdbc.JDBCStatsPublisher: Error during JDBC initialization.
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Specified key was too long; max key length is 767 bytes
        at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
        at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
        at java.lang.reflect.Constructor.newInstance(Constructor.java:526)
        at com.mysql.jdbc.Util.handleNewInstance(Util.java:409)
        at com.mysql.jdbc.Util.getInstance(Util.java:384)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1052)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4232)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4164)
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2615)
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2776)
        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2832)
        at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1755)
        at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1679)
        at org.apache.hadoop.hive.ql.stats.jdbc.JDBCStatsPublisher.init(JDBCStatsPublisher.java:292)
        at org.apache.hadoop.hive.ql.exec.mr.ExecDriver.execute(ExecDriver.java:411)
        at org.apache.hadoop.hive.ql.exec.mr.MapRedTask.execute(MapRedTask.java:137)
        at org.apache.hadoop.hive.ql.exec.Task.executeTask(Task.java:160)
        at org.apache.hadoop.hive.ql.exec.TaskRunner.runSequential(TaskRunner.java:88)
        at org.apache.hadoop.hive.ql.Driver.launchTask(Driver.java:1653)
        at org.apache.hadoop.hive.ql.Driver.execute(Driver.java:1412)
        at org.apache.hadoop.hive.ql.Driver.runInternal(Driver.java:1195)
        at org.apache.hadoop.hive.ql.Driver.run(Driver.java:1059)
        at org.apache.hadoop.hive.ql.Driver.run(Driver.java:1054)
        at org.apache.hive.service.cli.operation.SQLOperation.runQuery(SQLOperation.java:154)
        at org.apache.hive.service.cli.operation.SQLOperation.access$100(SQLOperation.java:71)
        at org.apache.hive.service.cli.operation.SQLOperation$1$1.run(SQLOperation.java:206)
        at java.security.AccessController.doPrivileged(Native Method)
        at javax.security.auth.Subject.doAs(Subject.java:415)
        at org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1548)
        at org.apache.hive.service.cli.operation.SQLOperation$1.run(SQLOperation.java:218)
        at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:471)
        at java.util.concurrent.FutureTask.run(FutureTask.java:262)
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
        at java.lang.Thread.run(Thread.java:745)



From: San Luo [mailto:luohui@damai.cn]
Sent: 30 November 2015 10:06
To: user@hive.apache.org<ma...@hive.apache.org>
Subject: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Specified key was too long; max key length is 767 bytes


hi guys,

     when I am trying to use mysql to manager hive metastore,I got a problem like below:

[root@master hive]# hive

Logging initialized using configuration in jar:file:/usr/lib/hive/lib/hive-common-1.1.1.jar!/hive-log4j.properties
SLF4J: Class path contains multiple SLF4J bindings.
SLF4J: Found binding in [jar:file:/usr/lib/hadoop/share/hadoop/common/lib/slf4j-log4j12-1.7.5.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: Found binding in [jar:file:/usr/lib/hive/lib/hive-jdbc-1.1.1-standalone.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation.
SLF4J: Actual binding is of type [org.slf4j.impl.Log4jLoggerFactory]
hive> show tables;
OK
invites
pokes
test
Time taken: 2.573 seconds, Fetched: 3 row(s)
hive> LOAD DATA LOCAL INPATH './examples/files/kv2.txt' OVERWRITE INTO TABLE invites PARTITION (ds='2008-08-15');
Loading data to table default.invites partition (ds=2008-08-15)
Interrupting... Be patient, this might take some time.
Press Ctrl+C again to kill JVM
Failed with exception MetaException(message:javax.jdo.JDODataStoreException: An exception was thrown while adding/validating class(es) : Specified key was too long; max key length is 767 bytes
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Specified key was too long; max key length is 767 bytes
        at sun.reflect.GeneratedConstructorAccessor32.newInstance(Unknown Source)
        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
        at java.lang.reflect.Constructor.newInstance(Constructor.java:422)
        at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
        at com.mysql.jdbc.Util.getInstance(Util.java:386)
…….


I tried hive 1.2.1 and hive 1.1.1, neither works fine. I also tried to modify mysql charcter set of hive database to latin1 and utf8 but it doesn’t work.

Any idea will be appreciated.



The Collinson Group Limited; Registered number: 2577557, Registered in England & Wales; Registered Office: Cutlers Exchange, 123 Houndsditch, London, EC3A 7BU.


This e-mail may contain privileged and confidential information and/or copyright material and is intended for the use of the addressee only. If you receive this e-mail by mistake please advise the sender immediately by using the reply facility in your e-mail software and delete this e-mail from your computer system. You may not deliver, copy or disclose its contents to anyone else. Any unauthorised use may be unlawful. Any views expressed in this e-mail are those of the individual sender and may not necessarily reflect the views of The Collinson Group Ltd and/or its subsidiaries or any other associated company (collectively “Collinson Group”).

As communications via the Internet are not secure Collinson Group cannot accept any liability if this e-mail is accessed by third parties during the course of transmission or is modified or amended in any way following despatch. Collinson Group cannot guarantee that any attachment to this email does not contain a virus, therefore it is strongly recommended that you carry out your own virus check before opening any attachment, as we cannot accept liability for any damage sustained as a result of software virus infection. Senders of messages shall be taken to consent to the monitoring and recording of e-mails addressed to members of the Company.


RE: UPDATE RE: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Specified key was too long; max key length is 767 bytes (beeline - hive server 2)

Posted by Timothy Garza <ti...@collinsongroup.com>.
Now leaning towards this being a bug in Hive v1.2.1. for the MySQL metastore classes…Let me show you why:

I’m running the following simple Hive QL:

INSERT OVERWRITE TABLE <tablename> SELECT <column1>,<column2>… FROM <otherTable>;

[HiveServer2-Background-Pool: Thread-20]: ERROR jdbc.JDBCStatsPublisher: Error during JDBC initialization.
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Specified key was too long; max key length is 767 bytes

…that MySQL error refers to the Index Key ‘prefix’ being too long (blah blah). So looking in the MySQL Log, I see the following entry (note the ID column):

6 Query     CREATE TABLE /*  */ PARTITION_STATS_V2 (TS TIMESTAMP DEFAULT CURRENT_TIMESTAMP, ID VARCHAR(4000) PRIMARY KEY , ROW_COUNT BIGINT , RAW_DATA_SIZE BIGINT )

…that DDL for MySQL is generated by Hive and it is this which is failing!!! If we create the table ‘manually’ (using the DDL captured from the MySQL Log) but instead using a Key-size lower than 767, eg. varchar(255):

mysql> CREATE TABLE PARTITION_STATS_V2 (TS TIMESTAMP DEFAULT CURRENT_TIMESTAMP, ID VARCHAR(255) PRIMARY KEY , ROW_COUNT BIGINT , RAW_DATA_SIZE BIGINT );

This succeeds in creating the table in the metastore, but then when I run the Map-Reduce jobs again I can see in the MySQL Log that Hive detects the ‘anomaly’ ID column ie. varchar(255) and tries to correct it by running some more DDL:

ALTER TABLE PARTITION_STATS_V2 ALTER COLUMN ID VARCHAR(4000);   <-- it doesn’t like varchar(255)!!!

Even this alteration by Hive fails due to Syntax error:

WARN jdbc.JDBCStatsPublisher: Failed to update ID (size 255)
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'VARCHAR(4000)' at line 1

…because you cannot have varchar(4000) in a Primary Key column (due to MySQL index key prefix limitations).

Conclusion:
               This can only be a bug in Hive which must be fixed by changing the requirement for a varchar(4000) in the Primary Key column.

References:
http://dev.mysql.com/doc/refman/5.7/en/innodb-restrictions.html

Workaround:
               Include the following beeline switch: --hiveconf hive.stats.autogather=false    …which sidesteps this particular problem stream in the code.

Q. Can anyone guide me how to log a bug for Hive?


From: San Luo [mailto:luohui@damai.cn]
Sent: 01 December 2015 10:03
To: user@hive.apache.org
Subject: 答复: UPDATE RE: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Specified key was too long; max key length is 767 bytes (beeline - hive server 2)

Thanks for response,

To Timothy
        I tried to set this global setting ,however message comes with “ERROR 1193 (HY000): Unknown system variable 'innodb_large_prefix' ”.

Here are mysql version and so on:
[root@master hive]# rpm -qa | grep mysql
mysql-5.1.73-5.el6_6.x86_64
mysql-libs-5.1.73-5.el6_6.x86_64
mysql-server-5.1.73-5.el6_6.x86_64

I didn’t start hive thrift server nor hiveserver2. Just simplely try to start hive and do some easy test with mysql metastore management, but got stucked by this character problem.

To Daniel:
         Yes, I tried to update mysql driver connector jar in $hive_home/lib, from mysql-connector-java-5.1.21-bin.jar to mysql-connector-java-5.1.37-bin.jar. unfortunately this does not bring improvement.


Thank you all again for sharing your time on my problem.


San.Luo

发件人: Daniel Haviv [mailto:daniel.haviv@veracity-group.com]
发送时间: 2015年12月1日 2:38
收件人: user@hive.apache.org<ma...@hive.apache.org>
主题: Re: UPDATE RE: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Specified key was too long; max key length is 767 bytes (beeline - hive server 2)

Hi,
I remember encountering a similar problem that was caused by an old mysql client driver.
You can try and upgrade your mysql connector.

Daniel

On Mon, Nov 30, 2015 at 8:12 PM, Timothy Garza <ti...@collinsongroup.com>> wrote:
We’ve been playing with the MySQL Global Settings: (Hive metastore)

mysql> set global innodb_large_prefix = ON;  (<-- this was set to OFF previously)

…and now the ERROR is thus:
Specified key was too long; max key length is 3072 bytes

So it’s still ‘failing’ (but the HDFS operation itself succeeds). This must be the problem area as the message has changed from:

Specified key was too long; max key length is 767 bytes
to
Specified key was too long; max key length is 3072 bytes

…simply by altering the MySQL Global settings. So is hiveserver2 trying to use a key larger than MySQL supports (v5.5.2, file format Antelope)?

NB. This only occurs when executing beeline INSERT, not CREATE nor SELECT statements on a Hive Table (in this case a Sequence File).

My colleague thinks this is SSL related (because of the use of the word ‘key’ in the error), is HiveServer2 connecting to the Metastore using SSL?

________________________________
Weirdly I’m experiencing exactly the same issue when trying to populate a Hive Table using INSERT OVERWRITE TABLE. We’re recently upgraded from Hive 0.13 to 1.2.1. NB. The Hive Table populates but the map-reduce returns an error code. I have run the hive Schema Tool:   schematool -dbType mysql -upgradeSchemaFrom 0.13

The only table I can see with 767 size column is “PART_COL_STATS” – implemented in one of the metastore upgrade scripts. Column Name: PARTITION_NAME         | varchar(767). <-- I changed this column to varchar(1000) but get the same message afterwards:

ERROR jdbc.JDBCStatsPublisher: Error during JDBC initialization.
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Specified key was too long; max key length is 767 bytes
        at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
        at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
        at java.lang.reflect.Constructor.newInstance(Constructor.java:526)
        at com.mysql.jdbc.Util.handleNewInstance(Util.java:409)
        at com.mysql.jdbc.Util.getInstance(Util.java:384)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1052)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4232)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4164)
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2615)
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2776)
        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2832)
        at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1755)
        at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1679)
        at org.apache.hadoop.hive.ql.stats.jdbc.JDBCStatsPublisher.init(JDBCStatsPublisher.java:292)
        at org.apache.hadoop.hive.ql.exec.mr.ExecDriver.execute(ExecDriver.java:411)
        at org.apache.hadoop.hive.ql.exec.mr.MapRedTask.execute(MapRedTask.java:137)
        at org.apache.hadoop.hive.ql.exec.Task.executeTask(Task.java:160)
        at org.apache.hadoop.hive.ql.exec.TaskRunner.runSequential(TaskRunner.java:88)
        at org.apache.hadoop.hive.ql.Driver.launchTask(Driver.java:1653)
        at org.apache.hadoop.hive.ql.Driver.execute(Driver.java:1412)
        at org.apache.hadoop.hive.ql.Driver.runInternal(Driver.java:1195)
        at org.apache.hadoop.hive.ql.Driver.run(Driver.java:1059)
        at org.apache.hadoop.hive.ql.Driver.run(Driver.java:1054)
        at org.apache.hive.service.cli.operation.SQLOperation.runQuery(SQLOperation.java:154)
        at org.apache.hive.service.cli.operation.SQLOperation.access$100(SQLOperation.java:71)
        at org.apache.hive.service.cli.operation.SQLOperation$1$1.run(SQLOperation.java:206)
        at java.security.AccessController.doPrivileged(Native Method)
        at javax.security.auth.Subject.doAs(Subject.java:415)
        at org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1548)
        at org.apache.hive.service.cli.operation.SQLOperation$1.run(SQLOperation.java:218)
        at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:471)
        at java.util.concurrent.FutureTask.run(FutureTask.java:262)
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
        at java.lang.Thread.run(Thread.java:745)



From: San Luo [mailto:luohui@damai.cn]
Sent: 30 November 2015 10:06
To: user@hive.apache.org<ma...@hive.apache.org>
Subject: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Specified key was too long; max key length is 767 bytes


hi guys,

     when I am trying to use mysql to manager hive metastore,I got a problem like below:

[root@master hive]# hive

Logging initialized using configuration in jar:file:/usr/lib/hive/lib/hive-common-1.1.1.jar!/hive-log4j.properties
SLF4J: Class path contains multiple SLF4J bindings.
SLF4J: Found binding in [jar:file:/usr/lib/hadoop/share/hadoop/common/lib/slf4j-log4j12-1.7.5.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: Found binding in [jar:file:/usr/lib/hive/lib/hive-jdbc-1.1.1-standalone.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation.
SLF4J: Actual binding is of type [org.slf4j.impl.Log4jLoggerFactory]
hive> show tables;
OK
invites
pokes
test
Time taken: 2.573 seconds, Fetched: 3 row(s)
hive> LOAD DATA LOCAL INPATH './examples/files/kv2.txt' OVERWRITE INTO TABLE invites PARTITION (ds='2008-08-15');
Loading data to table default.invites partition (ds=2008-08-15)
Interrupting... Be patient, this might take some time.
Press Ctrl+C again to kill JVM
Failed with exception MetaException(message:javax.jdo.JDODataStoreException: An exception was thrown while adding/validating class(es) : Specified key was too long; max key length is 767 bytes
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Specified key was too long; max key length is 767 bytes
        at sun.reflect.GeneratedConstructorAccessor32.newInstance(Unknown Source)
        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
        at java.lang.reflect.Constructor.newInstance(Constructor.java:422)
        at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
        at com.mysql.jdbc.Util.getInstance(Util.java:386)
…….


I tried hive 1.2.1 and hive 1.1.1, neither works fine. I also tried to modify mysql charcter set of hive database to latin1 and utf8 but it doesn’t work.

Any idea will be appreciated.



The Collinson Group Limited; Registered number: 2577557, Registered in England & Wales; Registered Office: Cutlers Exchange, 123 Houndsditch, London, EC3A 7BU.


This e-mail may contain privileged and confidential information and/or copyright material and is intended for the use of the addressee only. If you receive this e-mail by mistake please advise the sender immediately by using the reply facility in your e-mail software and delete this e-mail from your computer system. You may not deliver, copy or disclose its contents to anyone else. Any unauthorised use may be unlawful. Any views expressed in this e-mail are those of the individual sender and may not necessarily reflect the views of The Collinson Group Ltd and/or its subsidiaries or any other associated company (collectively “Collinson Group”).

As communications via the Internet are not secure Collinson Group cannot accept any liability if this e-mail is accessed by third parties during the course of transmission or is modified or amended in any way following despatch. Collinson Group cannot guarantee that any attachment to this email does not contain a virus, therefore it is strongly recommended that you carry out your own virus check before opening any attachment, as we cannot accept liability for any damage sustained as a result of software virus infection. Senders of messages shall be taken to consent to the monitoring and recording of e-mails addressed to members of the Company.


答复: UPDATE RE: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Specified key was too long; max key length is 767 bytes (beeline - hive server 2)

Posted by San Luo <lu...@damai.cn>.
Thanks for response,

 

To Timothy

        I tried to set this global setting ,however message comes with “ERROR 1193 (HY000): Unknown system variable 'innodb_large_prefix' ”.

 

Here are mysql version and so on:

[root@master hive]# rpm -qa | grep mysql

mysql-5.1.73-5.el6_6.x86_64

mysql-libs-5.1.73-5.el6_6.x86_64

mysql-server-5.1.73-5.el6_6.x86_64

 

I didn’t start hive thrift server nor hiveserver2. Just simplely try to start hive and do some easy test with mysql metastore management, but got stucked by this character problem.

 

To Daniel:

         Yes, I tried to update mysql driver connector jar in $hive_home/lib, from mysql-connector-java-5.1.21-bin.jar to mysql-connector-java-5.1.37-bin.jar. unfortunately this does not bring improvement.

 

 

Thank you all again for sharing your time on my problem.

 

 

San.Luo

 

发件人: Daniel Haviv [mailto:daniel.haviv@veracity-group.com] 
发送时间: 2015年12月1日 2:38
收件人: user@hive.apache.org
主题: Re: UPDATE RE: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Specified key was too long; max key length is 767 bytes (beeline - hive server 2)

 

Hi,

I remember encountering a similar problem that was caused by an old mysql client driver.

You can try and upgrade your mysql connector.

 

Daniel

 

On Mon, Nov 30, 2015 at 8:12 PM, Timothy Garza <ti...@collinsongroup.com> wrote:

We’ve been playing with the MySQL Global Settings: (Hive metastore)

 

mysql> set global innodb_large_prefix = ON;  (ß this was set to OFF previously)

 

…and now the ERROR is thus:

Specified key was too long; max key length is 3072 bytes

 

So it’s still ‘failing’ (but the HDFS operation itself succeeds). This must be the problem area as the message has changed from:

 

Specified key was too long; max key length is 767 bytes

to

Specified key was too long; max key length is 3072 bytes

 

…simply by altering the MySQL Global settings. So is hiveserver2 trying to use a key larger than MySQL supports (v5.5.2, file format Antelope)?

 

NB. This only occurs when executing beeline INSERT, not CREATE nor SELECT statements on a Hive Table (in this case a Sequence File). 

 

My colleague thinks this is SSL related (because of the use of the word ‘key’ in the error), is HiveServer2 connecting to the Metastore using SSL?

 

  _____  

Weirdly I’m experiencing exactly the same issue when trying to populate a Hive Table using INSERT OVERWRITE TABLE. We’re recently upgraded from Hive 0.13 to 1.2.1. NB. The Hive Table populates but the map-reduce returns an error code. I have run the hive Schema Tool:   schematool -dbType mysql -upgradeSchemaFrom 0.13

 

The only table I can see with 767 size column is “PART_COL_STATS” – implemented in one of the metastore upgrade scripts. Column Name: PARTITION_NAME         | varchar(767). ß I changed this column to varchar(1000) but get the same message afterwards:

 

ERROR jdbc.JDBCStatsPublisher: Error during JDBC initialization.

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Specified key was too long; max key length is 767 bytes

        at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)

        at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)

        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)

        at java.lang.reflect.Constructor.newInstance(Constructor.java:526)

        at com.mysql.jdbc.Util.handleNewInstance(Util.java:409)

        at com.mysql.jdbc.Util.getInstance(Util.java:384)

        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1052)

        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4232)

        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4164)

        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2615)

        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2776)

        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2832)

        at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1755)

        at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1679)

        at org.apache.hadoop.hive.ql.stats.jdbc.JDBCStatsPublisher.init(JDBCStatsPublisher.java:292)

        at org.apache.hadoop.hive.ql.exec.mr.ExecDriver.execute(ExecDriver.java:411)

        at org.apache.hadoop.hive.ql.exec.mr.MapRedTask.execute(MapRedTask.java:137)

        at org.apache.hadoop.hive.ql.exec.Task.executeTask(Task.java:160)

        at org.apache.hadoop.hive.ql.exec.TaskRunner.runSequential(TaskRunner.java:88)

        at org.apache.hadoop.hive.ql.Driver.launchTask(Driver.java:1653)

        at org.apache.hadoop.hive.ql.Driver.execute(Driver.java:1412)

        at org.apache.hadoop.hive.ql.Driver.runInternal(Driver.java:1195)

        at org.apache.hadoop.hive.ql.Driver.run(Driver.java:1059)

        at org.apache.hadoop.hive.ql.Driver.run(Driver.java:1054)

        at org.apache.hive.service.cli.operation.SQLOperation.runQuery(SQLOperation.java:154)

        at org.apache.hive.service.cli.operation.SQLOperation.access$100(SQLOperation.java:71)

        at org.apache.hive.service.cli.operation.SQLOperation$1$1.run(SQLOperation.java:206)

        at java.security.AccessController.doPrivileged(Native Method)

        at javax.security.auth.Subject.doAs(Subject.java:415)

        at org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1548)

        at org.apache.hive.service.cli.operation.SQLOperation$1.run(SQLOperation.java:218)

        at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:471)

        at java.util.concurrent.FutureTask.run(FutureTask.java:262)

        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)

        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)

        at java.lang.Thread.run(Thread.java:745)

 

 

 

From: San Luo [mailto:luohui@damai.cn] 
Sent: 30 November 2015 10:06
To: user@hive.apache.org
Subject: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Specified key was too long; max key length is 767 bytes

 

hi guys,

     when I am trying to use mysql to manager hive metastore,I got a problem like below:

 

[root@master hive]# hive

 

Logging initialized using configuration in jar:file:/usr/lib/hive/lib/hive-common-1.1.1.jar!/hive-log4j.properties

SLF4J: Class path contains multiple SLF4J bindings.

SLF4J: Found binding in [jar:file:/usr/lib/hadoop/share/hadoop/common/lib/slf4j-log4j12-1.7.5.jar!/org/slf4j/impl/StaticLoggerBinder.class]

SLF4J: Found binding in [jar:file:/usr/lib/hive/lib/hive-jdbc-1.1.1-standalone.jar!/org/slf4j/impl/StaticLoggerBinder.class]

SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation.

SLF4J: Actual binding is of type [org.slf4j.impl.Log4jLoggerFactory]

hive> show tables;

OK

invites

pokes

test

Time taken: 2.573 seconds, Fetched: 3 row(s)

hive> LOAD DATA LOCAL INPATH './examples/files/kv2.txt' OVERWRITE INTO TABLE invites PARTITION (ds='2008-08-15');

Loading data to table default.invites partition (ds=2008-08-15)

Interrupting... Be patient, this might take some time.

Press Ctrl+C again to kill JVM

Failed with exception MetaException(message:javax.jdo.JDODataStoreException: An exception was thrown while adding/validating class(es) : Specified key was too long; max key length is 767 bytes

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Specified key was too long; max key length is 767 bytes

        at sun.reflect.GeneratedConstructorAccessor32.newInstance(Unknown Source)

        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)

        at java.lang.reflect.Constructor.newInstance(Constructor.java:422)

        at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)

        at com.mysql.jdbc.Util.getInstance(Util.java:386)

…….

 

 

I tried hive 1.2.1 and hive 1.1.1, neither works fine. I also tried to modify mysql charcter set of hive database to latin1 and utf8 but it doesn’t work.

 

Any idea will be appreciated.

 



The Collinson Group Limited; Registered number: 2577557, Registered in England & Wales; Registered Office: Cutlers Exchange, 123 Houndsditch, London, EC3A 7BU.


This e-mail may contain privileged and confidential information and/or copyright material and is intended for the use of the addressee only. If you receive this e-mail by mistake please advise the sender immediately by using the reply facility in your e-mail software and delete this e-mail from your computer system. You may not deliver, copy or disclose its contents to anyone else. Any unauthorised use may be unlawful. Any views expressed in this e-mail are those of the individual sender and may not necessarily reflect the views of The Collinson Group Ltd and/or its subsidiaries or any other associated company (collectively “Collinson Group”).

As communications via the Internet are not secure Collinson Group cannot accept any liability if this e-mail is accessed by third parties during the course of transmission or is modified or amended in any way following despatch. Collinson Group cannot guarantee that any attachment to this email does not contain a virus, therefore it is strongly recommended that you carry out your own virus check before opening any attachment, as we cannot accept liability for any damage sustained as a result of software virus infection. Senders of messages shall be taken to consent to the monitoring and recording of e-mails addressed to members of the Company.

 


Re: UPDATE RE: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Specified key was too long; max key length is 767 bytes (beeline - hive server 2)

Posted by Daniel Haviv <da...@veracity-group.com>.
Hi,
I remember encountering a similar problem that was caused by an old mysql
client driver.
You can try and upgrade your mysql connector.

Daniel

On Mon, Nov 30, 2015 at 8:12 PM, Timothy Garza <
timothy.garza@collinsongroup.com> wrote:

> We’ve been playing with the MySQL Global Settings: (Hive metastore)
>
>
>
> *mysql*> set global innodb_large_prefix = ON;  (ß this was set to OFF
> previously)
>
>
>
> …and now the ERROR is thus:
>
> Specified key was too long; max key length is 3072 bytes
>
>
>
> So it’s still ‘failing’ (but the HDFS operation itself succeeds). This
> must be the problem area as the message has changed from:
>
>
>
> Specified key was too long; max key length is 767 bytes
>
> to
>
> Specified key was too long; max key length is 3072 bytes
>
>
>
> …simply by altering the MySQL Global settings. So is hiveserver2 trying to
> use a key larger than MySQL supports (v5.5.2, file format Antelope)?
>
>
>
> NB. This only occurs when executing beeline INSERT, not CREATE nor SELECT
> statements on a Hive Table (in this case a Sequence File).
>
>
>
> My colleague thinks this is SSL related (because of the use of the word
> ‘key’ in the error), is HiveServer2 connecting to the Metastore using SSL?
>
>
> ------------------------------
>
> Weirdly I’m experiencing exactly the same issue when trying to populate a
> Hive Table using INSERT OVERWRITE TABLE. We’re recently upgraded from Hive
> 0.13 to 1.2.1. NB. The Hive Table populates but the map-reduce returns an
> error code. I have run the hive Schema Tool:   schematool -dbType mysql
> -upgradeSchemaFrom 0.13
>
>
>
> The only table I can see with 767 size column is “PART_COL_STATS” –
> implemented in one of the metastore upgrade scripts. Column Name: PARTITION_NAME
> | varchar(767). ß I changed this column to varchar(1000) but get the same
> message afterwards:
>
>
>
> *ERROR jdbc.JDBCStatsPublisher:* Error during JDBC initialization.
>
> com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Specified key
> was too long; max key length is 767 bytes
>
>         at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native
> Method)
>
>         at
> sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
>
>         at
> sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
>
>         at java.lang.reflect.Constructor.newInstance(Constructor.java:526)
>
>         at com.mysql.jdbc.Util.handleNewInstance(Util.java:409)
>
>         at com.mysql.jdbc.Util.getInstance(Util.java:384)
>
>         at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1052)
>
>         at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4232)
>
>         at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4164)
>
>         at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2615)
>
>         at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2776)
>
>         at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2832)
>
>         at
> com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1755)
>
>         at
> com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1679)
>
>         at
> org.apache.hadoop.hive.ql.stats.jdbc.JDBCStatsPublisher.init(JDBCStatsPublisher.java:292)
>
>         at
> org.apache.hadoop.hive.ql.exec.mr.ExecDriver.execute(ExecDriver.java:411)
>
>         at
> org.apache.hadoop.hive.ql.exec.mr.MapRedTask.execute(MapRedTask.java:137)
>
>         at org.apache.hadoop.hive.ql.exec.Task.executeTask(Task.java:160)
>
>         at
> org.apache.hadoop.hive.ql.exec.TaskRunner.runSequential(TaskRunner.java:88)
>
>         at org.apache.hadoop.hive.ql.Driver.launchTask(Driver.java:1653)
>
>         at org.apache.hadoop.hive.ql.Driver.execute(Driver.java:1412)
>
>         at org.apache.hadoop.hive.ql.Driver.runInternal(Driver.java:1195)
>
>         at org.apache.hadoop.hive.ql.Driver.run(Driver.java:1059)
>
>         at org.apache.hadoop.hive.ql.Driver.run(Driver.java:1054)
>
>         at
> org.apache.hive.service.cli.operation.SQLOperation.runQuery(SQLOperation.java:154)
>
>         at
> org.apache.hive.service.cli.operation.SQLOperation.access$100(SQLOperation.java:71)
>
>         at
> org.apache.hive.service.cli.operation.SQLOperation$1$1.run(SQLOperation.java:206)
>
>         at java.security.AccessController.doPrivileged(Native Method)
>
>         at javax.security.auth.Subject.doAs(Subject.java:415)
>
>         at
> org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1548)
>
>         at
> org.apache.hive.service.cli.operation.SQLOperation$1.run(SQLOperation.java:218)
>
>         at
> java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:471)
>
>         at java.util.concurrent.FutureTask.run(FutureTask.java:262)
>
>         at
> java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
>
>         at
> java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
>
>         at java.lang.Thread.run(Thread.java:745)
>
>
>
>
>
>
>
> *From:* San Luo [mailto:luohui@damai.cn <lu...@damai.cn>]
> *Sent:* 30 November 2015 10:06
> *To:* user@hive.apache.org
> *Subject:* com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException:
> Specified key was too long; max key length is 767 bytes
>
>
>
> hi guys,
>
>      when I am trying to use mysql to manager hive metastore,I got a
> problem like below:
>
>
>
> [root@master hive]# hive
>
>
>
> Logging initialized using configuration in
> jar:file:/usr/lib/hive/lib/hive-common-1.1.1.jar!/hive-log4j.properties
>
> SLF4J: Class path contains multiple SLF4J bindings.
>
> SLF4J: Found binding in
> [jar:file:/usr/lib/hadoop/share/hadoop/common/lib/slf4j-log4j12-1.7.5.jar!/org/slf4j/impl/StaticLoggerBinder.class]
>
> SLF4J: Found binding in
> [jar:file:/usr/lib/hive/lib/hive-jdbc-1.1.1-standalone.jar!/org/slf4j/impl/StaticLoggerBinder.class]
>
> SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an
> explanation.
>
> SLF4J: Actual binding is of type [org.slf4j.impl.Log4jLoggerFactory]
>
> hive> show tables;
>
> OK
>
> invites
>
> pokes
>
> test
>
> Time taken: 2.573 seconds, Fetched: 3 row(s)
>
> hive> LOAD DATA LOCAL INPATH './examples/files/kv2.txt' OVERWRITE INTO
> TABLE invites PARTITION (ds='2008-08-15');
>
> Loading data to table default.invites partition (ds=2008-08-15)
>
> Interrupting... Be patient, this might take some time.
>
> Press Ctrl+C again to kill JVM
>
> Failed with exception
> MetaException(message:javax.jdo.JDODataStoreException: An exception was
> thrown while adding/validating class(es) : Specified key was too long; max
> key length is 767 bytes
>
> com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Specified key
> was too long; max key length is 767 bytes
>
>         at sun.reflect.GeneratedConstructorAccessor32.newInstance(Unknown
> Source)
>
>         at
> sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
>
>         at java.lang.reflect.Constructor.newInstance(Constructor.java:422)
>
>         at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
>
>         at com.mysql.jdbc.Util.getInstance(Util.java:386)
>
> …….
>
>
>
>
>
> I tried hive 1.2.1 and hive 1.1.1, neither works fine. I also tried to
> modify mysql charcter set of hive database to latin1 and utf8 but it
> doesn’t work.
>
>
>
> Any idea will be appreciated.
>
>
>
>
>
> The Collinson Group Limited; Registered number: 2577557, Registered in
> England & Wales; Registered Office: Cutlers Exchange, 123 Houndsditch,
> London, EC3A 7BU.
>
>
> This e-mail may contain privileged and confidential information and/or
> copyright material and is intended for the use of the addressee only. If
> you receive this e-mail by mistake please advise the sender immediately by
> using the reply facility in your e-mail software and delete this e-mail
> from your computer system. You may not deliver, copy or disclose its
> contents to anyone else. Any unauthorised use may be unlawful. Any views
> expressed in this e-mail are those of the individual sender and may not
> necessarily reflect the views of The Collinson Group Ltd and/or its
> subsidiaries or any other associated company (collectively “Collinson
> Group”).
>
> As communications via the Internet are not secure Collinson Group cannot
> accept any liability if this e-mail is accessed by third parties during the
> course of transmission or is modified or amended in any way following
> despatch. Collinson Group cannot guarantee that any attachment to this
> email does not contain a virus, therefore it is strongly recommended that
> you carry out your own virus check before opening any attachment, as we
> cannot accept liability for any damage sustained as a result of software
> virus infection. Senders of messages shall be taken to consent to the
> monitoring and recording of e-mails addressed to members of the Company.
>

RE: UPDATE RE: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Specified key was too long; max key length is 767 bytes (beeline - hive server 2)

Posted by Mich Talebzadeh <mi...@peridale.co.uk>.
Hi,

 

I don’t this issue with hive server connecting to our Oracle metastore.

 

Have you checked the errorlog of your MySQL?

 

HTH,

 

Mich Talebzadeh

 

Sybase ASE 15 Gold Medal Award 2008

A Winning Strategy: Running the most Critical Financial Data on ASE 15

http://login.sybase.com/files/Product_Overviews/ASE-Winning-Strategy-091908.pdf

Author of the books "A Practitioner’s Guide to Upgrading to Sybase ASE 15", ISBN 978-0-9563693-0-7. 

co-author "Sybase Transact SQL Guidelines Best Practices", ISBN 978-0-9759693-0-4

Publications due shortly:

Complex Event Processing in Heterogeneous Environments, ISBN: 978-0-9563693-3-8

Oracle and Sybase, Concepts and Contrasts, ISBN: 978-0-9563693-1-4, volume one out shortly

 

http://talebzadehmich.wordpress.com <http://talebzadehmich.wordpress.com/> 

 

NOTE: The information in this email is proprietary and confidential. This message is for the designated recipient only, if you are not the intended recipient, you should destroy it immediately. Any information in this message shall not be understood as given or endorsed by Peridale Technology Ltd, its subsidiaries or their employees, unless expressly so stated. It is the responsibility of the recipient to ensure that this email is virus free, therefore neither Peridale Ltd, its subsidiaries nor their employees accept any responsibility.

 

From: Timothy Garza [mailto:timothy.garza@collinsongroup.com] 
Sent: 30 November 2015 18:12
To: user@hive.apache.org
Subject: UPDATE RE: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Specified key was too long; max key length is 767 bytes (beeline - hive server 2)

 

We’ve been playing with the MySQL Global Settings: (Hive metastore)

 

mysql> set global innodb_large_prefix = ON;  (<-- this was set to OFF previously)

 

…and now the ERROR is thus:

Specified key was too long; max key length is 3072 bytes

 

So it’s still ‘failing’ (but the HDFS operation itself succeeds). This must be the problem area as the message has changed from:

 

Specified key was too long; max key length is 767 bytes

to

Specified key was too long; max key length is 3072 bytes

 

…simply by altering the MySQL Global settings. So is hiveserver2 trying to use a key larger than MySQL supports (v5.5.2, file format Antelope)?

 

NB. This only occurs when executing beeline INSERT, not CREATE nor SELECT statements on a Hive Table (in this case a Sequence File). 

 

My colleague thinks this is SSL related (because of the use of the word ‘key’ in the error), is HiveServer2 connecting to the Metastore using SSL?

 

  _____  

Weirdly I’m experiencing exactly the same issue when trying to populate a Hive Table using INSERT OVERWRITE TABLE. We’re recently upgraded from Hive 0.13 to 1.2.1. NB. The Hive Table populates but the map-reduce returns an error code. I have run the hive Schema Tool:   schematool -dbType mysql -upgradeSchemaFrom 0.13

 

The only table I can see with 767 size column is “PART_COL_STATS” – implemented in one of the metastore upgrade scripts. Column Name: PARTITION_NAME         | varchar(767). <-- I changed this column to varchar(1000) but get the same message afterwards:

 

ERROR jdbc.JDBCStatsPublisher: Error during JDBC initialization.

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Specified key was too long; max key length is 767 bytes

        at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)

        at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)

        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)

        at java.lang.reflect.Constructor.newInstance(Constructor.java:526)

        at com.mysql.jdbc.Util.handleNewInstance(Util.java:409)

        at com.mysql.jdbc.Util.getInstance(Util.java:384)

        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1052)

        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4232)

        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4164)

        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2615)

        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2776)

        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2832)

        at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1755)

        at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1679)

        at org.apache.hadoop.hive.ql.stats.jdbc.JDBCStatsPublisher.init(JDBCStatsPublisher.java:292)

        at org.apache.hadoop.hive.ql.exec.mr.ExecDriver.execute(ExecDriver.java:411)

        at org.apache.hadoop.hive.ql.exec.mr.MapRedTask.execute(MapRedTask.java:137)

        at org.apache.hadoop.hive.ql.exec.Task.executeTask(Task.java:160)

        at org.apache.hadoop.hive.ql.exec.TaskRunner.runSequential(TaskRunner.java:88)

        at org.apache.hadoop.hive.ql.Driver.launchTask(Driver.java:1653)

        at org.apache.hadoop.hive.ql.Driver.execute(Driver.java:1412)

        at org.apache.hadoop.hive.ql.Driver.runInternal(Driver.java:1195)

        at org.apache.hadoop.hive.ql.Driver.run(Driver.java:1059)

        at org.apache.hadoop.hive.ql.Driver.run(Driver.java:1054)

        at org.apache.hive.service.cli.operation.SQLOperation.runQuery(SQLOperation.java:154)

        at org.apache.hive.service.cli.operation.SQLOperation.access$100(SQLOperation.java:71)

        at org.apache.hive.service.cli.operation.SQLOperation$1$1.run(SQLOperation.java:206)

        at java.security.AccessController.doPrivileged(Native Method)

        at javax.security.auth.Subject.doAs(Subject.java:415)

        at org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1548)

        at org.apache.hive.service.cli.operation.SQLOperation$1.run(SQLOperation.java:218)

        at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:471)

        at java.util.concurrent.FutureTask.run(FutureTask.java:262)

        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)

        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)

        at java.lang.Thread.run(Thread.java:745)

 

 

 

From: San Luo [mailto:luohui@damai.cn] 
Sent: 30 November 2015 10:06
To: user@hive.apache.org <ma...@hive.apache.org> 
Subject: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Specified key was too long; max key length is 767 bytes

 

hi guys,

     when I am trying to use mysql to manager hive metastore,I got a problem like below:

 

[root@master hive]# hive

 

Logging initialized using configuration in jar:file:/usr/lib/hive/lib/hive-common-1.1.1.jar!/hive-log4j.properties

SLF4J: Class path contains multiple SLF4J bindings.

SLF4J: Found binding in [jar:file:/usr/lib/hadoop/share/hadoop/common/lib/slf4j-log4j12-1.7.5.jar!/org/slf4j/impl/StaticLoggerBinder.class]

SLF4J: Found binding in [jar:file:/usr/lib/hive/lib/hive-jdbc-1.1.1-standalone.jar!/org/slf4j/impl/StaticLoggerBinder.class]

SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation.

SLF4J: Actual binding is of type [org.slf4j.impl.Log4jLoggerFactory]

hive> show tables;

OK

invites

pokes

test

Time taken: 2.573 seconds, Fetched: 3 row(s)

hive> LOAD DATA LOCAL INPATH './examples/files/kv2.txt' OVERWRITE INTO TABLE invites PARTITION (ds='2008-08-15');

Loading data to table default.invites partition (ds=2008-08-15)

Interrupting... Be patient, this might take some time.

Press Ctrl+C again to kill JVM

Failed with exception MetaException(message:javax.jdo.JDODataStoreException: An exception was thrown while adding/validating class(es) : Specified key was too long; max key length is 767 bytes

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Specified key was too long; max key length is 767 bytes

        at sun.reflect.GeneratedConstructorAccessor32.newInstance(Unknown Source)

        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)

        at java.lang.reflect.Constructor.newInstance(Constructor.java:422)

        at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)

        at com.mysql.jdbc.Util.getInstance(Util.java:386)

…….

 

 

I tried hive 1.2.1 and hive 1.1.1, neither works fine. I also tried to modify mysql charcter set of hive database to latin1 and utf8 but it doesn’t work.

 

Any idea will be appreciated.

 



The Collinson Group Limited; Registered number: 2577557, Registered in England & Wales; Registered Office: Cutlers Exchange, 123 Houndsditch, London, EC3A 7BU.


This e-mail may contain privileged and confidential information and/or copyright material and is intended for the use of the addressee only. If you receive this e-mail by mistake please advise the sender immediately by using the reply facility in your e-mail software and delete this e-mail from your computer system. You may not deliver, copy or disclose its contents to anyone else. Any unauthorised use may be unlawful. Any views expressed in this e-mail are those of the individual sender and may not necessarily reflect the views of The Collinson Group Ltd and/or its subsidiaries or any other associated company (collectively “Collinson Group”).

As communications via the Internet are not secure Collinson Group cannot accept any liability if this e-mail is accessed by third parties during the course of transmission or is modified or amended in any way following despatch. Collinson Group cannot guarantee that any attachment to this email does not contain a virus, therefore it is strongly recommended that you carry out your own virus check before opening any attachment, as we cannot accept liability for any damage sustained as a result of software virus infection. Senders of messages shall be taken to consent to the monitoring and recording of e-mails addressed to members of the Company.