-- ###########################
-- RUNNING stop_on_error.sql (with ./cluster_sql.sh)
stop_on_error;


warning



CLI command sent: stop_on_error

warning
CLI command sent: stop_on_error

-- ###########################
-- RUNNING cd ../02_sql; ./cluster.sh init;

  
  /*
  killing cluster (pid:21812
  21816
  21817
  21818
  21819
  21820)
  done
  cluster is not running on this server
  init -> true
  foreground -> true
  [0] CLUSTER master port: 2220 (0)
  [0] CLUSTER slave count: 2 (0)
  [0] FIRST_NODE ____comment#5____
  [0] FIRST_NODE connecting to localhost : 2219 (0)
  [0] SECOND_NODE ____comment#6____
  [0] SECOND_NODE connecting to localhost : 2219 (0)
  [0] SECOND_NODE  -> authentication; (0)
  [0] FIRST_NODE  -> authentication; (0)
  [0] SECOND_NODE  <- data ( 104 ms ) (0)
  [0] SECOND_NODE  -> exec show_data_model(); (0)
  [0] SECOND_NODE  <- data ( 3 ms ) (0)
  [0] FIRST_NODE  <- data ( 203 ms ) (0)
  [0] FIRST_NODE  -> exec show_data_model(); (0)
  [0] FIRST_NODE  <- data ( 2 ms ) (0)
  [0] FIRST_NODE col_type t_item_id exec sql #1 (0)
  [0] FIRST_NODE  -> SELECT_ALL item_id FROM v_item_tags GROUP BY item_id; (0)
  [0] SECOND_NODE col_type t_item_id exec sql #1 (0)
  [0] SECOND_NODE  -> SELECT_ALL item_id FROM v_item_tags GROUP BY item_id; (0)
  [0] FIRST_NODE  <- data ( 4 ms ) (0)
  [0] FIRST_NODE #2 len: 14 (0)
  [0] FIRST_NODE #3 (0)
  [0] SECOND_NODE  <- data ( 4 ms ) (0)
  [0] SECOND_NODE #2 len: 14 (0)
  [0] SECOND_NODE #3 (0)
  [0] CLUSTER colType OK t_item_id 1/9 (0)
  [0] FIRST_NODE col_type sys#type#items#art_label exec sql #1 (0)
  [0] FIRST_NODE  -> SELECT_ALL art_label FROM v_items GROUP BY art_label; (0)
  [0] SECOND_NODE col_type sys#type#items#art_label exec sql #1 (0)
  [0] SECOND_NODE  -> SELECT_ALL art_label FROM v_items GROUP BY art_label; (0)
  [0] FIRST_NODE  <- data ( 4 ms ) (0)
  [0] FIRST_NODE #2 len: 12 (0)
  [0] FIRST_NODE #3 (0)
  [0] SECOND_NODE  <- data ( 3 ms ) (0)
  [0] SECOND_NODE #2 len: 12 (0)
  [0] SECOND_NODE #3 (0)
  [0] CLUSTER colType OK sys#type#items#art_label 2/9 (0)
  [0] FIRST_NODE col_type t_dept_id exec sql #1 (0)
  [0] FIRST_NODE  -> SELECT_ALL dept FROM v_items GROUP BY dept; (0)
  [0] SECOND_NODE col_type t_dept_id exec sql #1 (0)
  [0] SECOND_NODE  -> SELECT_ALL dept FROM v_items GROUP BY dept; (0)
  [0] FIRST_NODE  <- data ( 4 ms ) (0)
  [0] FIRST_NODE #2 len: 12 (0)
  [0] FIRST_NODE #3 (0)
  [0] SECOND_NODE  <- data ( 4 ms ) (0)
  [0] SECOND_NODE #2 len: 12 (0)
  [0] SECOND_NODE #3 (0)
  [0] CLUSTER colType OK t_dept_id 3/9 (0)
  [0] FIRST_NODE col_type t_customer_id exec sql #1 (0)
  [0] FIRST_NODE  -> SELECT_ALL customer_id FROM v_sales GROUP BY customer_id; (0)
  [0] SECOND_NODE col_type t_customer_id exec sql #1 (0)
  [0] SECOND_NODE  -> SELECT_ALL customer_id FROM v_sales GROUP BY customer_id; (0)
  [0] FIRST_NODE  <- data ( 3 ms ) (0)
  [0] FIRST_NODE #2 len: 13 (0)
  [0] FIRST_NODE #3 (0)
  [0] SECOND_NODE  <- data ( 4 ms ) (0)
  [0] SECOND_NODE #2 len: 13 (0)
  [0] SECOND_NODE #3 (0)
  [0] CLUSTER colType OK t_customer_id 4/9 (0)
  [0] FIRST_NODE col_type t_date exec sql #1 (0)
  [0] FIRST_NODE  -> SELECT_ALL sales_date FROM v_sales GROUP BY sales_date; (0)
  [0] SECOND_NODE col_type t_date exec sql #1 (0)
  [0] SECOND_NODE  -> SELECT_ALL sales_date FROM v_sales GROUP BY sales_date; (0)
  [0] FIRST_NODE  <- data ( 4 ms ) (0)
  [0] FIRST_NODE #2 len: 17 (0)
  [0] FIRST_NODE #3 (0)
  [0] SECOND_NODE  <- data ( 4 ms ) (0)
  [0] SECOND_NODE #2 len: 17 (0)
  [0] SECOND_NODE #3 (0)
  [0] CLUSTER colType OK t_date 5/9 (0)
  [0] FIRST_NODE col_type t_customer_info exec sql #1 (0)
  [0] FIRST_NODE  -> SELECT_ALL info FROM v_item_customer_infos GROUP BY info; (0)
  [0] SECOND_NODE col_type t_customer_info exec sql #1 (0)
  [0] SECOND_NODE  -> SELECT_ALL info FROM v_item_customer_infos GROUP BY info; (0)
  [0] FIRST_NODE  <- data ( 4 ms ) (0)
  [0] FIRST_NODE #2 len: 14 (0)
  [0] FIRST_NODE #3 (0)
  [0] SECOND_NODE  <- data ( 4 ms ) (0)
  [0] SECOND_NODE #2 len: 14 (0)
  [0] SECOND_NODE #3 (0)
  [0] CLUSTER colType OK t_customer_info 6/9 (0)
  [0] FIRST_NODE col_type sys#type#customers#customer_name exec sql #1 (0)
  [0] FIRST_NODE  -> SELECT_ALL customer_name FROM v_sales GROUP BY customer_name; (0)
  [0] SECOND_NODE col_type sys#type#customers#customer_name exec sql #1 (0)
  [0] SECOND_NODE  -> SELECT_ALL customer_name FROM v_sales GROUP BY customer_name; (0)
  [0] FIRST_NODE  <- data ( 3 ms ) (0)
  [0] FIRST_NODE #2 len: 12 (0)
  [0] FIRST_NODE #3 (0)
  [0] SECOND_NODE  <- data ( 3 ms ) (0)
  [0] SECOND_NODE #2 len: 12 (0)
  [0] SECOND_NODE #3 (0)
  [0] CLUSTER colType OK sys#type#customers#customer_name 7/9 (0)
  [0] FIRST_NODE col_type sys#type#sales#line_id exec sql #1 (0)
  [0] FIRST_NODE  -> SELECT_ALL line_id FROM v_sales GROUP BY line_id; (0)
  [0] SECOND_NODE col_type sys#type#sales#line_id exec sql #1 (0)
  [0] SECOND_NODE  -> SELECT_ALL line_id FROM v_sales GROUP BY line_id; (0)
  [0] SECOND_NODE  <- data ( 3 ms ) (0)
  [0] SECOND_NODE #2 len: 17 (0)
  [0] SECOND_NODE #3 (0)
  [0] FIRST_NODE  <- data ( 3 ms ) (0)
  [0] FIRST_NODE #2 len: 17 (0)
  [0] FIRST_NODE #3 (0)
  [0] CLUSTER colType OK sys#type#sales#line_id 8/9 (0)
  [0] FIRST_NODE col_type sys#type#item_tags#tag exec sql #1 (0)
  [0] FIRST_NODE  -> SELECT_ALL tag FROM v_item_tags GROUP BY tag; (0)
  [0] SECOND_NODE col_type sys#type#item_tags#tag exec sql #1 (0)
  [0] SECOND_NODE  -> SELECT_ALL tag FROM v_item_tags GROUP BY tag; (0)
  [0] FIRST_NODE  <- data ( 4 ms ) (0)
  [0] FIRST_NODE #2 len: 12 (0)
  [0] FIRST_NODE #3 (0)
  [0] SECOND_NODE  <- data ( 3 ms ) (0)
  [0] SECOND_NODE #2 len: 12 (0)
  [0] SECOND_NODE #3 (0)
  [0] CLUSTER colType OK sys#type#item_tags#tag 9/9 (0)
  */

-- ###########################
-- RUNNING cd ../02_sql; ./cluster.sh bounce;

  
  /*
  cluster is not running on this server
  archiving previous log file
  purging log archive (7 days)
  starting stormbase cluster .. OK: cluster listening on tcp port 2220
  */

-- ###########################
-- RUNNING status.sh

  
  /*
  PID: 25586, CMD: STORMBASE, TCP_PORT: 2219
  PID: 25645, CMD: CLUSTER, TCP_PORT: 2220
  PID: 25649, CMD: CLUSTER, TCP_PORT: 2220
  PID: 25650, CMD: CLUSTER, TCP_PORT: 2220
  */

-- ###########################
-- RUNNING doc_cluster.sql (with ./cluster_sql.sh)
--

Cluster

SQL steps: SB client -> SB cluster -> send SQL to all SBs of the cluster -> merge results -> reply to SB client

cluster_readme.txt

../02_sql/cluster_readme.txt
##############
## SB cluster
##############
1/ create file cluster.conf, sample here under
# cluster port
TCP_PORT:2220

# node 1
ID1:node1 (optional, defaults to N1)
HOST1:stormbase1_ip
TCP_PORT1:stormbase1_port

# node 12
ID2:node2
HOST2:stormbase2_ip
TCP_PORT2:stormbase2_port

# repartition keys
REPART_COL_TYPES:t_art_site_key,t_art_cinv
OR
REPART_COL_TYPES:/(t_art_site_key|t_art_cinv)/

# #process, defaults to 5
NB_WORKERS:a_number

# if an sql take more that x seconds then execution is cancelled and error 173 is sent, defaults to 100 seconds
SQL_TIMEOUT:a_number_in_seconds

# all SB nodes must have the same data model (otherwise init will return an error), defaults to y
SAME_DATA_MODEL:y/n

# if sql match the regex the sql will be sent to node #1 only, defaults to null
REGEX_FIRST_NODE_ONLY:a_javascript_regex

2/ init cluster
./cluster.sh init
Note. each time a cluster node receives a refresh the cluster must be re-init

3/ start cluster
./cluster start <|foreground>
Note. logs are in cluster.log

##############
## SB with PGSQL driver
##############
1/ create file cluster.conf, sample here under
create a classic SB cluster conf file (maybe with one SB node only)

2/ init companion
./cluster.sh init_pg_companion
PG_COMPANION_PORT:5432
PG_COMPANION_HOST:51.255.89.11
PG_COMPANION_USER:postgres
PG_COMPANION_PWD:toto

3/ start cluster
./cluster start <|foreground>

init cluster

SB cluster will connect to all SB nodes, download data model, and store it under 02_sql/INT_CLU

02_sql> ./cluster.sh init;

../02_sql/cluster.conf
TCP_PORT:2220
ID1:FIRST_NODE
HOST1:localhost
TCP_PORT1:2219
ID2:SECOND_NODE
HOST2:localhost
TCP_PORT2:2219
REGEX_FIRST_NODE_ONLY:/(create|insert|delete|refresh|set)\s/
NB_WORKERS:2
SQL_TIMEOUT:3

start cluster

Start SB cluster in companion mode

02_sql> ./cluster.sh start|bounce;

basics

Cluster of 2 (same) SB

--#SB_CLUSTER debug
select count(*) from v_sales;


COUNT(*)



14

COUNT(*)
14

desc table callback where(1,'sales');


table_name column_count line_count has_delete has_update parent_view_hidden node_id



sales 5 7 n n n FIRST_NODE
sales 5 7 n n n SECOND_NODE

table_name	column_count	line_count	has_delete	has_update	parent_view_hidden	node_id
sales	5	7	n	n	n	FIRST_NODE
sales	5	7	n	n	n	SECOND_NODE

desc

--#SB_CLUSTER desc('parameter')
nothing;


param_name current_value



TCP_PORT 2220
ID1 FIRST_NODE
HOST1 localhost
TCP_PORT1 2219
ID2 SECOND_NODE
HOST2 localhost
TCP_PORT2 2219
REPART_COL_TYPES -
REGEX_FIRST_NODE_ONLY /(create!insert!delete!refresh!set)\s/
NB_WORKERS 2
SQL_TIMEOUT 3
SAME_DATA_MODEL y

param_name	current_value
TCP_PORT	2220
ID1	FIRST_NODE
HOST1	localhost
TCP_PORT1	2219
ID2	SECOND_NODE
HOST2	localhost
TCP_PORT2	2219
REPART_COL_TYPES	-
REGEX_FIRST_NODE_ONLY	/(create!insert!delete!refresh!set)\s/
NB_WORKERS	2
SQL_TIMEOUT	3
SAME_DATA_MODEL	y

--#SB_CLUSTER set('same_data_model','n')
nothing;


response



done

response
done

--#SB_CLUSTER desc('parameter','same_data_model')
nothing;


param_name current_value



SAME_DATA_MODEL n

param_name	current_value
SAME_DATA_MODEL	n

custom.js

../02_sql/custom.js
module.exports = {};

module.exports.fnSetNodes = function (sql, nodeId2Execute) {
    if (sql.match(/(\s|^)v_item_tags(\s|;)/im)) {
        nodeId2Execute["FIRST_NODE"] = false;
    }
    if (sql.match(/(\s|^)v_items(\s|;)/im)) {
        nodeId2Execute["SECOND_NODE"] = false;
    }
}

desc view callback where(1,'v_item_tags');


view_name table_count rel_count is_dirty is_mp_dirty is_partial first_iline mp_rare_count mp_often_count mp_count r_online_count mp_r_online_count node_id



v_item_tags 1 2 n n n 10 0 12 12 0 0 FIRST_NODE
v_item_tags 1 2 n n n 10 0 12 12 0 0 SECOND_NODE

view_name	table_count	rel_count	is_dirty	is_mp_dirty	is_partial	first_iline	mp_rare_count	mp_often_count	mp_count	r_online_count	mp_r_online_count	node_id
v_item_tags	1	2	n	n	n	10	0	12	12	0	0	FIRST_NODE
v_item_tags	1	2	n	n	n	10	0	12	12	0	0	SECOND_NODE

select count(*) from v_item_tags;


COUNT(*)



2

COUNT(*)
2

desc view callback where(1,'v_items');


view_name table_count rel_count is_dirty is_mp_dirty is_partial first_iline mp_rare_count mp_often_count mp_count r_online_count mp_r_online_count node_id



v_items 1 2 n n n 10 0 12 12 0 0 FIRST_NODE
v_items 1 2 n n n 10 0 12 12 0 0 SECOND_NODE

view_name	table_count	rel_count	is_dirty	is_mp_dirty	is_partial	first_iline	mp_rare_count	mp_often_count	mp_count	r_online_count	mp_r_online_count	node_id
v_items	1	2	n	n	n	10	0	12	12	0	0	FIRST_NODE
v_items	1	2	n	n	n	10	0	12	12	0	0	SECOND_NODE

select count(*) from v_items;


COUNT(*)



2

COUNT(*)
2

SB_CLUSTER tag

Use SB_CLUSTER tag to help SB cluster

desc table sales;


table_name column_name column_type col_type_name node_id



sales item_id text t_item_id FIRST_NODE
sales customer_id text t_customer_id FIRST_NODE
sales sales_date text t_date FIRST_NODE
sales sales_qty number sys#type#sales#sales_qty FIRST_NODE
sales line_id text sys#type#sales#line_id FIRST_NODE
sales item_id text t_item_id SECOND_NODE
sales customer_id text t_customer_id SECOND_NODE
sales sales_date text t_date SECOND_NODE
sales sales_qty number sys#type#sales#sales_qty SECOND_NODE
sales line_id text sys#type#sales#line_id SECOND_NODE

table_name	column_name	column_type	col_type_name	node_id
sales	item_id	text	t_item_id	FIRST_NODE
sales	customer_id	text	t_customer_id	FIRST_NODE
sales	sales_date	text	t_date	FIRST_NODE
sales	sales_qty	number	sys#type#sales#sales_qty	FIRST_NODE
sales	line_id	text	sys#type#sales#line_id	FIRST_NODE
sales	item_id	text	t_item_id	SECOND_NODE
sales	customer_id	text	t_customer_id	SECOND_NODE
sales	sales_date	text	t_date	SECOND_NODE
sales	sales_qty	number	sys#type#sales#sales_qty	SECOND_NODE
sales	line_id	text	sys#type#sales#line_id	SECOND_NODE

--under construction
--#SB_CLUSTER broadcast merge('group,sum')

  --#SB_CLUSTER broadcast merge('group,sum,sum') compute(4,'ratio(line.sum_stk,line.sum_sales)') compute(5,'ratio(365,line.doi)')

REGEX_FIRST_NODE_ONLY

Use SB_CLUSTER tag to help SB cluster

errors management

Timeouts may happen in this mode

continue_on_disconnect;
--this sql is stopped because of timeout
--#SB_CLUSTER js_eval(require("child_process").execSync("sleep 10"); return "abcd")
nothing;


error



173: connection closed

error
173: connection closed

stop_on_disconnect;
--but the cluster is still working (killed thread has been re forked)
select count(*) from v_sales;


COUNT(*)



14

COUNT(*)
14

;

< * | *** >

Cluster

Cluster

cluster_readme.txt

../02_sql/cluster_readme.txt

init cluster

../02_sql/cluster.conf

start cluster

basics

desc

custom.js

../02_sql/custom.js

SB_CLUSTER tag

REGEX_FIRST_NODE_ONLY

errors management