Posts

Showing posts from 2020

How To Extend Selection To The End Of Column Or Entire Row In Excel

When working with a long column or row, you may need to select the whole column or row with data for some purpose, like formatting etc. In this article, we will show you how to extend the selection to the end of the column or the row after selecting the first cell. The first cell need not to be exactly the first cell of the sheet. The first cell means the cell under current selection. Extend selection to the end of column with shortcut key Select the first or any cell of the column and then press the ctrl + shift + down arrow keys simultaneously. Then you can see the selection is extended to the end of current column. Extend selection to the end of row with shortcut key Select the first or any cell of the row and then press the ctrl + shift + next arrow keys (-->) simultaneously. Then you can see the selection is extended to the end of current row. Remember If the first selected cell is empty, it will extend the selection to the first non-empty cell below the selected cell. If the

What is the difference between where and having clause

Where and Having both are used for filter purpose. Then what is the difference between them in sql statement. The below table will summarize the difference between them. WHERE HAVING WHERE Clause is used to filter the records from the table or used while joining more than one table.Only those records will be extracted who are satisfying the specified condition in WHERE clause. HAVING Clause is used to filter the records from the groups based on the given condition in the HAVING Clause. Those groups who will satisfy the given condition will appear in the final result. WHERE Clause can be used without GROUP BY Clause. HAVING Clause cannot be used without GROUP BY Clause. WHERE Clause implements in row operations. HAVING Clause implements in column operation. WHERE Clause cannot contain aggregate function. HAVING Clause can contain aggregate function. WHERE Clause can be used with SELECT, UPDAT

How to setup a Sharded Cluster in MongoDB using an Ubuntu Server 18.04

Image
Introduction MongoDB is a No-SQL, document based database system that scales well horizontally and implements data storage through a key-value system. A popular choice for web applications and websites, MongoDB is easy to implement and access pro-grammatically. MongoDB achieves scaling through a technique known as "sharding". Sharding is the process of writing data across different servers to distribute the read and write load and data storage requirements. MongoDB Sharding Topology Sharding is implemented through three separate components. Each part performs a specific function: Config Server : Each production sharding implementation must contain at least three configuration servers. This is to ensure redundancy and high availability. Config servers are used to store the metadata that links requested data with the shard that contains it. It organizes the data so that information can be retrieved reliably and consistently. Config servers are the brains of your cl

How to suppress warnings during drop procedure in MySQL

To suppress the warnings of NOTE level at session level do the following. Please note that I have shown mysql> prompt only for demonstration purpose. In real you need to execute the required SQL command only like DELIMITER; . mysql> DELIMITER ; mysql> mysql> SET @OLD_SQL_NOTES = @@session.SQL_NOTES; Query OK, 0 rows affected (0.00 sec) mysql> SET SESSION SQL_NOTES = 1; Query OK, 0 rows affected (0.00 sec) Now, let us drop a procedure which does not exist. As you can see a warning message is now visible. mysql> DROP PROCEDURE IF EXISTS Alter_Table_Remove_Column; Query OK, 0 rows affected, 1 warning (0.00 sec) To look at the above warning message, you need to issue the SHOW WARNINGS command. mysql> show warnings; This will produce the following output displaying the warning message. +-------+------+---------------------------------------------+ | Level | Code | Message                                     | +-------+------+------------------------------------------

How Replication transfer events from Master to Slave in MySQL

Image
In MySQL the events (write operations) are transferred from master to slave with the help of threads. Thread Name Runs On Binlog Dump Master IO Slave SQL Slave and in the following chronological order. The slave connects to the master (as a client) using the credentials that were given during setup. This credentials are set by executing CHANGE MASTER TO at slave side. It might be possible that MySQL is storing the credentials in a plain form. Hence it is advisable to create a separate user in Master with privileges that grant it the power of serving the purpose of replication only. IO thread from the slave asks for new events in the master if any. Binlog Dump thread from master checks this and send contents to IO thread if new events are do present. IO thread then writes the new events to the relay logs on slave. SQL thread on the slave then reads these events from relay logs and apply them to the database. This is how

How to set the proxy for APT on Ubuntu

This post is regarding: How to set the proxy for APT on Ubuntu 18.04 , as I have set up that version of Ubuntu in my test machine. You can see if that works for other versions also. APT a.k.a Aptitude will not use the HTTP Proxy environment variables. Instead, it has its own configuration file where you can set your proxy. This tutorial will show you how to set the proxy so that you may be able to install and update packages from remote repos. If you filled in your proxy information during installation, the Apt configuration file would have been automatically updated. However, if you did not, then you must follow the following instructions. Creating an Apt Proxy Conf File Apt loads all configuration files under /etc/apt/apt.conf.d. We can create a configuration specifically for our proxy there, keeping it separate from all other configurations. 1. Create a new configuration file named proxy.conf. sudo touch /etc/apt/apt.conf.d/proxy.conf 2. Open the proxy.conf file

What are the components of a sharded cluster in MongoDB

Image
Sharded Cluster Components A MongoDB sharded cluster consists of the following components: shard : Each shard contains a subset of the sharded data. As of MongoDB 3.6, shards must be deployed as a replica set . mongos : The mongos acts as a query router, providing an interface between client applications and the sharded cluster. Starting in MongoDB 4.4, mongos can support hedged reads to minimize latencies. config servers : Config servers store metadata and configuration settings for the cluster. As of MongoDB 3.4, config servers must be deployed as a replica set (CSRS).  Image courtesy by :  diagrams.net Production Configuration In a production cluster, ensure that data is redundant and that your systems are highly available. Consider the following for a production sharded cluster deployment: Deploy Config Servers as a 3 member replica set Deploy each Shard as a 3 member replica set Deploy one or more mongos routers Replica Set Distribution Where possible, consider deploying one me

What is CAP Theorem

Image
In theoretical computer science, the CAP theorem, also named as Brewer's theorem after computer scientist Eric Brewer, states that it is impossible for a distributed data store or distributed system to simultaneously provide more than two out of the following three guarantees: C onsistency : Every read receives the most recent write or an error. A guarantee that every node in a distributed cluster returns the same, most recent, successful write. Consistency refers to every client having the same view of the data. For this to happen, whenever data is written to one node, it must be instantly forwarded or replicated to all the other nodes in the system before the write is deemed successful to client. Consistency in CAP refers to sequential consistency, a very strong form of consistency. There are various types of consistency models like eventual consistency and strong consistency.  A vailability : Every request receives a (non-error) response, without the guarantee that it contains t

What is idempotent operation

Idempotent, in programming and mathematics, is a property of some operations such that no matter how many times you execute them, you achieve the same result. In programming, idempotent can be a property of many different code elements, including functions, methods, requests and statements. Idempotent is a language-agnostic property: It means the same thing in any programming context. Here’s a simple demonstration of idempotent in HTTP requests: HTTP GET requests are a method of retrieving specified data from a source, such as getting a bank account balance. GET requests are idempotent: Accessing the same data should always be consistent. On the other hand, POST requests are designed to change the target, such as adding a sum to a bank account. As such, a POST request should change the result and that means it’s not idempotent. In computing, an idempotent operation is one that has no additional effect if it is called more than once with the same input parameters. For example, removing

How to truncate the Slow Query Log on AWS RDS MySQL

I was in need to empty the slow_log table in MySQL database inside a Amazon Relational Database System to look into a performance issue. But I got the below error. I was surprised because I had login with the master user of the RDS. mysql> use mysql mysql> truncate table slow_log Error Code: 1044. Access denied for user 'dbroot'@'%' to database 'mysql' On surfing the internet I got this and it resolved my problem. "Amazon RDS will not give you SSH access to its database server. That means you don’t have the opportunity to view any of the log files, even the slow query log, for the database. But RDS provides a way to play with the slow queries. If your slow queries are many then this table will hold too many queries to handle. You cannot truncate the table or delete any row from there. There is only a way to move all the data from this table to another table named ‘slow_log_backup’, making the slow_log table empty. To do so you have to run a stored pro
Back To Top