Find locks in MySQL

In MySQL sometimes one transaction blocks another. The tables that contain information about InnoDB transactions and data locks enable you to determine which transaction is waiting for another, and which resource is being requested. Table used to check locks is information_schema.

We can get locks information using following two methods:

Method1: Using the SHOW FULL PROCESSLIST;

SHOW FULL PROCESSLIST;

ERROR 1040 (HY000): Too many connections MySQL

Following error occurs when connection limit reaches the maximum limit as defined in the configuration file. The variables holding this value is max_connections. If you want connect MySQL server either kill some of existing connections or login as root and then increase the max_connections limits.

ERROR 1040 (HY000): Too many connections

To check the current value of this variable, login as root user and run the following command:

show variables like “max_connections”;

mysql> show variables like “max_connections”;

Output

+-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | max_connections | 151 | +-----------------+-------+

Solution for ERROR 1040 (HY000): Too many connections

Show running processes in MySQL

We can get the current running processes in MySQL using ‘show processlist’. This will give us the username, connected database, CPU, query running by user.

show processlist;

mysql> show processlist; +----+---------+-----------+---------+---------+------+----------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+---------+-----------+---------+---------+------+----------+------------------+ | 26 | root | localhost | sakila | Query | 0 | starting | show processlist | | 29 | mgeorge | localhost | company | Sleep | 2 | | NULL | +----+---------+-----------+---------+---------+------+----------+------------------+ 2 rows in set (0.00 sec)

How to save MySQL Query results to csv file

In this tutorial, we are going to export the MySQL query results to csv file. CSV stands for comma separated values. We can often use the CSV file format to exchange the data between applications such as Microsoft Excel, Open Office, Google Docs, etc.

Here we are going to export the table ‘film’ from the sakila database.

For following query result, we have to export to csv file.

select actor_id,first_name,last_name, last_update from actor;

How to get MySQL Version

Using this tutorial, we can find the MySQL Version with different methods. We can find MySQL Server using MySQL command or Linux based commands. Following are easy methods

1. By running following MySQL command.

SELECT version();
or
SELECT VERSION();
or
select @@version;

Output:

mysql> select version(); +-------------------------+ | version() | +-------------------------+ | 5.7.20-0ubuntu0.16.04.1 | +-------------------------+

mysql> select VERSION(); +-------------------------+ | VERSION() | +-------------------------+ | 5.7.20-0ubuntu0.16.04.1 | +-------------------------+

How to check MySQL Server Status on Linux

This article explains to get MySQL Server status information on Linux using mysqladmin command or the mysql init script. The information return uptime of the MySQL Server,Version, Open Tables, Socket type,..

Method 1:Using Status option

mysqladmin -u root -p status

Output

Enter password: Uptime: 98350 Threads: 1 Questions: 18 Slow queries: 0 Opens: 107 Flush tables: 1 Open tables: 26 Queries per second avg: 0.000

Method 2: Using Version option

The maximum number of configured devices has already been reached

Following error recieved when tried to create raw device in the server.

disk init
name=’data43′,
physname=’/data/devices/sb_device_data43′,
size=’40000M’

Msg 5162, Level16, State1: Server 'SKN_UAT1',Line1: The maximum number 42 of configured devices has already been reached. Please reconfigure 'number of devices' to a larger value and retry disk initialization.

Solution:The maximum number of configured devices has already been reached

Increase the The maximum number of configured devices to larger value than 42 by using sp_configure procedure.

How to get PostgreSQL Version

We have following methods to know the version of PosgreSQL running on the server.

Method1: Using psql -V in shell(Linux) or command line(Windows)

psql -V

psql (PostgreSQL) 9.6.3

Method 2: Goto the installation path and open PG_VERSION file. Which contains PostgreSQL version information.)

On Linux
postgres@ubuntu:~/9.5/main$ more PG_VERSION
9.5

R(R Studio) Connection with PostgreSQL

R(R Studio) Connection with PostgreSQL: We have to use R package: RPostgreSQL

1. Install RPostgreSQL package

install.packages(“RPostgreSQL”)

Installing package into ‘C:/Users/pinnapav/Documents/R/win-library/3.3’ (as ‘lib’ is unspecified) trying URL 'https://cran.rstudio.com/bin/windows/contrib/3.3/RPostgreSQL_0.6-2.zip' Content type 'application/zip' length 433117 bytes (422 KB) downloaded 422 KB package ‘RPostgreSQL’ successfully unpacked and MD5 sums checked The downloaded binary packages are in C:\Users\pinnapav\AppData\Local\Temp\RtmpWcxGA7\downloaded_packages

2. Load RPostgreSQL library.

library(RPostgreSQL)

Load PostgreSQL sample database dvdrental

PostgreSQL sample database that can used for learning and practice PostgreSQL. We will use the DVD rental database for demonstrating the features of PostgreSQL. The DVD rental database has many objects including: 15 tables,one trigger,7 views, 8 functions, one domain and 13 sequences.

Follow below steps to load(install) PostgreSQL sample database dvdrental.

1. Download the PostgreSQL database from http://k2schools.com/wp-content/uploads/2017/12/dvdrental.zip

Powered by k2schools