Advice on the type of JOIN I should use in this scenario using MySQL Database

I have 3 sections as follows;

  1. TIER I
  2. TIER II
  3. TIER III

Each having tables as below

  1. Objectives
  2. Performance Measures which references Objectives
  3. Performance Actual which references Performance Measure

The Objectives of Tier II references those of Tier I & those of Tier III references those of Tier II.

My requirement I want to get Tier II Performance from Tier III performance, and also get Tier I performance as from Tier II performance.

What I have Tried

SELECT   d_o.ObjectiveName,   ia.Q1 AS 'Q1Actual',   ia.Q2 AS 'Q2Actual',   ia.Q3 AS 'Q3Actual',   ia.Q4 AS 'Q4Actual',   i_o.ObjectiveName AS 'Individual Objective',   fy.FY AS 'FY',   AVG(ia.Q1),   dpm.DeptMeasureId,   dpm.Q1,   ia.* FROM   individual_appraisal ia   LEFT JOIN individual_pm ipm on ia.MeasureId = ipm.MeasureId   LEFT JOIN individual_objectives i_o ON ipm.IndObjectiveId = i_o.IndObjectiveId   LEFT JOIN department_objectives d_o ON i_o.DeptObjectiveId = d_o.DeptObjectiveId   LEFT JOIN department_pm dpm ON dpm.DeptObjectiveId = d_o.DeptObjectiveId   LEFT JOIN tier_one_objectives t1_o ON d_o.TierOneObjectiveId = t1_o.TierOneObjectiveId   LEFT JOIN financial_years fy ON t1_o.FYID = fy.FYID WHERE   i_o.StaffUser = 'staffuserName...' GROUP BY   dpm.DeptMeasureId 

Note I have attached a database diagram for the same to better understand the question. Database Diagram for the above Illustration.

Your kind help is highly appreciated.

How does Blink work versus a readied action in this PvP scenario?

Bit of an odd one but I want rulings for this scenario:


Yes this is PVP and it ending up been fun at the table, and no one was upset.


Playing a Level 7 college of sword Bard with a Homemade Magic item that gives me access to the Blink Spell.

Fighting a Level 7 Paladin who is a new party member and wants to test their skills.

We are sparing with each other, the goal is first to score three hits or to KO the opponent.

During the fight, the Bard casts Blink, ends their turn, the roll is lucky – he gets a 15 and enters the ethereal plane.

The Paladin not knowing what to do ready’s an action to attack when they reappear.

On the Bards turn, the Bard reeappears and attacks. The Bard is a dual-wielder and has multi-attack.

The DM rules that the Bard gets their first attack, then the Paladin gets their ready action to attack back. After that, the Bard can then take their second attack, and then their bonus attack, if they wish.

Note that the Paladin had cast a spell a few turns ago that on their next hit the target has to pass a Wis-save or become frightened of them – I can’t remember the spell or ability

The first attack by the Bard is a miss.

The Paladin hits back and misses as well.

The Paladin uses a second Attack which the DM allows them before the Bard gets theirs and hits, the Bard fails their save-throw, is now frightened and at disadvantage.

The Bards second attack misses at disadvantage.

The Bards off-hand-attack misses at disadvantage.

The Bard rolls their blink, but the roll is low, so they stay.

The Paladin takes two attacks and both hit, accumulating three hits and is declared the winner.

We laugh and move on.

I think it should have gone different. The Bard should have been able to use all three of their attacks before the Paladin got any, and the Paladin should have only been able to attack once – Since a second attack should have only have been on their turn.

It might have made no difference, but the attacks could still have missed. I felt that the bard, who used a smart tactic of Blink – using their speed over the Paladins strength, was punished by the DM.

Did my Dm make a correct ruling?

OAuth2, SAML, OpenID connect – Which one to use for my scenario?

I work for a company where we give customer (hundreds/thousands of users) access to 2 sites. One owned by a 3rd party SaaS and one owned by us. Customers spend alot of time registering for both sites and we also spend alot of time removing accounts when customers no longer need access.

I would like users to register for Site A. After successful authentication; a user can click on a link within the site to access Site B but without the user entering credentials. I want Site A identity to be used to access site B and its resources. I do not need site B resources to be presented on Site A site, but simply allow users to access site B if already authenticated to site A.

Users may have different roles on site B.

What is my best option? Oauth2 sounds like a good option. But will it satisfy my requirement above?

Who will manage the authorisation server? I presume Site B?

Thank you.

What a malicious website can do in the worst scenario on a upgraded system [closed]

I use last Debian stable (buster as June 2020).

  • system upgraded everyday (and browser addons updated automatically)
  • Firefox 68.9.0esr (64 bits) (the one from apt package system)
  • decent hardware (less than 5 years old)
  • Debian security upgrade enabled

I’m aware of security concerns, I…

  • verify (before clicking a HTTP link) if the link looks like example.org, but are in fact example.org.random.tracker.io by example (I take care about phising and tracking)
  • take care of untrusted X509 certificates for https websites
  • avoid using non trusted Firefox addons
  • never open suspicious files in web or mails
  • don’t use weak passwords (and I don’t use the same on 2 websites)
  • never run Firefox as root (who do this ?)
  • use httpsEverywhere, uBlock-Origin, Ghostery, Decentraleyes Firefox addons

So my question:

  • what is the risk of opening a malicious website (if not in google safe browsing DB) ? What it can do, the worst way, apart phishing website ? (I guess crypto-mining at least, exploit of Firefox vulnerability…)

Looking for a specific Call of Cthulhu scenario

Around a month ago, I’ve read somewhere about a scenario, which right now I can’t seem to find.

All I remember, that it’s setting was either in England or in Scotland, and it has two key antagonists. If I am not mistaken, the hook is that a dilettante asks us to investigate his missing daughter, but that’s not 100% info, maybe I am mixing things up already in my head. The other things I know about it are:

I would be really grateful if anyone could point me to the name or the book of this scenario:)

Lower bound and worst case scenario

We know that the lower bound is the minimum amount of work needed to solve a problem. So for a given problem say x it has the best algorithm ( the most efficient algorithm to solve this problem ) say algorithm y, then the lower bound efficiency calculated from this algorithm y is the least time this problem x can be solved through . So why do we calculate the lower bound efficiency for this algorithm on the worst case input ? why not on the best case input ? I mean lower bound is the minimum amount of work which therefore occurs on the best case scenario . Every time I see a decision tree algorithm problem to solve the lower bound of some sorting algorithm , the usual word is always mentioned “the worst case lower bound is blah blah blah” which confuse me so much ! Someone please fix my understanding 🙁 .

MySQL Split-Brain Scenario

So basically a Fault-Tolerant InnoDB Cluster has 3 nodes. In my case I have the same setup as recommended, then suddenly the Master Node(Primary) crashed then when the Master Node(Primary) trying to join in the cluster again, this what we called “Split Brain” happened I have now 2 Node with R/W and 1 R/O. Surprisingly my app can write to this 2 R/W node knowing that I have MySQL ROUTER that the app can connect to. Can MySQL Router caused this error? or a bug?

Understanding INSERT … ON DUPLICATE KEY UPDATE deadlock scenario

I’m trying to understand a scenario where an INSERT ... ON DUPLICATE KEY UPDATE statement is causing deadlocks under high concurrency.

The two tables in question:

hosts:

        "CREATE TABLE `hosts` (" +         "`id` int(10) unsigned NOT NULL AUTO_INCREMENT," +         "`osquery_host_id` varchar(255) NOT NULL," +         "`created_at` timestamp DEFAULT CURRENT_TIMESTAMP," +         "`updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP," +         "`deleted_at` timestamp NULL DEFAULT NULL," +         "`deleted` tinyint(1) NOT NULL DEFAULT FALSE," +         "`detail_update_time` timestamp NULL DEFAULT NULL," +         "`node_key` varchar(255) DEFAULT NULL," +         "`host_name` varchar(255) NOT NULL DEFAULT ''," +         "`uuid` varchar(255) NOT NULL DEFAULT ''," +         "`platform` varchar(255) NOT NULL DEFAULT ''," +         "`osquery_version` varchar(255) NOT NULL DEFAULT ''," +         "`os_version` varchar(255) NOT NULL DEFAULT ''," +         "`build` varchar(255) NOT NULL DEFAULT ''," +         "`platform_like` varchar(255) NOT NULL DEFAULT ''," +         "`code_name` varchar(255) NOT NULL DEFAULT ''," +         "`uptime` bigint(20) NOT NULL DEFAULT 0," +         "`physical_memory` bigint(20) NOT NULL DEFAULT 0," +         "`cpu_type` varchar(255) NOT NULL DEFAULT ''," +         "`cpu_subtype` varchar(255) NOT NULL DEFAULT ''," +         "`cpu_brand` varchar(255) NOT NULL DEFAULT ''," +         "`cpu_physical_cores` int NOT NULL DEFAULT 0," +         "`cpu_logical_cores` int NOT NULL DEFAULT 0," +         "`hardware_vendor` varchar(255) NOT NULL DEFAULT ''," +         "`hardware_model` varchar(255) NOT NULL DEFAULT ''," +         "`hardware_version` varchar(255) NOT NULL DEFAULT ''," +         "`hardware_serial` varchar(255) NOT NULL DEFAULT ''," +         "`computer_name` varchar(255) NOT NULL DEFAULT ''," +         "`primary_ip_id` INT(10) UNSIGNED DEFAULT NULL, " +         "PRIMARY KEY (`id`)," +         "UNIQUE KEY `idx_host_unique_nodekey` (`node_key`)," +         "UNIQUE KEY `idx_osquery_host_id` (`osquery_host_id`)," +         "FULLTEXT KEY `hosts_search` (`host_name`)" +         ") ENGINE=InnoDB DEFAULT CHARSET=utf8;", 

networks_interfaces:

        "CREATE TABLE `network_interfaces` (" +         "`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT," +         "`host_id` INT(10) UNSIGNED NOT NULL," +         "`mac` varchar(255) NOT NULL DEFAULT ''," +         "`ip_address` varchar(255) NOT NULL DEFAULT ''," +         "`broadcast` varchar(255) NOT NULL DEFAULT ''," +         "`ibytes` BIGINT NOT NULL DEFAULT 0," +         "`interface` VARCHAR(255) NOT NULL DEFAULT ''," +         "`ipackets` BIGINT NOT NULL DEFAULT 0," +         "`last_change` BIGINT NOT NULL DEFAULT 0," +         "`mask` varchar(255) NOT NULL DEFAULT ''," +         "`metric` INT NOT NULL DEFAULT 0," +         "`mtu` INT NOT NULL DEFAULT 0," +         "`obytes` BIGINT NOT NULL DEFAULT 0," +         "`ierrors` BIGINT NOT NULL DEFAULT 0," +         "`oerrors` BIGINT NOT NULL DEFAULT 0," +         "`opackets` BIGINT NOT NULL DEFAULT 0," +         "`point_to_point` varchar(255) NOT NULL DEFAULT ''," +         "`type` INT NOT NULL DEFAULT 0," +         "PRIMARY KEY (`id`), " +         "FOREIGN KEY `idx_network_interfaces_hosts_fk` (`host_id`) " +         "REFERENCES hosts(id) " +         "ON DELETE CASCADE, " +         "FULLTEXT KEY `ip_address_search` (`ip_address`)," +         "UNIQUE KEY `idx_network_interfaces_unique_ip_host_intf` (`ip_address`, `host_id`, `interface`)" +         ") ENGINE=InnoDB DEFAULT CHARSET=utf8;", 

Lastest deadlock info:

------------------------ LATEST DETECTED DEADLOCK ------------------------ 2020-01-20 00:09:06 0x2b033abd2700 *** (1) TRANSACTION: TRANSACTION 78516922, ACTIVE 0 sec inserting mysql tables in use 1, locked 1 LOCK WAIT 5 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 2 MySQL thread id 286926, OS thread handle 47297573750528, query id 1045761878 10.107.51.236 username update INSERT INTO network_interfaces (                         host_id,                         mac,                         ip_address,                         broadcast,                         ibytes,                         interface,                         ipackets,                         last_change,                         mask,                         metric,                         mtu,                         obytes,                         ierrors,                         oerrors,                         opackets,                         point_to_point,                         type                 ) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)                 ON DUPLICATE KEY UPDATE                         id = LAST_INSERT_ID(id),                         mac = VALUES(mac),                         broadcast = VALUES(broadcast),                         ibytes = VALUES(ibytes),                         ipackets = VALUES(ipackets),                         last_change = VALUES(last_change),                         mask = VALUES(mask),                         metric = VALUES(metric),                         mtu = VALUES(mtu),                         obytes = VALUES(obytes),                         ierrors = VALUES(ierrors),                         oerrors = VALUES(oerrors),                         opackets = VALUES(opackets),                         point_to_point = VALUES(point_to_point),                         type = VALUES(type) *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 258 page no 2729 n bits 408 index FTS_DOC_ID_INDEX of table `kolide`.`network_interfaces` trx id 78516922 lock_mode Xinsert intention waiting Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0  0: len 8; hex 73757072656d756d; asc supremum;;  *** (2) TRANSACTION: TRANSACTION 78516915, ACTIVE 0 sec inserting mysql tables in use 1, locked 1 18 lock struct(s), heap size 1136, 33 row lock(s), undo log entries 12 MySQL thread id 281276, OS thread handle 47292870371072, query id 1045761879 10.107.78.241 username update INSERT INTO network_interfaces (                         host_id,                         mac,                         ip_address,                         broadcast,                         ibytes,                         interface,                         ipackets,                         last_change,                         mask,                         metric,                         mtu,                         obytes,                         ierrors,                         oerrors,                         opackets,                         point_to_point,                         type                 ) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)                 ON DUPLICATE KEY UPDATE                         id = LAST_INSERT_ID(id),                         mac = VALUES(mac),                         broadcast = VALUES(broadcast),                         ibytes = VALUES(ibytes),                         ipackets = VALUES(ipackets),                         last_change = VALUES(last_change),                         mask = VALUES(mask),                         metric = VALUES(metric),                         mtu = VALUES(mtu),                         obytes = VALUES(obytes),                         ierrors = VALUES(ierrors),                         oerrors = VALUES(oerrors),                         opackets = VALUES(opackets),                         point_to_point = VALUES(point_to_point),                         type = VALUES(type) *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 258 page no 2729 n bits 408 index FTS_DOC_ID_INDEX of table `kolide`.`network_interfaces` trx id 78516915 lock_mode X Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0  0: len 8; hex 73757072656d756d; asc supremum;;  *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 258 page no 2729 n bits 408 index FTS_DOC_ID_INDEX of table `kolide`.`network_interfaces` trx id 78516915 lock_mode Xinsert intention waiting Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0  0: len 8; hex 73757072656d756d; asc supremum;;  *** WE ROLL BACK TRANSACTION (1) 

The program starts a transaction, updates a host row, and uses that same transaction in a loop through all the host’s interfaces and issues a INSERT…ON DUPLICATE statement for each interface. As I understand it, because the transaction starts with a UPDATE (exclusive) lock on the host table, another transaction cannot be updating the same host. So I don’t think this is a scenario where two connections are trying to update the same set of host interfaces (this could easily result in a deadlock).

I think this might be due to different host updates competing over the AUTO_INCREMENT index of network_interfaces? I just don’t understand how, even after staring at the MySQL docs about locks. I understand that transaction 1 is waiting on an exclusive insert lock, transaction 2 has an exclusive lock and is also waiting on an exclusive insert lock. What I’m specifically not understanding is why TRANSACTION 2 has the exclusive lock lock_mode X to begin with.

What preventive measures can be taken to get over this scenario with minimal data loss

I am SQL server dba and came across weird scenario our cluster which had 10 nodes 5 primary 5 secondary each nodes had SQL role…. example Ams1pd11 to Ams1pd15 are primary.. and Ams3 side from pd11 to of 15 was secondary… in this scenario whole cluster behaved abnormally going 2 nodes down and all nodes availability groups were inaccessible leading to multicustomer outage.

Explanation of the real-time scenario as it started when I was in shift….

Ams1pd12 got down which was hosting primary server A So automatically the role A was failed over on best possible node and he choose Ams1pd11.

But Ams1pd11 already had one role on it e.g B Now Ams1pd11 was hosting both A and B as Ams1pd12 got down

As both primary roles were on one node it was a risk so to balance I failed over B node to one of secondary node Ams3 side.. now it was balanced and I was just about to investigate on Ams1pd12 why it got down and all…

But suddenly Ams1pd11 node also went down and the role was not failed over and stucked over there…

So now 2 nodes out of 10 were down and one role was stuck so the customers on that role were impacted….

We were troubleshooting with Microsoft for same and noticed that other nodes were showing up by the availability group for alll the nodes were stuck and they were not opening and stucked in expanding state….

So this way all the nodes on that cluster were impacted and as due to this our backups stopped.. There was data loss…

The one stucked role and customer on that node faced only 15 mins data loss as the service was down fr us and them too at the same time, but

The nodes which were showing up and AG groups were inaccessible weird thing was for the already logged in users they were able to change modify the data… It was only refusing new connections… But old connection were still active…

So if issue started and backup stopped at 7 am most of the customers were able to access the database till 6pm so there was 11 hours of data loss….

And it took lot of efforts to restore databases manually… Those online nodes were easy to recover as we just had to attach the dbs as we migrated data and log files bt for stucked role databases we had to manually recover them.

Please suggest the strategies which will be best to be followed in this type of disaster how we can obtain speedy recovery.

Scenario Categorization with Deep Packet Inspection – Intrusion Detection

I am doing some research on intrusion detection systems (IDS) and deep packet inspections (DPI). Assuming a system where values are forwarded to a validation system and the validation system does validate (check anomalies, e.g. by statistics, machine learning, etc.) the forwarded data.

  • Is the validation process called DPI, even when only the payload is investigated?
  • Is the validation process called network-based intrusion detection or is called something different?