Proposed changes to SQL schema

[v1shnya]

Hi Tim,

during the tests of Link-State with Juniper I've seen the following errors related to ls_links:

1. Unreserved bandwidth field should be increased at least to 100 characters
   2022-09-09 10:25:17.324 UTC [3238] ERROR: value too long for type character varying(60)
   2022-09-09 10:25:17.324 UTC [3238] STATEMENT: INSERT INTO ls_links

Current definition = unreserved_bw | character varying(60)

(hash_id,peer_hash_id,base_attr_hash_id,Seq,mt_id,interface_addr,neighbor_addr,isIPv4,protocol,local_link_id,remote_link_id,local_node_hash_id,remote_node_hash_id,admin_group,max_link_bw,max_resv_bw,unreserved_bw,te_def_metric,protection_type,mpls_proto_mask,igp_metric,srlg,name,local_igp_router_id,local_router_id,remote_igp_router_id,remote_router_id,local_asn,remote_asn,peer_node_sid,sr_adjacency_sids,iswithdrawn,timestamp) VALUES

('359ea440ea0c49b065d2ec417096c9cf'::uuid,'3fe8ff10bcbbf6ae103c47515b669133'::uuid,'d3f62be234bec1ac6c082069d57d87d1'::uuid,130,0,'172.27.173.17'::inet,'172.27.173.18'::inet,true::boolean,'IS-IS_L2'::ls_proto,0,0,
'9b98ed559d4be52bbe7ab64c9264f69f'::uuid,
'b1cfe8cfc5cb21336519fe6021a9b15d'::uuid,
671088644,200000,200000,'200000, 200000, 200000, 200000, 200000, 200000, 200000,200000',3000,'',''::ls_mpls_proto_mask,500,'','','1921.6825.5014.0000','0.0.0.0','0101.8425.5002.0000','0.0.0.0',65400,65400,'','',false,'2022-09-09 10:25:16.801633'::timestamp),

2. admin_group should be changed to bigint from int

2022-09-09 12:40:19.734 UTC [5970] ERROR: integer out of range
2022-09-09 12:40:19.734 UTC [5970] STATEMENT: INSERT INTO ls_links

(hash_id,peer_hash_id,base_attr_hash_id,seq,mt_id,interface_addr,neighbor_addr,isIPv4,protocol,local_link_id,remote_link_id,local_node_hash_id,remote_node_hash_id,admin_group,max_link_bw,max_resv_bw,unreserved_bw,te_def_metric,protection_type,mpls_proto_mask,igp_metric,srlg,name,local_igp_router_id,local_router_id,remote_igp_router_id,remote_router_id,local_asn,remote_asn,peer_node_sid,sr_adjacency_sids,iswithdrawn,timestamp) VALUES

('359ea440ea0c49b065d2ec417096c9cf'::uuid,'3fe8ff10bcbbf6ae103c47515b669133'::uuid,'d3f62be234bec1ac6c082069d57d87d1'::uuid,130,0,'172.27.173.17'::inet,'172.27.173.18'::inet,true::boolean,'IS-IS_L2'::ls_proto,0,0,'9b98ed559d4be52bbe7ab64c9264f69f'::uuid,'b1cfe8cfc5cb21336519fe6021a9b15d'::uuid,671088644,200000,200000,'200000, 200000, 200000, 200000, 200000, 200000, 200000, 200000',3000,'',''::ls_mpls_proto_mask,500,'','','1921.6825.5014.0000','0.0.0.0','0101.8425.5002.0000','0.0.0.0',65400,65400,'','',false,'2022-09-09 12:40:18.746014'::timestamp)

[TimEvens]

thank you for the details. For unreserved BW, I'm thinking of switching to array instead of string/varchar(). This will scale better and allow indexing, should that be needed.

For admin group, it is 4 bytes unsigned. int is signed, so I'll change that in the schema. Although, your example above uses admin-group 671088644, which works fine with int. I'm guessing there's another message that is using > 2147483647.

[TimEvens]

I'll make these changes next week.

[TimEvens]

@v1shnya, I have the changes ready, but I would like to double check with your stream. Would it be possible to email me a pcap for the link state session to tim@openbmp.org?

[v1shnya]

Send to you by email BMP stream from Juniper. It's a mix of multiple address-families, can't filter out link-state only.

let me a question, are you going to change API? I plan to offload the data from Kafka to ClickHouse, use Logstash. If yes, it could be great to understand what is changing

[TimEvens]

Updates look good. Thanks for the PCAP. I have updated docker image openbmp/psql-app:2.2.2 to include these updates. The container should upgrade your current DB schema. Have you installed/tested the previous 2.2.2 tag that I had in hub.docker.com? If so, I can provide you instructions to remove that and update to the latest 2.2.2 tag. If not, you can test this out by updating your docker-compose.yml file version for psql-app to use 2.2.2 and then do a OBMP_DATA_ROOT=${OBMP_DATA_ROOT} docker-compose -p obmp up -d to recreate the container.

[TimEvens]

Example of what I see now using your PCAP and updated schema changes.

[v1shnya]

I see two immediate obstacles:

1. /usr/local/openbmp/database/1_base.sql needs to be verified

CREATE TABLE ls_links_log (
… unreserved_bw varchar(60),

2. Check please type conversion

2022-10-12 10:04:23.485 UTC [252] ERROR: type "bigint10000000" does not exist at character 754
2022-10-12 10:04:23.485 UTC [252] STATEMENT: INSERT INTO ls_links (hash_id,peer_hash_id,base_attr_hash_id,seq,mt_id,interface_addr,neighbor_addr,isIPv4,protocol,local_link_id,remote_link_id,local_node_hash_id,remote_node_hash_id,admin_group,max_link_bw,max_resv_bw,unreserved_bw,te_def_metric,protection_type,mpls_proto_mask,igp_metric,srlg,name,local_igp_router_id,local_router_id,remote_igp_router_id,remote_router_id,local_asn,remote_asn,peer_node_sid,sr_adjacency_sids,iswithdrawn,timestamp) VALUES ('4f2ec93139646930530dcb06e38d5b83'::uuid,'f92a9df5363f01994fba5e71e3489a4f'::uuid,'cb5b55ae28185c13583ae5918e29b582'::uuid,112,0,'172.16.4.90'::inet,'172.16.4.89'::inet,true::boolean,'IS-IS_L2'::ls_proto,0,0,'e75fbe48fde2fdc3f9f537d9ebda3584'::uuid,'6265ad5389d1a11e850a1338fbd71ba8'::uuid,1::bigint10000000,0,'0, 0, 0, 0, 0, 0, 0, 0',10000,'',''::ls_mpls_proto_mask,10000,'','','1921.6825.5009.0000','0.0.0.0','1720.1600.0029.0000','0.0.0.0',65400,65400,'','',false,'2022-10-12 10:04:22.972064'::timestamp)

[TimEvens]

Sorry about that. The fresh install schema was missing the update, which has been fixed now. I also have updated the query to include the missing comma. I've ran both upgrade and fresh install tests and it looks good. Can you try this again?

You can do fresh install or you can do an upgrade. For either, you'll need to remove your current psql-app:2.2.2 image. For example:

OBMP_DATA_ROOT=${OBMP_DATA_ROOT} docker-compose -p obmp stop psql-app

docker rm -f obmp-psql-app
docker rmi openbmp/psql-app:2.2.2

rm -f  ${OBMP_DATA_ROOT}/config/psql-app-upgraded.2.2.2
 
OBMP_DATA_ROOT=${OBMP_DATA_ROOT} docker-compose -p obmp up -d

[v1shnya]

Works great! Thank you