Add free search for your website. Sign up now! https://webveta.alightservices.com/

Tag: MySQL

Categories
.Net EntityFramework MariaDB MySQL

MySQL DBFirst using Entity Framework

If there is an existing database and if you need to use Entity Framework, use Database First approach. Have Entity Framework generate the DBContext and the classes.

Install / Update Entity Framework tool:

dotnet tool install --global dotnet-ef
dotnet tool update --global dotnet-ef

Add the following nuget packages:

Microsoft.EntityFrameworkCore
Microsoft.EntityFrameworkCore.Design
Microsoft.EntityFrameworkCore.Relational
Pomelo.EntityFrameworkCore.MySql
Pomelo.EntityFrameworkCore.MySql.Design

Then run the following command:

dotnet ef dbcontext scaffold "Server=<SERVER>;User=<USERNAME>;Password=<PASSWORD>;Database=<DATABASE>;"  "Pomelo.EntityFrameworkCore.MySql"

The EF classes and DBContext gets scaffolded.

–

Mr. Kanti Kalyan Arumilli

Arumilli Kanti Kalyan, Founder & CEO
Arumilli Kanti Kalyan, Founder & CEO

B.Tech, M.B.A

Facebook

LinkedIn

Threads

Instagram

Youtube

Founder & CEO, Lead Full-Stack .Net developer

ALight Technology And Services Limited

ALight Technologies USA Inc

Youtube

Facebook

LinkedIn

Phone / SMS / WhatsApp on the following 3 numbers:

+91-789-362-6688, +1-480-347-6849, +44-07718-273-964

+44-33-3303-1284 (Preferred number if calling from U.K, No WhatsApp)

kantikalyan@gmail.com, kantikalyan@outlook.com, admin@alightservices.com, kantikalyan.arumilli@alightservices.com, KArumilli2020@student.hult.edu, KantiKArumilli@outlook.com and 3 more rarely used email addresses – hardly once or twice a year.

Categories
MySQL

MySQL “FOR UPDATE” And “SKIP LOCKED” clauses

MySQL has two special clauses that are very useful in certain scenarios.

FOR UPDATE:

In certain scenarios, we need atomicity i.e we try to lock certain rows, do something and then update the set of rows. FOR UPDATE clause is specifically for this reason.

Example:

START TRANSACTION;

SELECT * FROM table WHERE col1 BETWEEN 1 AND 5
FOR UPDATE;

....

COMMIT;

At this point whatever rows match the criteria, would be locked, the rows can be updated as per needs and COMMIT or ROLLBACK.

However, if there are other queries running in parallel, the performance might be slightly affected. Specifically, in large-scale multi-threaded applications. For this we would use SKIP LOCKED, i.e the other query would run, skipping the rows that were locked.

Now combing these two concepts, we can write a sql statement that locks some rows based on WHERE criteria and other sql statements can still continue happen.

START TRANSACTION;

SELECT * FROM table WHERE col1 BETWEEN 1 AND 5
FOR UPDATE SKIP LOCKED;

....

COMMIT;

–

Mr. Kanti Kalyan Arumilli

Arumilli Kanti Kalyan, Founder & CEO
Arumilli Kanti Kalyan, Founder & CEO

B.Tech, M.B.A

Facebook

LinkedIn

Threads

Instagram

Youtube

Founder & CEO, Lead Full-Stack .Net developer

ALight Technology And Services Limited

ALight Technologies USA Inc

Youtube

Facebook

LinkedIn

Phone / SMS / WhatsApp on the following 3 numbers:

+91-789-362-6688, +1-480-347-6849, +44-07718-273-964

+44-33-3303-1284 (Preferred number if calling from U.K, No WhatsApp)

kantikalyan@gmail.com, kantikalyan@outlook.com, admin@alightservices.com, kantikalyan.arumilli@alightservices.com, KArumilli2020@student.hult.edu, KantiKArumilli@outlook.com and 3 more rarely used email addresses – hardly once or twice a year.

Categories
MySQL SQL

Formatting the output of MySQL into JSON

Sometimes there would be a need to format the output of SQL statement into a format such as XML or JSON.

SQL Server has “FOR XML” clause. I have been familiar with SQL Server in the past. I had a similar use-case with MySQL. MySQL has functions for outputting as JSON.

This can be achieved by using JSON_OBJECT, JSON_ARRAYAGG functions.

Assume we have a table ‘Sample’ with columns Id, Name.

MySQL Table – Sample

Here is some sample data:

Sample data

We can use JSON_OBJECT to create JSON objects like this:

select json_object('id', id, 'name', name) from sample;

In the above line of code we are asking that for each row create a JSON document with the attribute names of ‘id’, ‘name’ and use the column values of id, name for values. The output looks like this:

JSON_OBJECT output

We can use JSON_ARRAYAGG for aggregating the values into a single result like this, and even works with grouping.

select json_arrayagg(id) from sample;

The following output would be obtained.

Output of JSON_ARRAYAGG

Combing these two functions to generate a single JSON document with array for each row of the output.

select json_arrayagg(json_object('id', id, 'name', name)) from sample;
Output of JSON_ARRAYAGG and JSON_OBJECT

This code snippet has been very helpful for me when I was doing something for my own I.T Startup – ALight Technology And Services Limited. I came across these functions while searching on Google in StackOverflow thread.

I am hoping this blog post helps someone.

Happy development.

–

Mr. Kanti Kalyan Arumilli

Arumilli Kanti Kalyan, Founder & CEO
Arumilli Kanti Kalyan, Founder & CEO

B.Tech, M.B.A

Facebook

LinkedIn

Threads

Instagram

Youtube

Founder & CEO, Lead Full-Stack .Net developer

ALight Technology And Services Limited

ALight Technologies USA Inc

Youtube

Facebook

LinkedIn

Phone / SMS / WhatsApp on the following 3 numbers:

+91-789-362-6688, +1-480-347-6849, +44-07718-273-964

+44-33-3303-1284 (Preferred number if calling from U.K, No WhatsApp)

kantikalyan@gmail.com, kantikalyan@outlook.com, admin@alightservices.com, kantikalyan.arumilli@alightservices.com, KArumilli2020@student.hult.edu, KantiKArumilli@outlook.com and 3 more rarely used email addresses – hardly once or twice a year.

Categories
MariaDB MySQL Security

Auditing MariaDB SQL statements and events

As part of implementing NIST Cyber Security Framework at ALight Technology And Services Limited, one of the important thing to audit / log was database server. I am currently ingesting some logs into CloudWatch. In a blog post / youtube video in the future, I would show how to ingest logs into CloudWatch.

As a one person I do multiple things, now, I digged into some DBA work 🙂

This blog post is about writing audit log for MariaDB. In this blog post MariaDB Audit Plugin would be enabled and configured.

Update conf file, usually /etc/mysql/mariadb.cnf on Ubuntu, but could be different. Add the following lines under [mysqld]:

[mariadb]
plugin_load_add = server_audit
server_audit=FORCE_PLUS_PERMANENT
server_audit_file_path=/var/log/mysql/mariadb-audit.log # path to the audit log
server_audit_logging=ON
server_audit_events = 'CONNECT,QUERY,TABLE'
server_audit_file_rotate_size=1000000 # in bytes
server_audit_file_rotations=10

That’s all. The variables are pretty much self-explanatory. There are few more variables that can be used. Here is a link explaining the variables: Audit Plugin Options.

plugin_load_add – loads the plugin.

server_audit – we want the plugin to be permanently activated.

server_audit_file_path – Path to the file.

server_audit_logging – ON – we want the logging to happen

server_audit_events – We are logging connection requests, queries including failed queries and the affected tables.

server_audit_file_rotate_size – Max file for log before generating new file.

server_audit_file_rotations – Number of older files to hold before deleting.

There is an option for writing into syslog, by settingserver_audit_logging = ‘syslog’ but that’s beyond the scope of current blog post and I would prefer having a seperate file instead of getting sql log mixed into syslog – Personal preference.

These steps are very important considering rogue R&AW spies equipment capabilities and the atrocities they are committing.

–

Mr. Kanti Kalyan Arumilli

Arumilli Kanti Kalyan, Founder & CEO
Arumilli Kanti Kalyan, Founder & CEO

B.Tech, M.B.A

Facebook

LinkedIn

Threads

Instagram

Youtube

Founder & CEO, Lead Full-Stack .Net developer

ALight Technology And Services Limited

ALight Technologies USA Inc

Youtube

Facebook

LinkedIn

Phone / SMS / WhatsApp on the following 3 numbers:

+91-789-362-6688, +1-480-347-6849, +44-07718-273-964

+44-33-3303-1284 (Preferred number if calling from U.K, No WhatsApp)

kantikalyan@gmail.com, kantikalyan@outlook.com, admin@alightservices.com, kantikalyan.arumilli@alightservices.com, KArumilli2020@student.hult.edu, KantiKArumilli@outlook.com and 3 more rarely used email addresses – hardly once or twice a year.

Categories
MySQL

Connecting to MySQL or MariaDB from Linux Command Line

There could be several reasons for working with MySQL from command prompt such as via SSH etc… This is a brief tutorial on some important mysql commands.

Connect by issuing the following command:

> mysql -u root -p

Then you might be prompted for password and after entering the correct password, MySQL shell would be accessible.

The following are some important commands for viewing databases, creating databases, creating users, and providing privileges. The commands are pretty much self-explanatory:

> show databases; -- Shows databases

> use <DATABASE_NAME>; -- Uses database, i.e next set of commands would be against the database, can be used on a necessity basis

> show tables; -- Viewing existing tables in a particular schema

> CREATE DATABSE <DATABASE_NAME>; - Creates a new schema

> CREATE USER 'new_user'@'localhost' IDENTIFIED BY 'password'; -- Creates a new user with userid of "new_user" and password of "password"

> GRANT PRIVILEGE ON database.table TO 'username'@'localhost'; -- Grants privileges on the specific table in the specific database

> GRANT PRIVILEGE ON database.* TO 'username'@'localhost'; -- Grants privileges on all tables in the specific database

> FLUSH PRIVILEGES; -- Not necessary but for the purpose of completeness

> exit; -- Exiting MySQL shell

MariaDB also has the same syntax.

Hoping this blog post helps someone.