MySQL InnoDB – Access Rights Issue with Temporary Tables

innodbMySQLtemporary-tables

For mysql Ver 14.14 Distrib 5.6.33, for Linux (x86_64) using EditLine wrapper

in my.cnf:

tmpdir          = /var/mysql_tmpfs

in /etc/apparmor.d/usr.sbin.mysqld:

  /var/mysql_tmpfs/ rw,
  /var/mysql_tmpfs/* rw,

Permissions for folder:

drwxrwxrwt  2 mysql    mysql     4096 Nov 28 19:46 mysql_tmpfs

Which, looks right to me. However, it is failing. Right now, mysql will start. However, when I load certain pages of my application, this is the error message that I see:

2016-11-28 20:23:41 39465 [ERROR] InnoDB: Unable to lock /var/mysql_tmpfs/#sql9a29_2_0.ibd, error: 13
2016-11-28 20:23:41 39465 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log files.
2016-11-28 20:23:41 7fd2105d3700  InnoDB: Operating system error number 13 in a file operation.
InnoDB: The error means mysqld does not have the access rights to
InnoDB: the directory.
2016-11-28 20:23:41 39465 [ERROR] InnoDB: Cannot create file '/var/mysql_tmpfs/#sql9a29_2_0.ibd'

However, I do not believe there is another process running. When I do a ps aux | grep mysqld, I get this:

mysql    38943  0.0  0.0   4396   736 pts/4    S    20:21   0:00 /bin/sh /usr/bin/mysqld_safe
mysql    39465 10.0  4.0 73330944 5301532 pts/4 Sl  20:21   0:16 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --log-error=/var/log/mysql/error.log --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/run/mysqld/mysqld.sock --port=3306

In addition, I was able to create a file in that directory as the mysql user, which is why I'm extra sure that the user should have the required permissions.

I'm guessing this is something that I'm missing, but I've now had three other people take a look and we have not gotten anywhere. Please help.

Best Answer

Found a solution:

Apparently we needed to use k to give lock file permission. So now usr.sbin.mysqld looks like this:

  /var/mysql_tmpfs/ r,
  /var/mysql_tmpfs/** rwk,

We found the answer on the oracle blog: https://blogs.oracle.com/jsmyth/entry/apparmor_and_mysql

Related Solutions

MySQL – Troubleshooting Startup Issues After Increasing innodb_buffer_pool_size

The two answers given from @RickJames and @drogart are essentially the remedies. (+1 for each).

Right from the error log you present, the last two lines say:

InnoDB: Error: log file ./ib_logfile0 is of different size 0 134217728 bytes

InnoDB: than specified in the .cnf file 0 268435456 bytes! `

At that point, it was evident that you set innodb_log_file_size to 256M (268435456) in my.cnf while the InnoDB Transaction Logs (ib_logfile0,ib_logfile1) were respectively 128M (134217728) each. Looking back at the link to my StackOverflow answer in your question, you had to do the following:

Step 01) Add this to my.cnf:

[mysqld]
innodb_buffer_pool_size=4G
innodb_log_file_size=1G

Step 02) Run these command in the OS

mysql -u... -p... -e"SET GLOBAL innodb_fast_shutdown = 1"
service mysql stop
rm -f /var/lib/mysql/ib_logfile*
service mysql start

So as to have confidence in what is happening, run tail -f against the error log. You will see message telling you when each innodb log file is being created.

MySQL – Differences Between mysqld_safe and mysqld

As we all know, mysqld_safe and mysqld are very different

mysqld : The database server instance daemon

mysqld_safe : Control program that examines and sets the environment for mysqld to execute. The mysqld executable is actually launched in a loop. When mysqld terminates, the mysqld_safe program will examine the return results and decide whether

mysqld terminated normally (intentional shutdown), leaves mysqld_safe
mysqld terminated abnormally (crash or kill -9 of mysqld)
- Loop back, mysqld fails on retry, leaves mysqld_safe
- Loop back, mysqld starts up, stays in the mysqld_safe loop

Why is it important to have mysqld and mysqld_safe using the same MySQL version?

Let me illustrate it this way: Percona Server sometimes has additional features in mysqld_safe for manipulating the OS. For example, I have seen numactl --interleave=all in a Percona Server mysqld_safe. If that line was not there, the mysqld for Percona Server may run into issues with memory and swapping.

The same scenario could possibly be the case for Oracle's (ugh, still hate saying that) mysqld and mysqld_safe. There could be improvements from one major release to another that would be removed if the mysqld_safe was older.

Rather than exploring the possibilities of using a old mysqld_safe and a new mysqld (or vica versa), please make your life simple and reinstall MySQL 5.5.30 from scratch.

Before doing so, please run

updatedb
locate mysqld_safe

in Linux and see if there are two lingering. If there are, get the paths straightened out. Otherwise, you may have to reinstall MySQL 5.5.30.

Best Answer

Related Solutions

MySQL – Troubleshooting Startup Issues After Increasing innodb_buffer_pool_size

MySQL – Differences Between mysqld_safe and mysqld

Related Question