如何解决myshard中找不到表[no_hash] 和table [tbl_test]的问题

2025-03-17 技术教程

本篇文章给大家分享的是有关如何解决myshard中找不到表[no_hash] 和table [tbl_test]的问题，小编觉得挺实用的，因此分享给大家学习，希望大家阅读完这篇文章后可以有所收获，话不多说，跟着小编一起来看看吧。

在创建表的时候

mysql>CREATETABLE`myshard`.`tbl_test`(->`col1`VARCHAR(32)DEFAULTNULL,->`col2`VARCHAR(11)DEFAULT'0',->PRIMARYKEY(col1)->)COLLATE='utf8_bin'ENGINE=InnoDBDEFAULTCHARSET=utf8PARTITIONKEYcol1SHARDING_RULEno_hash;ERROR65535(HY000):Cannotfindrule[no_hash]fortable[tbl_test]

然而admin_show allconfig能看到有no_hash的分表规则

|allconfig#rule|rule_name|default_server|mode|expression||allconfig#rule|no_hash|server1|5|

在打印日志时，重建这个表，看日志有什么报错

tail -f shardadmin_short_video_oss_40_d/shardadmin_short_video_oss_40_d.log

会发现日志会对myshard_metadata做一些插入和查询操作

May1709:46:44debugshardadmin_short_video_oss_40_d[46650]:CMySQLQueryServer::handle_mysql:cmd_type[3],cmd_len:[220],cmd[CREATETABLE`myshard`.`tbl_test`(`col1`VARCHAR(32)DEFAULTNULL,`col2`VARCHAR(11)DEFAULT'0',PRIMARYKEY(col1))COLLATE='utf8_bin'ENGINE=InnoDBDEFAULTCHARSET=utf8PARTITIONKEYcol1SHARDING_RULEno_hash]May1709:46:44debugshardadmin_short_video_oss_40_d[46650]:enteringCQueryProcessor::process.May1709:46:44debugshardadmin_short_video_oss_40_d[46650]:Vincent:CQueryProcessor::process,,itshouldbe0:ptSQLStruct->m_SysOP=7.May1709:46:44debugshardadmin_short_video_oss_40_d[46650]:Vincent:CQueryProcessor::process,objfoundtoprocessit.May1709:46:44infoshardadmin_short_video_oss_40_d[46650]:sqlPreView:[selectdefault_timestamp_modefrommyshard_table_rulewherebusi_name='short_video_oss'andmachine_room_no=4andrule_name='no_hash']May1709:46:44infoshardadmin_short_video_oss_40_d[46650]:sqlPreView:[insertintomyshard_table(busi_name,machine_room_no,table_name,rule_name,db_name,sharding_column,engine_type,charset_type,collation_type,partition2_key,partition2_range)values('short_video_oss',4,'tbl_test','no_hash','myshard','col1','InnoDB','utf8','utf8_bin','','')]May1709:46:44infoshardadmin_short_video_oss_40_d[46650]:sqlPreView:[insertintomyshard_table_columns(busi_name,machine_room_no,TABLE_NAME,COLUMN_NAME,ORDINAL_POSITION,COLUMN_DEFAULT,COLUMN_TYPE,DATA_TYPE)values('short_video_oss',4,'tbl_test','col1',1,'NULL','VARCHAR(32)','VARCHAR')]May1709:46:44infoshardadmin_short_video_oss_40_d[46650]:sqlPreView:[insertintomyshard_table_columns(busi_name,machine_room_no,TABLE_NAME,COLUMN_NAME,ORDINAL_POSITION,COLUMN_DEFAULT,COLUMN_TYPE,DATA_TYPE)values('short_video_oss',4,'tbl_test','col2',2,'0','VARCHAR(11)','VARCHAR')]May1709:46:44infoshardadmin_short_video_oss_40_d[46650]:sqlPreView:[updatemyshard_table_columnssetCOLUMN_KEY='PRI'wherebusi_name='short_video_oss'andmachine_room_no=4andtable_name='tbl_test'andcolumn_name='col1']May1709:46:44infoshardadmin_short_video_oss_40_d[46650]:sqlPreView:[insertintomyshard_table_statistics(busi_name,machine_room_no,TABLE_NAME,INDEX_NAME,COLUMN_NAME,SEQ_IN_INDEX)values('short_video_oss',4,'tbl_test','PRIMARY','col1',1)]May1709:46:44infoshardadmin_short_video_oss_40_d[46650]:sqlPreView:[selectdb_name,rule_namefrommyshard_tablewherebusi_name='short_video_oss'andmachine_room_no=4andtable_name='tbl_test']May1709:46:44infoshardadmin_short_video_oss_40_d[46650]:sqlPreView:[selectdefault_server,default_timestamp_modefrommyshard_table_rulewherebusi_name='short_video_oss'andmachine_room_no=4andrule_name='no_hash']May1709:46:44infoshardadmin_short_video_oss_40_d[46650]:sqlPreView:[selectsharding_rule_name,server,table_rewritefrommyshard_sharding_rulewherebusi_name='short_video_oss'andmachine_room_no=4andrule_name='no_hash']May1709:46:44infoshardadmin_short_video_oss_40_d[46650]:sqlPreView:[selectproperty,valuefrommyshard_server_con_configwherebusi_name='short_video_oss'andmachine_room_no=4andserver_name='server1']May1709:46:44infoshardadmin_short_video_oss_40_d[46650]:Creatingconnectiontoschema=myshard,ipAddress=127.0.0.1,user=db_myshard_rw,password=DauK4Ju92x,port=6301May1709:46:44debugshardadmin_short_video_oss_40_d[46650]:CREATETABLEmyshard.tbl_test(col1VARCHAR(32)DEFAULTNULL,col2VARCHAR(11)DEFAULT'0',__versionBIGINTunsigneddefault0,__deletedTINYINTdefault0,PRIMARYKEY(col1))ENGINE=InnoDBDEFAULTCHARACTERSETutf8COLLATEutf8_bin#-1946151400May1709:46:44errshardadmin_short_video_oss_40_d[46650]:[CShardAdmin2::send_msg_to_proxy]shenglong,send_msg_to_proxy[proxy_4_0_0]failed:errcode:65535,errmsg:Cannotfindrule[no_hash]fortable[tbl_test],strSQL:CREATETABLE`myshard`.`tbl_test`(`col1`VARCHAR(32)DEFAULTNULL,`col2`VARCHAR(11)DEFAULT'0',PRIMARYKEY(col1))COLLATE='utf8_bin'ENGINE=InnoDBDEFAULTCHARSET=utf8PARTITIONKEYcol1SHARDING_RULEno_hashMay1709:46:44errshardadmin_short_video_oss_40_d[46650]:send_msg_to_proxysql[CREATETABLE`myshard`.`tbl_test`(`col1`VARCHAR(32)DEFAULTNULL,`col2`VARCHAR(11)DEFAULT'0',PRIMARYKEY(col1))COLLATE='utf8_bin'ENGINE=InnoDBDEFAULTCHARSET=utf8PARTITIONKEYcol1SHARDING_RULEno_hash]to[proxy_4_0_0]getaerror[65535][Cannotfindrule[no_hash]fortable[tbl_test]]

与分表规则相关的查询有如下2个

selectdefault_server,default_timestamp_modefrommyshard_table_rulewherebusi_name='short_video_oss'andmachine_room_no=4andrule_name='no_hash';selectsharding_rule_name,server,table_rewritefrommyshard_sharding_rulewherebusi_name='short_video_oss'andmachine_room_no=4andrule_name='no_hash';

如果myshard底下挂的MySQL的myshard_metadata的schema里，找不到这些数据就大概是报错的原因，查询第一句是有的，但是查询第二个表myshard_sharding_rule是查询不到的，查询不到是正常的，因为no_hash就不存在分表规则sharding_rule

+----------------+------------------------+|default_server|default_timestamp_mode|+----------------+------------------------+|server1|5|+----------------+------------------------+

不过也发现一个报错，send_msg_to_proxy[proxy_4_0_0] failed: errcode:65535，这是一个proxy的错误，并不是shardadmin的错误，使用4455把表进行删除时，实际上是删除元数据关于表的信息，同样有个send_msg_to_proxy的报错

May1710:25:25debugshardadmin_short_video_oss_40_d[46650]:droptablemyshard.tbl_test#-1946110424May1710:25:25infoshardadmin_short_video_oss_40_d[46650]:sqlPreView:[deletefrommyshard_tablewherebusi_name='short_video_oss'andmachine_room_no=4andtable_name='tbl_test']May1710:25:25infoshardadmin_short_video_oss_40_d[46650]:sqlPreView:[deletefrommyshard_table_columnswherebusi_name='short_video_oss'andmachine_room_no=4andtable_name='tbl_test']May1710:25:25infoshardadmin_short_video_oss_40_d[46650]:sqlPreView:[deletefrommyshard_table_statisticswherebusi_name='short_video_oss'andmachine_room_no=4andtable_name='tbl_test']May1710:25:25infoshardadmin_short_video_oss_40_d[46650]:sqlPreView:[selectindex_namefrommyshard_indexwherebusi_name='short_video_oss'andmachine_room_no=4andtable_name='tbl_test']May1710:25:25infoshardadmin_short_video_oss_40_d[46650]:sqlPreView:[deletefrommyshard_indexwherebusi_name='short_video_oss'andmachine_room_no=4andtable_name='tbl_test']May1710:25:25errshardadmin_short_video_oss_40_d[46650]:[CShardAdmin2::send_msg_to_proxy]shenglong,send_msg_to_proxy[proxy_4_0_0]failed:errcode:65535,errmsg:Tablenotexisted!,strSQL:droptabletbl_testMay1710:25:25errshardadmin_short_video_oss_40_d[46650]:send_msg_to_proxysql[droptabletbl_test]to[proxy_4_0_0]getaerror[65535][Tablenotexisted!]May1710:25:27noticeshardadmin_short_video_oss_40_d[58881]:request:0proc:0droped:0active:0May1710:25:27noticeshardadmin_short_video_oss_40_d[58881]:measure:0|0=9999442,20,499972,500007

send_msg_to_proxy 这里有一个报错，8821-8827其实是一个proxy，它不一定转到127.0.0.1，也可以转到其他地方。怀疑是8821到8827的端口程序有问题，于是重启每次重启，只打印少量的日志，创建端口8821 OK，于是更换了，rds程序。对于no_hash的分表规则，shardadmin在MySQL创建了规则，但是shard不知道，怎么让shard知道，需要重启shard，但这个shard重启是有问题的

May1712:06:35infoshard_short_video_oss_210_d[130853]:createlistenport8826successMay1712:06:35infoshard_short_video_oss_210_d[130853]:serverstartMay1712:07:07infoshard_short_video_oss_210_d[131498]:HAVE_EPOLLMay1712:07:07debugshard_short_video_oss_210_d[131498]:outputqueueokMay1712:07:07debugshard_short_video_oss_210_d[131498]:outputqueueokMay1712:07:07infoshard_short_video_oss_210_d[131498]:createlistenport8826successMay1712:07:07infoshard_short_video_oss_210_d[131498]:serverstartMay1712:07:14infoshard_short_video_oss_210_d[132027]:HAVE_EPOLLMay1712:07:14debugshard_short_video_oss_210_d[132027]:outputqueueokMay1712:07:14debugshard_short_video_oss_210_d[132027]:outputqueueokMay1712:07:14infoshard_short_video_oss_210_d[132027]:createlistenport8826successMay1712:07:14infoshard_short_video_oss_210_d[132027]:serverstart

所以怀疑是rds程序有问题，于是更换别的rds程序，重启shard端口，会进行大量的初始化，打大量的日志，如果没有做这些初始化，比如刚才，说明shard没有成功成功，以前也遇到过类似的问题，

May1710:56:48infoshard_short_video_oss_40b_d[62191]:HAVE_EPOLLMay1710:56:48debugshard_short_video_oss_40b_d[62191]:outputqueueokMay1710:56:48debugshard_short_video_oss_40b_d[62191]:outputqueueokMay1710:56:48noticeshard_short_video_oss_40b_d[62191]:incpool,type:MySQL,name:server1,cur:1,min:20,max:20,idle:0[PartitionConnectionPool.cpp,#76]May1710:56:48noticeshard_short_video_oss_40b_d[62191]:incpool,type:MySQL,name:server1,cur:2,min:20,max:20,idle:1[PartitionConnectionPool.cpp,#76]May1710:56:48noticeshard_short_video_oss_40b_d[62191]:incpool,type:MySQL,name:server1,cur:3,min:20,max:20,idle:2[PartitionConnectionPool.cpp,#76]May1710:56:48noticeshard_short_video_oss_40b_d[62191]:incpool,type:MySQL,name:server1,cur:4,min:20,max:20,idle:3[PartitionConnectionPool.cpp,#76]May1710:56:48noticeshard_short_video_oss_40b_d[62191]:incpool,type:MySQL,name:server1,cur:5,min:20,max:20,idle:4[PartitionConnectionPool.cpp,#76]May1710:56:48noticeshard_short_video_oss_40b_d[62191]:incpool,type:MySQL,name:server1,cur:6,min:20,max:20,idle:5[PartitionConnectionPool.cpp,#76]May1710:56:48noticeshard_short_video_oss_40b_d[62191]:incpool,type:MySQL,name:server1,cur:7,min:20,max:20,idle:6[PartitionConnectionPool.cpp,#76]May1710:56:48noticeshard_short_video_oss_40b_d[62191]:incpool,type:MySQL,name:server1,cur:8,min:20,max:20,idle:7[PartitionConnectionPool.cpp,#76]May1710:56:48noticeshard_short_video_oss_40b_d[62191]:incpool,type:MySQL,name:server1,cur:9,min:20,max:20,idle:8[PartitionConnectionPool.cpp,#76]May1710:56:48noticeshard_short_video_oss_40b_d[62191]:incpool,type:MySQL,name:server1,cur:10,min:20,max:20,idle:9[PartitionConnectionPool.cpp,#76]May1710:56:48noticeshard_short_video_oss_40b_d[62191]:incpool,type:MySQL,name:server1,cur:11,min:20,max:20,idle:10[PartitionConnectionPool.cpp,#76]May1710:56:48noticeshard_short_video_oss_40b_d[62191]:incpool,type:MySQL,name:server1,cur:12,min:20,max:20,idle:11[PartitionConnectionPool.cpp,#76]May1710:56:48noticeshard_short_video_oss_40b_d[62191]:incpool,type:MySQL,name:server1,cur:13,min:20,max:20,idle:12[PartitionConnectionPool.cpp,#76]May1710:56:48noticeshard_short_video_oss_40b_d[62191]:incpool,type:MySQL,name:server1,cur:14,min:20,max:20,idle:13[PartitionConnectionPool.cpp,#76]May1710:56:48noticeshard_short_video_oss_40b_d[62191]:incpool,type:MySQL,name:server1,cur:15,min:20,max:20,idle:14[PartitionConnectionPool.cpp,#76]May1710:56:48noticeshard_short_video_oss_40b_d[62191]:incpool,type:MySQL,name:server1,cur:16,min:20,max:20,idle:15[PartitionConnectionPool.cpp,#76]May1710:56:48noticeshard_short_video_oss_40b_d[62191]:incpool,type:MySQL,name:server1,cur:17,min:20,max:20,idle:16[PartitionConnectionPool.cpp,#76]May1710:56:48noticeshard_short_video_oss_40b_d[62191]:incpool,type:MySQL,name:server1,cur:18,min:20,max:20,idle:17[PartitionConnectionPool.cpp,#76]May1710:56:48noticeshard_short_video_oss_40b_d[62191]:incpool,type:MySQL,name:server1,cur:19,min:20,max:20,idle:18[PartitionConnectionPool.cpp,#76]May1710:56:48noticeshard_short_video_oss_40b_d[62191]:incpool,type:MySQL,name:server1,cur:20,min:20,max:20,idle:19[PartitionConnectionPool.cpp,#76]May1710:56:48noticeshard_short_video_oss_40b_d[62191]:incpool,type:MySQL,name:server1_nobinlog,cur:1,min:20,max:20,idle:0[PartitionConnectionPool.cpp,#76]May1710:56:48noticeshard_short_video_oss_40b_d[62191]:incpool,type:MySQL,name:server1_nobinlog,cur:2,min:20,max:20,idle:1[PartitionConnectionPool.cpp,#76]May1710:56:48noticeshard_short_video_oss_40b_d[62191]:incpool,type:MySQL,name:server1_nobinlog,cur:3,min:20,max:20,idle:2[PartitionConnectionPool.cpp,#76]May1710:56:48noticeshard_short_video_oss_40b_d[62191]:incpool,type:MySQL,name:server1_nobinlog,cur:4,min:20,max:20,idle:3[PartitionConnectionPool.cpp,#76]May1710:56:48noticeshard_short_video_oss_40b_d[62191]:incpool,type:MySQL,name:server1_nobinlog,cur:5,min:20,max:20,idle:4[PartitionConnectionPool.cpp,#76]May1710:56:48noticeshard_short_video_oss_40b_d[62191]:incpool,type:MySQL,name:server1_nobinlog,cur:6,min:20,max:20,idle:5[PartitionConnectionPool.cpp,#76]May1710:56:48noticeshard_short_video_oss_40b_d[62191]:incpool,type:MySQL,name:server1_nobinlog,cur:7,min:20,max:20,idle:6[PartitionConnectionPool.cpp,#76]May1710:56:48noticeshard_short_video_oss_40b_d[62191]:incpool,type:MySQL,name:server1_nobinlog,cur:8,min:20,max:20,idle:7[PartitionConnectionPool.cpp,#76]May1710:56:48noticeshard_short_video_oss_40b_d[62191]:incpool,type:MySQL,name:server1_nobinlog,cur:9,min:20,max:20,idle:8[PartitionConnectionPool.cpp,#76]May1710:56:48noticeshard_short_video_oss_40b_d[62191]:incpool,type:MySQL,name:server1_nobinlog,cur:10,min:20,max:20,idle:9[PartitionConnectionPool.cpp,#76]May1710:56:48noticeshard_short_video_oss_40b_d[62191]:incpool,type:MySQL,name:server1_nobinlog,cur:11,min:20,max:20,idle:10[PartitionConnectionPool.cpp,#76]May1710:56:48noticeshard_short_video_oss_40b_d[62191]:incpool,type:MySQL,name:server1_nobinlog,cur:12,min:20,max:20,idle:11[PartitionConnectionPool.cpp,#76]May1710:56:48noticeshard_short_video_oss_40b_d[62191]:incpool,type:MySQL,name:server1_nobinlog,cur:13,min:20,max:20,idle:12[PartitionConnectionPool.cpp,#76]May1710:56:48noticeshard_short_video_oss_40b_d[62191]:incpool,type:MySQL,name:server1_nobinlog,cur:14,min:20,max:20,idle:13[PartitionConnectionPool.cpp,#76]May1710:56:48noticeshard_short_video_oss_40b_d[62191]:incpool,type:MySQL,name:server1_nobinlog,cur:15,min:20,max:20,idle:14[PartitionConnectionPool.cpp,#76]May1710:56:48noticeshard_short_video_oss_40b_d[62191]:incpool,type:MySQL,name:server1_nobinlog,cur:16,min:20,max:20,idle:15[PartitionConnectionPool.cpp,#76]May1710:56:48noticeshard_short_video_oss_40b_d[62191]:incpool,type:MySQL,name:server1_nobinlog,cur:17,min:20,max:20,idle:16[PartitionConnectionPool.cpp,#76]May1710:56:48noticeshard_short_video_oss_40b_d[62191]:incpool,type:MySQL,name:server1_nobinlog,cur:18,min:20,max:20,idle:17[PartitionConnectionPool.cpp,#76]May1710:56:48noticeshard_short_video_oss_40b_d[62191]:incpool,type:MySQL,name:server1_nobinlog,cur:19,min:20,max:20,idle:18[PartitionConnectionPool.cpp,#76]May1710:56:48noticeshard_short_video_oss_40b_d[62191]:incpool,type:MySQL,name:server1_nobinlog,cur:20,min:20,max:20,idle:19[PartitionConnectionPool.cpp,#76]May1710:56:48infoshard_short_video_oss_40b_d[62191]:createlistenport8821successMay1710:56:48infoshard_short_video_oss_40b_d[62191]:serverstart,epoll_prt=0x28e9e20May1710:56:58noticeshard_short_video_oss_40b_d[62191]:request:0proc:0droped:0active:0

做的工作是初始化一个pool，最后以一个port8821创建监听为标志，每次重启都会打印这么的初始化信息，再次进入4455进行建表

没有任何报错，使用8821-8827也能正常查询该表，问题的原因：RDS程序有问题，接下来要让myshard开发看看为什么不能重启了

以上就是如何解决myshard中找不到表[no_hash] 和table [tbl_test]的问题，小编相信有部分知识点可能是我们日常工作会见到或用到的。希望你能通过这篇文章学到更多知识。更多详情敬请关注亿速云行业资讯频道。