Friday, 13 October 2017

Understanding MySQL innodb_flush_log_at_trx_commit variable

The main objective of this post is to understand why innodb_fush_log_at_trx_commit variable is used? What are the applicable values for innodb_fush_log_at_trx_commit? How innodb_fush_log_at_trx_commit value will impact the MySQL performance and data safety?  How to change innodb_fush_log_at_trx_commit value? and How to change the frequency of InnoDB log flush?

Why innodb_fush_log_at_trx_commit?

In order to understand why we need innodb_fush_log_at_trx_commit variable, we should know about how InnoDB works. It is a huge and complex topic and it is not the scope of this article. I am trying to cover this topic in simple words and it is given below:

  1. InnoDB performs most of its operations at the memory (InnoDB Buffer Pool)
  2. It will write all changes from memory to the transaction log (InnoDB Log File)
  3. From transaction log - it will flush (write) data to storage disk (durable storage)

In order to achieve durability of data, we need to store each and every transaction data into hard disk storage. But consider, in a busy system, for each transaction commit, if InnoDB trying to flush (write) data to slow running disk, what will happen?, So how do we manage this situation, where we need to store each transaction data and at the same time maintaining good performance of the system.

The InnoDB provides the solution for this situation, based on your system, you can tell InnoDB, when you want to flush (write) data to disk. For example, you can tell InnoDB to work as mentioned below:

  1. Write to log file and flush data to disk in specified interval, not for every transaction commit.
  2. Write to log and flush to disk for each transaction commit.
  3. Write to log for every transaction commit but flush to disk at an interval not for each transaction commit.

Based on the application standard you can adjust these settings to maintain balance between performance and data safety. InnoDB provides a configurable variable to achieve this balance, this variable is called innodb_fush_log_at_trx_commit. This variable controls how often the log buffer is flushed.

What are the applicable values for innodb_fush_log_at_trx_commit and its impact?

InnoDB supports following 3 values for innodb_fush_log_at_trx_commit variable. Let see how these variables will change flush to disk behaviour:


When you set innodb_flush_log_trx_at_commit=0, InnoDB will write the modified data (in InnoDB Buffer Pool) to log file (ib_logfile) and flush the log file (write to disk) every second, but it will not do anything at transaction commit.

Here, in case if there is a power failure or system crash, all the unflushed data will not be recoverable, since, it is not either written to log file or stored disk.


When you set innodb_flush_log_trx_commit=1, InnoDB will write the log buffer to transaction log and flush to durable storage for every transaction.

Here, for all transaction commit, InnoDB will write to log and then write to disk, if in case the slower disk storage, it will badly impact the performance, i.e. the number of InnoDB transaction per second will be reduced.


When you set innodb_flush_log_trx_commit = 2, InnoDB will write the log buffer to log file at every commit, but don't write data to disk. InnoDB flushes data once in every second.
Option 2, even if there is a power failure or system crash, data will be available in log file and can be recoverable.

So which option to be used on your application?

We have seen, the behaviour of each values, now it is based on the application requirement, we need to choose this variable value.

If performance is the main goal, you can set the value as 2. Since, InnoDB writing to disk once in every second, not for every transaction commit and it will improve the performance dramatically. In case if there is power failure or crash, data can be recoverable from transaction log.

If data safety is the main goal, you can set the value as 1, so that for every transaction commit, InnoDB will flush to disk. But performance may reduce in this case.

If you set value as 0, InnoDB will write from buffer to log once in every second and it will not perform any flush to disk operation for every transaction commit. The problem with this option is if in case of any power failure or system crash, there may be a chance of losing data up to one second.

Key points:

During buffer to log operation, i.e. writing from InnoDB buffer pool to InnoDB transaction log file - data is simply moved from InnoDB buffer to Operating system's cache, actually not written to the durable storage. So if you set innodb_fush_log_at_trx_commit either 0 or 2, there is a possibility of losing data up to one second.

If innodb_fush_log_at_trx_commit is set to 1 - InnoDB compels Operating System to flush data to durable storage. Writing to disk is a slow process and it is I/O blocking operation to ensure data is written to disk. So using this option, there is a chance; number of transaction per second will be reduced.

Note that, by default MySQL will auto commit the transactions.

Setting innodb_fush_log_at_trx_commit as 2 is useful when restoring huge databases, it will reduce the restoration time dramatically, and there are different opinions on this point, it is better to test in our own hand. In my experience, it really helped with the reduced restoration time.

Data Definition Language (DDL) changes flush the InnoDB log, independent of the innodb_fush_log_at_trx_commit setting.

Crash recovery operation works regardless of the innodb_fush_log_at_trx_commit setting, I am writing another article on InnoDB crash recovery.

How to configure innodb_fush_log_at_trx_commit variable?

The scope of the innodb_fush_log_at_trx_commit is GLOBAL and it can be set at dynamically without restarting server.

Dynamically on command line you can set innodb_fush_log_at_trx_commit as follows:

SET GLOBAL innodb_fush_log_at_trx_commit = 2;

On configuration file, you can set it as follows:

innodb_fush_log_at_trx_commit = 2

Note: It requires server restart. Before making changes to configuration file, analyse the impact by setting it on dynamically.

If you try to set innodb_fush_log_at_trx_commit as session level variables, you will encounter the following error:

mysql> set session innodb_fush_log_at_trx_commit = 2;
ERROR 1229 (HY000): Variable 'innodb_fush_log_at_trx_commit' is a GLOBAL variable and should be set with SET GLOBAL

How to change the frequency of InnoDB log file flushing?

The variable innodb_flush_log_at_timeout controls the frequency in which InnoDB flush log files. The frequency ranges from 1 to 2700 seconds, with the default value 1.

Note that, the higher this number, there is a higher the chance of losing data, in case of power failure or system crash.

For example, if you set this value as 5 seconds, in case of power failure, you may lose data upto 5 seconds.

In replication topology, to maintain durability and consistency of data, you can leave the default value, i.e. innodb_fush_log_at_trx_commit = 1.

I hope this article help you to understand, how InnoDB flush data and how it impact MySQL performance and data safety, also, how you can configure this variable to achieve maximum benefit. 

In future article let us see about how InnoDB crash recovery works and its configuration settings. If I missed something or if you wish to share your thoughts on this article, please mention in comment section, I will edit the post after review.

Tuesday, 22 August 2017

How to store IP (internet protocol) address in MySQL?

Internet Protocol address is a special kind of data. This data does not correspond directly to available MySQL built-in data types. I have seen in many servers, the IP(Internet Protocol) address is stored in CHAR(12), VARCHAR(15), TEXT and so on. 

The dotted-quad notation is just a way of writing for better readability, it is not the original form of raw data. The IP address is a UNSIGNED INTEGERS they are not strings. 

So the question is how we can store the IP addresses with dot in integer columns? The MySQL provides the built-it function to do this operation. The functions are given below:

For IPv4 addresses:

mysql> select inet_aton('');

| inet_aton('') |
|             2130706433 | 
1 row in set (0.11 sec)
SELECT inet_ntoa('2130706433');

| inet_ntoa('2130706433') |
|               | 
1 row in set (0.02 sec)

For IPv6 addresses:

The MySQL provides the following built-in functions to store and retrieve the IPv6 addresses. Here, IPv6 addresses are not stored as integers, since numeric-format IPv6 addresses require more bytes than the UNSIGNED BIGINT. So the below functions returns the VARBINARY(16) data types. Let us see with an example.

mysql> select hex(inet6_aton(''));
| hex(inet6_aton('')) |
| 7F000001                             |
1 row in set (0.00 sec)

mysql> select hex(inet6_aton('2001:0db8:85a3:0000:0000:8a2e:0370:7334'));
| hex(inet6_aton('2001:0db8:85a3:0000:0000:8a2e:0370:7334')) |
| 20010DB885A3000000008A2E03707334                                 |
1 row in set (0.00 sec)

mysql> select inet6_ntoa(unhex('20010DB885A3000000008A2E03707334'));
| inet6_ntoa(unhex('20010DB885A3000000008A2E03707334')) |
| 2001:db8:85a3::8a2e:370:7334                                                    |
1 row in set (0.00 sec)


Assume that, you are writing a query to find out the users, who are connected from the internet protocol address So the following query can be derived.

SELECT name FROM user WHERE inet_ntoa(ipaddress) = '';

Be aware that, this query will not use the index created on the ipaddress column, since, we are modifying the index column during the execution, also, it will convert the integer into IP adress row by row. So the work around will be:

SET @ip = inet_aton('');
SELECT name FROM user WHERE ipaddress = @ip;

SELECT name FROM user WHERE ipaddress = inet_aton('');

Now if you check the query execution plan, you will see that, the query using index on ipaddress column. I hope this post may help you. Please share your views on comment section and if you have other better methods, please share it on comment section.

Tuesday, 1 August 2017

[Solved] Can’t connect to MySQL Server

There are a lot of different reasons behind this error. Sometime, you may not see the exact issue on the error message. You have to dig deeper to identify the exact cause. It may take a lot of time and energy to debug this error. I have encountered this error many times, so based on my experience and research on this issue; I have gathered and given the following possible reasons and workarounds to fix this error.

Error Message:

Can't connect to local MySQL server

Reason 1:
MySQL Server is not running on the server.

Workaround 1:
Check MySQL server is running on the server.

MySQL process name                    : mysqld
MySQL default port                        : 3306

On Linux:
To check the service status           : systemctl status mysql
To check the process list               : ps ax | grep mysql
To check the listening port           : netstat –anp | grep mysql

On Windows:
To check the service status          : Ctrl + R -> MySQL
To check the process list               : Task Manager -> mysqld           

If you are trying to connect to MySQL service running on remote server, use telnet.
telnet 3306                      (Replace the IP and Port)

If not running, start the MySQL service and try to connect to MySQL server. If MySQL is not starting, see the error log to find out the reason and fix the error. If this is the reason, the error could have resolved now.

Reason 2:
MySQL socket file is missing / deleted / different location.

MySQL server can be connected through either socket file or TCP/IP protocol.  When you are trying to connect, MySQL service running on localhost, it will be connected through the socket file.  If socket file missing, you will get error message with location of the file.

Can’t connect to local MySQL server through socket ‘/var/run/mysqld/mysqld.sock’ (2) when trying to connect.

Socket file is a special purpose file, it is used as interface to connect MySQL server from clients. This file is created automatically by the server. If the file is not exists, it is because either of the following issues:

MySQL server was not started.
MySQL is created socket file in different location.
Socket file unexpectedly removed / deleted /missing.
MySQL service is started with socket variable but client trying to connect to default socket.

Example: mysqld –socket=/var/lib/mysqld.sock

If MySQL server is not started:

Use workaround 1 to verify MySQL is running or not. If MySQL service is not running, start the MySQL service by using the following method:

On Linux:

sudo systemctl start mysql

On Windows:

Go to services.msc and start MySQL service.

Note: you can also start the mysql service by other methods too.

If MySQL is created socket file but it is in different location / MySQL service is started with socket variable but client trying to connect to default socket.

You can use mysqladmin utility to check the current location of the MySQL socket file, as below:

mysqladmin -u root  -h -p version

When you connect it from client, you can specify the client explicitly using - -socket variable or change the socket file location in configuration file (This required service restart).


If socket file is missing / removed /deleted:

I have seen some cases; socket file is missing for no reason. In this case, connect with TCP / IP protocol to restart the service. Socket, file cannot be created as plain empty file manually. The file should be created by server.

The worst part is you cannot shutdown the server. While stopping the server, it will look for socket file and if it not exists, it will through the socket file missing error. So the alternative method should be as follows:

To shutdown mysql server          : mysqladmin –h –protocol=tcp –u root –p shutdown
To login to MySQL server              : mysql –h –protocol=tcp –u root –p

Reason 3:
Security Enhanced Linux is protecting mysqld process.

Workaround 3:
Security Enhanced Linux (SELinux) is a Linux feature that provides mechanism for supporting access control security policies.

In simple words, it does not allow MySQL to access other directories and ports apart from pre-configured access objects. It means, by default, MySQL is configured to access /var/lib/mysql, but when you configured data directory and socket files to different folders, it does not permit access.

You can disable the SELinux protection for mysqld process. This you can done by:

To set SELinux into permissive mode        : Setenforce 0 (It will allow but show warnings!)

This settings is applicable still the next server restart. In order to permanently disable the SELinux (private networks), edit the configuration as follows:

To edit the SELinux configuration file      : vi /etc/selinux/config
To disable the SELinux protection             : SELINUX=disabled (restart required!)

If you wish to configure SELinux protection for mysqld process with proper privileges (production environment), you can refer this page for complete details.

Reason 4:

MySQL server is bound to single address (bind_address)

Workaround 4:

To edit bind_address variable                    : vi /etc/my.cnf
To change bind_address                               : bind_address=

Here, the problem is, sometimes, by default MySQL bind_address variable is set to localhost or some IP address. When you try to connect MySQL server from other hosts, MySQL does not allow, and provides the error message, since MySQL service is bound to only that bind_address value.

By changing bind_address value into will accept connections from all server hosts.

Reason 5:
Firewall blocking MySQL port:

Workaround 5:
If the firewall is blocking MySQL port 3306, you can open the port on host machine to fix this issue.

sudo iptables -I INPUT -p tcp --dport 3306 -j ACCEPT
sudo service iptables save

Note: The firewall rules for Windows and latest version of CentOS may vary, use accordingly.

Reason 6:
Access restricted to the server hosts.

Workaround 6:
There is a possibility that access restricted to server. You can allow access to server by editing the host.allow (Linux) / hosts (Windows file)

To change in Linux : vi /etc/host.allow
To change in Windows : %windows%\system32\drivers\etc
To allow access from all hosts : mysqld: ALL: allow (This is not best practice to allow all – allow only the web server / client hosts allow)

Note: See the TCP wrapper for more details (if you are interested!!!).

Other Possible Reasons:

  • Incorrect host name / IP address – verify you are giving correct IP address / hostname.
  • The user may not access to database - give access in MySQL using GRANT command.
  • The MySQL variable old_passwords = 1, old_password() is deprecated in MySQL 5.7. It is hashing method to store MySQL password. Setting this variable to 0 will allow you connect MySQL. (See more about this here)
  • The mysqld service unable to create pid file – this issue due to mysql user does not have access to create the file. Once, you given access this issue will be resolved.

I hope this article helps you in fixing this error. Still, if you are unable to fix this issue, please mention in comment section, I will get back to you to fix this error. 

Do you know, other workarounds to solve this issue? please mention in comment section, so that I will update the answer, it will be helpful to others.

Monday, 24 July 2017

[Solved] ERROR 1040: Too many connections - How to fix MySQL Too many connections error?

Error Message:

ERROR 1040 (HY000): Too many connections

MySQL Too many connections error
MySQL Too many connections

Reason for this error:

This error occurs when connection reaches the maximum limit as defined in the configuration file. The variables holding this value is max_connections

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

show global variables like max_connections;

MySQL max_connections variable
MySQL max_connections variable

Note: In above step, you can login using root, you may wonder, when it is showing Too many connection error, how can you login as root? The answer is, MySQL by default will consider maximum allowed connection as max_connection + 1 for SUPER user to fix this issue.

In our example - max_connection = 1, so MySQL will consider maximum allowed connection as 1 + 1 = 2.

Workaround 1:

Login to MySQL using root user and increase the max_connections variable to higher value.

SET GLOBAL max_connections = 100;

SET GLOBAL max_connections
SET GLOBAL max_connections

Now login to MySQL, the too many connection error fixed. This method does not require server restart. After MySQL server restart, the max_connection variable value again roll back to previous value. In order to make the max_connection value persistent, modify the value in the configuration file.

Workaround 2:

Stop the MySQL server:
Service mysql stop
Edit the configuration file my.cnf.
vi /etc/my.cnf
Find the variable max_connections under mysqld section.
max_connections = 100
Set into higher value and save the file.
Start the server.
Service mysqld start

Note: use systemctl manager to stop and start the service if, service command not working.

I hope this post, will help you to fix the issue. If still you are unable to fix this issue, mention on the below comment section. I will get back to you fix this issue.

Update 1:

You can avoid this too many connection error by using this method. For example, 5 applications are connecting MySQL server. So, the calculations to set the max_connections value is as follows.

Total users = 5 applications = 5 Users
Concurrent connections = 200 (depends on your applications)

max_connections = 5 x 200 + 200 = 1200.

In case of all users are connecting, maximum it reaches 1000 users and still 200 connections are remaining. This example is only for our understanding. You should not implement this method on production system, we should have a detailed understanding of the application access to limit the max_connections value. 

Update 2:

Before increasing the max_connections variable value, make sure that, the server has adequate memory for new requests and connections.

MySQL pre-allocate memory for each connections and de-allocate only when the connection get closed. And, when new connections are querying, system should have enough resources such memory, network and computation power to satisfy the user requests.

Also, you should consider increasing the open tables limit in MySQL server to accommodate the additional request. And finally. it is very important to close the connections which are completed transaction on the server.

Friday, 5 May 2017

Sunday, 9 April 2017

[Solved] ERROR 1045 (28000): Access denied for user 'user'@'host' (using password: YES)

Error message

Error 1045 (28000): Access denied for user ‘user’@’host’ (using password: YES)

Debugging Summary

  • Check for typo error: username or password. 
  • Check the host name and compare it with mysql.user table host name. 
  • Check user exists or not. 
  • Check whether host contains IP address or host name.
There is a great chance that, you might have encountered this issue multiple times in your work. This issue occurred to me most of times due to the incorrectly entering user name or password. Though this is one of the reasons, there are other many chances you might get this issue. Sometimes, it looks very similar, but when you dig deeper, you will realize multiple factors contributing to this error. This post will explain in detail, most of the common reasons and work around to fix this issue. 

Tuesday, 17 January 2017

How to change InnoDB log file size in MySQL?

The InnoDB log file is the transactional log, it captures all the transactions. The transaction log size is a very important factor in determining the performance of a database engine. The variable innodb_log_file_size represents the transactional log size.  This log size can be changed as follows:

Set the required log file size in the MySQL configuration file.

Perform the clean shutdown. This can be verified with the help of MySQL error log. A clean shutdown is also known as slow shutdown. This process will perform a full purge of InnoDB buffer pool. The value of the innodb_fast_shutdown variable indicates the shutdown type. Slow shutdown can be performed by setting up as follows. 

SET GLOBAL innodb_fast_shutdown = 0;

innodb_fast_shutdown = 0
Take a backup of the existing InnoDB log files - just a precaution.

Remove the existing InnoDB log files. By default 2 InnoDB log files in the group, it is named as ib_logfile0, ib_logfile1.

rm /var/lib/mysql/ib_logfile*
Start the MySQL service. 

sudo service mysqld start

When the server is starting, new log files are created with the same name. This can be verified by checking the error log.  

tail  /var/lib/mysql/mysqld.err

Note: If the existing files not removed, the server will not start and it will write the error statement on the error log.

InnoDB: Error: log file ./ib_logfile0 is of different size 0 xxxxxxxxxxx bytes
InnoDB: than specified in the .cnf file 0 xxxxxxxxxx bytes! 

Why innodb_log_file_size is important?

As we have seen, the InnoDB log file is an InnoDB transaction log, to understand the importance of the InnoDB transaction log, we need to know the UNDO and REDO operations. Before that, let us understand how MySQL works when a query executes:

When a write operation is happening on MySQL, it is not directly written to the disk. The MySQL simultaneously write into InnoDB buffer pool and InnoDB transaction log. All the modifications are happening on the memory (InnoDB buffer pool). The modified pages on memory (InnoDB buffer pool) are called as Dirty pages. Once the changes are completed, the dirty pages are flushed to disk, this is called the COMMIT. So only after flushed to disk it is called committed data.

UNDO operation:

Assume that, data is being modified on the memory (InnoDB buffer pool), during this modification, if there is a ROLLBACK statement executed, the InnoDB pages should be rolled back to the original state and modified pages roll back from the ROLLBACK segment and should be removed from the InnoDB pages on buffer pool. In simple words undo operation is called as rolling back a transaction.

REDO operation:

Assume that, the modification is there in memory, the unexpected server crashed, without the transaction log, whatever there in memory gone forever. Since modified data is committed in InnoDB buffer pool(memory) but not in a disk. In order to avoid that, while restarting the server, it needs to recover the uncommitted transaction from the transaction log, The MySQL will match the sequential number of the disk and the transactional log sequence number and start to recover. In simple words, redo operation is called as replaying a committed transaction.

So what is the importance of the InnoDB log file size? Here size matters!!!.

If log file size is small: few transactions are captured in the transaction log, so it means, faster recovery but slow writes. More IOPS on a single log file.

If log file size is large: more transactions are captured in the transaction log, so it means, faster writes but slow recovery. Less IOPS on a single log file.

Finally, having huge log size will be a problem but small size also will be a problem. So what is the best size for InnoDB transaction log? From my understanding, there is no right exact value, but we have a formula, which is accepted by most of the database administrators. Here goes the rule of thumb:

InnoDB log file size: Total size of the redo logs fits in 1 hour worth of writes during peak traffic. 

How much InnoDB is writing per hour?

This can be calculated by using the following statement:

mysql> pager grep seq;
mysql> show engine innodb status \G select sleep(60); show engine innodb status \G

This will give you the Log Sequence Number with one minute gap. By using this LSN we can calculate the how much writes InnoDB performs in an hour. The following sample output will give you a good idea of this:

mysql> pager grep seq;
PAGER set to 'grep seq' 
mysql> show engine innodb status \G select sleep(60); show engine innodb status \G 
Log sequence number 392311720379
1 row in set (0.17 sec) 
1 row in set (1 min 0.00 sec) 
Log sequence number 392323861206
1 row in set (0.09 sec)
mysql> select (392323861206 - 392311720379)*60/1024/1024;
| (392323861206 - 392311720379)*60/1024/1024 |
|                               694.70369339 |
1 row in set (0.00 sec)
Here, on my testing server, the InnoDB writes around 694 MB per hour. So as per my default configuration, I have 2 transaction log files, unless I change. this can be verified by running the following statement:

 mysql> show variables like '%innodb_log_files_in_group%';
| Variable_name             | Value |
| innodb_log_files_in_group | 2     |
1 row in set (0.01 sec)
It means, my log file should be around 350 MB. I suggest, try this on your test environment and check the performance before implement on the production environment. Always remember to take a backup of the existing files before removing from the system. If you feel something missing or if you want to share something on the innodb_log_file_size please mention in the comment section. Please click on FOLLOW button on the right side to get more posts on database administration.