Yet another meaningless MySQL error: Incorrect key file for table

I was running a data collection query against all the MySQL servers for a client and I kept getting an error with a message like this: "Incorrect key file for table '/tmp/#sql_4d51_0.MYI'; try to repair it"

I was thinking...what? That is entirely meaningless to me. Initially, I wasn't even sure what was causing that error. Every time I gather data from a server, I import it to different server for safe keeping. I thought it was the import process that had "incorrect key". After some troubleshooting, I narrowed it down to a single server which I was running the query on.

I found this answer here: http://stackoverflow.com/questions/11805793/incorrect-key-file-for-table-tmp-sql-3c51-0-myi-try-to-repair-it

However, the accepted answer was wrong for my case. The next answer was more correct but still not exactly what had happened. I logged onto the server and /tmp wasn't full. The data drive where the MySQL data files are stored was full! I don't know why but this particular server was not alerting in the monitoring system but I happened to find this problem indirectly by trying to run a query on the MySQL instance.

I wish that MySQL error like this were more clear about what are some of the potential causes of this error message.

GET_LOCK problem

I saw an interesting problem with the use of MySQL's GET_LOCK today. A client sent a notification that they are getting a lot of the following type of errors:

DAEMON: job=importFILE; exception=System.Exception: GET_LOCK failed: 0 

I logged onto the database that was having the problems and at any given time, I see about 5 or 6 running connections with this:

SELECT GET_LOCK('MyTableLock',10)

The application was running code like this:

                @"START TRANSACTION;

                SELECT GET_LOCK('MyTableLock',10);

                INSERT INTO MyTable (id, customer_id, process_id, value) VALUES (-1,-1,null,'-');

                INSERT INTO MyTable (id, customer_id, process_id, value) SELECT -1, -1,(LAST_INSERT_ID() + @vnum - 1) + 20, '-' from MyTable limit 1;

                SELECT LAST_INSERT_ID() + 20;

                SELECT RELEASE_LOCK('MyTableLock');

                ROLLBACK;";

It appeared the code would issue a GET_LOCK which would be followed by an error before the RELEASE_LOCK happened. The error didn’t cause the session to terminate and it was released back into the pool with the lock still held.  

The code didn't reset sessions so the next connection from the connection pool would still have that lock held. At this point, there would no longer be anything running that would have needed that lock. 

For a work around, we restarted the application process and killed the sessions that were running the GET_LOCK. Ultimately the code needs to be re-written to take this into account.

Here is some reading on what GET_LOCK does:
http://techblog.procurios.nl/k/news/view/41405/14863/mysql-get_lock()-explained.html
https://dev.mysql.com/doc/refman/5.7/en/miscellaneous-functions.html

Setting up SSL on MySQL database servers

I was trying to get SSL to work on a server. I had read up on these posts:

http://serverfault.com/questions/451487/configured-mysql-for-ssl-but-ssl-is-still-disabled
http://dba.stackexchange.com/questions/91514/mysql-ssl-setup-failed
https://www.percona.com/blog/2013/06/22/setting-up-mysql-ssl-and-secure-connections/
http://xmodulo.com/enable-ssl-mysql-server-client.html

I added my certs to the file system and added entries to my.cnf like this:

[mysqld]
ssl-ca=/path/to/ca.pem
ssl-cert=/path/to/server-cert.pem
ssl-key=/path/to/server-key.pem

However, SSL still wasn't working. Looking at the error log I kept seeing this:

2017-01-10 11:46:45 8738 [Warning] Failed to setup SSL
2017-01-10 11:46:45 8738 [Warning] SSL error: SSL_CTX_set_default_verify_paths failed
2017-01-10 11:46:45 8738 [Note] RSA private key file not found: /path/to/mysql//private_key.pem. Some authentication plugins will not work.
2017-01-10 11:46:45 8738 [Note] RSA public key file not found: /path/to/mysql//public_key.pem. Some authentication plugins will not work.

When I log into MySQL and show variables related to SSL, I would see this:

mysql> show global variables like '%ssl%';
+---------------+--------------------------------+
| Variable_name | Value                          |
+---------------+--------------------------------+
| have_openssl  | DISABLED                       |
| have_ssl      | DISABLED                       |
| ssl_ca        | /path/to/ca.pem                |
| ssl_capath    |                                |
| ssl_cert      | /path/to/server-cert.pem       |
| ssl_cipher    |                                |
| ssl_crl       |                                |
| ssl_crlpath   |                                |
| ssl_key       | /path/to/server-key.pem        |
+---------------+--------------------------------+
9 rows in set (0.00 sec)

Some one suggested checking that the MySQL user can correctly view the file like this:

sudo -u mysql cat /path/to/ca.pem

That command was successful on older servers but not on my new servers. I checked the permissions on the certs and those looked fine. Finally I checked the permissions on the directory which the certs were in. That was the problem!

I ran this to correct:

chown -R mysql:mysql /path/to/security/
chmod -R 644 /path/to/security/
chmod 755 /path/to/security/

Restarted MySQL.

Then when I tried to logon, I started getting this error:

ERROR 2026 (HY000): SSL connection error: error:00000001:lib(0):func(0):reason(1)

Read up on that here:

http://stackoverflow.com/questions/31413031/mysql-error-2026-hy000-ssl-connection-error-error00000001lib0func0re

I told the client to skip ssl like this:

mysql -uUSERNAME -p  --skip-ssl

This worked but I actually didn't want to have to do that so I removed these entries from my.cnf and restarted:

[client]
ssl-cert=/path/to/server-cert.pem
ssl-key=/path/to/server-key.pem


mysql> show global variables like '%ssl%';
+---------------+--------------------------------+
| Variable_name | Value                          |
+---------------+--------------------------------+
| have_openssl  | YES                            |
| have_ssl      | YES                            |
| ssl_ca        | /path/to/ca.pem                |
| ssl_capath    |                                |
| ssl_cert      | /path/to/server-cert.pem       |
| ssl_cipher    |                                |
| ssl_crl       |                                |
| ssl_crlpath   |                                |
| ssl_key       | /path/to/server-key.pem        |
+---------------+--------------------------------+

Works!

Can't start server : Bind on unix socket: Permission denied

I had a test instance of MySQL running on a VM and for whatever reason things got messed up and I couldn't access it anymore

Looking in the error log I saw this:

2017-01-06 10:58:44 5227 [ERROR] Can't start server : Bind on unix socket: Permission denied
2017-01-06 10:58:44 5227 [ERROR] Do you already have another mysqld server running on socket: /var/lib/mysql/mysql.sock ?
2017-01-06 10:58:44 5227 [ERROR] Aborting

I didn't understand what was going on but Google yielded a solution that worked here:

http://serverfault.com/questions/497194/mysql-bind-on-unix-socket-permission-denied

Very important: Make sure the permission for the MySQL database directory under /var/lib/folder should be mysql:root

chown -R mysql:root /var/lib/mysql