Installing Elasticsearch in AWS

Installing Elasticsearch is very well documented in the elastic.co site. I installed it in a t2 micro instance for testing purpose. I used the below commands to install it in Ubuntu 20.04.

#download the latest deb file
wget https://artifacts.elastic.co/downloads/elasticsearch/elasticsearch-7.9.1-amd64.deb

#download the published checksum
wget https://artifacts.elastic.co/downloads/elasticsearch/elasticsearch-7.9.1-amd64.deb.sha512

#check the shasum
shasum -a 512 -c elasticsearch-7.9.1-amd64.deb.sha512

#install
sudo dpkg -i elasticsearch-7.9.1-amd64.deb

After installing we need to start it as it does not start automatically. So to configure Elasticsearch to start automatically we have to run the following commands:

sudo /bin/systemctl daemon-reload
sudo /bin/systemctl enable elasticsearch.service
#start the service
sudo systemctl start elasticsearch.service

To check the status of the service

sudo systemctl status elasticsearch.service

To stop the service

sudo systemctl stop elasticsearch.service

Since I was using a t2 micro which has only 1 GB RAM so the Elasticsearch could not be started. To fix it I used Swap memory. It is not available by default and also not recommended in EBS as it is slow however we can use it for testing purpose. Below are the command to create swap space:

#allocates 2GB for swap file
sudo fallocate -l 2G /swapfile
#permission to be only accessible to root
sudo chmod 600 /swapfile
#mark file as swap space
sudo mkswap /swapfile
#enable the swap file to be used by the system
sudo swapon /swapfile
#check if swap is available
sudo swapon --show
#make the swap file permanent by adding it to /etc/fstab
/swapfile swap swap defaults 0 0

While we have installed the Elasticsearch but we need an interface to manage it and the best tool available is Kibana. Follow this article to install Kibana in AWS and access it publicly.

mysqldump for tables with white spaces

The usual command to take a dump for a single table is

mysqldump -u root -p dbname tablename > filename.sql

But what about table names that have white spaces in between like Order Master? While this kind of naming should be avoided but nevertheless they are perfectly valid. These kind of names are often enclosed within backticks(``) to make it work in sql statements. But this does not work with mysqldump. To make it work we have to enclose the table name within double quotes (""). So the correct syntax is:

mysqldump -u username -p dbname "table name" > filename.sql

MariaDB Full backup and restore

We all are aware of mysqldump which we use in command line to take backups of MySQL/MariaDB databases. However MariaDB has backup solution through mariabackup. It is very easy. Below is the command to take a compressed backup with 7z command.

mariabackup --user=root --backup --stream=xbstream | 7z a -si backup.xb.7z

Similarly we can uncompress it and replace the existing MariaDB data directory to restore the database. The command to uncompress is

7z e backup.xb.7z -so | mbstream -x

I have been using these method to backup the database to S3 and restore it when required for my clients. The beauty of it is we can take incremental backups.
For complete details follow this link and also here is an out of box script to use.

MSSQL administrator tool

I am a MySQL developer and recently started to work on MSSQL for a project. I use MySQL workbench in local and PhpMyAdmin in servers. I wanted to have similar tools that I could use in local and in the server for MSSQL.

I googled and found a nifty tool Adminer. The beauty of this tool is it supports a lot of databases like PostgreSQL, Oracle and also NoSQL databases like MongoDB, Elasticsearch etc. The interface is not that intuitive though but it is fine to run most of the commands. I do not recommend it to use it for serious development but I liked it as I can do most of the works without switching to different tools for different database servers.

Similarly SQL Server Management Studio is too heavy and I needed a light weight tool. I found sqlectron and it made my life easy :-).

Vertical middle align text in a cell for TCPDF

TCPDF library is one of the recommended libraries to convert html to PDF. It supports most of the styles but not all. Today I came along such an issue. When I was giving a height to the td tag then the text was getting top aligned. I tried with valign=“middle” but no luck. After googling found that valign is not yet supported. But I was desperate to have the design so after a bit of hit and trial able to fix it.
I changed the code from

<td style="height: 30px" valign="middle">Your text</td>
to
<td style="height: 30px; line-height: 3px">Your text</td>

This is the same procedure we used to implement to middle align texts in divs. Its generally recommended that line-height = height / 2. But for this TCPDF case it did not work and setting 3px worked for me. So if anyone comes across such an issue then tinker with line-height and I think it will solve the problem.

MYSQL select all columns except one

Today a thought occurred to me of how to select all columns except one. I searched and found the answer in stackoverflow. I tried the below answer

SET @SQL = CONCAT('SELECT ', (SELECT GROUP_CONCAT(COLUMN_NAME) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'users' AND COLUMN_NAME NOT IN ('id')), ' FROM users'); 
PREPARE stmt1 FROM @SQL; 
EXECUTE stmt1;

This gives error when column names contains spaces or – or MySQL keywords.I modified the code a bit and generalized it by adding backticks to the column names. Below is the modified code.

SET @SQL = CONCAT('SELECT ', (SELECT GROUP_CONCAT(CONCAT("`", COLUMN_NAME, "`")) FROM
INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'users' AND COLUMN_NAME NOT IN ('id')), ' FROM users');
PREPARE stmt1 FROM @SQL;
EXECUTE stmt1;

MySQL Integer data types with length

We have come across integer data types like INT, TINYINT etc. While creating/modifying a column we generally give a length to it like INT(10). Today I really found its usefulness.

Often it is documented that the length just specifies how many characters to display when selecting data. But the length only works only when the column is created as UNSIGNED ZEROFILL and its for display purpose only.

For example a column created as INT(5) UNSIGNED having a value of 100 will always display 100 when we select the column. But if the column is created with INT(5) UNSIGNED ZEROFILL then it will display 00100 i.e  with 2 leading zeroes.

Thus integers created with length and ZEROFILL option is same as padding 0’s in the left which can be achieved with LPAD.

I wanted to left pad a number with leading zeroes and thought this one is better than updating the column with LPAD because the column still remains numeric and I can still perform numeric operations. Also in case I want to change the number of leading zeroes then I only need to alter the length of the column instead of updating. Hope this helps anyone who need to add leading zeroes to a number.

PHP switch AND break

Though switch and break statements have lots of explanation scattered all around the net but there are few concepts which we rarely use. One such thing which I came across today is to use a conditional statement in case. The general syntax for switch is

switch($n) {
    case 1: echo 1; break;
    case 2: echo 2; break;
    default: echo 'No conditions satisfied'; break;
}

The below syntax is not well known to new programmers i.e. to print messages if n is between 2 different values

switch($n) {
    case ($n>=1 && $n<=2): echo $n." is between 1 and 2"; break;
    case ($n>=3 && $n<=4): echo $n." is between 3 and 4"; break;
    default: echo "No conditions satisfied"; break;
}

Similarly break is used to come out of the loop. For example

for ($i=0; $i<10; $i++) {
    //code to be executed
    if($i == 5) break;
}

//rest code will execute 

The code here gets executed till $i becomes 5 after which the for loop execution will stop. Now what if we want to break out of nested loops. The lesser known syntax is break n where n is the loop number. For example

for ($j=0; $j<20; $j++) {
  for ($i=0; $i<10; $i++) {
	//code to be executed
	if ($i == 5) break 2;
  }
}
//rest code will execute

Here 2 means it will stop execution for the outer $j loop. The loop number counting starts from the loop where the break is called and increases by 1 for its parent loop.

Reload configuration changes without restarting Apache

It has been a day to day activity to restart Apache when ever there is any change in its configuration files or while changing in php.ini files.

But what if someone is viewing our site while we want to restart to make new changes effective. This definitely gives a bad experience to the end users. I wanted to see the changes without restarting the Apache server. I Googled and found the command i.e. reload. What it does is reloads any configuration changes without restarting Apache. Really cool. Now I am armed with one more command.

sudo service apache2 reload

But there is a caveat to this. The Apache server will stop if there is syntax error in configuration file. So we need to check it first with

sudo apachectl configtest or sudo apachectl -t

Now the order of my commands when I make any changes are

sudo apachectl configtest or sudo apachectl -t
sudo service apache2 reload or apachectl graceful
sudo service apache2 restart
sudo service apache2 stop
sudo service apache2 start