kunlun-0.9.2 涉及到表结构变化

[jd-zhang]

Issue migrated from trac ticket # 651

component: DBA GUI | priority: major

2022-05-09 12:52:20: barney created the issue

---

1.添加创建rbr主备切换记录表
CREATE TABLE rbr_consfailover (
id int unsigned NOT NULL AUTO_INCREMENT,
host varchar(255) NOT NULL,
shard_id int NOT NULL,
cluster_id int NOT NULL,
taskid varchar(255) NOT NULL,
step varchar(255) NOT NULL,
new_master_host varchar(255) DEFAULT NULL,
description varchar(255) DEFAULT NULL,
consfailover_msg text,
timestamp timestamp NULL DEFAULT CURRENT_TIMESTAMP,
err_code int DEFAULT NULL,
err_msg varchar(255) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=6679 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

2. 记录node_mgr扫描mysqlerr日志情况表
   CREATE TABLE node_mgr_mysqlerr (
   hostaddr varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
   port int unsigned NOT NULL,
   err_file varchar(8192) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
   scan_timestamp int unsigned NOT NULL,
   scan_filesize int unsigned NOT NULL,
   PRIMARY KEY (hostaddr,port)
   ) ENGINE=InnoDB DEFAULT CHARSET=latin1

3. 记录cluster_mgr 主备关系表
   CREATE TABLE cluster_mgr_nodes (
   id int unsigned NOT NULL AUTO_INCREMENT,
   hostaddr varchar(8192) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
   port int unsigned DEFAULT NULL,
   member_state enum('source','replica') DEFAULT 'replica',
   PRIMARY KEY (id)
   ) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=latin1

CREATE TABLE node_map_master (
id int unsigned NOT NULL AUTO_INCREMENT,
cluster_id int NOT NULL,
node_host varchar(255) NOT NULL,
master_host varchar(255) NOT NULL,
master_uuid varchar(1024) NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=3

4. 需要添加字段的表
   CREATE TABLE shard_nodes (
   id int unsigned NOT NULL AUTO_INCREMENT,
   ro_weight smallint DEFAULT '0',
   hostaddr varchar(8192) NOT NULL,
   port smallint unsigned NOT NULL,
   user_name varchar(64) NOT NULL,
   passwd varchar(120) NOT NULL,
   shard_id int unsigned NOT NULL,
   db_cluster_id int unsigned NOT NULL,
   svr_node_id int unsigned NOT NULL,
   when_created timestamp NULL DEFAULT CURRENT_TIMESTAMP,
   master_priority smallint NOT NULL DEFAULT '1',
   status enum('creating','inactive','active') DEFAULT 'creating',
   cpu_cores smallint unsigned NOT NULL DEFAULT '0',
   initial_storage_GB int unsigned NOT NULL DEFAULT '0',
   max_storage_GB int unsigned NOT NULL DEFAULT '0',
   innodb_buffer_pool_MB int unsigned NOT NULL DEFAULT '0',
   rocksdb_buffer_pool_MB int unsigned NOT NULL DEFAULT '0',
   extra_props text,
   sync_state enum('fsync','async') DEFAULT 'fsync',
   member_state enum('source','replica') DEFAULT 'replica',
   replica_delay int NOT NULL,
   backup_node enum ('OFF', 'ON') DEFAULT 'OFF',
   PRIMARY KEY (id),
   UNIQUE KEY hostaddr_port_svrnodeid_uniq (hostaddr(512),port,svr_node_id),
   KEY db_cluster_id (db_cluster_id),
   KEY svr_node_id (svr_node_id),
   KEY shard_id (shard_id),
   CONSTRAINT shard_nodes_ibfk_1 FOREIGN KEY (db_cluster_id) REFERENCES db_clusters (id),
   CONSTRAINT shard_nodes_ibfk_2 FOREIGN KEY (svr_node_id) REFERENCES server_nodes (id),
   CONSTRAINT shard_nodes_ibfk_3 FOREIGN KEY (shard_id) REFERENCES shards (id)
   ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb3

CREATE TABLE shards (
id int unsigned NOT NULL AUTO_INCREMENT,
name varchar(64) NOT NULL,
when_created timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
num_nodes smallint unsigned NOT NULL,
space_volumn bigint unsigned NOT NULL DEFAULT '0',
num_tablets int unsigned NOT NULL DEFAULT '0',
db_cluster_id int unsigned NOT NULL,
sync_num smallint unsigned NOT NULL DEFAULT '1',
extra_props text,
degrade_conf_state enum('ON','OFF') DEFAULT 'OFF',
degrade_run_state enum('ON','OFF') DEFAULT 'OFF',
degrade_conf_time int NOT NULL,
PRIMARY KEY (id),
UNIQUE KEY name (db_cluster_id,name),
CONSTRAINT shards_ibfk_1 FOREIGN KEY (db_cluster_id) REFERENCES db_clusters (id)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb3

CREATE TABLE meta_db_nodes (
id bigint unsigned NOT NULL AUTO_INCREMENT,
hostaddr varchar(8192) NOT NULL,
port smallint unsigned NOT NULL,
user_name varchar(64) NOT NULL,
passwd varchar(120) NOT NULL,
master_priority smallint NOT NULL DEFAULT '1',
member_state enum('source','replica') DEFAULT 'replica',
sync_state enum('fsync','async') DEFAULT 'fsync',
degrade_conf_state enum('ON','OFF') DEFAULT 'OFF',
degrade_run_state enum('ON','OFF') DEFAULT 'OFF',
replica_delay int NOT NULL,
degrade_conf_time int NOT NULL,
sync_num smallint unsigned NOT NULL DEFAULT '1',
nodemgr_bin_path varchar(8192) DEFAULT NULL,
UNIQUE KEY id (id)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb3

CREATE TABLE server_nodes (
id int unsigned NOT NULL AUTO_INCREMENT,
hostaddr varchar(8192) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
dc_id int unsigned DEFAULT NULL,
rack_id varchar(256) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
comp_datadir varchar(8192) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
datadir varchar(8192) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
logdir varchar(8192) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
wal_log_dir varchar(8192) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
total_mem int unsigned DEFAULT '0',
total_cpu_cores int unsigned DEFAULT '0',
svc_since timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
nodemgr_port int DEFAULT NULL,
nodemgr_bin_path text,
extra_props text,
node_stats enum('running','idle','dead') DEFAULT 'running',
PRIMARY KEY (id),
UNIQUE KEY hostaddr_dcid_uniq (hostaddr(512),dc_id),
KEY dc_id (dc_id),
CONSTRAINT server_nodes_ibfk_1 FOREIGN KEY (dc_id) REFERENCES data_centers (id)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1

kunlun_sysdb库下
CREATE TABLE cluster_info (
id int NOT NULL AUTO_INCREMENT,
cluster_name varchar(30) DEFAULT NULL,
shard_name varchar(30) DEFAULT NULL,
leader_term int DEFAULT 0,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

[jd-zhang]

2022-05-11 11:46:41: barney edited the issue description

[jd-zhang]

2022-05-11 14:40:21: barney edited the issue description

[jd-zhang]

2022-05-11 14:46:32: barney edited the issue description

[jd-zhang]

2022-05-11 14:56:30: barney edited the issue description

[jd-zhang]

2022-05-11 16:11:01: barney edited the issue description

[jd-zhang]

2022-05-13 09:38:45: barney edited the issue description

[jd-zhang]

2022-05-13 12:10:01: barney edited the issue description

[jd-zhang]

2022-05-13 12:13:21: barney edited the issue description

[jd-zhang]

2022-05-18 14:43:07: barney edited the issue description

[jd-zhang]

2022-05-18 15:15:19: barney edited the issue description

[jd-zhang]

2022-05-19 19:01:44: barney edited the issue description

[jd-zhang]

2022-05-19 19:10:37: barney edited the issue description

[jd-zhang]

2022-05-24 18:26:42: zhangjindong@zettadb.com commented

---

Tue May 24 17:57:51 2022 tid:0x6af1 [ERROR] [/home/kunlun/releasebuild/node_mgr/src/instance_info.cc:247 get_meta_instance]: metadata db query:[select port,user_name,passwd,datadir from meta_db_nodes where hostaddr='192.168.0.143'] failed: execute query failed: Unknown column 'datadir' in 'field list', error number: 1054, sql: select port,user_name,passwd,datadir from meta_db_nodes where hostaddr='192.168.0.143'

[jd-zhang]

2022-05-24 18:26:58: zhangjindong@zettadb.com changed owner from winter to barney