Can't create file /var/lib/mysql/user.lower-test

Question

MySQL database has been problem free for a few months. Today when I checked for upgradeable packages using the Synaptic Package Manager it came up with the various MySQL components (-common, -client, -server, etc). I therefore decided to upgrade (I forgot to note the installed version but think it was 5.6.21) to 5.6.25-1-ubuntu2.0.Afterwards when I went to query the database I got the message:-

Error occured: Can't connect to MySQL server on '127.0.0.1' (111)

I then found the server was not running. In the log file I fond that the message:-

Can't create file /var/lib/mysql/user.lower-test

I have spent an hour or so searching on line and although the issue has been reported previously I have not seen it in the context of a 'post-update' issue so unsure of my next step.

Answer 1

This problems seems to permission issues or change the mysql data direcory to other location then /var/lib/mysql.

Try to run with sudo

Looks like /var/lib/mysql is owned by the mysql user and the group is set to mysql as well.

$ sudo chown -R mysql /var/lib/mysql
$ sudo chgrp -R mysql /var/lib/mysql
$ sudo chmod 755 /var/lib/mysql

Have you checked that the directory /var/lib/mysql does actually exist and corresponds to your data directory? If it does not, then you probably must specify otherwise the datadir parameter under the mysqld section

[mysqld]
datadir=/var/lib/mysql

Then you can chose between configuring apparmor to allow MySQL to read\execute\modify the new data directory and sub directories or, at your very own risk, you can remove apparmor. You may need to be root to do the following:

/etc/init.d/apparmor stop
/etc/init.d/apparmor teardown
update-rc.d -f apparmor remove
apt-get purge apparmor
reboot

Answer 2

I ran into this problem when trying to run multiple instances of MySQL, but instead of removing apparmor, I updated the usr.sbin.mysqld file:

vim /etc/apparmor.d/usr.sbin.mysqld

For example, this is what my file looks like, and once I added the folders that need write permissions, all worked as it should.

/usr/sbin/mysqld {

  capability dac_override,
  capability sys_resource,
  capability setgid,
  capability setuid,

  network tcp,

  /etc/hosts.allow r,
  /etc/hosts.deny r,

  /etc/mysql/*.pem r,
  /etc/mysql/conf.d/ r,
  /etc/mysql/conf.d/* r,
  /etc/mysql/*.cnf r,
  /usr/lib/mysql/plugin/ r,
  /usr/lib/mysql/plugin/*.so* mr,
  /usr/sbin/mysqld mr,
  /usr/share/mysql/** r,
  /var/log/mysql.log rw,
  /var/log/mysql.err rw,
  /var/lib/mysql/ r,
  /var/lib/mysql/** rwk,
  /var/lib/mysql1/ r,
  /var/lib/mysql1/** rwk,
  /var/log/mysql/ r,
  /var/log/mysql/* rw,
  /var/run/mysqld/mysqld.pid rw,
  /var/run/mysqld/mysqld.sock w,
  /var/run/mysqld/mysqld1.pid rw,
  /var/run/mysqld/mysqld1.sock w,
  /run/mysqld/mysqld.pid rw,
  /run/mysqld/mysqld.sock w,

  /sys/devices/system/cpu/ r,

}

Answer 3

The issue happened to me when I tried to move /var/lib/mysql to another disk and replace the original directory with a symlink.

Apparmor will deny the access in this configuration see

• https://bugs.launchpad.net/apparmor/+bug/1485055

why this is considered not to be a bug by the Apparmor developers. The work around is to use a bind mount. See

• https://unix.stackexchange.com/questions/198590/what-is-a-bind-mount

for an explanation.

So

mount --bind /newmysqldatadir /var/lib/mysql
service mysql start

fixed the issue for me.

Answer 4

I had a similar problem. I reinstalled Ubuntu Server on my machine, made a full backup of /etc/ on a hard drive, after reinstalling I made a copy of all important files from /drive/etc/ to my systems /etc.

While doing this, I created a bak for the usr.sbin.mysqld file called usr.sbin.mysqld.bak and kept it in the same /etc/apparmor.d/ directory.

Upon further searching I found this blog post: https://blogs.oracle.com/jsmyth/apparmor-and-mysql

Installed apparmor-utils and tried to aa-complain on /usr/sbin/mysqld and it said that I have 2 apparmor configs defined (both of my files).

I know it might not be the problem that you are facing, but just for the sake of clarity, don't be as dumb as I was, making a backup file in the same directory of apparmor sbin directory.

Answer 5

Also check if your partition is not full, which was my case.

For an unknown reason, moving the data to another partition did not work either.

So for a quick fix, instead of messing with llvm or gparted, I reduced my /swapfile size.