mysql导入出现:/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER */

本文介绍了一种常见的MySQL数据库导入问题及解决方法。当遇到特殊SQL语句导致导入失败时,可通过文本编辑器删除相关行来解决问题。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

导入数据库出现:/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */怎么解决

当时试着网站搬家的时候,在备份数据后然后在新的数据库中导入原来备份好的数据库提示这个:

“/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;”

不知道什么原因,在网上查看各种资料,后来终于找到解决办法如下: 先备份原来的数据,然后后用文本编辑器编辑(我用的Editplus) 找到:

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8 */;

删除,然后导入就ok了,

提醒的有的底部也有这3行代码,同样需要删除就OK了。

[root@hadoop0 conf]# schematool -dbType mysql -initSchema -verbose SLF4J: Class path contains multiple SLF4J bindings. SLF4J: Found binding in [jar:file:/opt/apps/hive3.1/lib/log4j-slf4j-impl-2.17.1.jar!/org/slf4j/impl/StaticLoggerBinder.class] SLF4J: Found binding in [jar:file:/opt/apps/hadoop-2.7.6/share/hadoop/common/lib/slf4j-log4j12-1.7.10.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.apache.logging.slf4j.Log4jLoggerFactory] Metastore connection URL: jdbc:mysql://hadoop0:3306/metastore?useSSL=false Metastore Connection Driver : com.mysql.cj.jdbc.Driver Metastore connection User: root Starting metastore schema initialization to 3.1.0 Initialization script hive-schema-3.1.0.mysql.sql Connecting to jdbc:mysql://hadoop0:3306/metastore?useSSL=false Connected to: MySQL (version 8.0.42) Driver: MySQL Connector/J (version mysql-connector-java-8.0.23 (Revision: 18bbd5e68195d0da083cbd5bd0d05d76320df7cd)) Transaction isolation: TRANSACTION_READ_COMMITTED 0: jdbc:mysql://hadoop0:3306/metastore> !autocommit on Autocommit status: true 0: jdbc:mysql://hadoop0:3306/metastore> /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */ No rows affected (0.033 seconds) 0: jdbc:mysql://hadoop0:3306/metastore> /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */ No rows affected (0.016 seconds) 0: jdbc:mysql://hadoop0:3306/metastore> /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */ No rows affected (0.014 seconds) 0: jdbc:mysql://hadoop0:3306/metastore> /*!40101 SET NAMES utf8 */ No rows affected (0.015 seconds) 0: jdbc:mysql://hadoop0:3306/metastore> /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */ No rows affected (0.019 seconds) 0: jdbc:mysql://hadoop0:3306/metastore> /*!40103 SET TIME_ZONE='+00:00' */ No rows affected (0.016 seconds) 0: jdbc:mysql://hadoop0:3306/metastore> /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */ No rows affected (0.004 seconds) 0: jdbc:mysql://hadoop0:3306/metastore> /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */ No rows affected (0.016 seconds) 0: jdbc:mysql://hadoop0:3306/metastore> /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */ No rows affected (0.008 seconds) 0: jdbc:mysql://hadoop0:3306/metastore> /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */ No rows affected (0.024 seconds) 0: jdbc:mysql://hadoop0:3306/metastore> /*!40101 SET @saved_cs_client = @@character_set_client */ No rows affected (0.014 seconds) 0: jdbc:mysql://hadoop0:3306/metastore> /*!40101 SET character_set_client = utf8 */ No rows affected (0.02 seconds) 0: jdbc:mysql://hadoop0:3306/metastore> CREATE TABLE IF NOT EXISTS `BUCKETING_COLS` ( `SD_ID` bigint(20) NOT NULL, `BUCKET_COL_NAME` varchar(256) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, `INTEGER_IDX` int(11) NOT NULL, PRIMARY KEY (`SD_ID`,`INTEGER_IDX`), KEY `BUCKETING_COLS_N49` (`SD_ID`), CONSTRAINT `BUCKETING_COLS_FK1` FOREIGN KEY (`SD_ID`) REFERENCES `SDS` (`SD_ID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 No rows affected (0.024 seconds) 0: jdbc:mysql://hadoop0:3306/metastore> /*!40101 SET character_set_client = @saved_cs_client */ No rows affected (0.002 seconds) 0: jdbc:mysql://hadoop0:3306/metastore> /*!40101 SET @saved_cs_client = @@character_set_client */ No rows affected (0.011 seconds) 0: jdbc:mysql://hadoop0:3306/metastore> /*!40101 SET character_set_client = utf8 */ No rows affected (0.001 seconds) 0: jdbc:mysql://hadoop0:3306/metastore> CREATE TABLE IF NOT EXISTS `CDS` ( `CD_ID` bigint(20) NOT NULL, PRIMARY KEY (`CD_ID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 No rows affected (0.035 seconds) 0: jdbc:mysql://hadoop0:3306/metastore> /*!40101 SET character_set_client = @saved_cs_client */ No rows affected (0.013 seconds) 0: jdbc:mysql://hadoop0:3306/metastore> /*!40101 SET @saved_cs_client = @@character_set_client */ No rows affected (0.001 seconds) 0: jdbc:mysql://hadoop0:3306/metastore> /*!40101 SET character_set_client = utf8 */ No rows affected (0.008 seconds) 0: jdbc:mysql://hadoop0:3306/metastore> CREATE TABLE IF NOT EXISTS `COLUMNS_V2` ( `CD_ID` bigint(20) NOT NULL, `COMMENT` varchar(256) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, `COLUMN_NAME` varchar(767) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, `TYPE_NAME` MEDIUMTEXT DEFAULT NULL, `INTEGER_IDX` int(11) NOT NULL, PRIMARY KEY (`CD_ID`,`COLUMN_NAME`), KEY `COLUMNS_V2_N49` (`CD_ID`), CONSTRAINT `COLUMNS_V2_FK1` FOREIGN KEY (`CD_ID`) REFERENCES `CDS` (`CD_ID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 No rows affected (0.022 seconds) 0: jdbc:mysql://hadoop0:3306/metastore> /*!40101 SET character_set_client = @saved_cs_client */ No rows affected (0.022 seconds) 0: jdbc:mysql://hadoop0:3306/metastore> /*!40101 SET @saved_cs_client = @@character_set_client */ No rows affected (0.025 seconds) 0: jdbc:mysql://hadoop0:3306/metastore> /*!40101 SET character_set_client = utf8 */ No rows affected (0.002 seconds) 0: jdbc:mysql://hadoop0:3306/metastore> CREATE TABLE IF NOT EXISTS `DATABASE_PARAMS` ( `DB_ID` bigint(20) NOT NULL, `PARAM_KEY` varchar(180) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, `PARAM_VALUE` varchar(4000) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, PRIMARY KEY (`DB_ID`,`PARAM_KEY`), KEY `DATABASE_PARAMS_N49` (`DB_ID`), CONSTRAINT `DATABASE_PARAMS_FK1` FOREIGN KEY (`DB_ID`) REFERENCES `DBS` (`DB_ID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 No rows affected (0.021 seconds) 0: jdbc:mysql://hadoop0:3306/metastore> /*!40101 SET character_set_client = @saved_cs_client */ No rows affected (0.004 seconds) 0: jdbc:mysql://hadoop0:3306/metastore> CREATE TABLE `CTLGS` ( `CTLG_ID` BIGINT PRIMARY KEY, `NAME` VARCHAR(256), `DESC` VARCHAR(4000), `LOCATION_URI` VARCHAR(4000) NOT NULL, UNIQUE KEY `UNIQUE_CATALOG` (`NAME`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 Error: Table 'CTLGS' already exists (state=42S01,code=1050) Closing: 0: jdbc:mysql://hadoop0:3306/metastore?useSSL=false org.apache.hadoop.hive.metastore.HiveMetaException: Schema initialization FAILED! Metastore state would be inconsistent !! Underlying cause: java.io.IOException : Schema script failed, errorcode 2 org.apache.hadoop.hive.metastore.HiveMetaException: Schema initialization FAILED! Metastore state would be inconsistent !! at org.apache.hive.beeline.HiveSchemaTool.doInit(HiveSchemaTool.java:594) at org.apache.hive.beeline.HiveSchemaTool.doInit(HiveSchemaTool.java:567) at org.apache.hive.beeline.HiveSchemaTool.main(HiveSchemaTool.java:1517) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) at org.apache.hadoop.util.RunJar.run(RunJar.java:221) at org.apache.hadoop.util.RunJar.main(RunJar.java:136) Caused by: java.io.IOException: Schema script failed, errorcode 2 at org.apache.hive.beeline.HiveSchemaTool.runBeeLine(HiveSchemaTool.java:1226) at org.apache.hive.beeline.HiveSchemaTool.runBeeLine(HiveSchemaTool.java:1204) at org.apache.hive.beeline.HiveSchemaTool.doInit(HiveSchemaTool.java:590) ... 8 more *** schemaTool failed ***
最新发布
07-10
[root@hadoop0 conf]# schematool -dbType mysql -initSchema -verbose SLF4J: Class path contains multiple SLF4J bindings. SLF4J: Found binding in [jar:file:/opt/apps/hive3.1/lib/log4j-slf4j-impl-2.17.1.jar!/org/slf4j/impl/StaticLoggerBinder.class] SLF4J: Found binding in [jar:file:/opt/apps/hadoop-2.7.6/share/hadoop/common/lib/slf4j-log4j12-1.7.10.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.apache.logging.slf4j.Log4jLoggerFactory] Metastore connection URL: jdbc:mysql://hadoop0:3306/metastore?useSSL=false Metastore Connection Driver : com.mysql.cj.jdbc.Driver Metastore connection User: root Starting metastore schema initialization to 3.1.0 Initialization script hive-schema-3.1.0.mysql.sql Connecting to jdbc:mysql://hadoop0:3306/metastore?useSSL=false Connected to: MySQL (version 8.0.42) Driver: MySQL Connector/J (version mysql-connector-java-8.0.23 (Revision: 18bbd5e68195d0da083cbd5bd0d05d76320df7cd)) Transaction isolation: TRANSACTION_READ_COMMITTED 0: jdbc:mysql://hadoop0:3306/metastore> !autocommit on Autocommit status: true 0: jdbc:mysql://hadoop0:3306/metastore> /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */ No rows affected (0.012 seconds) 0: jdbc:mysql://hadoop0:3306/metastore> /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */ No rows affected (0.005 seconds) 0: jdbc:mysql://hadoop0:3306/metastore> /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */ No rows affected (0.008 seconds) 0: jdbc:mysql://hadoop0:3306/metastore> /*!40101 SET NAMES utf8 */ No rows affected (0.003 seconds) 0: jdbc:mysql://hadoop0:3306/metastore> /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */ No rows affected (0.026 seconds) 0: jdbc:mysql://hadoop0:3306/metastore> /*!40103 SET TIME_ZONE='+00:00' */ No rows affected (0.018 seconds) 0: jdbc:mysql://hadoop0:3306/metastore> /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */ No rows affected (0.012 seconds) 0: jdbc:mysql://hadoop0:3306/metastore> /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */ No rows affected (0.016 seconds) 0: jdbc:mysql://hadoop0:3306/metastore> /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */ No rows affected (0.027 seconds) 0: jdbc:mysql://hadoop0:3306/metastore> /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */ No rows affected (0.019 seconds) 0: jdbc:mysql://hadoop0:3306/metastore> /*!40101 SET @saved_cs_client = @@character_set_client */ No rows affected (0.015 seconds) 0: jdbc:mysql://hadoop0:3306/metastore> /*!40101 SET character_set_client = utf8 */ No rows affected (0.011 seconds) 0: jdbc:mysql://hadoop0:3306/metastore> CREATE TABLE IF NOT EXISTS `BUCKETING_COLS` ( `SD_ID` bigint(20) NOT NULL, `BUCKET_COL_NAME` varchar(256) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, `INTEGER_IDX` int(11) NOT NULL, PRIMARY KEY (`SD_ID`,`INTEGER_IDX`), KEY `BUCKETING_COLS_N49` (`SD_ID`), CONSTRAINT `BUCKETING_COLS_FK1` FOREIGN KEY (`SD_ID`) REFERENCES `SDS` (`SD_ID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 No rows affected (0.048 seconds) 0: jdbc:mysql://hadoop0:3306/metastore> /*!40101 SET character_set_client = @saved_cs_client */ No rows affected (0.013 seconds) 0: jdbc:mysql://hadoop0:3306/metastore> /*!40101 SET @saved_cs_client = @@character_set_client */ No rows affected (0.007 seconds) 0: jdbc:mysql://hadoop0:3306/metastore> /*!40101 SET character_set_client = utf8 */ No rows affected (0.001 seconds) 0: jdbc:mysql://hadoop0:3306/metastore> CREATE TABLE IF NOT EXISTS `CDS` ( `CD_ID` bigint(20) NOT NULL, PRIMARY KEY (`CD_ID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 No rows affected (0.018 seconds) 0: jdbc:mysql://hadoop0:3306/metastore> /*!40101 SET character_set_client = @saved_cs_client */ No rows affected (0.001 seconds) 0: jdbc:mysql://hadoop0:3306/metastore> /*!40101 SET @saved_cs_client = @@character_set_client */ No rows affected (0.016 seconds) 0: jdbc:mysql://hadoop0:3306/metastore> /*!40101 SET character_set_client = utf8 */ No rows affected (0.012 seconds) 0: jdbc:mysql://hadoop0:3306/metastore> CREATE TABLE IF NOT EXISTS `COLUMNS_V2` ( `CD_ID` bigint(20) NOT NULL, `COMMENT` varchar(256) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, `COLUMN_NAME` varchar(767) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, `TYPE_NAME` MEDIUMTEXT DEFAULT NULL, `INTEGER_IDX` int(11) NOT NULL, PRIMARY KEY (`CD_ID`,`COLUMN_NAME`), KEY `COLUMNS_V2_N49` (`CD_ID`), CONSTRAINT `COLUMNS_V2_FK1` FOREIGN KEY (`CD_ID`) REFERENCES `CDS` (`CD_ID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 No rows affected (0.005 seconds) 0: jdbc:mysql://hadoop0:3306/metastore> /*!40101 SET character_set_client = @saved_cs_client */ No rows affected (0.002 seconds) 0: jdbc:mysql://hadoop0:3306/metastore> /*!40101 SET @saved_cs_client = @@character_set_client */ No rows affected (0.008 seconds) 0: jdbc:mysql://hadoop0:3306/metastore> /*!40101 SET character_set_client = utf8 */ No rows affected (0.004 seconds) 0: jdbc:mysql://hadoop0:3306/metastore> CREATE TABLE IF NOT EXISTS `DATABASE_PARAMS` ( `DB_ID` bigint(20) NOT NULL, `PARAM_KEY` varchar(180) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, `PARAM_VALUE` varchar(4000) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, PRIMARY KEY (`DB_ID`,`PARAM_KEY`), KEY `DATABASE_PARAMS_N49` (`DB_ID`), CONSTRAINT `DATABASE_PARAMS_FK1` FOREIGN KEY (`DB_ID`) REFERENCES `DBS` (`DB_ID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 No rows affected (0.015 seconds) 0: jdbc:mysql://hadoop0:3306/metastore> /*!40101 SET character_set_client = @saved_cs_client */ No rows affected (0.003 seconds) 0: jdbc:mysql://hadoop0:3306/metastore> CREATE TABLE `CTLGS` ( `CTLG_ID` BIGINT PRIMARY KEY, `NAME` VARCHAR(256), `DESC` VARCHAR(4000), `LOCATION_URI` VARCHAR(4000) NOT NULL, UNIQUE KEY `UNIQUE_CATALOG` (`NAME`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 Error: Table 'CTLGS' already exists (state=42S01,code=1050) Closing: 0: jdbc:mysql://hadoop0:3306/metastore?useSSL=false org.apache.hadoop.hive.metastore.HiveMetaException: Schema initialization FAILED! Metastore state would be inconsistent !! Underlying cause: java.io.IOException : Schema script failed, errorcode 2 org.apache.hadoop.hive.metastore.HiveMetaException: Schema initialization FAILED! Metastore state would be inconsistent !! at org.apache.hive.beeline.HiveSchemaTool.doInit(HiveSchemaTool.java:594) at org.apache.hive.beeline.HiveSchemaTool.doInit(HiveSchemaTool.java:567) at org.apache.hive.beeline.HiveSchemaTool.main(HiveSchemaTool.java:1517) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) at org.apache.hadoop.util.RunJar.run(RunJar.java:221) at org.apache.hadoop.util.RunJar.main(RunJar.java:136) Caused by: java.io.IOException: Schema script failed, errorcode 2 at org.apache.hive.beeline.HiveSchemaTool.runBeeLine(HiveSchemaTool.java:1226) at org.apache.hive.beeline.HiveSchemaTool.runBeeLine(HiveSchemaTool.java:1204) at org.apache.hive.beeline.HiveSchemaTool.doInit(HiveSchemaTool.java:590) ... 8 more *** schemaTool failed ***
07-10
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值