MongoDB : Capped Collections

There are two types of collections normal collection and capped collection

• In comparison to the normal collection, capped collections are created in advance and are fixed in size 
• Normal collections are created dynamically and automatically grow in size to fit extra data
• These capped collections are designed to consume less space and are rotating that means once allocated space is full, it will start writing from older documents again.
• Below operations are not allowed on a capped collection

• Documents can not be removed
• Updates that make documents to grow in size are not allowed 

Capped collections can not be sharded

Creating Capped collection

As discussed above, capped collection needs to be created explicitly, we will discuss commands for the same

> db.createCollection("my_cap_col1",

... {"capped" : true, "size":100000});

{ "ok" : 1 }

> 

above command creates capped collection with below details
capped collection name : my_cap_col1
fixed size : 100000 byes

same way capped collection can be created with limit on number of documents
for example

> db.createCollection("my_cap_col2",

... {"capped":true, "size" : 100000, "max" : 100});

{ "ok" : 1 }

> show collections;

my_cap_col1

my_cap_col2

Once capped collections are created, it can not be modified, it must be dropped and recreated with desired parameters

Converting normal collection to capped collection

> db.runCommand({"convertToCapped": "techno_col",size:10000});

{ "ok" : 1 }

> 

There is no way to convert capped collection to normal collection

Read More

ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES

In previous articles, we have learned about user creation and grants in MySQL in detail, but there are a few privileges called global privileges that are meant to be granted to DBA accounts only
for example
PROCESS privileges is a global privilege. 
we will try to grant it to user tech_user on author database

mysql>grant process on author.* to tech_user;

ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES

Here we see it failed with error 1221 (HY000).

In order to resolve this error, we need to reconsider this grant statement and its usage.
we must know global privileges can not be granted to the individual database, instead, it should be on all databases. i.e. use of *.* instead of an author.*

so the statement should look like this

grant process on *.* to tech_user;

Below are some of the well know global privileges

• FILE
• PROCESS
• REPLICATION CLIENT
• REPLICATION SLAVE
• SUPER

In the below way, grants can be executed using the root account without error. 

mysql> grant file on *.* to tech_user;

Query OK, 0 rows affected (0.01sec)

mysql>flush privileges;

Query OK, 0 rows affected (0.01sec)

Read More

MySQL: pid-file (process id file)

In previous articles have gone through options available with mysql_safe, one of the option is pid file i.e. process identifier file

• The server writes its process id into pid file when mysql services are started and removes file when stopped.
• The pid file is the means by which a server allows itself to be found by other processes.

Location
default location of  pid file is data directory or it can be specified by specific variable 
pid-file=file_name
generally pid file is given as hostname.pid

Content
The pid file content the process identification number of mysqld process, you can see content by using below command
cat

and also see the process id  of mysql daemon process using 
ps -ef|grep mysqld

you can see content of pid file and this process id identified using ps -ef command are same.

Read More

Aggregation Function in MySQL

There are number of functions available in MySQL library, in this particular article we will  discuss aggregation function
Aggregation function s are generally used with SELECT statements containing GROUP BY clauses, however these may be used without group by clauses as well.
Below table describes aggregation functions available in MySQL (including MySQL 8.0 version)

Name	Description
AVG()	Returns average value of argument
BIT_AND()	Return bitwise AND
BIT_OR()	Return bitwise OR
BIT_XOR()	Return bitwise XOR
COUNT()	Return a count of rows
COUNT(DISTINCT)	Return a count of number of different values
GROUP_CONCAT()	Returns concatenated string
JSON_ARRAYAGG()	Return result set as single array
JSON_OBJECTAGG()	Return result set as a single object
MAX()	Returns maximum value
MIN()	Returns minimum value
STD(), STDDEV(), STDDEV_POP()	Returns the population standard  deviation
STDDEV_SAMP()	Returns the sample standard deviation
SUM()	Return the summation
VAR_POP()	Return population standard variance
VAR_SAMP()	Returns sample variance
VARIANCE()	Returns Population standard variance

In below section, we will discuss few of these with examples, 
first we will create table agg_tut for use with few records in it.

CREATE TABLE agg_tut (
emp_id int NOT NULL,
emp_name varchar(255),
salary double,
itax double,
age int
);

insert into agg_tut values ('101','John','12500','0','25');
insert into agg_tut values ('102','Smith','52500','2500','45');
insert into agg_tut values ('103','Jinh','22500','2200','32');
insert into agg_tut values ('104','Mist','15500','1000','23');
insert into agg_tut values ('105','Suki','13000','1200','28');
insert into agg_tut values ('108','Rock','33000','1800','30');

count ()

select count of all rows present in table

select count(*) from agg_tut;

+----------+

| count(*) |

+----------+

| 6        |

+----------+

min() and max()

Apply min() and max() on emp_id column first

select min(emp_id) from agg_tut;

+-------------+

| min(emp_id) |

+-------------+

| 101         |

+-------------+

select max(emp_id) from agg_tut;

+-------------+

| max(emp_id) |

+-------------+

| 108         |

+-------------+

Now apply min(), min() on salary column to rectify how much company is paying

select min(salary) from agg_tut;

+-------------+

| min(salary) |

+-------------+

| 12500       |

+-------------+

select max(salary) from agg_tut;

+-------------+

| max(salary) |

+-------------+

| 52500       |

+-------------+

Now calculate the average using avg() salary of employee in organization

select avg(salary) from agg_tut;

+-------------------------+

| avg(salary)             |

+-------------------------+

| 24833.33333333332       |

+-------------------------+

Now calculate the total salary of employee using sum(), so organization will have estimation towards employee cost

select sum(salary) from agg_tut;

+--------------+

| sum(salary)  |

+--------------+

| 149000       |

+--------------+

JSON_OBJECTAGG

Consider we have below data in employee table

select * from agg_tut;

+--------+-----------+--------+-------+------+

| emp_id | emp_name  | salary |itax   |age   |

+--------+-----------+--------+-------+------+

| 101    | John      |12500   |0      |25    |

| 102    | Smith     |52500   |2500   |45    |

| 103    | Jinh      |22500   |2200   |32    |

| 104    | Mist      |15500   |1000   |23    |

| 105    | Suki      |13000   |1200   |28    |

| 108    | Rock      |33000   |1800   |30    |

+--------+-----------+--------+-------+------+

as we know JSON is key value pair language , consider emp_id a as key and other columns such as emp_name and salary are value

Select emp_id,JSON_OBJECTAGG(emp_name,salary) from agg_tut group by emp_id;

+--------+---------------------+

| emp_id | JSON_OBJECTAGG      |

+--------+---------------------+

| 101    | {“John” : 2500.0    |

| 102    | {“Smith” : 52500.0  |

| 103    | {“Jinh” : 22500.0   |

| 104    | {“Mist” : 15500.0   |

| 105    | {“Suki” : 13000.0   |

| 108    | {“Rock” : 33000.0   |

+--------+---------------------+

We have discussed, daily used aggregation function, as described in first table, there are number of aggregation functions available in MySQL.

Read More

MySQL : mysqld_safe

mysqld_safe is wrapper used to mysql server startup and is recommended way to start a mysqld server on UNIX

• mysqld_safe provides safety feature such as restarting the server when error occurs and write run time information to error log
• mysqld_safe tries to start and executable mysqld to override the default behavior and specify explicitly the name of the server you want to run 
• specify --mysqld or --mysqld --version to mysqld_safe
• mysqld_safe reads all options  from the [mysqld][server] and [mysqld_safe] sections in option files.

options with mysqld_safe

Format	Description
--basedir	Path to mysql installation directory
--core-file-size	Size of core file
--datadir	Data directory path
--defaults-extra-file	Read named option file in addition to usual option files
--default-file	Read only names option file
--help	Help message and exit
--ledir	Path to directory where server is located
--log-error	Write error log to named file
--malloc-lib	Alternative malloc library to use for mysqld
--mysqld	Name of server program to start
--mysqld-safe—log-timestamp	Timestamp format logging
--mysqld-version	Suffix for server program name
--nice	Use nice program to set server scheduling priority
--no-defaults	Read no options file
--open-files-limit	Number of files that mysqld should be able to open
--pid-file	Path named server process ID file
--port	Port number
--skip-kill-mysqld	Do not try kill stray mysqld processes
--skip-syslog	Do not write error message to syslog, use log error
--socket	Socket file
--syslog	Write error message to syslog
--syslog-tag	Tag suffix for message written to syslog
--timezone	Set timezone
--user	Run mysqld as user having name user_name or numeric used ID user_id

Read More

Creating User Account in MongoDB

For any database whether it is RDBMS or NoSQL, the user account is a very important aspect and is a must to connect to a database
There are different types of users depending on usage like, admin user account, read-only account, read write only.
Users can be created or added to the database using db.createUser() method available with MongoDB
The db.createUser() method accepts a document object that enables you to specify the username, roles and password for user to be created

Definition of db.createUser() method
db.createUser(user, writeConcern)

Field	Type	Description
User	Document	The document with authentication and access information about user
writeConcern	Document	Optional, the level of writeConcern for creation operation

The user document has the following fields

Field	Type	Description
user	String	The name of the new user
pwd	String	The user password
customData	Document	Optional, any information about user
roles	Array	This defines roles, The roles granted to a user can be an empty array[] to create a user without any roles
AuthenticationRestrictions	Array	Optional, the authentication restrictions the server enforces on the user. Specifies a list of IP addresses and CIDR ranges from which user will be able to connect
Mechanisms	Array	Optional, specify the specific SCRAM mechanism for creating SCRAM user creation
passwordDigester	string	Optional, Indicates whether the server or the client digest the password

Roles
In the role field, you can specify the build-in roles and user-defined roles. You can specify directly role name or in the form of a document as well
i.e.

"readWrite" or {role : "" : db ""}

External Credentials
Users created on $external database should have credentials stored externally to MongoDB
Local database
user can not be created on local database
Examples
Create account with roles assigned

> db.createUser({ user : "tech_owner",

... pwd : "techo!23",

... customData : {user : "blogUser"},

... roles :[{role: "clusterAdmin",

... db : "admin" },

... "readWrite"]}

... );

Successfully added user: {

        "user" : "tech_owner",

        "customData" : {

                "user" : "blogUser"

        },

        "roles" : [

                {

                        "role" : "clusterAdmin",

                        "db" : "admin"

                },

                "readWrite"

        ]

}

> 

Create an account with roles

> db.createUser({ user : "tech_user",

... pwd : "techo!23",

... roles :["readWrite", "dbAdmin"]}

... );

Successfully added user: { "user" : "tech_user", "roles" : [ "readWrite", "dbAdmin" ] }

> 

> 

Create user without any roles

> db.createUser({ user : "tech_wo_roles",

... pwd : "techo!23",

... roles :[]

... }

... );

Successfully added user: { "user" : "tech_wo_roles", "roles" : [ ] }

> 

> 

Listing users created

> db.getUsers();

[

        {

                "_id" : "techno_db.tech_owner",

                "user" : "tech_owner",

                "db" : "techno_db",

                "customData" : {

                        "user" : "blogUser"

                },

                "roles" : [

                        {

                                "role" : "clusterAdmin",

                                "db" : "admin"

                        },

                        {

                                "role" : "readWrite",

                                "db" : "techno_db"

                        }

                ],

                "mechanisms" : [

                        "SCRAM-SHA-1"

                ]

        },

        {

                "_id" : "techno_db.tech_user",

                "user" : "tech_user",

                "db" : "techno_db",

                "roles" : [

                        {

                                "role" : "readWrite",

                                "db" : "techno_db"

                        },

                        {

                                "role" : "dbAdmin",

                                "db" : "techno_db"

                        }

                ],

                "mechanisms" : [

                        "SCRAM-SHA-1"

                ]

        },

        {

                "_id" : "techno_db.tech_wo_roles",

                "user" : "tech_wo_roles",

                "db" : "techno_db",

                "roles" : [ ],

                "mechanisms" : [

                        "SCRAM-SHA-1"

                ]

        }

]

> 

> 

Removing/Dropping User in MongoDB
First list the users using db.getUsers() method and decide which one to drop
then use db.dropUser() method to drop user, this dropUser() method takes username as argument
We will drop tech_wo_roles user 

> db.dropUser("tech_wo_roles");

true

> 

If you want to drop all the users if you can user db.dropAllUsers() method.

More will come on MongoDB. follow this space for more and explore more.

Read More