如何处理myshard中找不到表 no_hash 和table tbl_test 的问题
发布时间：2022-01-13 22:15:51 所属栏目：MySql教程 来源：互联网
导读：本篇文章给大家分享的是有关如何解决myshard中找不到表[no_hash] 和table [tbl_test]的问题，小编觉得挺实用的，因此分享给大家学习，希望大家阅读完这篇文章后可以有所收获，话不多说，跟着小编一起来看看吧。 在创建表的时候 mysql CREATE TABLE `myshar
|
本篇文章给大家分享的是有关如何解决myshard中找不到表[no_hash] 和table [tbl_test]的问题，小编觉得挺实用的，因此分享给大家学习，希望大家阅读完这篇文章后可以有所收获，话不多说，跟着小编一起来看看吧。 在创建表的时候 mysql> CREATE TABLE `myshard`.`tbl_test` ( -> `col1` VARCHAR(32) DEFAULT NULL , -> `col2` VARCHAR(11) DEFAULT '0' , -> PRIMARY KEY (col1) -> ) COLLATE='utf8_bin' ENGINE=InnoDB DEFAULT CHARSET=utf8 PARTITION KEY col1 SHARDING_RULE no_hash; ERROR 65535 (HY000): Cannot find rule [no_hash] for table [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做一些插入和查询操作 May 17 09:46:44 debug shardadmin_short_video_oss_40_d[46650]: CMySQLQueryServer::handle_mysql: cmd_type[3], cmd_len:[220], cmd[CREATE TABLE `myshard`.`tbl_test` ( `col1` VARCHAR(32) DEFAULT NULL , `col2` VARCHAR(11) DEFAULT '0' , PRIMARY KEY (col1) ) COLLATE='utf8_bin' ENGINE=InnoDB DEFAULT CHARSET=utf8 PARTITION KEY col1 SHARDING_RULE no_hash] May 17 09:46:44 debug shardadmin_short_video_oss_40_d[46650]: entering CQueryProcessor::process. May 17 09:46:44 debug shardadmin_short_video_oss_40_d[46650]: Vincent: CQueryProcessor::process,, it should be 0: ptSQLStruct->m_SysOP=7. May 17 09:46:44 debug shardadmin_short_video_oss_40_d[46650]: Vincent: CQueryProcessor::process, obj found to process it. May 17 09:46:44 info shardadmin_short_video_oss_40_d[46650]: sqlPreView:[select default_timestamp_mode from myshard_table_rule where busi_name='short_video_oss' and machine_room_no=4 and rule_name='no_hash'] May 17 09:46:44 info shardadmin_short_video_oss_40_d[46650]: sqlPreView:[insert into myshard_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', '', '')] May 17 09:46:44 info shardadmin_short_video_oss_40_d[46650]: sqlPreView:[insert into myshard_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')] May 17 09:46:44 info shardadmin_short_video_oss_40_d[46650]: sqlPreView:[insert into myshard_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')] May 17 09:46:44 info shardadmin_short_video_oss_40_d[46650]: sqlPreView:[update myshard_table_columns set COLUMN_KEY='PRI' where busi_name='short_video_oss' and machine_room_no=4 and table_name='tbl_test' and column_name='col1'] May 17 09:46:44 info shardadmin_short_video_oss_40_d[46650]: sqlPreView:[insert into myshard_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)] May 17 09:46:44 info shardadmin_short_video_oss_40_d[46650]: sqlPreView:[select db_name, rule_name from myshard_table where busi_name='short_video_oss' and machine_room_no=4 and table_name = 'tbl_test'] May 17 09:46:44 info shardadmin_short_video_oss_40_d[46650]: sqlPreView:[select default_server,default_timestamp_mode from myshard_table_rule where busi_name='short_video_oss' and machine_room_no=4 and rule_name='no_hash'] May 17 09:46:44 info shardadmin_short_video_oss_40_d[46650]: sqlPreView:[select sharding_rule_name,server,table_rewrite from myshard_sharding_rule where busi_name='short_video_oss' and machine_room_no=4 and rule_name='no_hash'] May 17 09:46:44 info shardadmin_short_video_oss_40_d[46650]: sqlPreView:[select property, value from myshard_server_con_config where busi_name='short_video_oss' and machine_room_no=4 and server_name='server1'] May 17 09:46:44 info shardadmin_short_video_oss_40_d[46650]: Creating connection to schema=myshard, ipAddress=127.0.0.1, user=db_myshard_rw, password=DauK4Ju92x, port=6301 May 17 09:46:44 debug shardadmin_short_video_oss_40_d[46650]: CREATE TABLE myshard.tbl_test(col1 VARCHAR(32) DEFAULT NULL,col2 VARCHAR(11) DEFAULT '0' , __version BIGINT unsigned default 0 , __deleted TINYINT default 0,PRIMARY KEY (col1)) ENGINE = InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_bin#-1946151400 May 17 09:46:44 err shardadmin_short_video_oss_40_d[46650]: [CShardAdmin2::send_msg_to_proxy]shenglong, send_msg_to_proxy[proxy_4_0_0] failed: errcode:65535, errmsg:Cannot find rule [no_hash] for table [tbl_test], strSQL:CREATE TABLE `myshard`.`tbl_test` ( `col1` VARCHAR(32) DEFAULT NULL , `col2` VARCHAR(11) DEFAULT '0' , PRIMARY KEY (col1) ) COLLATE='utf8_bin' ENGINE=InnoDB DEFAULT CHARSET=utf8 PARTITION KEY col1 SHARDING_RULE no_hash May 17 09:46:44 err shardadmin_short_video_oss_40_d[46650]: send_msg_to_proxy sql [CREATE TABLE `myshard`.`tbl_test` ( `col1` VARCHAR(32) DEFAULT NULL , `col2` VARCHAR(11) DEFAULT '0' , PRIMARY KEY (col1) ) COLLATE='utf8_bin' ENGINE=InnoDB DEFAULT CHARSET=utf8 PARTITION KEY col1 SHARDING_RULE no_hash] to [proxy_4_0_0] get a error [65535][Cannot find rule [no_hash] for table [tbl_test]] 与分表规则相关的查询有如下2个 select default_server,default_timestamp_mode from myshard_table_rule where busi_name='short_video_oss' and machine_room_no=4 and rule_name='no_hash'; select sharding_rule_name,server,table_rewrite from myshard_sharding_rule where busi_name='short_video_oss' and machine_room_no=4 and rule_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的报错 May 17 10:25:25 debug shardadmin_short_video_oss_40_d[46650]: drop table myshard.tbl_test#-1946110424 May 17 10:25:25 info shardadmin_short_video_oss_40_d[46650]: sqlPreView:[delete from myshard_table where busi_name='short_video_oss' and machine_room_no=4 and table_name='tbl_test'] May 17 10:25:25 info shardadmin_short_video_oss_40_d[46650]: sqlPreView:[delete from myshard_table_columns where busi_name='short_video_oss' and machine_room_no=4 and table_name='tbl_test'] May 17 10:25:25 info shardadmin_short_video_oss_40_d[46650]: sqlPreView:[delete from myshard_table_statistics where busi_name='short_video_oss' and machine_room_no=4 and table_name='tbl_test'] May 17 10:25:25 info shardadmin_short_video_oss_40_d[46650]: sqlPreView:[select index_name from myshard_index where busi_name='short_video_oss' and machine_room_no=4 and table_name = 'tbl_test'] May 17 10:25:25 info shardadmin_short_video_oss_40_d[46650]: sqlPreView:[delete from myshard_index where busi_name='short_video_oss' and machine_room_no=4 and table_name='tbl_test'] May 17 10:25:25 err shardadmin_short_video_oss_40_d[46650]: [CShardAdmin2::send_msg_to_proxy]shenglong, send_msg_to_proxy[proxy_4_0_0] failed: errcode:65535, errmsg:Table not existed!, strSQL:drop table tbl_test May 17 10:25:25 err shardadmin_short_video_oss_40_d[46650]: send_msg_to_proxy sql [drop table tbl_test] to [proxy_4_0_0] get a error [65535][Table not existed!] May 17 10:25:27 notice shardadmin_short_video_oss_40_d[58881]: request:0 proc:0 droped:0 active:0 May 17 10:25:27 notice shardadmin_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重启是有问题的 May 17 12:06:35 info shard_short_video_oss_210_d[130853]: create listen port 8826 success May 17 12:06:35 info shard_short_video_oss_210_d[130853]: server start May 17 12:07:07 info shard_short_video_oss_210_d[131498]: HAVE_EPOLL May 17 12:07:07 debug shard_short_video_oss_210_d[131498]: output queue ok May 17 12:07:07 debug shard_short_video_oss_210_d[131498]: output queue ok May 17 12:07:07 info shard_short_video_oss_210_d[131498]: create listen port 8826 success May 17 12:07:07 info shard_short_video_oss_210_d[131498]: server start May 17 12:07:14 info shard_short_video_oss_210_d[132027]: HAVE_EPOLL May 17 12:07:14 debug shard_short_video_oss_210_d[132027]: output queue ok May 17 12:07:14 debug shard_short_video_oss_210_d[132027]: output queue ok May 17 12:07:14 info shard_short_video_oss_210_d[132027]: create listen port 8826 success May 17 12:07:14 info shard_short_video_oss_210_d[132027]: server start 所以怀疑是rds程序有问题，于是更换别的rds程序，重启shard端口，会进行大量的初始化，打大量的日志，如果没有做这些初始化，比如刚才，说明shard没有成功成功，以前也遇到过类似的问题， May 17 10:56:48 info shard_short_video_oss_40b_d[62191]: HAVE_EPOLL May 17 10:56:48 debug shard_short_video_oss_40b_d[62191]: output queue ok May 17 10:56:48 debug shard_short_video_oss_40b_d[62191]: output queue ok May 17 10:56:48 notice shard_short_video_oss_40b_d[62191]: inc pool, type: MySQL, name: server1, cur: 1, min: 20, max: 20, idle: 0 [PartitionConnectionPool.cpp, #76] May 17 10:56:48 notice shard_short_video_oss_40b_d[62191]: inc pool, type: MySQL, name: server1, cur: 2, min: 20, max: 20, idle: 1 [PartitionConnectionPool.cpp, #76] May 17 10:56:48 notice shard_short_video_oss_40b_d[62191]: inc pool, type: MySQL, name: server1, cur: 3, min: 20, max: 20, idle: 2 [PartitionConnectionPool.cpp, #76] May 17 10:56:48 notice shard_short_video_oss_40b_d[62191]: inc pool, type: MySQL, name: server1, cur: 4, min: 20, max: 20, idle: 3 [PartitionConnectionPool.cpp, #76] May 17 10:56:48 notice shard_short_video_oss_40b_d[62191]: inc pool, type: MySQL, name: server1, cur: 5, min: 20, max: 20, idle: 4 [PartitionConnectionPool.cpp, #76] May 17 10:56:48 notice shard_short_video_oss_40b_d[62191]: inc pool, type: MySQL, name: server1, cur: 6, min: 20, max: 20, idle: 5 [PartitionConnectionPool.cpp, #76] May 17 10:56:48 notice shard_short_video_oss_40b_d[62191]: inc pool, type: MySQL, name: server1, cur: 7, min: 20, max: 20, idle: 6 [PartitionConnectionPool.cpp, #76] May 17 10:56:48 notice shard_short_video_oss_40b_d[62191]: inc pool, type: MySQL, name: server1, cur: 8, min: 20, max: 20, idle: 7 [PartitionConnectionPool.cpp, #76] May 17 10:56:48 notice shard_short_video_oss_40b_d[62191]: inc pool, type: MySQL, name: server1, cur: 9, min: 20, max: 20, idle: 8 [PartitionConnectionPool.cpp, #76] May 17 10:56:48 notice shard_short_video_oss_40b_d[62191]: inc pool, type: MySQL, name: server1, cur: 10, min: 20, max: 20, idle: 9 [PartitionConnectionPool.cpp, #76] May 17 10:56:48 notice shard_short_video_oss_40b_d[62191]: inc pool, type: MySQL, name: server1, cur: 11, min: 20, max: 20, idle: 10 [PartitionConnectionPool.cpp, #76] May 17 10:56:48 notice shard_short_video_oss_40b_d[62191]: inc pool, type: MySQL, name: server1, cur: 12, min: 20, max: 20, idle: 11 [PartitionConnectionPool.cpp, #76] May 17 10:56:48 notice shard_short_video_oss_40b_d[62191]: inc pool, type: MySQL, name: server1, cur: 13, min: 20, max: 20, idle: 12 [PartitionConnectionPool.cpp, #76] May 17 10:56:48 notice shard_short_video_oss_40b_d[62191]: inc pool, type: MySQL, name: server1, cur: 14, min: 20, max: 20, idle: 13 [PartitionConnectionPool.cpp, #76] May 17 10:56:48 notice shard_short_video_oss_40b_d[62191]: inc pool, type: MySQL, name: server1, cur: 15, min: 20, max: 20, idle: 14 [PartitionConnectionPool.cpp, #76] May 17 10:56:48 notice shard_short_video_oss_40b_d[62191]: inc pool, type: MySQL, name: server1, cur: 16, min: 20, max: 20, idle: 15 [PartitionConnectionPool.cpp, #76] May 17 10:56:48 notice shard_short_video_oss_40b_d[62191]: inc pool, type: MySQL, name: server1, cur: 17, min: 20, max: 20, idle: 16 [PartitionConnectionPool.cpp, #76] May 17 10:56:48 notice shard_short_video_oss_40b_d[62191]: inc pool, type: MySQL, name: server1, cur: 18, min: 20, max: 20, idle: 17 [PartitionConnectionPool.cpp, #76] May 17 10:56:48 notice shard_short_video_oss_40b_d[62191]: inc pool, type: MySQL, name: server1, cur: 19, min: 20, max: 20, idle: 18 [PartitionConnectionPool.cpp, #76] May 17 10:56:48 notice shard_short_video_oss_40b_d[62191]: inc pool, type: MySQL, name: server1, cur: 20, min: 20, max: 20, idle: 19 [PartitionConnectionPool.cpp, #76] May 17 10:56:48 notice shard_short_video_oss_40b_d[62191]: inc pool, type: MySQL, name: server1_nobinlog, cur: 1, min: 20, max: 20, idle: 0 [PartitionConnectionPool.cpp, #76] May 17 10:56:48 notice shard_short_video_oss_40b_d[62191]: inc pool, type: MySQL, name: server1_nobinlog, cur: 2, min: 20, max: 20, idle: 1 [PartitionConnectionPool.cpp, #76] May 17 10:56:48 notice shard_short_video_oss_40b_d[62191]: inc pool, type: MySQL, name: server1_nobinlog, cur: 3, min: 20, max: 20, idle: 2 [PartitionConnectionPool.cpp, #76] May 17 10:56:48 notice shard_short_video_oss_40b_d[62191]: inc pool, type: MySQL, name: server1_nobinlog, cur: 4, min: 20, max: 20, idle: 3 [PartitionConnectionPool.cpp, #76] May 17 10:56:48 notice shard_short_video_oss_40b_d[62191]: inc pool, type: MySQL, name: server1_nobinlog, cur: 5, min: 20, max: 20, idle: 4 [PartitionConnectionPool.cpp, #76] May 17 10:56:48 notice shard_short_video_oss_40b_d[62191]: inc pool, type: MySQL, name: server1_nobinlog, cur: 6, min: 20, max: 20, idle: 5 [PartitionConnectionPool.cpp, #76] May 17 10:56:48 notice shard_short_video_oss_40b_d[62191]: inc pool, type: MySQL, name: server1_nobinlog, cur: 7, min: 20, max: 20, idle: 6 [PartitionConnectionPool.cpp, #76] May 17 10:56:48 notice shard_short_video_oss_40b_d[62191]: inc pool, type: MySQL, name: server1_nobinlog, cur: 8, min: 20, max: 20, idle: 7 [PartitionConnectionPool.cpp, #76] May 17 10:56:48 notice shard_short_video_oss_40b_d[62191]: inc pool, type: MySQL, name: server1_nobinlog, cur: 9, min: 20, max: 20, idle: 8 [PartitionConnectionPool.cpp, #76] May 17 10:56:48 notice shard_short_video_oss_40b_d[62191]: inc pool, type: MySQL, name: server1_nobinlog, cur: 10, min: 20, max: 20, idle: 9 [PartitionConnectionPool.cpp, #76] May 17 10:56:48 notice shard_short_video_oss_40b_d[62191]: inc pool, type: MySQL, name: server1_nobinlog, cur: 11, min: 20, max: 20, idle: 10 [PartitionConnectionPool.cpp, #76] May 17 10:56:48 notice shard_short_video_oss_40b_d[62191]: inc pool, type: MySQL, name: server1_nobinlog, cur: 12, min: 20, max: 20, idle: 11 [PartitionConnectionPool.cpp, #76] May 17 10:56:48 notice shard_short_video_oss_40b_d[62191]: inc pool, type: MySQL, name: server1_nobinlog, cur: 13, min: 20, max: 20, idle: 12 [PartitionConnectionPool.cpp, #76] May 17 10:56:48 notice shard_short_video_oss_40b_d[62191]: inc pool, type: MySQL, name: server1_nobinlog, cur: 14, min: 20, max: 20, idle: 13 [PartitionConnectionPool.cpp, #76] May 17 10:56:48 notice shard_short_video_oss_40b_d[62191]: inc pool, type: MySQL, name: server1_nobinlog, cur: 15, min: 20, max: 20, idle: 14 [PartitionConnectionPool.cpp, #76] May 17 10:56:48 notice shard_short_video_oss_40b_d[62191]: inc pool, type: MySQL, name: server1_nobinlog, cur: 16, min: 20, max: 20, idle: 15 [PartitionConnectionPool.cpp, #76] May 17 10:56:48 notice shard_short_video_oss_40b_d[62191]: inc pool, type: MySQL, name: server1_nobinlog, cur: 17, min: 20, max: 20, idle: 16 [PartitionConnectionPool.cpp, #76] May 17 10:56:48 notice shard_short_video_oss_40b_d[62191]: inc pool, type: MySQL, name: server1_nobinlog, cur: 18, min: 20, max: 20, idle: 17 [PartitionConnectionPool.cpp, #76] May 17 10:56:48 notice shard_short_video_oss_40b_d[62191]: inc pool, type: MySQL, name: server1_nobinlog, cur: 19, min: 20, max: 20, idle: 18 [PartitionConnectionPool.cpp, #76] May 17 10:56:48 notice shard_short_video_oss_40b_d[62191]: inc pool, type: MySQL, name: server1_nobinlog, cur: 20, min: 20, max: 20, idle: 19 [PartitionConnectionPool.cpp, #76] May 17 10:56:48 info shard_short_video_oss_40b_d[62191]: create listen port 8821 success May 17 10:56:48 info shard_short_video_oss_40b_d[62191]: server start, epoll_prt=0x28e9e20 May 17 10:56:58 notice shard_short_video_oss_40b_d[62191]: request:0 proc:0 droped:0 active:0 做的工作是初始化一个pool，最后以一个port8821创建监听为标志，每次重启都会打印这么的初始化信息，再次进入4455进行建表 mysql> CREATE TABLE `myshard`.`tbl_test` ( -> `col1` VARCHAR(32) DEFAULT NULL , -> `col2` VARCHAR(11) DEFAULT '0' , -> PRIMARY KEY (col1) -> ) COLLATE='utf8_bin' ENGINE=InnoDB DEFAULT CHARSET=utf8 PARTITION KEY col1 SHARDING_RULE no_hash; Query OK, 0 rows affected (0.00 sec) 没有任何报错，使用8821-8827也能正常查询该表，问题的原因：RDS程序有问题，接下来要让myshard开发看看为什么不能重启了 以上就是如何解决myshard中找不到表[no_hash] 和table [tbl_test]的问题，小编相信有部分知识点可能是我们日常工作会见到或用到的。 （编辑：温州站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
