Posts

Showing posts from 2019

what is character encoding and decoding

Characters are abstract entities that can be represented in many different ways. A character encoding is a system that pairs each character in a supported character set with some value that represents that character. For example, Morse code is a character encoding that pairs each character in the Roman alphabet with a pattern of dots and dashes that are suitable for transmission over telegraph lines. A character encoding for computers pairs each character in a supported character set with a numeric value (also known as code point) that represents that character. A character encoding has two distinct components:   An encoder, which translates a sequence of characters into a sequence of numeric values (bytes). A decoder, which translates a sequence of bytes into a sequence of characters.    A computer can only work with 0s and 1s. It does not understand human languages like English, Hindi etc. Hence you need to mention the correct character set while either rendering or transferrin

Understanding character set and collations

An important yet most ignored part in software concepts is Character Set and Collations. It is a vital thing that every developer must familiar with. The objective of this article is to make you understand what is meant by character set and collations. I will take help of MySQL in the process. Please remember that character set and collation are valid for text data like CHAR, VARCHAR, TEXT etc. It has no significance for data of type INT, BINARY etc. Character Set decides which characters you are allowed to use while storing information in your application. Collation decides how the two piece of information will be compared or sort in case you do an operation on the data in your application. mysql> CREATE TABLE `person` -> ( -> `id` int not null auto_increment, -> `name` varchar(100) COLLATE utf8_unicode_ci NOT NULL, -> PRIMARY KEY (`id`), -> UNIQUE KEY (`name`) -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode

MongoDB database Data Types

MongoDB is a No SQL database that uses document as its data structure to store user's data. MongoDB is a schema-less database in the sense that you do not require to define your fields in advance as well as different structured document can reside in the same collection. This is not possible in case of traditional RDBMS like MySQL, SQL Server, Oracle etc. A document in MongoDB consists of key value pairs. Keys are written as strings but values can belong to one of the several data types allowed in MongoDB. Please always refer to the documentation or online resources for your specific version of MongoDB as an application features are always tend to change. DataType Description String This commonly used datatype contains a string of text (or any other kind of characters). This datatype is used mostly for storing text values (for example, "Country" : "India"}. Integer (32b and 64b) This type is used to store a numerical value (for example, { &quo

Online editors and useful links

HTML, CSS and JavaScript jsFiddle HTML, CSS and JavaScript Demo - Liveweave Unix SHELL ShellCheck - shell script analysis tool Bash Pitfalls - Greg's Wiki Bash Guide - Greg's Wiki

select ends with killed in mysql

I ran a query in mysql and it hangs for a while and then I get killed as a response. mysql> select * from usagehistory; Killed SHELL:~$ SHELL:~$ date Thu Mar 14 06:26:17 UTC 2019 SHELL:~$ Reason The mysql process/client itself runs out of memory and is terminated by the OS. I do not have a good view of the implementation, but it appears that mysql is trying something like pulling the whole table into memory to do the select *. You can use the --quick mysql option to not buffer the results in memory. I also found out that the data in table is around 2 GB and Memory in my jump-box is around 1 GB. Two more proofs that I see in my jump box (where mysql is run) system is: 1. I see OOM messages in syslog. root@ip-XXX-XXX-XXX-XXX:~# grep mysql /var/log/syslog | less Mar 14 06:25:40 ip-XXX-XXX-XXX-XXX kernel: [529731.301028] mysql invoked oom-killer: gfp_mask=0x24201ca, order=0, oom_score_adj=0 Mar 14 06:25:40 ip-XXX-XXX-XXX-XXX kernel: [529731.301032] mysql cpuset=/ mems

mysql is not using the mentioned foreign key constraint name

I faced a strange situation, so I thought to share it with you, in case someone else finds himself in similar situation. I added a column to an existing table in MySQL. I also added a foreign key constraint on that column. So what is strange about that. Let me show you. First of all let me display the original structure of the table. mysql> SHOW CREATE TABLE T_DEVICE_RESULTANTSTATE\G *************************** 1. row *************************** Table: T_DEVICE_RESULTANTSTATE Create Table: CREATE TABLE `t_device_resultantstate` ( `RESULTANTSTATEID` int(11) NOT NULL AUTO_INCREMENT, `RESULTANTSTATE` blob, `DEVICEID` int(11) NOT NULL, `CREATEDBY` varchar(50) NOT NULL DEFAULT 'ADMIN', `CREATEDATE` datetime DEFAULT NULL, `MODIFIEDBY` varchar(50) NOT NULL DEFAULT 'ADMIN', `MODIFIEDDATE` datetime DEFAULT NULL, PRIMARY KEY (`RESULTANTSTATEID`), KEY `FK_T_DEVICE_RESULTANT_STATE_T_DEVICE` (`DEVICEID`), CONSTRAINT `FK_T_DEVICE_RESULTANT_STAT

A simple yet effective change password UI

Image
I believe that most of us had faced this situation which I am going to mention. Say you have an account for an online service. You visit their website, Login and found a message that your password will expire in 5 days, Change Password or Continue. You decided to change your password. A new page opens up asking for current and new password. Apart from that, the page also mentions some policy that you must adhere to in order to change your password. You enter your current password, new password, confirm password and press Submit. But you get surprised when you see "Your new password does not matches the required criteria" and you have no information that what goes wrong. But I saw a eye catching solution on Amazon Web Service website, so I thought of sharing it with you all. What they do is, when you key in your new password, they will tick mark the password policy that you have passed. They handle your key deletes as well. These little little things makes one s

Errors and Exceptions in Python

There are two kinds of errors in Python: syntax error and exceptions. Syntax Error Syntax errors are the errors that are caused by not following the proper structure (syntax) of the language. They are also known as parsing error. When you forget a colon at the end of a line, accidentally add one space too many when indenting under an if statement, or forget a parenthesis, you will encounter a syntax error. This means that Python couldn’t figure out how to read your program. This is similar to forgetting punctuation in English.  def some_function() msg = "hello, world!" print(msg) return msg The above function definition has two issues with syntax. First one is that the function definition is missing a colon (:) at the end. The second one is that the lines in the function definition do not all have the same indentation ( IndentationError ). Exceptions Even if a statement or expression is syntactically correct, it may cause an error when an attempt i

Identifiers and Keywords in Python

Python Identifier is the name we give to identify a variable, function, class, module or any other object. That means whenever we want to give an entity a name, that's called identifier. Think of this as we are identified by our names. Without names it is not possible to reference a person from a group of people in a room. Remember that identifier belongs to a namespace. Namespace is like family in real world. Hence two identifier with same name can be used independently in your program on a condition that they must belong to different namespace. Module introduce namespace in Python.  Rules for writing Identifiers 1. Identifiers can be a combination of letters in lowercase (a to z) or uppercase (A to Z) or digits (0 to 9) or an underscore (_). Identifiers like myClass, var_1 and print_this_to_screen, are all valid example. 2. An identifier cannot start with a digit. 1variable is invalid, but variable1 is perfectly fine. 3. Keywords cannot be used as identifiers. 4. We cannot

mysql stored procedure template for defining a cursor and do error handling

This article will define a basic blueprint for a mysql stored procedure that will declare a cursor to fetch employee details of a particular department and log them into a separate table. The stored procedure will also handle any error occurred during the script execution. The behavior is to rollback the work done and exit from the procedure. SET @sql_notes = @@session.sql_notes; SET SESSION sql_notes = 0; DROP PROCEDURE IF EXISTS usp_fetch_and_log_emp_name; DELIMITER $$ CREATE PROCEDURE usp_fetch_and_log_emp_name(IN p_deptid INT) BEGIN declare v_empid INT; declare v_empname VARCHAR(100); declare v_deptid INT; declare no_more_emp boolean; declare emp_cursor cursor for select emp.empid, emp.name, emp.deptid from employees emp where emp.deptid = p_deptid; declare exit handler for sqlexception begin rollback; close emp_cursor; select @@warning_count as warning_count; show errors; end; declare exit handler for sqlwarning begin rollback; c

mysql query stuck in statistics state

I was working on a customer case where I ran into an interesting problem - Query joining about 23 tables got stuck in statistics stage. The customer need to generate a report in pipe separated values format from their database. Since the database is hosted as AWS MySQL RDS, using SELECT ... INTO OUTFILE was not an option. Therefore I decided to dump the data by using mysql command line tool along with sed command to convert tab delimited to pipe delimited column values. mysql -e "select te.EId AS \"EID\", tea.AId AS \"AID\", tea.ActivationState AS \"Status\", tp.PRDName AS \"Product\", tea.FamilyId AS \"Product Family\", tp.Ver AS \"Product Version\", tc.CSTMRName AS \"Customer Name\", tc.CSTMRIdentifier AS \"Customer ID\", tea.ActivationDateTime AS \"Activation Date\", tea.quantity AS \"Activated Quantity\", caswuid.AttrValue AS \"SWUID\", cadm.AttrValue AS \"D

Why SQL is a set language and not a programming language

SQL stands for Structured Query Language that works on a set of data. A set can consist of a single record or multiple records. SQL is called a language because it has syntax ( the set of rules that defines the combinations of symbols that are considered to be a correctly structured document or fragment in that language.) as well as semantics ( the meaning of the formed sentence). SQL is called a set language and not a programming language because in SQL we only tell the system what we want and the system decides the how to part itself. But in a programming language, we mention what and how to part ourselves. Let us take an example: In MySQL, we only tell the system that we want all the records from the employee table where the first name is John. The MySQL system decides itself that how to retrieve that information. It decides that should I scan the whole table or use another method, with the help of available statistics and optimizer. Now take a real world example to und

notepad++ encode vs convert

In this article I will explain the difference between Encode In and Convert To. You will find similar menus in one of the popular text editor called Notepad++. Encode In means keep the raw information (byte sequence) same and map that to the specified character set. Convert To means keep the character information same while map byte sequence to the specified character set. Let us take a hypothetical example to understand it. We have two character set: X and Y as follows: Character Set X Character - A, B, C Hex Format - 3C, 49, 2D Character Set Y Character - A, B, C Hex Format - 49, 2D , 3C We have a file in character set X named message.txt with content as ACB BC. Hex Content as 3C2D49 492D. Encode In character set Y, file becomes :  Hex Content - 3C2D49 492D Character - CBA AB Convert To character set Y, file becomes :  Hex Content - 493C2D 2D3C Character - ACB BC (I have not replaced the space character with its hex value.) In short Encode in

error 18 at 0 depth lookup: self signed certificate

I was trying to test SSL connection between MySQL client and server. For that I created SSL certificate and keys by following the MySQL documentation at: Creating SSL Certificates and Keys Using openssl After finishing up all the commands when I verify the certificates by using: openssl verify -CAfile ca.pem server-cert.pem client-cert.pem I got the following output: error 18 at 0 depth lookup : self signed certificate error server-cert.pem: verification failed client-cert.pem: OK  Reason : The Common Name value used for the server and client certificates/keys must each differ from the Common Name value used for the CA certificate. Otherwise, the certificate and key files will not work for servers compiled using OpenSSL. Solution : When OpenSSL prompts you for the Common Name for each certificate, use different names. Common Name (e.g. server FQDN or YOUR name) []: I used values as follows: CA Cert:  ssl-ca-cert Server Cert: ssl-ca-server-cert Client Cert:  ssl-c

General Quotes

A list of quotes from my own experiences and other sources. It is not about Remembering 100%, It is about Understanding 100%. Be Better Everyday. Great stories are not written, they are made. The power of a system is dictated by the knowledge of its creator.  Past is a history, Future is a mystery. But present is a gift. Give me the Wings and I will Fly, Give me a Chance and I will Try. Sometimes High and sometimes Low, the Nature of the Water is to keep its Flow.

Indexing and Slicing in Python

Indexing and Slicing are basic operations in Python with the help of which you can extract items from inside of an ordered sequence. Because strings are defined as ordered sequence of characters, we will use them to explain the operations. The knowledge gained here will apply to other ordered sequences in python like lists. To extract the items, you need to provide positional offset into the sequence. The offset start at 0 from left and at -1 from right. S = 'Python' P y t h o n 0 1 2 3 4 5 -6 -5 -4 -3 -2 -1 Indexing In Python, individual character in a string can be fetched by indexing - providing the numeric offset of the desired component in square brackets after the string. You get back the one character string at the specified position. >>> S[3],S[-2] ('h', 'o') Technically, a negative offset is added to the length of a string to derive a positive offset. More mathematically minded

Why mutexes are required in MySQL

What is a mutex? In computer programming, a mutex (mutual exclusion object) is a program object that is created so that multiple threads in the application can take turns sharing the same resource, such as access to a file. Typically, when a program is started, it creates a mutex for a given resource at the beginning by requesting it from the system and the system returns a unique name or ID for it. After that, any thread needing the resource must use the mutex to lock the resource from other threads while it is using the resource. If the mutex is already locked, a thread needing the resource is typically queued by the system and then given control when the mutex becomes unlocked (the thread that will get the mutex depends upon the thread scheduling algorithm applicable to the mutex context in question). This is needed to avoid race condition and to employ synchronization among threads to keep the database system consistent all the time. What is a Race Condition? A race conditio
Back To Top