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

最新推荐文章于 2024-06-06 10:46:49 发布
最新推荐文章于 2024-06-06 10:46:49 发布
阅读量1.3w 收藏 2
点赞数
分类专栏： HBase 文章标签： hbase hive mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/wind520/article/details/39890967
版权

Hive 0.13和HBase 0.98.6.1整合出现错误记录下

hive> CREATE TABLE hbase_table_1(key int, value string)              
    > STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
    > WITH SERDEPROPERTIES ("hbase.columns.mapping" = ":key,cf1:val")
    > TBLPROPERTIES ("hbase.table.name" = "xyz2");
FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. 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.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:408)
        at com.mysql.jdbc.Util.getInstance(Util.java:383)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1062)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4208)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4140)
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2597)
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2758)
        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2820)
        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2769)
        at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:907)
        at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:791)
        at com.jolbox.bonecp.StatementHandle.execute(StatementHandle.java:254)
        at org.datanucleus.store.rdbms.table.AbstractTable.executeDdlStatement(AbstractTable.java:760)
        at org.datanucleus.store.rdbms.table.AbstractTable.executeDdlStatementList(AbstractTable.java:711)
        at org.datanucleus.store.rdbms.table.AbstractTable.create(AbstractTable.java:425)
        at org.datanucleus.store.rdbms.table.AbstractTable.exists(AbstractTable.java:488)
        at org.datanucleus.store.rdbms.RDBMSStoreManager$ClassAdder.performTablesValidation(RDBMSStoreManager.java:3380)
        at org.datanucleus.store.rdbms.RDBMSStoreManager$ClassAdder.addClassTablesAndValidate(RDBMSStoreManager.java:3190)
        at org.datanucleus.store.rdbms.RDBMSStoreManager$ClassAdder.run(RDBMSStoreManager.java:2841)
        at org.datanucleus.store.rdbms.AbstractSchemaTransaction.execute(AbstractSchemaTransaction.java:122)
        at org.datanucleus.store.rdbms.RDBMSStoreManager.addClasses(RDBMSStoreManager.java:1605)
        at org.datanucleus.store.AbstractStoreManager.addClass(AbstractStoreManager.java:954)
        at org.datanucleus.store.rdbms.RDBMSStoreManager.getDatastoreClass(RDBMSStoreManager.java:679)
        at org.datanucleus.store.rdbms.RDBMSStoreManager.getPropertiesForGenerator(RDBMSStoreManager.java:2045)
        at org.datanucleus.store.AbstractStoreManager.getStrategyValue(AbstractStoreManager.java:1365)
        at org.datanucleus.ExecutionContextImpl.newObjectId(ExecutionContextImpl.java:3827)
        at org.datanucleus.state.JDOStateManager.setIdentity(JDOStateManager.java:2571)
        at org.datanucleus.state.JDOStateManager.initialiseForPersistentNew(JDOStateManager.java:513)
        at org.datanucleus.state.ObjectProviderFactoryImpl.newForPersistentNew(ObjectProviderFactoryImpl.java:232)
        at org.datanucleus.ExecutionContextImpl.newObjectProviderForPersistentNew(ExecutionContextImpl.java:1414)
        at org.datanucleus.ExecutionContextImpl.persistObjectInternal(ExecutionContextImpl.java:2218)
        at org.datanucleus.ExecutionContextImpl.persistObjectWork(ExecutionContextImpl.java:2065)
        at org.datanucleus.ExecutionContextImpl.persistObject(ExecutionContextImpl.java:1913)
        at org.datanucleus.ExecutionContextThreadedImpl.persistObject(ExecutionContextThreadedImpl.java:217)
        at org.datanucleus.api.jdo.JDOPersistenceManager.jdoMakePersistent(JDOPersistenceManager.java:727)
        at org.datanucleus.api.jdo.JDOPersistenceManager.makePersistent(JDOPersistenceManager.java:752)
        at org.apache.hadoop.hive.metastore.ObjectStore.createTable(ObjectStore.java:719)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:606)
        at org.apache.hadoop.hive.metastore.RawStoreProxy.invoke(RawStoreProxy.java:108)
        at com.sun.proxy.$Proxy9.createTable(Unknown Source)
        at org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.create_table_core(HiveMetaStore.java:1261)
        at org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.create_table_with_environment_context(HiveMetaStore.java:1294)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:606)
        at org.apache.hadoop.hive.metastore.RetryingHMSHandler.invoke(RetryingHMSHandler.java:105)
        at com.sun.proxy.$Proxy10.create_table_with_environment_context(Unknown Source)
        at org.apache.hadoop.hive.metastore.HiveMetaStoreClient.createTable(HiveMetaStoreClient.java:558)
        at org.apache.hadoop.hive.metastore.HiveMetaStoreClient.createTable(HiveMetaStoreClient.java:547)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:606)
        at org.apache.hadoop.hive.metastore.RetryingMetaStoreClient.invoke(RetryingMetaStoreClient.java:89)
        at com.sun.proxy.$Proxy11.createTable(Unknown Source)
        at org.apache.hadoop.hive.ql.metadata.Hive.createTable(Hive.java:613)
        at org.apache.hadoop.hive.ql.exec.DDLTask.createTable(DDLTask.java:4189)
        at org.apache.hadoop.hive.ql.exec.DDLTask.execute(DDLTask.java:281)
        at org.apache.hadoop.hive.ql.exec.Task.executeTask(Task.java:153)
        at org.apache.hadoop.hive.ql.exec.TaskRunner.runSequential(TaskRunner.java:85)
        at org.apache.hadoop.hive.ql.Driver.launchTask(Driver.java:1503)
        at org.apache.hadoop.hive.ql.Driver.execute(Driver.java:1270)
        at org.apache.hadoop.hive.ql.Driver.runInternal(Driver.java:1088)
        at org.apache.hadoop.hive.ql.Driver.run(Driver.java:911)
        at org.apache.hadoop.hive.ql.Driver.run(Driver.java:901)
        at org.apache.hadoop.hive.cli.CliDriver.processLocalCmd(CliDriver.java:268)
        at org.apache.hadoop.hive.cli.CliDriver.processCmd(CliDriver.java:220)
        at org.apache.hadoop.hive.cli.CliDriver.processLine(CliDriver.java:423)
        at org.apache.hadoop.hive.cli.CliDriver.executeDriver(CliDriver.java:792)
        at org.apache.hadoop.hive.cli.CliDriver.run(CliDriver.java:686)
        at org.apache.hadoop.hive.cli.CliDriver.main(CliDriver.java:625)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:606)
        at org.apache.hadoop.util.RunJar.main(RunJar.java:212)

        at org.datanucleus.api.jdo.NucleusJDOHelper.getJDOExceptionForNucleusException(NucleusJDOHelper.java:451)
        at org.datanucleus.api.jdo.JDOPersistenceManager.jdoMakePersistent(JDOPersistenceManager.java:732)
        at org.datanucleus.api.jdo.JDOPersistenceManager.makePersistent(JDOPersistenceManager.java:752)
        at org.apache.hadoop.hive.metastore.ObjectStore.createTable(ObjectStore.java:719)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:606)
        at org.apache.hadoop.hive.metastore.RawStoreProxy.invoke(RawStoreProxy.java:108)
        at com.sun.proxy.$Proxy9.createTable(Unknown Source)
        at org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.create_table_core(HiveMetaStore.java:1261)
        at org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.create_table_with_environment_context(HiveMetaStore.java:1294)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:606)
        at org.apache.hadoop.hive.metastore.RetryingHMSHandler.invoke(RetryingHMSHandler.java:105)
        at com.sun.proxy.$Proxy10.create_table_with_environment_context(Unknown Source)
        at org.apache.hadoop.hive.metastore.HiveMetaStoreClient.createTable(HiveMetaStoreClient.java:558)
        at org.apache.hadoop.hive.metastore.HiveMetaStoreClient.createTable(HiveMetaStoreClient.java:547)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:606)
        at org.apache.hadoop.hive.metastore.RetryingMetaStoreClient.invoke(RetryingMetaStoreClient.java:89)
        at com.sun.proxy.$Proxy11.createTable(Unknown Source)
        at org.apache.hadoop.hive.ql.metadata.Hive.createTable(Hive.java:613)
        at org.apache.hadoop.hive.ql.exec.DDLTask.createTable(DDLTask.java:4189)
        at org.apache.hadoop.hive.ql.exec.DDLTask.execute(DDLTask.java:281)
        at org.apache.hadoop.hive.ql.exec.Task.executeTask(Task.java:153)
        at org.apache.hadoop.hive.ql.exec.TaskRunner.runSequential(TaskRunner.java:85)
        at org.apache.hadoop.hive.ql.Driver.launchTask(Driver.java:1503)
        at org.apache.hadoop.hive.ql.Driver.execute(Driver.java:1270)
        at org.apache.hadoop.hive.ql.Driver.runInternal(Driver.java:1088)
        at org.apache.hadoop.hive.ql.Driver.run(Driver.java:911)
        at org.apache.hadoop.hive.ql.Driver.run(Driver.java:901)
        at org.apache.hadoop.hive.cli.CliDriver.processLocalCmd(CliDriver.java:268)
        at org.apache.hadoop.hive.cli.CliDriver.processCmd(CliDriver.java:220)
        at org.apache.hadoop.hive.cli.CliDriver.processLine(CliDriver.java:423)
        at org.apache.hadoop.hive.cli.CliDriver.executeDriver(CliDriver.java:792)
        at org.apache.hadoop.hive.cli.CliDriver.run(CliDriver.java:686)
        at org.apache.hadoop.hive.cli.CliDriver.main(CliDriver.java:625)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:606)
        at org.apache.hadoop.util.RunJar.main(RunJar.java:212)
NestedThrowablesStackTrace:
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:408)
        at com.mysql.jdbc.Util.getInstance(Util.java:383)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1062)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4208)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4140)
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2597)
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2758)
        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2820)
        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2769)
        at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:907)
        at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:791)
        at com.jolbox.bonecp.StatementHandle.execute(StatementHandle.java:254)
        at org.datanucleus.store.rdbms.table.AbstractTable.executeDdlStatement(AbstractTable.java:760)
        at org.datanucleus.store.rdbms.table.AbstractTable.executeDdlStatementList(AbstractTable.java:711)
        at org.datanucleus.store.rdbms.table.AbstractTable.create(AbstractTable.java:425)
        at org.datanucleus.store.rdbms.table.AbstractTable.exists(AbstractTable.java:488)
        at org.datanucleus.store.rdbms.RDBMSStoreManager$ClassAdder.performTablesValidation(RDBMSStoreManager.java:3380)
        at org.datanucleus.store.rdbms.RDBMSStoreManager$ClassAdder.addClassTablesAndValidate(RDBMSStoreManager.java:3190)
        at org.datanucleus.store.rdbms.RDBMSStoreManager$ClassAdder.run(RDBMSStoreManager.java:2841)
        at org.datanucleus.store.rdbms.AbstractSchemaTransaction.execute(AbstractSchemaTransaction.java:122)
        at org.datanucleus.store.rdbms.RDBMSStoreManager.addClasses(RDBMSStoreManager.java:1605)
        at org.datanucleus.store.AbstractStoreManager.addClass(AbstractStoreManager.java:954)
        at org.datanucleus.store.rdbms.RDBMSStoreManager.getDatastoreClass(RDBMSStoreManager.java:679)
        at org.datanucleus.store.rdbms.RDBMSStoreManager.getPropertiesForGenerator(RDBMSStoreManager.java:2045)
        at org.datanucleus.store.AbstractStoreManager.getStrategyValue(AbstractStoreManager.java:1365)
        at org.datanucleus.ExecutionContextImpl.newObjectId(ExecutionContextImpl.java:3827)
        at org.datanucleus.state.JDOStateManager.setIdentity(JDOStateManager.java:2571)
        at org.datanucleus.state.JDOStateManager.initialiseForPersistentNew(JDOStateManager.java:513)
        at org.datanucleus.state.ObjectProviderFactoryImpl.newForPersistentNew(ObjectProviderFactoryImpl.java:232)
        at org.datanucleus.ExecutionContextImpl.newObjectProviderForPersistentNew(ExecutionContextImpl.java:1414)
        at org.datanucleus.ExecutionContextImpl.persistObjectInternal(ExecutionContextImpl.java:2218)
        at org.datanucleus.ExecutionContextImpl.persistObjectWork(ExecutionContextImpl.java:2065)
        at org.datanucleus.ExecutionContextImpl.persistObject(ExecutionContextImpl.java:1913)
        at org.datanucleus.ExecutionContextThreadedImpl.persistObject(ExecutionContextThreadedImpl.java:217)
        at org.datanucleus.api.jdo.JDOPersistenceManager.jdoMakePersistent(JDOPersistenceManager.java:727)
        at org.datanucleus.api.jdo.JDOPersistenceManager.makePersistent(JDOPersistenceManager.java:752)
        at org.apache.hadoop.hive.metastore.ObjectStore.createTable(ObjectStore.java:719)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:606)
        at org.apache.hadoop.hive.metastore.RawStoreProxy.invoke(RawStoreProxy.java:108)
        at com.sun.proxy.$Proxy9.createTable(Unknown Source)
        at org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.create_table_core(HiveMetaStore.java:1261)
        at org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.create_table_with_environment_context(HiveMetaStore.java:1294)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:606)
        at org.apache.hadoop.hive.metastore.RetryingHMSHandler.invoke(RetryingHMSHandler.java:105)
        at com.sun.proxy.$Proxy10.create_table_with_environment_context(Unknown Source)
        at org.apache.hadoop.hive.metastore.HiveMetaStoreClient.createTable(HiveMetaStoreClient.java:558)
        at org.apache.hadoop.hive.metastore.HiveMetaStoreClient.createTable(HiveMetaStoreClient.java:547)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:606)
        at org.apache.hadoop.hive.metastore.RetryingMetaStoreClient.invoke(RetryingMetaStoreClient.java:89)
        at com.sun.proxy.$Proxy11.createTable(Unknown Source)
        at org.apache.hadoop.hive.ql.metadata.Hive.createTable(Hive.java:613)
        at org.apache.hadoop.hive.ql.exec.DDLTask.createTable(DDLTask.java:4189)
        at org.apache.hadoop.hive.ql.exec.DDLTask.execute(DDLTask.java:281)
        at org.apache.hadoop.hive.ql.exec.Task.executeTask(Task.java:153)
        at org.apache.hadoop.hive.ql.exec.TaskRunner.runSequential(TaskRunner.java:85)
        at org.apache.hadoop.hive.ql.Driver.launchTask(Driver.java:1503)
        at org.apache.hadoop.hive.ql.Driver.execute(Driver.java:1270)
        at org.apache.hadoop.hive.ql.Driver.runInternal(Driver.java:1088)
        at org.apache.hadoop.hive.ql.Driver.run(Driver.java:911)
        at org.apache.hadoop.hive.ql.Driver.run(Driver.java:901)
        at org.apache.hadoop.hive.cli.CliDriver.processLocalCmd(CliDriver.java:268)
        at org.apache.hadoop.hive.cli.CliDriver.processCmd(CliDriver.java:220)
        at org.apache.hadoop.hive.cli.CliDriver.processLine(CliDriver.java:423)
        at org.apache.hadoop.hive.cli.CliDriver.executeDriver(CliDriver.java:792)
        at org.apache.hadoop.hive.cli.CliDriver.run(CliDriver.java:686)
        at org.apache.hadoop.hive.cli.CliDriver.main(CliDriver.java:625)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:606)
        at org.apache.hadoop.util.RunJar.main(RunJar.java:212)
)

原因：

MySQL的varchar主键只支持不超过768个字节 或者 768/2=384个双字节 或者 768/3=256个三字节的字段 
而 GBK是双字节的，UTF-8是三字节的。

查询：

mysql> show variables like '%char%';
+--------------------------+--------------------------------------------+
| Variable_name            | Value                                      |
+--------------------------+--------------------------------------------+
| character_set_client     | gbk                                        |
| character_set_connection | gbk                                        |
| character_set_database   | utf8                                       |
| character_set_filesystem | binary                                     |
| character_set_results    | gbk                                        |
| character_set_server     | utf8                                       |
| character_set_system     | utf8                                       |
| character_sets_dir       | D:\soft\REDMIN~1.2-0\mysql\share\charsets\ |
+--------------------------+--------------------------------------------+
8 rows in set (0.05 sec)

详细的方法：http://blog.csdn.net/cindy9902/article/details/6215769

解决：

 (1) 最简单的修改方法，就是修改mysql的my.ini文件中的字符集键值，
如    default-character-set = utf8
      character_set_server =  utf8
   修改完后，重启mysql的服务，service mysql restart
   使用 mysql> SHOW VARIABLES LIKE 'character%';查看

 (2) 还有一种修改字符集的方法，就是使用mysql的命令

mysql> SET character_set_client = utf8 ;
Query OK, 0 rows affected (0.02 sec)

mysql> SET character_set_connection = utf8 ;
Query OK, 0 rows affected (0.00 sec)

mysql> SET character_set_database = utf8 ;
Query OK, 0 rows affected (0.00 sec)

mysql> SET character_set_results = utf8 ;
Query OK, 0 rows affected (0.00 sec)

mysql> SET character_set_server = utf8 ;
Query OK, 0 rows affected (0.00 sec)

mysql> SET collation_connection = utf8 ;
ERROR 1273 (HY000): Unknown collation: 'utf8'
mysql> SET collation_database = utf8 ;
ERROR 1273 (HY000): Unknown collation: 'utf8'
mysql> SET collation_server = utf8 ;
ERROR 1273 (HY000): Unknown collation: 'utf8'
mysql> SET NAMES 'utf8';
Query OK, 0 rows affected (0.00 sec)
查询修改后的字符集： 

mysql> SHOW VARIABLES LIKE 'character%';
+--------------------------+--------------------------------------------+
| Variable_name            | Value                                      |
+--------------------------+--------------------------------------------+
| character_set_client     | utf8                                       |
| character_set_connection | utf8                                       |
| character_set_database   | utf8                                       |
| character_set_filesystem | binary                                     |
| character_set_results    | utf8                                       |
| character_set_server     | utf8                                       |
| character_set_system     | utf8                                       |
| character_sets_dir       | D:\soft\REDMIN~1.2-0\mysql\share\charsets\ |
+--------------------------+--------------------------------------------+
8 rows in set (0.00 sec)



wind520
关注
  • 0
    点赞
  • 踩
  • 2
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
专栏目录
Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: ….. this is incompatible with sq
12-14
1、写在开头 标题之前我想说一下Linux的mysql真的实在是太坑了。太坑了。总是会出现这样那样的你想不到的问题。崩溃了。首先来罗列一下我遇到过的一些问题吧。 1、大小写敏感 2、连接数超过系统最大连接数 ...
bitronix 连接 MySQL 出现MySQLSyntaxErrorException 的解决方法
01-19
bitronix 连接 MySQL 出现MySQLSyntaxErrorException 的解决方法 1. 开发环境 hibernate 版本：5.1.0.Final Bitronix btm 版本：2.1.3 2 异常堆栈信息 Caused by: ...
Mysql报错：Specified key was too long; max key length is 767 bytes
张云生的博客
03-02 3万+
在执行mysql脚本时，报错：Specified key was too long; max key length is 767 bytes。 解决办法： 一、修改mysql的字符集为uft8 再次执行时正常。 二、mysql的字符集为utf8mb4 1、先检查一下数据库被限制了索引的大小 SHOW variables like ‘innodb_large_prefix’; 如果...
踩坑mysql报错MySQLSyntaxErrorException: Specified key was too long; max key length is 767 bytes
gsls200808的专栏
11-24 2089
报错信息： Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Specified key was too long; max key length is 767 bytes 这个是flowable引擎自动创建表的时候报的错，建表语句如下： CREATE TABLE flowable_test.ACT...
Mysql报错：Specified key was too long； max key length is 767 bytes、SQLlyog、Navicat无法连接mysql？、my.ini配置文件
最新发布
m0_73864806的博客
06-06 873
起初是因为我使用mysql5.6导入数据的时候出现了错误： Specified key was too long; max key length is 767 bytes错误 ​1、先将本地的mysql中的my.ini文件和data文件夹进行备份 2、干净的删除掉本地的mysql文件：​ 3、去官网安装mysql8的版本，大家可以去网上搜索相关的教程，我在这里给大家推荐一个我用的MySQL安装教程（详细版）_mysql8.0.36安装教程-CSDN博客 4、将mysql8安装到本地以后去找到mysql的服务
报错“Specified key was too long； max key length is 767 bytes “ 执行sql语句解决
qq_61116557的博客
02-02 1232
Specified key was too long； max key length is 767 bytes执行3行sql语句解决
MySQL “Specified key was too long； max key length is 767 bytes”解决办法
x235cl的专栏
11-27 972
对于参与索引的字段，增加字段长度时可能会出现错误“Specified key was too long; max key length is 767 bytes”。 因为数据库建立索引时允许的最大长度是767字节。如果开启innodb_large_prefix，则长度扩展至3072字节。 对于UTF-8而言，一个字符占3个字节，varchar(300)长度就是300*3=900字节>767字 方案一 启用innodb_large_prefix，将约束项扩展至3072byte。 my..
【MySql】Specified key was too long； max key length is 767 bytes
Hyatt的博客
06-09 3274
问题报错信息“ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes”，其实意思就是“索引字段长度太长，超过了767bytes”。解决方法mysql的varchar索引字段只支持不超过767个字节或者768/2=384个双字节 或者767/3=255个三字节的字段 而GBK是双字节的，UTF8是三字节的...
Mysql错误：Every derived table must have its own alias
08-14
在MySQL数据库操作中，"Every derived table must have its own alias" 是一个常见的错误提示，意味着在你的SQL查询语句中，使用了派生表（也称为子查询）但没有为它们分配唯一的别名。这个错误通常发生在试图从子...
解决mysql导入新数据库大小写问题（Table ‘zup.Domain_System’ doesn’t exist）
12-14
问题 Table ‘zup.Domain_System’ doesn’t exist ...### Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'zup.Domain_System' doesn't exist ; bad SQL grammar []; nested
[Err] 1071 - Specified key was too long； max key length is 767 bytes，【各版本mysql均已解决】
君临天下tjm的博客
12-28 7734
一般在MySQL 5.7及以上版本不会遇到此问题，只有在MySQL 5.6版本会遇到此问题，建议MySQL 5.6 升级到MySQL 5.7，因为MySQL 5.6 修改比较麻烦。此参数控制索引前缀长度的，不开启最长的长度是 765byte，开启最长3072byte，一般出现此报错的数据库版本是小于MYSQL5.7，同时不同的版本开启的方式也略有不同。innodb_default_row_format=dynamic (默认是dynamic)需要调整的参数的默认值是符合要求。
hive 异常jdbc4.MySQLSyntaxErrorException: Specified key was too long; max key length is 767 bytes的解决
a904364908的博客
12-13 1383
最近在操作hive,在进入hive后,在 show databases; 的时候报了异常,如图 在网上百度了之后,发现应该是hive元数据库的编码问题. 按如下办法得到解决 1.删除原hive连接MySQL数据库中存放hive元数据的数据库。drop database hive; 2.新建存放元数据库的数据。create database hive; 3.修改数据的编码为lat...
Mysql执行脚本Specified key was too long； max key length is 767 bytes问题解决
沛哥儿的专栏
07-22 875
在用Navicate执行Mysql脚本的时候，一帆风顺。 因为生产库不允许远程操作，这个你懂的。所以没办法就把数据库脚本整体从本地导出来了，然后使用阿里云的数据导入功能。 在执行数据导入任务的时候，Burt！就弹出了“Specified key was too long; max key length is 767 bytes”这个错误，还好哥们选择的是错误不 继续的方式，否则等下找错误，就不知该从 何处下手了。 分析了下(假的 ），直接百度，说是因为5.7版本的Mysql默认将innodb_larg
Hive安装好之后创建表时报错MySQLSyntaxErrorException: Specified key was too long; max key length is 767 bytes
panguoyuan的专栏
08-20 2954
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Specified key was too long; max key length is 767 bytes at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
hive的Specified key was too long; max key length is 767 bytes问题解决
宏轩ＪＡＶＡ技术专栏
06-03 4491
把hive 的metadata配置成mysql,执行命令时报以下错误 hive> show tables; FAILED: Error in metadata: MetaException(message:Got exception: javax.jdo.JDODataStoreException An exception was thrown while adding/validat
MySQL提示 “Specified key was too long； max key length is 767 bytes”
热门推荐
叫我酋长好吧
07-24 3万+
mysql 索引过长1071-max key length is 767 byte 处理 RDS Mysql 问题描述 RDS MySQL在大字段上创建索引时，偶尔会遇到如下错误。 Error 1071: Specified key was too long; max key length is 767 bytes. 问题原因 由于MySQL的InnoDB引擎表索引字段长度的限制为767字节，因此对于多字节字符集的大字段或者多字段组合，创建索引时会出现该问题。 注：以utf8mb4字符集字符串类型字段为例
hive集成mysql作为元数据_Hive集成Mysql作为元数据时，提示错误：Specified key was too long; max key length is 767 bytes...
weixin_42117032的博客
02-15 198
在进行Hive集成Mysql作为元数据过程中。做全然部安装配置工作后。进入到hive模式，运行show databases。运行正常，接着运行show tables；时却报错。关键错误信息例如以下：com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Specified key was too long; max key length...
Mysql5.6:Specified key was too long
云淡风轻
09-29 9477
Specified key was too long错误原因解决方案 错误 create table: Specified key was too long; max key length is 767 bytes 原因 数据库表采用utf8编码，其中varchar(255)的column进行了唯一键索引 而mysql默认情况下单个列的索引不能超过767位(不同版本可能存在差异) 于是utf8字...
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Specified key was too long; max key lengt...
aagwpsh7758的博客
11-01 206
创建表示遇到报错： SQL: create index ind_member_login_plugin_id_o_i on xx_member(login_plugin_id, open_id) com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Specified key was too long; max ke...
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Specified key was too long; max key length is 1000 bytes
02-28
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Specified key was too long; max key length is 1000 bytes 是一个MySQL数据库的异常错误。这个错误通常发生在创建表时，当指定的键的长度超过了MySQL...
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值