Recently, I decided to upgrade a database server from RHEL 6 (CentOS 6) to RHEL 7 (CentOS 7), which involves switching from MySQL 5.5 to MariaDB 5.5. Our server hosts about 100 databases, when I was testing them individually, I didn’t see any problem. However, when I ran the back up all databases one by one using mysqldump (i.e., running mysqldump command for each database, one after one, 100 times), something funny happened. Here is the error message:

#The system was running a brunch of mysqldump commands, one by one (not via background)

Got error: 1016: "Can't open file: './db_my_database/tbl_mytable.frm' (errno: 24)" when using LOCK TAB                                                                                                   LES
mysqldump: Error: 'Out of resources when opening file '/var/tmp/#sql_2d6c_2.MAI' (Errcode: 24)' when trying to dump tablespaces
mysqldump: Error: 'Out of resources when opening file '/var/tmp/#sql_2d6c_2.MAI' (Errcode: 24)' when trying to dump tablespaces
mysqldump: Error: 'Out of resources when opening file '/var/tmp/#sql_2d6c_2.MAI' (Errcode: 24)' when trying to dump tablespaces
mysqldump: Error: 'Out of resources when opening file '/var/tmp/#sql_2d6c_2.MAI' (Errcode: 24)' when trying to dump tablespaces
mysqldump: Error: 'Out of resources when opening file '/var/tmp/#sql_2d6c_2.MAI' (Errcode: 24)' when trying to dump tablespaces
mysqldump: Error: 'Out of resources when opening file '/var/tmp/#sql_2d6c_2.MAI' (Errcode: 24)' when trying to dump tablespaces
mysqldump: Error: 'Out of resources when opening file '/var/tmp/#sql_2d6c_2.MAI' (Errcode: 24)' when trying to dump tablespaces
mysqldump: Error: 'Out of resources when opening file '/var/tmp/#sql_2d6c_2.MAI' (Errcode: 24)' when trying to dump tablespaces
mysqldump: Error: 'Out of resources when opening file '/var/tmp/#sql_2d6c_2.MAI' (Errcode: 24)' when trying to dump tablespaces

At the mean time, I tried to access the database via MySQL terminal,

MariaDB [(none)]> SHOW DATABASES;
ERROR 1018 (HY000): Can't read dir of '.' (errno: 24)

This error message means the MySQL cannot access the file. If you google the message, you will notice that there are tons of solutions, and almost every of them suggests you to increase the open_files_limit variable in my.cnf.

Therefore, I checked my configurations (/etc/my.cnf), and I noticed that the value was already set to 30000. I also checked the lsof command and I found something very interesting. Notice that I have 100 database, each of them contains about 60 tables. Each table has about 3 files. Depending on the timeout settings, if all database and tables are opened, the total number of opened file will be 100x60x3 = 18,000

sudo lsof -u mysql | wc
1045   25811 239248

This result suggests that at the time of crashing, the mysql user (the system user that run the MariaDB service) was accessing 1045 files at the same time.

So I was scratching my head. Why I already set the open_files_limit value to 30000 already, and the system crashed at 1045th files? I also verified the memory (command: free) and current process (command: top), and I didn’t find anything unusual. One last thing, I checked the open_files_limit value using MySQL terminal, and this is what I found:

MariaDB [(none)]> SHOW VARIABLES LIKE 'open_files_limit';
| Variable_name    | Value |
| open_files_limit | 1024  |

It seems that MariaDB didn’t honor the open_files_limit I set in config file, instead it uses the default one, which isn’t right. So after some investigations, I’ve noticed that RHEL 7 set up some security stuffs, such that you will need to set the open_file_limit variable at the system level rather than the application level. In the other words, whatever you put in the /etc/my.cnf, it won’t go through the security check at RHEL.

Here is how to set the equivalent open_files_limit at the system level:

sudo mkdir -p /etc/systemd/system/mariadb.service.d/
sudo nano /etc/systemd/system/mariadb.service.d/limits.conf
#Add the following, for me, I like to set the open_files_limit to 30000:
sudo systemctl daemon-reload
sudo systemctl restart mariadb

I tried to rerun the command again and that’s what I got:

MariaDB [(none)]> SHOW VARIABLES LIKE 'open_files_limit';
| Variable_name    | Value |
| open_files_limit | 30000 |
1 row in set (0.00 sec)

That’s it! Did I save you from heart attack?

One of the biggest selling points of RHEL is the stability. When we upgraded from RHEL 6 to RHEL 7 (clean install), we expected that everything should work fine without too much modifications. Unfortunately, what I saw is a broken system. I really don’t expect that this happens in an enterprise class product.

Our sponsors:

Recently, I am experiencing a weird error when connecting to a MySQL server remotely:

ERROR 2013 (HY000): Lost connection to MySQL server at 'reading authorization packet', system error: 0

Basically, this error is similar to the busy tone when you are making calls. The key thing is, it happens randomly. Sometimes the connection is okay, sometimes it takes any where from 0.01 to 30 seconds to establish a connection. Sometimes it gets time-out.

Long story short. Continue to read this article if you have met the following conditions:

  • You try to connect to a MySQL server remotely, i.e., not localhost(
  • It happens randomly. It can take anywhere from 0.01 seconds to 30 seconds to establish a connection. Sometimes it fails.
  • You connect the server using IP address, i.e., it has nothing to do with the domain name, or skip-name-resolve in my.cnf
  • You have included the client IP address in /etc/hosts.allow.

The key thing is: Random.

You probably have scratched your head for few hours (or days), gone through tons of useless suggestions on Google/Stackoverflow/Serverfault etc, and the problem still exists. Oh well, at least this has been happened on me in the past 24 hours.

Before we discussed the problem, let’s try to reproduce the problem:

#In the client computer, we try to connect to 
#the MySQL database remotely and run a simply command:
time mysql -u root -pPASSWORD -h IP_ADDRESS -e "show databases;"

#Case 1: Everything is okay
real    0m0.001s
user    0m0.001s
sys     0m0.001s

#Case 2: it takes 20 seconds to establish a connection. 
#That's not right.
real    0m20.001s
user    0m0.003s
sys     0m0.003s

#Case 3: Cannot even make the connection.
ERROR 2013 (HY000): Lost connection to MySQL server at 'reading authorization packet', system error: 0
real    0m49.617s
user    0m0.003s
sys     0m0.003s

If you also observe a similar symptoms, I can tell you that the problem may not be related to MySQL server or MySQL settings. I recommend you to check your network traffic. Here are my suggestions:

#Check which process is running.

#Or you can check which process is running by the web server user
#In my case, apache is the web server user
ps -u apache

#Or you can check the current traffic using nload
nload -u M

If you are lucky, you may notice that there is a huge network traffic going on. The traffic is the main problem that cause the problem. Try to kill that process or perform a reboot.

Let’s take my case an example. I noticed a weird process running by the apache user:

ps -u apache

  PID TTY          TIME CMD
 8112 ?        00:00:09 httpd
 8113 ?        00:00:08 httpd
 8334 ?        00:00:08 httpd
 8796 ?        00:00:06 httpd
 8802 ?        00:00:07 httpd
 8891 ?        00:00:07 something (This is a malware)

After I kill that process, everything is back to normal again.


Our sponsors:

There are many different ways to improve the MySQL performance. In general, it breaks down into two different categories: Server side and client side.

On the server side, we can optimize the database and table structure, such as indexing the columns etc. On the client side, we can optimize the queue to minimize the workload, or we can cache and share the result such that the traffic to the server will be minimized. However, these methods are doable if you have access to the source code, or you understand the logic of the software. If you are a system administrator, you may not want to touch the source code, because you never know what will happen after the modifications. Plus your modification may be overwritten in the next update.

I am going to show you a quick and easy way to solve this problem. First, I am assuming that you build the MySQL from source. In the other words, this article will not work if you install the MySQL through pkg_add, yum, apt-get etc.

My solution is very simple: Compiling the MySQL server with static option enabled

By default, we will compile the software from source, it is not built in static. According to MySQL documentation, building the binary using static will result a 13% improvement comparing to building the binary using dynamic. Here is an example how to build MySQL with static option enabled:

cd /usr/ports/databases/mysql56-server
make install clean

This method will work for the first time. It may be a problem in the long run. For example, I use portsnap to update the port tree, and I use portmaster to upgrade the application. By default, portmaster will use the default options to rebuild the port. In the other words, the MySQL will not be built using static.

To solve this problem, I will need to make the build static option as a default settings. First of all, try to include the following in /etc/make.conf

sudo nano /etc/make.conf


We can update the port tree and ports again. This will make the system to use the new settings.

sudo portsnap fetch update
sudo portmaster -Day
#Don't forget to restart the MySQL server.
sudo /usr/local/etc/rc.d/mysql-server start

Now your MySQL server and other applications are built using static.

Please click here to learn more about building static versus building dynamics.


Our sponsors:

Today, I noticed that the MySQL server on my FreeBSD server is down for unknown reason, and today is Thanksgiving.

Initially, I tried to restart the MySQL server using the following command, but it didn’t help to restart the MySQL:

sudo /usr/local/etc/rc.d/mysql-server restart

and it gives me the following error message:

mysql not running? (check /var/db/mysql/

That’s interesting. So I check the pid file and I found that it is not available. After scratching my head for a while, I noticed one thing:

Filesystem     Size    Used   Avail Capacity  Mounted on
/dev/ad4s1d    7.7G    7.7G     -1M   100%    /var

Obviously, the /var directory is full. No wonder why the MySQL could not create any file, which explains why it stopped working.

Since the server is already up and running, it will be too late to increase the size of /var without reinstalling the entire system. The simplest way is to soft link the /var directory to somewhere else. Since a lot of system applications depends on /var, I don’t want to move the entire /var into a different places. So I decide to softlink /var/log into /usr/var/log.

The steps are easy:

sudo su
mkdir -p /usr/var/
mv /var/log /usr/var/
ln -s /usr/var/log /var/

To verify your work, it should look something like that:

ls /var
lrwxr-xr-x   1 root    wheel       13B Nov 22 11:48 log -> /usr/var/log/
Filesystem     Size    Used   Avail Capacity  Mounted on
/dev/ad4s1d    7.7G    1.1G    6.0G    15%    /var

Now, if you try to start the MySQL server again, it may or may not work. If you experience any difficulties, try to reboot the server. After the server is reboot, MySQL should be up and running again.

Lesson learned: Always create single partition. 🙂


Our sponsors:

When I tried to connect to the MySQL server through PHP, I got the following error: Can’t connect to local MySQL server through socket ‘/var/lib/mysql/mysql.sock’ (2)

To give you some background, here is my situation. My Fedora Linux is a 64-bit machine, and I have installed a 32-bit version of XAMPP (i.e., 32-bit PHP, 32-bit MySQL) for the web server. Also, I have some PHP scripts that will be run in background. These scripts are run using 64-bit PHP. Both 32-bit and 64-bit PHP need to access the MySQL database. Long story short. I got the MySQL error message when I tried to execute using the 64-bit PHP, while the 32-bit PHP has no complain.

When PHP talks to MySQL, it needs to talk to the MySQL representative, i.e., mysql.sock. If it cannot talk to the mysql.sock, then PHP will be unable to access the MySQL database.

The reason why my 64-bit PHP could not talk to MySQL because of a wrong location of mysql.sock. To solve this problem, simply do the following:

First, let’s find out where is the PHP.ini of the 64-bit PHP:

sudo find / -name "php.ini"

In Fedora Linux, it is:


Then, we need to find out where is the mysql.sock:

sudo find / -name "mysql.sock"

In my case, it is:


Now, let’s tell PHP the location of the mysql.sock:

sudo nano /etc/php.ini

And change the following line from:

mysql.default_socket =


mysql.default_socket = /opt/lampp/var/mysql/mysql.sock

Now, try to run your script again. The problem should be gone.

Have fun with MySQL.


Our sponsors:

MySQL has a biggest missing feature: It does not sort naturally. This has been an opened request since 2003, and MySQL team has no plan to fix this in a near future. I have looked for a solution to solve this problem for a while. Unfortunately, I found nothing useful. Here is a summary:

The + 0 Trick

Many developers suggest this solution:

SELECT names FROM your_table ORDER BY age + 0 ASC

This is not a good solution because it only works in some very specific situations. This query fails in most general cases.

Drupal Patch

Some developers suggest to use the patch from Drupal. Forget it, I could not even install it in my system.

My solution: Let PHP to handle the dirty work

So I decide to move the process to PHP. Before you say something about the performance issue, please keep reading my story first. I will talk about it in the later section.

MySQL is a relational database, it stores everything in a table. If we express that in an array, it will look something like this:

    [1] => Array
            [Name] => John
            [Identification] => 10
            [Address] => 100 Michigan Ave.

    [2] => Array
            [Name] => Peter
            [Identification] => 1000
            [Address] => 1 Michigan Ave.

    [3] => Array
            [Name] => Tom
            [Identification] => 2
            [Address] => 10 Michigan Ave.

    [4] => Array
            [Name] => Paul
            [Identification] => 1
            [Address] => 10 Michigan Ave.


where the array key represents the record ID in the table.

Now, let say I want to sort the contents in the following orders:

  1. Sort by Identification: Ascending
  2. Then by Address: Descending
  3. Then by Name: Ascending

I specify the orders in an array:

$order = array('Identification' => 'ASC',
			   'Address' => 'DESC',
			   'Name' => 'ASC');

And include the following function in my code:

//array_multiSort with natural sort
function naturalSort2D(&$array){

   if (!function_exists(naturalSort2DCompare)){
	function naturalSort2DCompare($a, $b){
		global $order;

		foreach($order as $key => $value){
			if (!isset($a[$key])) continue;


                        //Case insensitive string comparisons using a "natural order" algorithm
			$compareResult = strnatcasecmp($a[$key], $b[$key]);
			if ($compareResult === 0) continue;
			$value = strtoupper(trim($value));
			if ($value === 'DESC'){
				$compareResult = $compareResult*-1;
			return $compareResult;
		return 0;
   //Maintain index association
   //Preserve array keys
   //Use usort if you don't care about key index
   uasort($array, 'naturalSort2DCompare');
   return true;

Now I sort the array:


And I get my result like the following:

    [4] => Array
            [Name] => Paul
            [Identification] => 1
            [Address] => 10 Michigan Ave.

    [3] => Array
            [Name] => Tom
            [Identification] => 2
            [Address] => 10 Michigan Ave.

    [1] => Array
            [Name] => John
            [Identification] => 10
            [Address] => 100 Michigan Ave.

    [2] => Array
            [Name] => Peter
            [Identification] => 1000
            [Address] => 1 Michigan Ave.



Before we talk about the performance issue, I like to talk about my database design first. In my application, the records are stored in a table. It is a multiple user system. Each user can access their own records only. Each user has about 3000 records, and we have 1000 users. Therefore we have about 3 millions of records in one table.

Here is a data structure:

#Information input by users

#Information input by system

So the SQL quere is nothing more than something like:

Select * FROM `database`.`table` WHERE `UserID` = '1'

Previously, I let MySQL to handle the filter and sorting, i.e.,

Select * FROM `database`.`table` WHERE (`UserID` = '1') AND (`Address` LIKE `%Michigan%`) ORDER BY `Identification` ASC LIMIT 5, 10

After moving to PHP, I just let MySQL to handle the filter,

Select * FROM `database`.`table` WHERE (`UserID` = '1') AND (`Address` LIKE `%Michigan%`)

Notice that I will get all qualified records, could be few thousands. Then I let PHP to do the sorting:

$order = array('Identification' => 'ASC');

Now I have a sorted array. Next I need to slice the records:

$array = array_slice($array, 5, 10);

Sounds like lots of work huh? Now let’s talk about the performance impact. Guess what, I see performance improvement for about 15%!

In the new approach, MySQL does lesser work. PHP does more work. Apparently, PHP works more effectively than MySQL. However, this may not true for all database model. Remember that in my situation, the system needs to handle at most few thousands records at a time? So I think PHP is doing better in this range. If your application needs to handle millions of records at a time, e.g., bookstore, then you better let MySQL to do the work.

By the way, the new approach allows to sort the dynamic generated content, which is a big plus in my situation. That is something I cannot do in MySQL approach.

Have fun.


Our sponsors:

This step-by-step guide is mainly for FreeBSD, however the idea is the same for Linux. Every once a while, when I update my FreeBSD box, the system likes to shutdown my MySQL server. Therefore, I need to start it again after the update is done. Unfortunately, the upgrade process is not smooth every time. Sometimes it will throw me some error.

/usr/local/etc/rc.d/mysql.server start

Oh well, I got the following error messages:

Starting MySQL..... ERROR! The server quit without updating PID file.

Sometimes, the message will tell you the exact location of which PID file:

Starting MySQL..... ERROR! The server quit without updating PID file (/var/db/mysql/

There are several solutions to troubleshoot these problems. I will go over each one by one.

Solution 1: Reboot The Computer

Although it sounds simple, but it really works. During the system upgrade, the OS may disable some of your daemons. Instead of troubleshooting each one by one, the easiest way is to start everything over. For example, I experienced this problem today after upgrading the Apache and Ruby (Yes, MySQL is not part of the update), and I got this error message afterward. After rebooting the computer, the error message is gone.

Solution 2: Remove Your MySQL Config File

If you have modified your MySQL configuration file, MySQL may not like it few versions after (MySQL is not backward compatibility friendly). It can be the problem of using an unsupported variable, or something similar. The easiest way is to remove your configuration file, and try to start the MySQL server again:

Backup your MySQL configuration first.

mv /etc/my.cnf /etc/my.cnf.backup

And restart the MySQL server again:

/usr/local/share/mysql/mysql.server start

Hopefully you will see the following message:

Starting MySQL. SUCCESS!

Solution 3: Upgrade Your Database File

Sometimes, the newer MySQL doesn’t like the database created in earlier version. I discovered this when I upgrade to MySQL 5.5.7:

Starting MySQL..... ERROR! The server quit without updating PID file (/var/db/mysql/

Since MySQL tells me which PID file causes the problem, I open the file and take a look what’s going on:

sudo tail /var/db/mysql/

And I saw something interesting: tables: Table ‘mysql.proxies_priv’ doesn’t exist:

101112 10:49:16  InnoDB: Initializing buffer pool, size = 128.0M
101112 10:49:16  InnoDB: Completed initialization of buffer pool
101112 10:49:16  InnoDB: highest supported file format is Barracuda.
101112 10:49:17  InnoDB: 1.1.3 started; log sequence number 1589404
101112 10:49:17 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.proxies_priv' doesn't exist
101112 10:49:17 mysqld_safe mysqld from pid file /var/db/mysql/ ended

The reason is very simple. MySQL could not open a table created in the earlier version (< 5.7.7) because it is not compatible with the current version. So, we can try to start the MySQL in safe mode through rc.d. First, you can edit the /etc/rc.conf and put the following into the file:

mysql_args="--skip-grant-tables --skip-networking"

Restart MySQL through rc.d:

/usr/local/etc/rc.d/mysql-server start

If you did it right, you should see something like the following:

Starting MySQL.. SUCCESS!

Now, MySQL is already running the safe-mode. We want to perform a MySQL upgrade on all tables:

sudo mysql_upgrade

You should see something like this:

Looking for 'mysql' as: mysql
Looking for 'mysqlcheck' as: mysqlcheck
Running 'mysqlcheck' with connection arguments: '--port=3306' '--socket=/tmp/mysql.sock'
Running 'mysqlcheck' with connection arguments: '--port=3306' '--socket=/tmp/mysql.sock'
mysql.columns_priv                                 OK
mysql.db                                           OK
mysql.event                                        OK
mysql.func                                         OK
mysql.general_log                                  OK
mysql.help_category                                OK
mysql.help_keyword                                 OK
mysql.help_relation                                OK
mysql.help_topic                                   OK                                         OK
mysql.ndb_binlog_index                             OK
mysql.plugin                                       OK
mysql.proc                                         OK
mysql.procs_priv                                   OK
mysql.servers                                      OK
mysql.slow_log                                     OK
mysql.tables_priv                                  OK
mysql.time_zone                                    OK
mysql.time_zone_leap_second                        OK
mysql.time_zone_name                               OK
mysql.time_zone_transition                         OK
mysql.time_zone_transition_type                    OK
mysql.user                                         OK
Running 'mysql_fix_privilege_tables'...

Now, we want to switch the MySQL back to normal mode by commenting the extra options in /etc/rc.conf:

#mysql_args="--skip-grant-tables --skip-networking"

And restart MySQL through /etc/rc.d:

/usr/local/etc/rc.d/mysql-server restart

Now the MySQL is up and running again!

Happy MySQLing.


Our sponsors:

I like to experiment all kind of computer technologies especially server applications. I am not one of those who is satisfied with just making a website and putting it in somewhere. I like the full control of the server because I like to try different combination of applications for performance tuning. For example, I doubt regular web hosting company allows you to host the entire system on a ram disk with a reasonable price tag. That’s why I choose to host a server at home. That’s a lot cheaper, plus I have the full control. However, running server application may generate lots of upstream traffic. That’s why most internet server providers (such as Charter) do not allow their customers to run any server related applications using their internet connection service. They do it by blocking most service related common ports, e.g., 80 (HTTP), 21 (FTP), 22 (SSH) etc. So there is really nothing you can do other than hosting your applications on different ports.

Two years ago, I started hosting all of my websites using my own computers. I found a number of benefits.

Benefits of hosting websites at home

1. It saves me tons of money.

I was paying $72/year per domain for web hosting. Since I have more than ten domains, the total running costs per year is pretty high. This amount is really nothing comparing to the cost of the electricity.

Monthly cost for web hosting:

$72 per domain/yr * 10 domains / 12 months
= $60 per domain / month

My monthly electricity cost at home, which includes everything such as running 10 non-gaming computers, washer, dryer, lighting etc:

$80 / month

I haven’t tried measuring the exact energy but you can imagine the electricity used by computers should be under $10 / month.

2. It is fun (and environmental friendly too).

I have few stone-age computers including a Pentium II laptop, a Mac G3 (speed wise similar to Pentium II), a Pentium M Celeron laptop etc. I integrated them to a web server farm (web clusters). Since running a web server does not require a lot of CPU power, they are doing okay for hosting low-traffic websites. Also, it is cool to show off my friends the global data center that I build for my websites.

3. Your data is secured!

Have you ever heard of any bank host their web sites on web hosting? No matter what type of encryption you use for your web applications, you still need to process the raw, original, and unencrypted data on the server side at one point. Processing confidential information on a shared server is like talking your secrets in a study room in a public library. You think you are in an isolated environment, but you can be surveillanced, it’s very simple and easy.

Here is an example:
Supposes I have a web application which accepts the confidential information from my users, and all traffics are encrypted. After the confidential information is decrypted on the server-side, my web application processes the raw information and do further things.

Let’s say the server environment is Apache + PHP + MySQL, the most popular combination of web application environment. Since they are all open-source, it is very easy to modify the source codes and log every single thing into a file, including the raw, original, unencrypted data processed by my web application.

You may think this may require lots of work and it will never happen on you. What if your competitor wants your confidential information? It doesn’t cost much to hire someone to do it.

Sounds scary?

More scary things come along. Shared web hosting (hosting multiple domains on one single server) always come with lots of trouble that many people are ignored. In theory, every website on a shared hosting lives in a virtual, independent environment, think about it as a virtual machine like VMWare or Hyper-V. Practically, it is not easy to set up such environment (e.g., FreeBSD Jail) and many web hosting companies choose to go with a less difficult path, because customers will not realize it anyway. Now here is the interesting part, supposes my domain and your domain are hosted on the same server. I can access the resource at the operating system level first (which will required some hacking), then access your file after that. Now I have access your source code and I can do whatever I want.

The most secure place in the world is the place that can be accessed by you, and no body else, i.e., your home, or any place you have full control

Our sponsors:

Today I imported the MySQL database and I got the following error:

# mysql -u root -p db_test < test.db
Enter password:
ERROR 2006 (HY000) at line XX: MySQL server has gone away

It is because the data (SQL query) is too large to imported. To solve this problem, simply edit the MySQL configuration files: /etc/my.cnf

Replace the following from:

max_allowed_packet = 1M


max_allowed_packet = 16M

Or something higher.

Restart the MySQL server again and the problem will be gone.

/usr/local/share/mysql/mysql.server restart


Our sponsors:

This article is a bit outdated. Please visit the following article instead. It includes a better discussion on how to solve this problem using different scenarios:



Our sponsors: