The total number of locks exceeds the lock table size

 SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

SET GLOBAL innodb_buffer_pool_size = 2147483648;  -- 2GB

Note: The value is in bytes (2GB = 2 * 1024 * 1024 * 1024). Adjust it based on your available memory.

🔸 Limitations:

• This change is temporary and will reset when MySQL restarts.
• To make it permanent, you need to update my.cnf or my.ini

openssl PEM to CRT

 openssl x509 -outform der -in your-cert.pem -out your-cert.crt

How to print log4j logs in Intellij Console

 log4j.properities file

log4j.rootLogger=INFO, console log4j.appender.console=org.apache.log4j.ConsoleAppender log4j.appender.console.layout=org.apache.log4j.PatternLayout log4j.appender.console.layout.ConversionPattern=%d{yyyy-MM-dd HH:mm:ss} %-5p %c{1} - %m%n

Add this to VM options

-Dlog4j.configuration=file:{path}/log4j.properties

Access Mysql user without sudo in ubuntu command line

 First login in as the root user using sudo command

sudo mysql -u root -p password

CREATE USER 'newuser'@'%' IDENTIFIED BY 'password';

GRANT ALL PRIVILEGES ON *.* TO 'newuser'@'%' WITH GRANT OPTION;

or

GRANT ALL PRIVILEGES ON DatabaseName.* TO 'newuser
'@'%' WITH GRANT OPTION;

FLUSH PRIVILEGES;

now everything is completed, logout from the sudo user with Exit 

or Ctrl + Z. Then try with 

mysql -u newuser -p password

MSSQL Data formatter

Below is a list of formats and an example of the output.  The date used for all of these examples is "2006-12-30 00:38:54.840".

DATE ONLY FORMATS
Format #	Query	Sample
1	select convert(varchar, getdate(), 1)	12/30/06
2	select convert(varchar, getdate(), 2)	06.12.30
3	select convert(varchar, getdate(), 3)	30/12/06
4	select convert(varchar, getdate(), 4)	30.12.06
5	select convert(varchar, getdate(), 5)	30-12-06
6	select convert(varchar, getdate(), 6)	30 Dec 06
7	select convert(varchar, getdate(), 7)	Dec 30, 06
10	select convert(varchar, getdate(), 10)	12-30-06
11	select convert(varchar, getdate(), 11)	06/12/30
12	select convert(varchar, getdate(), 12)	061230
23	select convert(varchar, getdate(), 23)	2006-12-30
101	select convert(varchar, getdate(), 101)	12/30/2006
102	select convert(varchar, getdate(), 102)	2006.12.30
103	select convert(varchar, getdate(), 103)	30/12/2006
104	select convert(varchar, getdate(), 104)	30.12.2006
105	select convert(varchar, getdate(), 105)	30-12-2006
106	select convert(varchar, getdate(), 106)	30 Dec 2006
107	select convert(varchar, getdate(), 107)	Dec 30, 2006
110	select convert(varchar, getdate(), 110)	12-30-2006
111	select convert(varchar, getdate(), 111)	2006/12/30
112	select convert(varchar, getdate(), 112)	20061230
TIME ONLY FORMATS
8	select convert(varchar, getdate(), 8)	00:38:54
14	select convert(varchar, getdate(), 14)	00:38:54:840
24	select convert(varchar, getdate(), 24)	00:38:54
108	select convert(varchar, getdate(), 108)	00:38:54
114	select convert(varchar, getdate(), 114)	00:38:54:840
DATE & TIME FORMATS
0	select convert(varchar, getdate(), 0)	Dec 12 2006 12:38AM
9	select convert(varchar, getdate(), 9)	Dec 30 2006 12:38:54:840AM
13	select convert(varchar, getdate(), 13)	30 Dec 2006 00:38:54:840AM
20	select convert(varchar, getdate(), 20)	2006-12-30 00:38:54
21	select convert(varchar, getdate(), 21)	2006-12-30 00:38:54.840
22	select convert(varchar, getdate(), 22)	12/30/06 12:38:54 AM
25	select convert(varchar, getdate(), 25)	2006-12-30 00:38:54.840
100	select convert(varchar, getdate(), 100)	Dec 30 2006 12:38AM
109	select convert(varchar, getdate(), 109)	Dec 30 2006 12:38:54:840AM
113	select convert(varchar, getdate(), 113)	30 Dec 2006 00:38:54:840
120	select convert(varchar, getdate(), 120)	2006-12-30 00:38:54
121	select convert(varchar, getdate(), 121)	2006-12-30 00:38:54.840
126	select convert(varchar, getdate(), 126)	2006-12-30T00:38:54.840
127	select convert(varchar, getdate(), 127)	2006-12-30T00:38:54.840
ISLAMIC CALENDAR DATES
130	select convert(nvarchar, getdate(), 130)
131	select convert(nvarchar, getdate(), 131)	10/12/1427 12:38:54:840AM

You can also format the date or time without dividing characters, as well as concatenate the date and time string:

Sample statement	Output
select replace(convert(varchar, getdate(),101),'/','')	12302006
select replace(convert(varchar, getdate(),101),'/','') + replace(convert(varchar, getdate(),108),':','')	12302006004426

ref link: https://www.mssqltips.com/sqlservertip/1145/date-and-time-conversions-using-sql-server/

MySql script to provide tables names and its row count in a DB

SELECT table_name, table_rows
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = '**YOUR SCHEMA**';

SELECT CONCAT(
    'SELECT "', 
    table_name, 
    '" AS table_name, COUNT(*) AS exact_row_count FROM `', 
    table_schema,
    '`.`',
    table_name, 
    '` UNION '
) 
FROM INFORMATION_SCHEMA.TABLES 
WHERE table_schema = '**my_schema**';

ref link: https://stackoverflow.com/questions/286039/get-record-counts-for-all-tables-in-mysql-database

MSSQL server script to provide tables names and its row count in a DB

SELECT 
    TableName = t.NAME,
    TableSchema = s.Name,
    RowCounts = p.rows
FROM 
    sys.tables t
INNER JOIN 
    sys.schemas s ON t.schema_id = s.schema_id
INNER JOIN      
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
WHERE 
    t.is_ms_shipped = 0
GROUP BY
    t.NAME, s.Name, p.Rows
ORDER BY 
    s.Name, t.Name

ref link: https://stackoverflow.com/questions/9954775/script-that-provides-the-row-counts-and-table-names

Spring boot, spring security jwt authentication

Please refer the below link

https://www.callicoder.com/spring-boot-spring-security-jwt-mysql-react-app-part-2/

For roles and privileges

https://www.baeldung.com/role-and-privilege-for-spring-security-registration

How to call a request before executing an API call in postman?

Add the below code in the Pre-request Script tab

pm.sendRequest({
    url: "http://localhost:8080/login",
    method: 'POST',
    header: {'Content-Type': 'application/json'},
    body: {
        mode: 'raw',
        raw: JSON.stringify({
            username: postman.getEnvironmentVariable("username"),
            password:postman.getEnvironmentVariable("password") })
    }
}, function (err, res) {
    postman.setEnvironmentVariable("userId", res.json().userId);
});

Reference: https://gist.github.com/madebysid/b57985b0649d3407a7aa9de1bd327990

Import Mysql script

Problem:

When importing a Sql script into Mysql DB and the process hangs

Solutions:

1. Due to memory issue. In this case memory need to be increased, which can be achieved by the below script

SET GLOBAL max_allowed_packet=1073741824;

How to fix Port already in use windows ?

Open command prompt and run the following commands

C:\Users\username>netstat -o -n -a | findstr 0.0:8080
   TCP    0.0.0.0:8080      0.0.0.0:0              LISTENING       2138

C:\Users\username>taskkill /F /PID 2138

How to check a port is open and listening?

Solution


netstat -tuplen

How to run maven build through command prompt?

Change directory and move into the folder where the pom.xml file exists. Then run the following two commands to build the maven.

Clean Compile:

mvn clean compile -Dmaven.test.skip=true

Run:

mvn install -Dmaven.test.skip=true

-Dmaven.test.skip=true  will skip the test folder and files.

How to create admin and db based user in mongodb?

Admin User:

db.createUser(
    {
      user: "admin",
      pwd: "password123",
      roles: ["userAdminAnyDatabase","userAdmin","readWrite","dbAdmin","clusterAdmin","readWriteAnyDatabase","dbAdminAnyDatabase"]
    }
)

DB based User:

User who can access only a specific database.

db.createUser(
   {
     user: "myuser",
     pwd: "password123",
     roles: [ { role: "readWrite", db: "databasename" } ]
   }
);

How to get total number of active connections in linux?

Solution

netstat -n | grep [:portnumber] | grep ESTABLISHED | wc -l

Example

netstat -n | grep :8080 | grep ESTABLISHED | wc -l

Explanation 

netstat (network statistics) command displays network connections (both incoming and outgoing), routing tables, and a number of network interface (network interface controller or software-defined network interface) and network protocol statistics.

-n: Show numerical addresses instead of trying to determine symbolic host, port or user names

grep (global regular expression print) command processes text line by line and prints any lines which match a specified pattern.

wc (word count) command prints a count of newlines, words, and bytes for each input file. 

-l: print the newline counts.