Mycat中间件连接MySQL垂直分库时各种Bug记录与解决总结

问题总述

前两天正常登录Mycat练习水平分表DB01后，开始进行垂直分库的练习，练习的库为SHOPPING库，一共13张表，其实总数据量也不大，可能也就几千条，当天导入后是正常可以查询到表的数据，结果第二天有事，去了一趟其他地方，再开电脑Mycat可以正常登录，在mycat/logs/wrapper.log中也有sucessfully的行信息，但是运行use SHOPPING数据库时会卡死，或者切换数据库成功后，查询里面某一张表数据也卡死，运行的时候报了各种各样的错，就是查不到数据，而直接使用Mysql登录查询3306端口中的表数据又是正常，可以判定应该是Mycat的某些地方有问题，总而言之就是现象是稳定错误的，但报的错误是却是动态变化的，解决了几个通宵各种各样的问题，这里简单记录一下。

环境:Ubuntu 20/Centos 7 + MySQL 8.0.36-0ubuntu0.20.04.1 (Ubuntu)+Datagrip + Mycat 1.6.7.5 + jdk 1.8.0.171 +Xshell远程访问终端 + XFTP进行文件上传 + Navicat偶尔数据库连接测试

重要提示：mycat报错除了看它的logs下的wrapper.log，还要关注mycat.log，使用less指令打开log文件，命令行模式下按大写G跳转到文件末尾可以观察是否有运行错误

问题一

使用某马提供的shopping-table.sql和shopping-insert.sql插入时提示数据库表不存在，但是明明已经配置好了mycat的server.xml和schema.xml文件，并且登录成功了Mycat，先执行了shopping-table.sql建表语句，后执行的数据插入语句shopping-insert.sql，报错可能提示为:

ERROR 1146 (HY000): Table 'shopping.tb_areas_city' doesn't exist
ERROR 1146 (HY000): Table 'shopping.tb_areas_provinces' doesn't exist
ERROR 1146 (HY000): Table 'shopping.tb_areas_region' doesn't exist
ERROR 1146 (HY000): Table 'shopping.tb_user' doesn't exist
ERROR 1146 (HY000): Table 'shopping.tb_goods_base' doesn't exist
ERROR 1146 (HY000): Table 'shopping.tb_goods_brand' doesn't exist
ERROR 1146 (HY000): Table 'shopping.tb_goods_cat' doesn't exist
ERROR 1146 (HY000): Table 'shopping.tb_goods_desc' doesn't exist
ERROR 1146 (HY000): Table 'shopping.tb_goods_item' doesn't exist
ERROR 1146 (HY000): Table 'shopping.tb_order_item' doesn't exist
ERROR 1146 (HY000): Table 'shopping.tb_order_master' doesn't exist
ERROR 1146 (HY000): Table 'shopping.tb_order_paylog' doesn't exist

原因:
后面查到是因为Mycat执行建表语句时会自动将我们小写字母表转换为大写，Linux中的MySQL默认是区分大小写的，所以后面执行shopping-table.sql文件建表后其实存在硬盘上的表名都是大写的，所以后面再执行某马的shopping-insert.sql进行数据插入，就会提示表不存在，而我们使用大写表名去查询时却可以查看有对应的空表存在，同理也可以插入，问题表现如下:
![在这里插入图片描述](https://img-blog.csdnimg.cn/direct/30a02a349acd4a928856b047a7b5ca17.png
权宜之计:
有的说要配置linux的/etc/my.cnf配置文件下的mysqld节点，添加忽略大小写选项，但是经我测试，添加后会导致Mycat无法正常启动，mycat的wrapper.log可以看到报错，没有过多深究。

权宜之计是用Notepadd++或者word打开那两个SQL文件，CRTL+A键全选内容，然后右键将它们的英文字符统一转换为大写（来源于B站一位网友的弹幕评论），再传到Linux下登录MySQL重新使用source命令执行，从而避免建表成功，插入数据提示不存在的问题。

问题二:

因为后面重新开机后Mycat可以正常连接Mysql，Wrapper.log可以看到启动成功提示，但是登录进入后，总是执行sql指令卡死不动，重新在Ubuntu安装配置Mycat和jdk无效，尝试切换系统，我将Mycat从Ubuntu重装到CentOS，出现报错
ERROR 2013 (HY000): Lost connection to MySQL server during query

并且后面使用DataGrip和Navicat测试连接Mycat主片服务器（安装Mycat的服务器）和分片服务器时的3306端口时，主片服务器可以正常连接，但是分片服务器如192.168.24.131测试连接时提示用户名和密码错误，但我确定我的用户名和密码无误，在Linux上可以直接登录成功到MySQL，报错弹出的ip地址是我的VMWARE8的ip如192.168.24.128

情况如下,Navicat和它报错一样:

后面经研究，是因为此前我在Ubuntu中的Mycat的schema.xml文件访问分片服务器配置的是自定义的mofuchu用户，mysql下有创建所有权限的’mofuchu’@'%‘用户，但在CentOS中重新安装配置Mycat时，schemal.xml文件配置的是root用户，密码是对的，但是在CentOS中我的mysql里没有创建root@’%'这样的的用户，只有root@localhost，导致分片服务器中root用户只有在本机登录时才可以正常访问服务器

解决办法

检查自己数据库中是否未设置可以其他通过其他ip连接的登录用户，可以使用如下指令:

mysql -uroot -p你设置的root用户密码
use mysql;
select use,host from user;

你需要看到有host值为%的用户，表示该用户名和密码可以通过任意ip访问该数据库

如果你只有一个host值为localhost的root用户，报错和我上面提到的一样，那么就可以执行下面的语句创建一个用户:

-- 创建用户（如果尚未存在）
CREATE USER IF NOT EXISTS 'root'@'%' IDENTIFIED BY '123456';

-- 授予所有权限给新用户
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;

-- 刷新权限，使设置立即生效
FLUSH PRIVILEGES;

创建完成后退出Mysql，重启MySQL服务和Mycat服务,这里我的Mycat已经添加了环境变量配置文件/etc/profile里，如果没有配置可以参考jdk方式再复制粘贴一行即可进行路径和名称修改保存即可。

#如果重新配置了环境变量文件，记得使用source指令重新载入
source /etc/profile
#重启Mysql服务
systemctl restart mysql
#如果是CentOS
systemctl restart mysqld
mycat restart

重新使用DataGrip或者Naivcat使用root用户访问分片服务器的3306端口，则不会再弹出和输入ip地址不一样的报错了，该问题算解决。

问题三:

这个报错我直接贴错误代码了:

2024-05-02 18:35:10.785 ERROR [Timer1] (io.mycat.backend.jdbc.JDBCHeartbeat.heartbeat(JDBCHeartbeat.java:123)) - JDBCHeartBeat error
java.sql.SQLNonTransientConnectionException: Public Key Retrieval is not allowed

解决办法为，在mycat的conf目录下打开schema.xml文件，每个writeHost节点的url属性中的 ? 后面，添加allowPublicKeyRetrieval键，示例如下，可自行修改:

		<writeHost host="master" url="jdbc:mysql://192.168.24.142:3306?allowPublicKeyRetrieval=true&amp;useSSL=false&amp;serverTimezone=Asia/Shanghai&amp;characterEncoding=utf8" user="root" password="123456" />

配置完成后记得重启mysql服务，之后重启mycat服务!
其实添加的这一行和后面的useSSL应该是相斥的，可是就会报提到的这个错误，只能先暂时这样解决，后面其实再登录Mycat查询垂直分库的表数据时已经正常了。

问题四:

其实还有像下面这样的报错:

2024-05-02 18:42:30.681 ERROR [BusinessExecutor5] (io.mycat.backend.jdbc.JDBCConnection.executeddl(JDBCConnection.java:432)) - 
java.sql.SQLException: Can not issue SELECT via executeUpdate() or executeLargeUpdate().

2024-05-01 18:22:33.693 ERROR [Timer1] (io.mycat.backend.jdbc.JDBCHeartbeat.heartbeat(JDBCHeartbeat.java:1
23)) - JDBCHeartBeat error
java.sql.SQLException: null,  message from server: "Host '192.168.24.129' is blocked because of many conne
ction errors; unblock with 'mysqladmin flush-hosts'"```

问题一目前来看暂时不影响运行，因为方法都不是我们写的，我们只是暂时使用，也不好解决，问题二我执行了刷新hosts指令，影响好像也不是特别大，没有解决Mycat执行sql指令卡死的问题。

mysqladmin -u root -p123456 flush-hosts

此外，在官网的旧网页文档中看到过如果select很卡，可以添加-A选项，可能可以解决,如:

mysql -uroot -p123456 -P8066 -h192.168.24.131 -A

或者切换数据库时,如shopping数据库:

use SHOPPING -A;

总结

其实还有一些错误，因为日志的原因，解决后报错又被删了，光这个旧版本的Mycat垂直分库，debug都耗费了本人两三个通宵，后面有想到后再来补充吧，另外的我的虚拟机ip其实也主动暴露了，希望搞安全的朋友别攻击我，谢谢了，希望能对大家有些帮助或者参考性作用，如果有用还请点个赞!