Mysql table locking

Locking is important in many scenarios to prevent other sessions from modifying tables during periods when a session requires exclusive access to them. for example altering table definition online or any kind of table definition changes.locking Mysql provides an option to lock table/s with different types of locks, depends on need.

syntax for lock table:

    tbl_name [[AS] alias] lock_type
    [, tbl_name [[AS] alias] lock_type] ...



Following are the examples for READ and WRITE LOCK:


session1> create table t1( c1 int);
Query OK, 0 rows affected (0.06 sec)

session1> insert into test.t1 values(1001);
Query OK, 1 row affected (0.01 sec)

session1> lock table t1 READ;
Query OK, 0 rows affected (0.00 sec)

session1> select count(*) from t1;
| count(*) |
| 1 |
1 row in set (0.00 sec)
session1> insert into t1 values(1002);
ERROR 1099 (HY000): Table 't1' was locked with a READ lock and can't be updated

Session1 acquired READ lock on table t1 explicitly. After applying READ lock on table users can read the table but not write it.

session2> lock table t1 READ;
Query OK, 0 rows affected (0.00 sec)

session2> insert into t1 values(1002);
ERROR 1099 (HY000): Table 't1' was locked with a READ lock and can't be updated

session3> select * from t1;
| c1 |
| 1001 |
1 row in set (0.00 sec)

Multiple sessions can acquire a READ lock on the table at the same time and other sessions can read the table without explicitly acquiring a READ lock.

currently, READ lock s acquired by session1 and session2, both locks need to be unlocked in order to perform a write operation on lock table.

session1> UNLOCK TABLES;
session1> insert into t1 values(1005);

INSERT operation executed from session1  will go in waiting state, since READ lock acquired on table t1 by session2 and not  released yet.

You can see this using:

session3> show processlist;
| Id | User | Host | db | Command | Time | State | Info |
| 2 | session1 | localhost | test | Query | 89 | Waiting for table metadata lock | insert into test.t1 values(1005) |
| 3 | session3 | localhost | test | Query | 0 | starting | show processlist |
| 4 | session2 | localhost | test | Sleep | 77 | | NULL |
3 rows in set (0.00 sec)

After releasing READ lock by session2 insert operation will execute on t1 table.

session2> UNLOCK TABLES;
session1>  select * from t1;
| c1 |
| 1001 |
| 1005 |
2 rows in set (0.00 sec)


FLUSH TABLE:  Closes all open tables, forces all tables in use to be closed, and flushes the query cache. FLUSH TABLES also removes all query results from the query cache, like the RESET QUERY CACHE statement. FLUSH TABLE will not work when table acquires READ LOCK.

UNLOCK TABLES: UNLOCK TABLES explicitly releases any table locks held by the current session. LOCK TABLES implicitly releases any table locks held by the current session before acquiring new locks.



-The session that holds the lock can read and write the table.

session1> lock table t1 write;
Query OK, 0 rows affected (0.00 sec)

session1> insert into test.t1 values(1006);
Query OK, 1 row affected (0.01 sec)

session1> select count(*) from t1;
| c1 |
| 1001 |
| 1005 |
| 1006 |
3 rows in set (0.00 sec)

-Only the session that holds the lock can access the table. No other session can access it until the lock is released.

session2> select count(*) from t1;
session3> insert into test.t1 values(1002);

session1> show processlist;
| Id | User | Host | db | Command | Time | State | Info |
| 2 | session1 | localhost | test | Query | 0 | starting | show processlist |
| 3 | session2 | localhost | test | Query | 127 | Waiting for table metadata lock | select count(*) from t1 |
| 4 | session3 | localhost | test | Query | 116 | Waiting for table metadata lock | insert into test.t1 values(1002) |
3 rows in set (0.00 sec)

Look into performance_schema.metadata_locks table for more status information on table locks. (Thanks for the hint daniel )

– Enable Locking related instruments (if it’s not enabled) :

UPDATE performance_schema.setup_instruments SET ENABLED=’YES’, TIMED=’YES’ WHERE NAME=’wait/lock/metadata/sql/mdl’;

SELECT * FROM performance_schema.metadata_locks WHERE OBJECT_SCHEMA=’test’ AND OBJECT_NAME LIKE ‘t_’;

-Lock requests for the table by other sessions block while the WRITE lock is held.

All set  🙂 ……

2014 in review . Thank you all and Happy new year

The stats helper monkeys prepared a 2014 annual report for this blog.

Here's an excerpt:

A San Francisco cable car holds 60 people. This blog was viewed about 3,600 times in 2014. If it were a cable car, it would take about 60 trips to carry that many people.

Click here to see the complete report.

[ERROR] COLLATION ‘utf8_general_ci’ is not valid for CHARACTER SET ‘latin1’

Recently came across the problem where mysql server stop running and refusing to start with an error

The server quit without updating PID file (/var/run/mysqld/

Checked Mysql error log and found that an invalid usage of charset with collation causing problem.

error log:
141017 12:20:41 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data
while 15088 [ERROR] COLLATION 'utf8_general_ci' is not valid for CHARACTER SET 'latin1'
15088 [ERROR] Aborting

In this case mysqld trying to start  with  character-set-server = latin1 and collation-server = utf8_general_ci, which is not valid.
The following is the right charset value for COLLATION ‘utf8_general_ci’

node1 [localhost] {msandbox} ((none)) > SHOW COLLATION LIKE ‘utf8_general_ci’;


| Collation       | Charset | Id | Default | Compiled | Sortlen |


| utf8_general_ci | utf8    | 33 | Yes     | Yes      |       1 |



Add following options in my.cnf

character-set-server = utf8
collation-server = utf8_general_ci
character-set-client-handshake = false

And start  mysql server.It will start without any error.


linux swap and mysql memory

After mysql installation, To make mysql server production ready we have to tune default mysql variables considering the hardware, production load, performance, durability etc.

But What about the optimizing  Linux OS settings  with mysql ?


Here it is, i encountered  an issue once when i was checking TOP output for mysqld process and found that the mysqld  memory continuously increasing. Also there was one more thing  that it was using swap memory even if there was enough RAM memory available. we have monitored for a week and this was causing an issue like slow query processing, performance and slowness on DB server.

To solve this problem we have to set correct swappiness

  •       To check vm.swappiness

                    cat /proc/sys/vm/swappiness    (Default value : 60 (Range 0 to 100))

  •    To set a new non-persistent value :

                     sysctl -w vm.swappiness=0

  •    To set a new persistent value :

                     add vm.swappiness=0 in the /etc/sysctl.conf file

All Set !!

About SWAP space:

The swappiness parameter controls the tendency of the kernel to move processes out of physical memory and onto the swap disk. Because disks are much slower than RAM, this can lead to slower response times for system and applications if processes are too aggressively moved out of memory.

  1. swappiness can have a value of between 0 and 100
  2. swappiness=0 tells the kernel to avoid swapping processes out of physical memory for as long as possible
  3. swappiness=100 tells the kernel to aggressively swap processes out of physical memory and move them to swap cache

Following are basic instructions for checking swappiness, emptying your swap and changing the swappiness to 0:

To check memory usage status :


To check the swappiness value:

         cat /proc/sys/vm/swappiness

To turn swapoff :
This will empty your swap and transfer all the swap back into memory. First make sure you have enough memory available by viewing the resources tab of gnome-system-monitor, your free memory should be greater than your used swap. This process may take a while, use gnome-system-monitor to monitor and verify the progress.

sudo swapoff -a

To set the new value to 0:

echo 0 | sudo tee /proc/sys/vm/swappiness

To turn swap back on:

sudo swapon -a

mysql geographical replication lag

Recently while working on geographical master slave replication we encountered an issue, where in peak hours replication slave lagging.

when we checked SHOW SLAVE STATUS value of Seconds_Behind_Master was jumping from 0 to a random high value, and then back to 0.

We also found this in the processlist of Master,

1366318 rpluser [IP Address]:15120  NULL  Binlog  Dump   9033    Writing to net  NULL

The status “Writing to net” means that MASTER is sending (or trying to send) data to SLAVE.

In a healthy replication, the status would be “Has sent all binlog to slave; waiting for binlog to be updated“.

It usually happens when MASTER-SLAVE geographically distributed in a different data-center, network , and slow network.

Here limited network bandwidth was causing a problem, and we needed a solution to compress the transmission between the master and slave databases. MySQL provides a variable, which is dynamic and is set on the slave: slave_compressed_protocol=1

If this option is set to 1, use compression for the slave/master protocol if both the slave and the master support it. The default is 0 (no compression).

changes on slave:

show global variables like 'slave_compressed_protocol';   //default OFF

stop slave;

set global slave_compressed_protocol=1;

start slave;

show global variables like 'slave_compressed_protocol';     // ON

Also,  check PROCESSLIST on master and replication user status,


FreeRadius 3.0.x Installation and configuration with Mysql

This document describes how to setup a FreeRADIUS server. A MySQL server is used as backend and for the user accounting.

RADIUS is an industry-standard protocol for providing authentication, authorization, and accounting services.

  • Authentication is the process of verifying a user’s identity and associating additional information (attributes) to the user’s login session.
  • Authorization is the process of determining whether the user is allowed on the network and controlling network access values based on a defined security policy.
  • Accounting is the process of generating log files that record session statistics used for billing, system diagnosis, and usage planning.

Continue reading

mysql 5.6 GTID replication errors and fixes

What is GTID? 


  • This is the server’s 128 bit identification number (SERVER_UUID). It identifies where the transaction was originated. Every server has its own SERVER_UUID.

What problems GTID solves?

  • It is possible to identify a transaction uniquely across the replication servers. Make the automation of failover process much easier. There is no need to do calculations, inspect the binary log and so on. Just MASTER_AUTO_POSITION=1.
  • At application level, it is easier to do WRITE/READ split. After a write on the MASTER, you have a GTID so just check if that GTID has been executed on the SLAVE that you use for reads.
  • Development of new automation tools isn’t a pain now.

How can I implement it?

Three variables are needed in ALL servers of the replication chain

  • gtid_mode: It can be ON or OFF (not 1 or 0). It enables the GTID on the server.
  • log_bin: Enable binary logs. Mandatory to create a replication environment.
  • log-slave-updates: Slave servers must log the changes that come from the master in its own binary log.
  • enforce-gtid-consistency: Statements that can’t be logged in a transactionally safe manner are denied by the server.


Replication errors and fixes:

“‘Got fatal error 1236 from master when reading data from binary log: ”The slave is connecting using CHANGE MASTER TO MASTER_AUTO_POSITION = 1, but the master has purged binary logs containing GTIDs that the slave requires.” slave_io thread  stop running.

Resolution: Considering  following  are the master – slave UUID’s

MASTER UUID: 4c2ad77f-697e-11e3-b2c3-c80aa9f17dc4

SLAVE UUID: 5b37def1-6189-11e3-bee0-e89a8f22a444


slave>stop slave;


slave>show master  status;


(HERE 83345127  Last GTID executed on master and 5779965 Last slave GTID executed on Master )

slave> reset master;

slave>set global GTID_PURGED=’4c2ad77f-697e-11e3-b2c3-c80aa9f17dc4:1-83345127,5b37def1-6189-11e3-bee0-e89a8f22a444:1-5779965′;

slave>start slave;

slave>unlock  tables;

  slave>show slave status;

NOTE: After this Re-start slave other chain-slaves  if  they stop replicating;

ERROR: ‘Error ”Table … ‘doesn”t exist” on query. Default database: …Query: ”INSERT INTO OR Last_SQL_Error: ….Error ‘Duplicate entry’ SKIP Transaction on slave (slave_sql Thread stop  running) NOTE:

  • SQL_SLAVE_SKIP_COUNTER doesn’t work anymore with GTID.
  • We need to find what transaction is causing the replication to fail.
    • –  From binary log
    • –  From SHOW SLAVE STATUS (retrieved vs executed)

Type of  errors: (check  last sql error in show slave status)

Resolution: Considering  following  are the master – slave UUID’s

MASTER UUID: 4c2ad77f-697e-11e3-b2c3-c80aa9f17dc4

SLAVE UUID: 5b37def1-6189-11e3-bee0-e89a8f22a444

slave>show slave status;

copy  the ‘Executed_Gtid_Set’ value. ‘4c2ad77f-697e-11e3-b2c3-c80aa9f17dc4:1-659731804,5b37def1-6189-11e3-bee0-e89a8f22a444:1-70734947-80436012:80436021-80437839

-Seems that slave (with  uuid 5b37def1-6189-11e3-bee0-e89a8f22a444)  transaction ‘80437840‘ is causing the problem here.

slave> STOP SLAVE;

slave> SET GTID_NEXT=”5b37def1-6189-11e3-bee0-e89a8f22a444:80437840“;  (last_executed_slave_gtid_on_master + 1)




slave>  show slave status;

and it’s ALL SET !!!

freeradius refusing to start with libssl version OpenSSL Security advisory CVE-2014-0160 (Heartbleed)

When you start freeradius server ( ./radiusd -X  for debug mode), it will refuse to start with libssl version which is currently installed on your server. Root cause for this is “Security advisory CVE-2014-0160 (Heartbleed)”. FreeRadius 3.0.3 would not allow to start radius server if  you have openssl version in range 1.0.1 – 1.0.1f installed.

For more information see  .

Here is the steps to fix this,

1.Check current Openssl version.

openssl version -a

2.First you need to completely remove the old openssl

apt-get purge openssl
apt-get autoremove && apt-get autoclean

3. Download and compile the new open version you want

tar xzvf openssl-1.0.1g.tar.gz
cd  opemssl-1.0.1g

./Configure  OR  ./config
make install

cp /usr/local/ssl/bin/openssl /usr/bin/

4.Check Openssl version

openssl version -a

OpenSSL 1.0.1g 7 Apr 2014

5.Add “allow_vulnerable_openssl” in the “security” subsection of “radiusd.conf”

allow_vulnerable_openssl = 'CVE-2014-0160'

6. Start ./radiusd -X

All Set!! 🙂

Truncate multiple database tables In MySQL

To  truncate table we use TRUNCATE command As follows.

TRUNCATE  TABLE  <table_name>;

What if  you  want to  TRUNCATE  ALL tables from different   databases in mysql ,It is possible using  metadata information of  database. INFORMATION_SCHEMA database created while installing  mysql ,which  contains  all information like stores information about all the other databases that the MySQL server maintains.

Here  Is the Query  which  gives output  script with  truncate commands   for all table.You  don’t  have to  write truncate command  one by  one  for each  table.

SELECT Concat('TRUNCATE TABLE ',table_schema,'.',TABLE_NAME, ';') FROM INFORMATION_SCHEMA.TABLES where table_schema in (db1_name,db2_name);

Use Query Result to truncate tables

Note: may be you will get this error:

ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails

That happens if there are tables with foreign keys references to the table you are trying to drop.

Before truncating tables All you need to check  FOREIGN_KEY_CHECKS variable value

SET FOREIGN_KEY_CHECKS=0;   (This will  disable  FOREIGN KEY check)

Truncate your tables and change it back to