DEV Community: Amit Tiwary

context in Golang

Amit Tiwary — Sun, 28 Jan 2024 14:18:34 +0000

There are times when we are required to abandon some task, for any reason, and also want to include some helpful reason for the cancellation. 'context' can be very handy in such a scenario. Golang has a 'context' package that contains the ‘Context' type and cancellation function. 'context' package was not a standard go package initially but it is so useful that later, in version 1.7, it was included in the Golang standard package(the latest version while writing this blog is 1.21).
The ‘Context' type is an interface with four methods Done, Err, Deadline, and value. We are not required to implement this method to use context. These functions are implemented in the package and the 'context' package exposes functions WithCancel, WithCancelCause, WithDeadline, WithDeadlineCause, WithTimeout, and WithTimeoutCause that we can use. These functions return 'CancelFunc' and 'Context'(derived context). The below code explains the usage of context and how it can be used to abandon a task.

func doJob(firstChildContext context.Context, secondChildContext context.Context) {
    defer wg.Done()
    for {
        select {
        case <-firstChildContext.Done():
            fmt.Println("first child exit")
            return
        default:
        }

        select {
        case <-secondChildContext.Done():
            fmt.Println("second child exit")
            return
        default:
        }
    }
}

func TryContext(flag int) {
    parentCtx, parentCancel := context.WithCancel(context.Background())
    firstChildContext, firstChildCancel := context.WithCancel(parentCtx)
    secondChildContext, secondChildCancel := context.WithCancel(parentCtx)

    go func() {
        wg.Add(1)
        doJob(firstChildContext, secondChildContext)
        fmt.Println("done with job")
    }()

    time.Sleep(5 * time.Second)
    switch flag {
    case 1:
        parentCancel()
    case 2:
        firstChildCancel()
    case 3:
        secondChildCancel()
    }
    wg.Wait()
}

TryContext function take a flag as input and based on flag value we cancel parent context or first context or second context.

flag value 1:
first child exit
done with job

flag value 2:
first child exit
done with job

flag value 3:
second child exit
done with job

When the flag value is 1, we cancel the parent context and it signal all child Done channel but return from the first context. For the flag value 3, we cancel the second context and it signal the second context 'Done' and return etc.

Another version of WithCancel function is WithCancelCause that take cause as parameter in the CancelFunc and we can get the error message using the context.Cause function

firstChildContext, firstChildCancel := context.WithCancelCause(parentCtx)
firstChildCancel(errors.New("cancel first child"))

select {
    case <-firstChildContext.Done():
            fmt.Printf("first child exit %v\n", context.Cause(firstChildContext))
            return
    default:
}

From the above code it is clear that the Context.Done is a channel. Context doesn’t automatically cancel what we are doing but we get function like WithCancel or WithTimeout that provide CancelFunc and CancelFunc signal Context Done channel and, then we have a handle in code that when we receive the signal then stop what we are doing. Calling the parent CancelFunc remove the parent reference to child, and it means that garbage collector is free to garbage collect the child.

Similarly WithDeadline, WithTimeout function provide the CancelFunc but WithDeadline also signal the Done channel once the deadline cross i.e current time is greater than the time provided in deadline and WithTimeout when it timeout i.e when current time is greater than the duration plus the time when the WithTimeout function triggered.

We can use the context to signal and stop the ongoing http request because it is taking too long to respond or something went wrong and we don’t the response any more. It will help us to save the resource. I will cover this example in different blog, 'network request in golang'

Defer in Golang

Amit Tiwary — Sat, 20 Jan 2024 16:28:17 +0000

In my last blog I used defer function and use resolve inside it. defer is one of the important feature of golang. defer statement execute just before the function, in which defer added, returns.

One of the main uses of a defer statement is to clean resources like network connection, open files etc. After program is done with these resources then these resources should be closed to avoid program limit exhaustion and the resource wastage. In last blog recover is added inside defer function that make sure that if program panic then before exit defer statement execute and recover from panic. Take below code as an example.

func TryPanic() {
    defer func() {
        fmt.Println("inside main function first defer")
        recover()
    }()
    fmt.Println("inside main function")
    panic("panic in main")
}

The output is

inside main function
inside main function first defer

This function panic still program doesn’t exit because we have recover inside defer.

Example of free up resource after the use

func createFile() error {
    file, err := os.Create("inside-defer.txt")
    defer file.Close()
    if err != nil {
        fmt.Println("failed to create file")
    return err
    }
  _, err = io.WriteString(file, text) 
  if err != nil {
    fmt.Println("failed to write to file")
    return err
  }
 return nil
}

Once we are done with the file and before the program exit, it’s important to close the file so that resource can be saved.

We can add multiple defer statement in a function although single defer statement can work. Multiple defer statement can helps to write the readable code where we clean different resource in separate defer statement. The multiple defer statement execute in LIFO order i.e that last defer execute first.

recover from panic in Golang

Amit Tiwary — Sat, 13 Jan 2024 12:22:43 +0000

I am back after almost a year. Next few blogs will be on golang. Go is good language to build efficient and reliable software. Like any other programming language, there is possibility of error also in Go. Compile time errors detected by compiler before application run. Runtime time error occur during program execution, and if it is not handle properly, it can break the application and abort program. While writing code, a programmer try to cover all the error scenario, but there is possibility we might not handle some errors. When runtime error occur, it refer as program panicked. Runtime error is panic in golang.

Recover is a built-in function provided by go to handle panic. A recover can stop a panic from aborting the program. A recover function can handle the panic in the goroutine that has implemented the recover and not all goroutine. You can refer the below code to understand how recover handle the panic in respective goroutine.

package gortntry

import "fmt"

func panicFun(flag int) {
    if flag == 2 {
        defer func() {
            recover()
        }()
    }
    fmt.Println("inside the panic func")
    panic("panic in goroutine")
}

func TryPanic(flag int) {
    if flag == 1 || flag == 2 {
        defer func() {
            recover()
        }()
    }
    go panicFun(flag)
    panic("panic in main")
}

We have two function TryPanic and panicFunc. TryPanic is the main entry function, and we call panicFunc inside it. Recover enabled in TryPanic for flag value 1 or 2 and in panincFunc for value 2. I have used defer here that I will explain in different blog post.

When we pass flag value 1 in TryPanic the output is:

panic: panic in goroutine

goroutine 5 [running]:
golibcustom/goroutineplgrnd.panicFun(0x0?)
    /Users/amitt/Documents/Personal Data/personalproj/go-practice/goroutineplgrnd/gortn-panic.go:13 +0x50
created by golibcustom/goroutineplgrnd.TryPanic in goroutine 1
    /Users/amitt/Documents/Personal Data/personalproj/go-practice/goroutineplgrnd/gortn-panic.go:22 +0x64
exit status 2

It means recover in TryPanic handle the main goroutine panic but not the panic that occur in other goroutine in panicFunc.

For flag value 2 there is no panic because both goroutine has recover function.

If we use any other value for example 3 then main goroutine panic and it abort the program without waiting for the panicFunc.

panic: panic in main

goroutine 1 [running]:
golibcustom/goroutineplgrnd.TryPanic(0x2)
    /Users/amitt/Documents/Personal Data/personalproj/go-practice/goroutineplgrnd/gortn-panic.go:23 +0x78
main.main()
    /Users/amitt/Documents/Personal Data/personalproj/go-practice/main.go:36 +0x20
exit status 2

Understanding MySQL EXPLAIN output

Amit Tiwary — Mon, 27 Feb 2023 05:30:20 +0000

In my last blog, I wrote about how to read the output of the explain query in MySQL. I provided the information for all the columns except type and extra. In this blog post, I will cover type column.

type column provides information about how the tables are joined. Different types of the type are described below.
I used two tables student_details and address table. I have attached the table structure and respective index structure at the end of this blog.

const: If the type is const it means that there is at most one matching row. Since the number of matched rows is maximum one, it is treated as constant. When we used all the primary keys (if the primary key is a composite primary key) or all the ‘unique’ indexes to compare to a constant value to query data then the type is const.
For example, in below query I used both the unique index(class, roll_no) to query the data.

SELECT * FROM student_detail sd INNER JOIN 
address ad ON sd.address_key = ad.id 
WHERE roll_no = 12 and class = 4;

eq_ref: It shows that one row is fetched from this table for each combination of rows of the previous table. If all the parts of the primary index or the unique not null index are used to fetch the data then the type is eq_ref.

SELECT * FROM student_detail sd INNER JOIN 
address ad ON sd.address_key = ad.id 
WHERE roll_no = 12;

Here all the rows of the student_detail tables is scanned because roll_no is not indexed (class, roll_no is indexed but not only roll_no. MySql can use class or class and roll_no for index but not only roll_no). There is only one row in the address table for each combination in the student_detail table(We used the id of address to join and it is primary key in address table).

ref: If there are multiple rows in the table for each combination of rows of the previous table then the type is ref. If the index is not a primary index or not a unique index or the leftmost prefix of the index is used, that means it is not possible to get the single row for each column, then the type is ref.

SELECT * FROM student_detail sd INNER JOIN 
address ad ON sd.address_key = ad.id 
WHERE state = 'Karnataka';

state is not primary or unique index, so for address table type is ref because there can be multiple rows for each state.

one more example

EXPLAIN SELECT * FROM address ad WHERE ad.state = 'Karnataka';

index_merge: Sometimes MySQL may choose to merge the rows fetched after multiple range queries on the same table. This is shown as index_merge in the type

range: If only the rows mentioned in the range are retrieved using the index then it appears as the range in the type.

index: MySQL saves all the indexes separately in storage. Sometimes for the query, only the index need to be scanned and it satisfies all the requirement.

If the index is a covering index for the queries and can be used to satisfy all data required from the table, only the index tree is scanned. In this case, the Extra column says Using index. An index-only scan usually is faster than ALL because the size of the index usually is smaller than the table data.
A full table scan is performed using reads from the index to look up data rows in index order. Using index does not appear in the Extra column.

EXPLAIN SELECT class FROM student_detail;

Here we query only the class column. It is indexed in the student_detail table and directly fetched from the index instead of going to table and fetch the data.

ALL: A full table scan is required to get the requested data. Lets take this query as example, we have used same query above.

SELECT * FROM student_detail sd INNER JOIN 
address ad ON sd.address_key = ad.id 
WHERE roll_no = 12;

Here roll_no col is not indexed so its going to scan the every row of first table and then find the match row in the other join table. Normally, you can avoid ALL by adding indexes.
But even if we add index we can't avoid it completely. Sometimes it happens that we are fetching too many rows in a query and query planner may decide that going through index and then fetching the row from the leaf of the B-Tree(index structure) is more costly then full table scan. For full table scan it needs to get the leftmost leaf once and then traverse only leaves because leaves are connected in chain.

EXPLAIN is a powerful tool for optimizing MySQL queries. By understanding the output of EXPLAIN, you can identify performance issues and make changes to improve query speed.

In my next blog, I will cover how to use the output of EXPALIN to optimize the query.

Note:
student_detail table structure

student_details indexes

address table structure

address table indexes

Understanding MySQL EXPLAIN output

Amit Tiwary — Sat, 18 Feb 2023 18:32:12 +0000

MySQL is one of the most used RDMS. We write different types of queries in MySQL. Some used to be simple, and some quries used to be complex that includes even more than 4 - 5 tables. When we write complex queries, understanding the effect of the query is essential. Sometimes poor written complex queries can be the reason for a database's poor performance. We can use EXPLAIN to find the keys used in the query and how we can optimize them. This blog is about how we can get useful information from the result of EXPLAIN.

The output of explain contains information on each table used in the query and in the order in which MySQL read the tables while processing the query. It means that MySQL reads the data from the table present in the first row, then finds the matching row in the table present in the second row and so on.

Columns present the EXPLAIN output are id, select_type, table, partitions, possible_keys, key, key_len, ref, rows, filtered, type and extra.

select_type: There are different types of select. This column shows the types of select. The various types of selects are SIMPLE, UNION, SUBQUERY, and DERIVED.

1.1 SIMPLE: Select is simple. It means no sub-query or union is used here.

1.2 Subquery: Any select query used in another select query but not in the FROM clause. For example,
SELECT * FROM student_detail where address_key in (SELECT id from address where state = 'Karnataka');

1.3 DERIVED: SELECT query used in the FROM clause of another SELECT query is a DERIVED select_type. For example, SELECT id FROM (SELECT * FROM address) ad;

1.4 UNION: Second SELECT query in UNION or subsequent SELECT query type is a UNION select_type. For example,
SELECT state FROM address where id = 2 UNION SELECT state from address where id = 4 UNION SELECT state from address where id = 3;

table: Name of the table. It can be the table name or the name provided by us during a query or (derived table in the row with id vale n).

partitioned: Partition of the table from which the data is fetched. If the table is not partitioned then it shows a NULL value, or it may be possible that this column is not present in the output result if none of the tables is partitioned.

possible_keys: It shows all the possible indexes from which MySQL can use the index to find the rows in the table. But this key is not dependent on the order of the table in the output result i.e it does not depend on the order of the table in which MySQL process the query. So it may be possible that none of the keys is used. I have explained it below.

key: It shows the actual key that is used to find the rows in the table. Normally this key used to be one of the keys present in possible_keys, but it can be a different key too. For example, If it is optimized to fetch all the required columns from an index directly using the index scan then MySQL can use the index scan instead of the data row scan.

key_len: It shows the length of the key used to get the data. If the key is a multi-part key then key_len helps us to find how many parts of the multi_part key is used to get the data.

ref: It indicates which column is used against the column present in the key to fetch the data from the table. It can be const too i.e a const value is used to compare. For example, SELECT * FROM student_detail sd INNER JOIN address ad ON sd.address_key = ad.id WHERE sd.id = 2. In this query, the key used in the JOIN clause is also present in the where clause and it means that we are fetching a fixed number of rows using that key from the table.

rows: It indicated the approx(not actual) number of rows need to examine.

filtered: The filtered column indicates an estimated percentage of table rows filtered by the table condition. The maximum value is 100, which means no filtering of rows occurred. Values decreasing from 100 indicate increasing amounts of filtering. rows show the estimated number of rows examined and rows × filtered shows the number of rows joined with the following table. For example, if the row is 1000 and the filtered is 50.00 (50%), the number of rows to be joined with the following table is 1000 × 50% = 500. You can check the official document the MySQL.

I will explain type and extra column usage in the next blog. Both required detailed explanation and one blog is not enough for this.

How SQL engine implement the isolation levels in MySQL

Amit Tiwary — Sat, 24 Dec 2022 18:47:21 +0000

Isolation is one of the ACID properties of DBMS. Isolation ensures that each transaction is isolated from other transactions and that transactions don’t affect each other. There are multiple ways a transaction can be affected by other transactions. It is known as a read phenomenon. Different types of read phenomena are:

Dirty read: It happens when a transaction reads data written by another transaction that has not been committed yet. This is bad because we are not sure if that other transaction will eventually be committed or rolled back. So we might end up using incorrect data in case rollback occurs.
Non-repeatable read: It happens when a transaction read the same row multiple times and the value is different each time because the row is modified by another transaction that is committed after the previous read. This is unexpected.
Phantom read: If the same query is executed multiple times but returns a different number of rows, due to some changes made by other recently-committed transactions, such as inserting new rows or deleting existing rows, it is known as a phantom read. For example, we want to query students that have heights between 5’’ to 6’’. It might possible that a new student is added, while the previous read transaction is still in progress, whose height is between 5 to 6 feet, in that case, the query will return this newly added user too.
Lost Update: It happens when two different transactions are trying to update the same column on the same row of a table and one transaction overrides the update of another transaction.

For example, there are 4 students with id id1, id2, id3 and id4. id1, id2 belongs to team A and id3, id4 belongs to team B.
```
mysql> select * from students_team;
+----+-------+
| id | team  |
+----+-------+
|id1 | A     |
|id2 | A     |
|id3 | B     |
|id4 | B     |
```

The first transaction updated team A to B but before it
commit, the second transaction already started that updated team B to A. Once the first transaction commits team A converted to team B.

After the update in first transaction doing the read query in same transaction
mysql> select * from students_team;
+----+-------+
| id | team  |
+----+-------+
|id1 | B     |
|id2 | B     |
|id3 | B     |
|id4 | B     |

So all the students belong to team B now. The second transaction converts team B to A. Because all students belong to team B now, the second transaction converts all of them to team A.


mysql> select * from students_team;
+----+-------+
| id | team  |
+----+-------+
|id1 | A     |
|id2 | A     |
|id3 | A     |
|id4 | A     |

SQL engine offers four isolation levels to handle the read phenomenon. In this blog post, we are going to discuss what are those four isolation levels and how SQL engines ensure these isolation levels.

SQL use locking to protect a transaction from seeing or changing data that is being queried or changed by other transactions. SQL use different locking strategy to for different isolation levels

Read uncommitted: In read uncommitted isolation level one transaction can read the changes of another transaction even if the other transaction update is not committed. SQL engines don’t lock any row in this isolation level. So one transaction can affect the other transactions. All the read phenomenon is possible at this isolation level.
Read committed: If a transaction tries to read a row which is already locked by another transaction then until that transaction is completed it will wait and once the transaction is committed new changed value will be used. Here exclusive lock is used by the SQL engine. So if any update transaction comes, it acquires the exclusive lock and changes the value and releases the lock. Other transactions read the value after that. Read committed resolve the dirty read issue but another read phenomenon is still. Because it doesn’t prevent the new insertion or update in data even if any other transaction is reading the same row.
Repeatable Read: Repeatable Read isolation is achieved by placing a shared lock on the row read by each transaction and the lock is held until the transaction is committed. Until the lock is removed other transactions can’t change the data of row held by a different transaction. But it doesn’t prevent the new row insertion. So it prevents dirty reads and non-repeatable read but phantom read is still possible.
Serializable: SQL engine holds the lock on the row until is committed. It is similar to a Repeatable read but if required locks can be acquired on the key range, preventing other transactions from updating or inserting rows into the data set until the transaction is complete. It helps to prevent phantom reads. This is the most strict isolation level and it prevents all the read phenomena.

MySQL innodb engine default isolation level is Repeatable Read.

How sql engine handle join internally - part 2

Amit Tiwary — Sat, 10 Dec 2022 13:17:05 +0000

In the last blog, I wrote about the physical joins used by the SQL engine. Before version 8, MySQL had only nested loop join, but hash join was included in version 8. Postgresql and sql server has nested loop join, hash join and merge join. In this blog post, I will explain how SQL engine decide which physical join to use.

When two tables joined then, both tables traversed once or multiple times to find the matched data. Merge join is best because it requires traversing both tables once. Merge join requires both inputs to be sorted on join keys or the join key is clustered index. It traverses the outer table and finds the matching data in the inner table. Once it found the data, then it moved to the next row. If data is not found, then move to the next row of the table that has the lowest value.

Since both tables are sorted on the join key, it can move to the next row of the table that has the lowest value because the value in the next row will be higher than the current row. There is a chance that the value in the other table has a high value that can be found in the next row. If the table is not already sorted, then the SQL engine may decide to sort it and do the inner join if it is more optimized. But for that, it is necessary that the SQL engine should be able to sort the data on the join key. Also, one more important thing for merge join is that there should be at least one equijoin(=) expression.

Nested loop join traverses through the inner table multiple times. For each value of the outer table, it traverses the inner table to find the matched value. If the join key is indexed in the inner table then it will use to find the data in a more optimized way. A nested inner join is used when one table is small and the other is large. The small table is used as the outer table and the large table is used as the inner table. The inner table is traversed for each row of the outer table that’s why it is more time-consuming.

If the outer table has n elements and the inner table has m elements then the time complexity can be n*m or n*logm(if the join key is indexed, then the index will use to find the data in the table). If it requires sorting the data on the join key and this operation is expensive then the loop through all rows to find the matching data, then nested loop join might prefer over merge join.

We discussed merge join and nested loop join and hash join need to discuss. There are two phases in hash join. One phase is the build phase and the other is the probe phase. One of the tables is used in the build phase and the other is used in the probe phase.

In the build phase, all the rows of the build table are scanned and used to generate the hash key(using a hash function) and save it in the in-memory hash table. In the probe phase, we go through all the rows of the probe table and generate the hash key using the hash function and the join key of the probe table. Find the hash key in the in-memory hash table. If the key is found then join the row of both tables. Key lookup is fast in the hash table. Like merge join, at least one equijoin(=) expression requires in hash join too.

The hash table generated during the build phase is saved in the in-memory that’s why we chose the small table for the build phase so that less memory is used. If there is a memory issue, some of the partitions of the hash table are swapped to tempdb and whenever there is a need it is loaded in the cache.

If the join table is large and there is no index on the join key then the hash join can be very efficient. Merge join works If the join key is indexed, nested loops join need to go through all the rows of the inner table again and again and if the table is very large then it can be expensive.

You don’t need to specify which join type SQL should use. SQL engine is intelligent enough to find the optimized physical join to use depending on the conditions. But you can make sure you are doing your best to design the database schema, index the column and do the join query using the correct columns.

How sql engine handle join internally-part 1

Amit Tiwary — Sat, 03 Dec 2022 18:36:21 +0000

There are different types of logical join in SQL. Inner join and outer join are mainly used join. When we need to get the data from tables we use the join. But how do these joins work? Physical join is implemented inside RDMS. The user uses a logical join to write the query and a physical join is used by RDMS to do join operations. Part 2 of this blog post is here.

There are three different physical joins Nested loop join, Hash Join and merge join. Depending on the data in the joined tables, RDMS use any one physical join.

Nested loop joins: In nested loop join, out of the two tables, the table with a smaller number of records is selected, and it will loop through the second table until matches are found. This is available in MYSQL, Postgres and even in SQL servers too. This is not a scalable option for a large table. It is used mostly when the join operator doesn’t use equality. MySQL has only this option in versions lower than MYSQL 8.
For example lets say we need to get the students that height is smaller then the teacher.

SELECT 
    s.student_id, 
    t.teacher_id, 
    s.student_name, 
    t.teacher_name 
FROM 
    student s, teacher t 
WHERE 
    s.height < t.height

Hash Join: Hash join is a way of executing a join where the hash table is used to find the match record. A Hash table creates in the memory. If the data is very large and memory is not sufficient to hold the data then it writes to disk. It is efficient compared to the Nested loop join. During the execution, RDMS build the in-memory hash table where rows from one of the join tables is stored using the join attributes as the key. Once it is done then the server starts reading rows from another table and finds the matching row from the hash table. This is used mostly when the join operators use equality.
Lets say we created two table. First table is student table and second table is class table. Each student belongs to one of the class. Now when we join these two table using the class id to get the student and details of class that this student belongs to, then sql server may use the hash join.

SELECT 
    s.student_Id ,
    s.student_name ,
    c.fund ,
    c.strength 
FROM 
    student AS s 
    JOIN
    class AS c ON 
        s.class_id = c.id;

When we do the join sql server create the hash table using the class table. 'class_id' is the key here. Now when sql go through student table, it get the class data from hash table using the class_id.

Merge Join: Merge join is used if the join condition uses an equality operator and both sides of the join are large. Merge join uses the sorted data inputs. So if there is an index on the expressions used in the join column then it is used to get the sorted data. But if the server is doing any operation to sort the data then please look at the indexes and better try to modify the indexes in order to achieve better results.

Read part 2 here.

How spring boot application work as web application

Amit Tiwary — Sat, 26 Nov 2022 18:31:23 +0000

Spring boot has everything to create a web application and makes web application development fast and hassle-free. The embedded server makes web application creation easy in Spring boot. If embedded server doesn't exist, then we required to install web server like tomcat and deploy application there. After that, only we can use our Java application as web application. But with the help of embedded server, when we create the deployable unit of application i.e JAR, web-server becomes part of it.

Tomcat is the default embedded server for the Spring boot application. So we don't need to do anything to add tomcat in our Spring boot application because it comes with spring boot starter web.

<dependency>

 <groupId>org.springframework.boot</groupId>
 <artifactId>spring-boot-starter-web</artifactId>

</dependency>

If any client makes a request to our Java server, tomcat handles the request.This functionality is made possible by the HTTP Connector element. Two important attributes of HTTP connector are protocol and SSLEnabled. The protocol attribute defines the protocol the connector will use to communicate and bydefault set to HTTP/1.1. SSLEnabled attribute helps connector to decide to use SSL handshake/encryption/decryption. Tomcat has more than one HTTP connector, and we can use anyone. In this blog we will talk about NIO connector.

The NIO connector do non blocking I/O. NIO connector has two thread pools, one holds the poller thread, and other holds the worker thread. Whenever a client makes a request to our Java server, NIO connector uses a thread from poller thread pool and keeps the connection alive. It push these request to handle by the thread from worker threads. Size of both pools are configurable.

Hide credentials in spring boot

Amit Tiwary — Sat, 12 Nov 2022 16:39:43 +0000

Spring boot make it easy to create spring applications. It makes it easier and faster to set up, configure and run the web application. When I was working on one of the projects, I was required to use some credentials. But I can't use it directly in the code. It should be hidden and still accessible in the code. We know that we can add the variable in the application.properties file and then use it in the java code. But I was not ablw to find how can add the env variable in a file and then access it in the application.properties file. If variables can be added in a file and accessed in the application.properties then the file can be included in the gitignore, and code can be pushed to github and shared with others without exposing the credential.

So I did research and find out that we can import a file in the application.properties and use the variables. We are going to use the env.properties file to save the credentials. Create a env.properties file. I created this file in the resources folder, so that I can easily access in the application.properties.

Add some credentials in the env.properties file like

DB_USER=name_of_sql_db_user
DB_DATABASE_NAME=name_of_database
DB_PASSWORD=database_password
GOOGLE_API_KEY=google_api_credential

These are the secret info and can't be shared with everyone. But it is required to connect with the database server or to use the google service.

Now to access these variables in our java file, we have to import these variables in the application.properties file. Import the env.properties file so that we can get these variables in the application.properties file.

spring.config.import = env.properties
spring.datasource.username = DB_USER

env.properties file is in the same folder with the application.properties so it can be imported directly using the file name. We are telling the code to use the config from the import file i.e from env.properties.

Now to make sure that the credentials is not commited and pushed to github, we have to make sure that env.properties file is included in .gitignore.

/src/main/resources/env.properties

How FullText index works in MySQL

Amit Tiwary — Sun, 18 Sep 2022 06:56:05 +0000

MySQL is one of the most used relational database management systems. We all know that it is fast and easy to use. In MySQL, storage engines are the components that handle the SQL operations. InnoDB is that widely used storage engine in MySQL. One of the reasons that make SQL operations fast is the index. If you create the index correctly, your query can be very fast. We can create an index on int, char type(one word) columns, but we can also create a FULLTEXT index on the text-based columns where we save multi-word strings. In this blog, I will explain how the FULLTEXT index works.
We can create a full-text index while creating a table or altering the table and creating the index. InnoDB full-text indexes have an inverted index design. What is the inverted index? Inverted index stores a list of words and mapping of that word to the document in which that word appears. When we create the FULLTEXT index, InnoDB creates a hidden column FTS_DOC_ID. This FTS_DOC_ID is used in the index table to map the word to a document. What is an index table? When we create a FULL-TEXT index, then a set of index tables also get created automatically.

The index table is associated with the indexed table by the hex value. For example, in the above image, the indexed table table_id is 2243 and, in hex, it is 8c3. So index table FTS_00000000000008c3
The first six index table saves the mapping of the word and document id(FTS_DOC_ID) in which that word appears. When any new document(data in a column that has a FULLTEXT index) is inserted, it is tokenized and an individual word(known as a token) is inserted in the index table along with the doc_id of that document and the position of the word in the document. We don’t insert the stop words. StopWords are the list of commonly-used words that we can ignore for the purpose of the FULLTEXT index. YOu can get the default stop words from the table INNODB_FT_DEFAULT_STOPWORD. The words are partitioned among six index tables based on the character set sort weight of each word. If any word is already present in the index table then we update and include the doc_id of the new document. If we do this full process for each document, it may result in numerous insertions in the index table that can make concurrent access to these tables a point of contention. To avoid this problem InnoDB uses the cache. InnoDB temporarily caches index table insertion for recent rows, and holds the insertion until the cache is full. Once the cache is full, InnoDB does batch flushes to disk. The caching and batch flushing behaviour avoids frequent updates to index tables that could result in concurrent access issues during busy insert and update times. The batching technique also, avoids multiple insertions for the same word and minimizes duplicate entries. Instead of flushing each word individually, insertions for the same word are merged and flushed to the disk as a single entry, improving insertion efficiency while keeping index tables as small as possible. INNODB_FT_CACHE_INDEX_TABLE holds the newly inserted rows in a FULLTEXT index.

Index deletion
Deleting a record that has a full-text index column could result in numerous small deletions in the index tables, making concurrent access to these tables a point of contention. To avoid this problem, the DOC_ID of a deleted document is logged in a special FTS_DELETED[1] table whenever a record is deleted from an indexed table, and the indexed record remains in the full-text index. Before returning query results, information in the FTS_DELETED table is used to filter out deleted DOC_ID. The benefit of this design is that deletions are fast and inexpensive. The drawback is that the size of the index is not immediately reduced after deleting records. To remove full-text index entries for deleted records, run OPTIMIZE_TABLE on the indexed table with innodb_optimize_fulltext_only=ONto rebuild the full-text index.

MySQL performs a full-text search using the MATCH() AGAINST() syntax. We provide all the columns, separated by a comma, that need to be searched in MATCH and a string that need to search in AGAINST. Let's assume that the columns provided in the MATCH are indexed. When we query, then InnoDB checks index tables for the string. If the string is present then it fetches the DOC_ID and gets the row using the DOC_ID.

[1]: FTS_00000000000008c3_DELETED in above image contain the document IDs (DOC_ID) for documents that are deleted but whose data is not yet removed from the full-text index. FTS_00000000000008c3_DELETED_CACHE is in-memory version of FTS_00000000000008c3_DELETED. We first save it in cache and once cache is full, flush it to FTS_*_DELETED table.

localhost not point to correct ip address in node 18

Amit Tiwary — Sat, 02 Jul 2022 17:53:38 +0000

Nodejs is becoming very popular among developers for backend development. There are lots of resources to learn it. Recently, I faced an issue when I was running my node application. I am using MySQL for the database and use MySQL npm library to connect to my database and query data. Before you start doing the query, you need to config the MySQL library and then only it can connect to your database. You must pass the IP address where your database is hosted, user name, and password. I was testing locally, so I ran the MySQL server on my machine and used the localhost as the IP address to connect to it. But I was getting the continuous error that the library was not able to make connection to SQL. After research, I found that I am using node 18 in my system and in node 18 localhost is converted to ipv6 instead of ipv4. So I need to add the IP address of localhost i.e 127.0.0.1 to connect to my local SQL server.