Hive drop table时候卡死-各种情况的解决方案(持续更新中)

－－－－－－－－－－－－－－hive常识－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－

hive默认的是iso8859（latin1）[4]

下面内容来自[5]

进入mysql中hive的元数据库hivemetastore
修改以下涉及注解comment的表和字段
表字段注解和表注解
alter table COLUMNS_V2 modify column COMMENT varchar(4000) character set utf8
alter table TABLE_PARAMS modify column PARAM_VALUE varchar(4000) character set utf8
分区字段注解：
alter table PARTITION_PARAMS modify column PARAM_VALUE varchar(4000) character set utf8 ;
alter table PARTITION_KEYS modify column PKEY_COMMENT varchar(4000) character set utf8;
索引注解：
alter table INDEX_PARAMS modify column PARAM_VALUE varchar(4000) character set utf8;

－－－－－－－－－－－－－－－－－情况１－－－－－－－－－－－－－－－－－－－－－－－－－－－

檢查hive-site.xml中的用戶名和密碼是否和mysql中的一致

－－－－－－－－－－－－－－－－－情况２－－－－－－－－－－－－－－－－－－－－－－－－－－－－－

確保$HIVE_HOME/lib下面的驅動mysql-connector-java-8.0.22.jar版本與mysql版本一致

－－－－－－－－－－－－－－－－－情况3－－－－－－－－－－－－－－－－－－－－－－－－－－

hive-site.xml中的配置要有：

<property>
<name>javax.jdo.option.ConnectionURL</name>
<value>jdbc:mysql://Desktop:3306/hive?useUnicode=true&characterEncoding=utf8</value>（mysql地址localhost）
</property>

show variables like "char%";

character_set_client	命令行编码
character_set_connection
character_set_database
character_set_filesystem
character_set_results
character_set_server
character_set_system
character_sets_dir

①jps中kill掉所有RunJar的进程

②mysql中drop database hive

③检查各个相关组件的版本

组件	版本
Hive	apache-hive-3.0.0-bin
驱动	mysql-connector-java-8.0.20.jar
Mysql	8.0.22-0ubuntu0.20.04.2

service mysql stop

路径	哪个标签下面	设置
/etc/mysql/debian.cnf	[client]	default-character-set=latin1;
/etc/mysql/mysql.conf.d/mysqld.cnf	[mysqld]	character-set-server=latin1;
/etc/mysql/mysql.conf.d/mysql.cnf	[mysql]	default-character-set=latin1;

service mysql start

create database hive character set latin1;

schematool -dbType mysql -initSchema

上面这个命令会在mysql 中生成元数据库

+---------------------------+
| Tables_in_hive            |
+---------------------------+
| AUX_TABLE                 |
| BUCKETING_COLS            |
| CDS                       |
| COLUMNS_V2                |
| COMPACTION_QUEUE          |
| COMPLETED_COMPACTIONS     |
| COMPLETED_TXN_COMPONENTS  |
| CTLGS                     |
| DATABASE_PARAMS           |
| DBS                       |
| DB_PRIVS                  |
| DELEGATION_TOKENS         |
| FUNCS                     |
| FUNC_RU                   |
| GLOBAL_PRIVS              |
| HIVE_LOCKS                |
| IDXS                      |
| INDEX_PARAMS              |
| I_SCHEMA                  |
| KEY_CONSTRAINTS           |
| MASTER_KEYS               |
| METASTORE_DB_PROPERTIES   |
| MIN_HISTORY_LEVEL         |
| MV_CREATION_METADATA      |
| MV_TABLES_USED            |
| NEXT_COMPACTION_QUEUE_ID  |
| NEXT_LOCK_ID              |
| NEXT_TXN_ID               |
| NEXT_WRITE_ID             |
| NOTIFICATION_LOG          |
| NOTIFICATION_SEQUENCE     |
| NUCLEUS_TABLES            |
| PARTITIONS                |
| PARTITION_EVENTS          |
| PARTITION_KEYS            |
| PARTITION_KEY_VALS        |
| PARTITION_PARAMS          |
| PART_COL_PRIVS            |
| PART_COL_STATS            |
| PART_PRIVS                |
| REPL_TXN_MAP              |
| ROLES                     |
| ROLE_MAP                  |
| RUNTIME_STATS             |
| SCHEMA_VERSION            |
| SDS                       |
| SD_PARAMS                 |
| SEQUENCE_TABLE            |
| SERDES                    |
| SERDE_PARAMS              |
| SKEWED_COL_NAMES          |
| SKEWED_COL_VALUE_LOC_MAP  |
| SKEWED_STRING_LIST        |
| SKEWED_STRING_LIST_VALUES |
| SKEWED_VALUES             |
| SORT_COLS                 |
| TABLE_PARAMS              |
| TAB_COL_STATS             |
| TBLS                      |
| TBL_COL_PRIVS             |
| TBL_PRIVS                 |
| TXNS                      |
| TXN_COMPONENTS            |
| TXN_TO_WRITE_ID           |
| TYPES                     |
| TYPE_FIELDS               |
| VERSION                   |
| WM_MAPPING                |
| WM_POOL                   |
| WM_POOL_TO_TRIGGER        |
| WM_RESOURCEPLAN           |
| WM_TRIGGER                |
| WRITE_SET                 |
+---------------------------+

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

各个节点启动zookeeper

操作命令	新增jps进程
hive --service metastore &	RunJar
hiveserver2	RunJar

beeline

0: jdbc:hive2://Desktop:10000> show databases;
+----------------+
| database_name |
+----------------+
| default |
+----------------+
1 row selected (1.096 seconds)
0: jdbc:hive2://Desktop:10000> create database db1;
No rows affected (0.481 seconds)
0: jdbc:hive2://Desktop:10000> drop database db1;
No rows affected (2.673 seconds)

-----------------------------------------------------------------------------情况4-------------------------------------------------------------------------------------------------------------------------

最近发现一种卡死的情况，hadoop3.x与hive2.x配合使用时缺包导致的卡死情况[6]

因为卡死很可能是yarn那边出问题了，所以要查下yarn那边的报错信息，来调试卡死的问题．(hive on tez)

--------------------------------------------------------------------------------情况5------------------------------------------------------------------------------------------------------------------------

PS：

在hive操作过程中强制退出后，会导致锁表，并且unlock命令无效，此时可以去mysql的元数据库中解锁

mysql的元数据中解锁的命令是

mysql>use hive;

mysql> delete from HIVE_LOCKS;

--------------------------------------------------------------------------------情况6------------------------------------------------------------------------------------------------------------------------

更换hive版本后，记得要重新初始化mysql中的元数据库hive

--------------------------------------------------------------------------------情况7------------------------------------------------------------------------------------------------------------------------

hive -hiveconf hive.root.logger=DEBUG,console

进入调试模式，

然后drop table的时候发现在心跳检测的地方卡住了，如下:

leader在google查到:

When you drop a table from Hive Metastore, it removes the table/column data and their metadata. It can be a normal table (stored in Metastore) or an external table (stored in local file system);

Heartbeats are not being sent when DbLockMgr is used and an operation holds locks (Alan Gates via Ashutosh Chauhan)

[8]里面有提到这个并发功能

根据[7]hive.support.concurrency改成false

hive.txn.manager取消配置

顺利drop