Bug #85778 | Custom apparmor profile not correctly replaced if user agrees to replace cnf | ||
---|---|---|---|
Submitted: | 4 Apr 2017 11:39 | Modified: | 7 Apr 2017 12:07 |
Reporter: | Purvez Desai | Email Updates: | |
Status: | Verified | Impact on me: | |
Category: | MySQL Server: Packaging | Severity: | S2 (Serious) |
Version: | 5.6+ | OS: | Ubuntu (ver 14.04) |
Assigned to: | CPU Architecture: | Any | |
Tags: | MYSQL5.5, mysql5.6, ubuntu14.04, upgrade |
[4 Apr 2017 11:39]
Purvez Desai
[4 Apr 2017 11:42]
Purvez Desai
The only other thing to add is that my original my.cnf had a different data location than /var/lib/mysql. I don't know whether this is relevant but I'll mention it anyway. I had changed the default behaviour of a single INNODB file per database to a table based on in 5.5. I'm just wondering whether the upgrade scripts assume that a single INNODB file is still being used.
[4 Apr 2017 11:45]
Lars Tangvald
Is it possible you changed the apparmor profile when you moved the datadir originally? Could you try running dmesg -T |grep mysql and see if there are any "DENIED" messages from the time of the failed upgrade?
[4 Apr 2017 11:48]
Lars Tangvald
Wait, sorry, you mentioned that. Will look into it a bit more.
[4 Apr 2017 12:11]
Lars Tangvald
I at least found one set of steps that reproduce this issue: * Install mysql-5.5 on Ubuntu 14.04, then customize datadir to be /var/lib/mysql2 (changing my.cnf and apparmor profile). * Before upgrading, copy (with sudo) /var/lib/mysql2 to /var/lib/mysql (this leaves all files in var/lib/mysql as owned by root) * Upgrade to 5.6.35, saying yes to use its default config, which reverts datadir to the default /var/lib/mysql To fix, I could either reapply the changes to make datadir /var/lib/mysql2, or run chown -R mysql:mysql /var/lib/mysql and run apt-get -f install. So what does your /var/lib/mysql look like with regards to contents and ownership?
[4 Apr 2017 12:58]
Purvez Desai
Hi Lars The mysql directory in /var/lib has the following: permissions: drwx------ I couldn't just sudo cd into /var/lib/mysql so I did sudo su here is the content of /var/lib/mysql -rw-r--r-- 1 mysql mysql 0 Apr 1 15:00 debian-5.5.flag drwx------ 2 mysql mysql 4096 Apr 1 14:29 mysql ...and the mysql directory underneath /var/lib/mysql is empty. Here's the output: root@server-02:/var/lib/mysql# cd mysql root@server-02:/var/lib/mysql/mysql# ls -l total 0 Thanks very much for your prompt response.
[4 Apr 2017 13:35]
Lars Tangvald
Ok, then the problem here is that you don't have a working database in /var/lib/mysql, but you have files that makes the installation of 5.6 think you do, so it doesn't do anything to initialize a new one. Try renaming /var/lib/mysql to something else (just in case there's anything there you want to keep), then run apt-get -f install and see if that helps
[4 Apr 2017 13:45]
Purvez Desai
Hi Lars I moved /var/lib/mysql to /var/lib/mysqlprev Then ran nexargi@server-02:/var/lib$ sudo apt-get -f install and here is the output ===== Reading package lists... Done Building dependency tree Reading state information... Done 0 upgraded, 0 newly installed, 0 to remove and 3 not upgraded. 2 not fully installed or removed. After this operation, 0 B of additional disk space will be used. Setting up mysql-community-server (5.6.35-1ubuntu14.04) ... mkdir: cannot create directory ‘/var/lib/mysql/mysql’: No such file or directory dpkg: error processing package mysql-community-server (--configure): subprocess installed post-installation script returned error exit status 1 dpkg: dependency problems prevent configuration of mysql-server: mysql-server depends on mysql-community-server (= 5.6.35-1ubuntu14.04); however: Package mysql-community-server is not configured yet. dpkg: error processing package mysql-server (--configure): dependency problems - leaving unconfigured Errors were encountered while processing: mysql-community-server mysql-server E: Sub-process /usr/bin/dpkg returned an error code (1) nexargi@server-02:/var/lib$
[4 Apr 2017 14:00]
Lars Tangvald
Ah, 5.6.35 is a bit weird about the directory creation: It creates /var/lib/mysql in preinst, then tries creating /var/lib/mysql/mysql in postinst (which I think is all that's run when you do -f install) Try first running: mkdir /var/lib/mysql chown mysql:mysql /var/lib/mysql chmod 750 /var/lib/mysql
[4 Apr 2017 14:13]
Purvez Desai
Ok these are the commands that I tried and the output follows below: ============ nexargi@server-02:/var/lib$ sudo su [sudo] password for nexargi: root@server-02:/var/lib# mkdir /var/lib/mysql root@server-02:/var/lib# chown mysql:mysql /var/lib/mysql root@server-02:/var/lib# chmod 750 /var/lib/mysql root@server-02:/var/lib# exit exit nexargi@server-02:/var/lib$ sudo apt-get -f install ============================ Reading package lists... Done Building dependency tree Reading state information... Done 0 upgraded, 0 newly installed, 0 to remove and 3 not upgraded. 2 not fully installed or removed. After this operation, 0 B of additional disk space will be used. Setting up mysql-community-server (5.6.35-1ubuntu14.04) ... 2017-04-04 14:11:31 0 [Note] Ignoring --secure-file-priv value as server is running with --bootstrap. 2017-04-04 14:11:31 0 [Note] /usr/sbin/mysqld (mysqld 5.6.35) starting as process 5333 ... 2017-04-04 14:11:31 5333 [Warning] Can't create test file /var/lib/mysql/server-02.lower-test 2017-04-04 14:11:31 5333 [Warning] Can't create test file /var/lib/mysql/server-02.lower-test dpkg: error processing package mysql-community-server (--configure): subprocess installed post-installation script returned error exit status 141 dpkg: dependency problems prevent configuration of mysql-server: mysql-server depends on mysql-community-server (= 5.6.35-1ubuntu14.04); however: Package mysql-community-server is not configured yet. dpkg: error processing package mysql-server (--configure): dependency problems - leaving unconfigured Errors were encountered while processing: mysql-community-server mysql-server E: Sub-process /usr/bin/dpkg returned an error code (1) nexargi@server-02:/var/lib$
[4 Apr 2017 14:30]
Lars Tangvald
Was there anything in MySQL's error log after that? And were files created in /var/lib/mysql?
[4 Apr 2017 14:39]
Lars Tangvald
Also, you should be able to just run mysql_install_db to initialize the database after creating /var/lib/mysql
[4 Apr 2017 16:26]
Purvez Desai
Sorry for delay in replying Lars but I had to step out. Here is what happened: root@server-02:/var/lib# cd mysql root@server-02:/var/lib/mysql# ls -l total 4 drwxr-x--- 2 mysql mysql 4096 Apr 4 14:11 mysql root@server-02:/var/lib/mysql# cd mysql root@server-02:/var/lib/mysql/mysql# ls -l total 0 root@server-02:/var/lib/mysql/mysql# exit exit nexargi@server-02:/var/lib$ mysql_install_db WARNING: Could not write to config file /usr/my-new.cnf: Permission denied Installing MySQL system tables...2017-04-04 16:18:35 0 [Note] Ignoring --secure-file-priv value as server is running with --bootstrap. 2017-04-04 16:18:35 0 [Note] /usr/sbin/mysqld (mysqld 5.6.35) starting as process 26633 ... 2017-04-04 16:18:35 26633 [Warning] Can't create test file /var/lib/mysql/server-02.lower-test 2017-04-04 16:18:35 26633 [Warning] Can't create test file /var/lib/mysql/server-02.lower-test /usr/sbin/mysqld: Can't change dir to '/var/lib/mysql/' (Errcode: 13 - Permission denied) 2017-04-04 16:18:35 26633 [ERROR] Aborting 2017-04-04 16:18:35 26633 [Note] Binlog end 2017-04-04 16:18:35 26633 [Note] /usr/sbin/mysqld: Shutdown complete nexargi@server-02:/var/lib$ sudo mysql_install_db Installing MySQL system tables...2017-04-04 16:18:52 0 [Note] Ignoring --secure-file-priv value as server is running with --bootstrap. 2017-04-04 16:18:52 0 [Note] /usr/sbin/mysqld (mysqld 5.6.35) starting as process 26762 ... 2017-04-04 16:18:52 26762 [Warning] Can't create test file /var/lib/mysql/server-02.lower-test 2017-04-04 16:18:52 26762 [Warning] Can't create test file /var/lib/mysql/server-02.lower-test nexargi@server-02:/var/lib$ ===========
[4 Apr 2017 16:32]
Purvez Desai
Sorry I forgot to look at the error.log before running the mysql_install_db command. Anyway here is the additional stuff in error.log since the last time and includes any output that may have happened whilst running 'mysql_install_db'. =========== 2017-04-04 14:11:31 5333 [Note] InnoDB: Using atomics to ref count buffer pool pages 2017-04-04 14:11:31 5333 [Note] InnoDB: The InnoDB memory heap is disabled 2017-04-04 14:11:31 5333 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins 2017-04-04 14:11:31 5333 [Note] InnoDB: Memory barrier is not used 2017-04-04 14:11:31 5333 [Note] InnoDB: Compressed tables use zlib 1.2.8 2017-04-04 14:11:31 5333 [Note] InnoDB: Using Linux native AIO 2017-04-04 14:11:31 5333 [Note] InnoDB: Using CPU crc32 instructions 2017-04-04 14:11:31 5333 [Note] InnoDB: Initializing buffer pool, size = 128.0M 2017-04-04 14:11:31 5333 [Note] InnoDB: Completed initialization of buffer pool 2017-04-04 14:11:31 7f0c74d45780 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. 2017-04-04 14:11:31 7f0c74d45780 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. 2017-04-04 14:11:31 5333 [ERROR] InnoDB: Creating or opening ./ibdata1 failed! 2017-04-04 14:11:31 5333 [ERROR] InnoDB: Could not open or create the system tablespace. If you tried to add new data files to the system tablespace, and it failed here, you should now edit innodb_data_file_path in my.cnf back to what it was, and remove the new ibdata files InnoDB created in this failed attempt. InnoDB only wrote those files full of zeros, but did not yet use them in any way. But be careful: do not remove old data files which contain your precious data! 2017-04-04 14:11:31 5333 [ERROR] Plugin 'InnoDB' init function returned error. 2017-04-04 14:11:31 5333 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed. 2017-04-04 14:11:31 5333 [ERROR] Unknown/unsupported storage engine: InnoDB 2017-04-04 14:11:31 5333 [ERROR] Aborting 2017-04-04 14:11:31 5333 [Note] Binlog end 2017-04-04 14:11:31 5333 [Note] /usr/sbin/mysqld: Shutdown complete 2017-04-04 16:18:52 26762 [Note] InnoDB: Using atomics to ref count buffer pool pages 2017-04-04 16:18:52 26762 [Note] InnoDB: The InnoDB memory heap is disabled 2017-04-04 16:18:52 26762 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins 2017-04-04 16:18:52 26762 [Note] InnoDB: Memory barrier is not used 2017-04-04 16:18:52 26762 [Note] InnoDB: Compressed tables use zlib 1.2.8 2017-04-04 16:18:52 26762 [Note] InnoDB: Using Linux native AIO 2017-04-04 16:18:52 26762 [Note] InnoDB: Using CPU crc32 instructions 2017-04-04 16:18:52 26762 [Note] InnoDB: Initializing buffer pool, size = 128.0M 2017-04-04 16:18:52 26762 [Note] InnoDB: Completed initialization of buffer pool 2017-04-04 16:18:52 7f901bdbc780 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. 2017-04-04 16:18:52 7f901bdbc780 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. 2017-04-04 16:18:52 26762 [ERROR] InnoDB: Creating or opening ./ibdata1 failed! 2017-04-04 16:18:52 26762 [ERROR] InnoDB: Could not open or create the system tablespace. If you tried to add new data files to the system tablespace, and it failed here, you should now edit innodb_data_file_path in my.cnf back to what it was, and remove the new ibdata files InnoDB created in this failed attempt. InnoDB only wrote those files full of zeros, but did not yet use them in any way. But be careful: do not remove old data files which contain your precious data! 2017-04-04 16:18:52 26762 [ERROR] Plugin 'InnoDB' init function returned error. 2017-04-04 16:18:52 26762 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed. 2017-04-04 16:18:52 26762 [ERROR] Unknown/unsupported storage engine: InnoDB 2017-04-04 16:18:52 26762 [ERROR] Aborting 2017-04-04 16:18:52 26762 [Note] Binlog end 2017-04-04 16:18:52 26762 [Note] /usr/sbin/mysqld: Shutdown complete
[4 Apr 2017 16:35]
Purvez Desai
Sorry I copied a bit too much of the error.log file. It should really start from lines as follows. Ignore everything before that. 2017-04-04 16:18:52 26762 [Note] InnoDB: Using atomics to ref count buffer pool pages 2017-04-04 16:18:52 26762 [Note] InnoDB: The InnoDB memory heap is disabled
[4 Apr 2017 16:54]
Lars Tangvald
So it looks like running sudo mysql_install_db worked. Can you paste the contents of /var/lib/mysql now? And does apt-get -f install still fail?
[4 Apr 2017 17:24]
Lars Tangvald
Eh, no. Misread that :) Can you check what I mentioned right at the start, running dmesg -T | grep mysql and look for DENIED messages at the time of running mysql_install_db?
[4 Apr 2017 17:37]
Purvez Desai
Sorry Lars but I don't think that worked. /var/lib/mysql just has another mysql dir underneath it which is also empty: Here's the output root@server-02:/var/lib# cd mysql root@server-02:/var/lib/mysql# ls -l total 4 drwxr-x--- 2 mysql mysql 4096 Apr 4 14:11 mysql root@server-02:/var/lib/mysql# cd mysql root@server-02:/var/lib/mysql/mysql# ls -l total 0 root@server-02:/var/lib/mysql/mysql# Also sadly sudo apt-get -f install also still fails. Here's the output of that: nexargi@server-02:/var/lib$ sudo apt-get -f install Reading package lists... Done Building dependency tree Reading state information... Done 0 upgraded, 0 newly installed, 0 to remove and 3 not upgraded. 2 not fully installed or removed. After this operation, 0 B of additional disk space will be used. Setting up mysql-community-server (5.6.35-1ubuntu14.04) ... 2017-04-04 17:34:33 0 [Note] Ignoring --secure-file-priv value as server is running with --bootstrap. 2017-04-04 17:34:33 0 [Note] mysqld (mysqld 5.6.35) starting as process 6929 ... 2017-04-04 17:34:33 6929 [Warning] Can't create test file /var/lib/mysql/server-02.lower-test 2017-04-04 17:34:33 6929 [Warning] Can't create test file /var/lib/mysql/server-02.lower-test dpkg: error processing package mysql-community-server (--configure): subprocess installed post-installation script returned error exit status 1 dpkg: dependency problems prevent configuration of mysql-server: mysql-server depends on mysql-community-server (= 5.6.35-1ubuntu14.04); however: Package mysql-community-server is not configured yet. dpkg: error processing package mysql-server (--configure): dependency problems - leaving unconfigured Errors were encountered while processing: mysql-community-server mysql-server E: Sub-process /usr/bin/dpkg returned an error code (1) nexargi@server-02:/var/lib$ Thanks very much for your continued help with this.
[4 Apr 2017 17:55]
Purvez Desai
Hi Lars....looks like pretty much everything is DENIED: Here is the outpu from dmesg -T ========== nexargi@server-02:/var/lib$ dmesg -T | grep mysql [Tue Apr 4 10:46:32 2017] type=1400 audit(1491302793.039:11): apparmor="STATUS" operation="profile_load" profile="unconfined" name="/usr/sbin/mysqld" pid=1434 comm="apparmor_parser" [Tue Apr 4 10:46:32 2017] type=1400 audit(1491302793.207:13): apparmor="STATUS" operation="profile_replace" profile="unconfined" name="/usr/sbin/mysqld" pid=1524 comm="apparmor_parser" [Tue Apr 4 11:14:31 2017] type=1400 audit(1491304472.176:14): apparmor="DENIED" operation="open" profile="/usr/sbin/mysqld" name="/proc/7634/status" pid=7634 comm="mysqld" requested_mask="r" denied_mask="r" fsuid=0 ouid=0 [Tue Apr 4 11:14:31 2017] type=1400 audit(1491304472.176:15): apparmor="DENIED" operation="open" profile="/usr/sbin/mysqld" name="/sys/devices/system/node/" pid=7634 comm="mysqld" requested_mask="r" denied_mask="r" fsuid=0 ouid=0 [Tue Apr 4 11:14:31 2017] type=1400 audit(1491304472.176:16): apparmor="DENIED" operation="open" profile="/usr/sbin/mysqld" name="/proc/7634/status" pid=7634 comm="mysqld" requested_mask="r" denied_mask="r" fsuid=0 ouid=0 [Tue Apr 4 11:14:31 2017] type=1400 audit(1491304472.180:17): apparmor="DENIED" operation="mknod" profile="/usr/sbin/mysqld" name="/var/lib/mysql/server-02.lower-test" pid=7634 comm="mysqld" requested_mask="c" denied_mask="c" fsuid=0 ouid=0 [Tue Apr 4 11:14:31 2017] type=1400 audit(1491304472.180:18): apparmor="DENIED" operation="mknod" profile="/usr/sbin/mysqld" name="/var/lib/mysql/server-02.lower-test" pid=7634 comm="mysqld" requested_mask="c" denied_mask="c" fsuid=0 ouid=0 [Tue Apr 4 11:14:31 2017] type=1400 audit(1491304472.192:19): apparmor="DENIED" operation="mknod" profile="/usr/sbin/mysqld" name="/var/lib/mysql/ibdata1" pid=7634 comm="mysqld" requested_mask="c" denied_mask="c" fsuid=104 ouid=104 [Tue Apr 4 14:11:30 2017] type=1400 audit(1491315091.668:20): apparmor="DENIED" operation="open" profile="/usr/sbin/mysqld" name="/proc/5333/status" pid=5333 comm="mysqld" requested_mask="r" denied_mask="r" fsuid=0 ouid=0 [Tue Apr 4 14:11:30 2017] type=1400 audit(1491315091.668:21): apparmor="DENIED" operation="open" profile="/usr/sbin/mysqld" name="/sys/devices/system/node/" pid=5333 comm="mysqld" requested_mask="r" denied_mask="r" fsuid=0 ouid=0 [Tue Apr 4 14:11:30 2017] type=1400 audit(1491315091.668:22): apparmor="DENIED" operation="open" profile="/usr/sbin/mysqld" name="/proc/5333/status" pid=5333 comm="mysqld" requested_mask="r" denied_mask="r" fsuid=0 ouid=0 [Tue Apr 4 14:11:30 2017] type=1400 audit(1491315091.672:23): apparmor="DENIED" operation="mknod" profile="/usr/sbin/mysqld" name="/var/lib/mysql/server-02.lower-test" pid=5333 comm="mysqld" requested_mask="c" denied_mask="c" fsuid=0 ouid=0 [Tue Apr 4 14:11:30 2017] type=1400 audit(1491315091.672:24): apparmor="DENIED" operation="mknod" profile="/usr/sbin/mysqld" name="/var/lib/mysql/server-02.lower-test" pid=5333 comm="mysqld" requested_mask="c" denied_mask="c" fsuid=0 ouid=0 [Tue Apr 4 14:11:31 2017] type=1400 audit(1491315091.688:25): apparmor="DENIED" operation="mknod" profile="/usr/sbin/mysqld" name="/var/lib/mysql/ibdata1" pid=5333 comm="mysqld" requested_mask="c" denied_mask="c" fsuid=104 ouid=104 [Tue Apr 4 16:18:34 2017] type=1400 audit(1491322715.132:26): apparmor="DENIED" operation="open" profile="/usr/sbin/mysqld" name="/proc/26633/status" pid=26633 comm="mysqld" requested_mask="r" denied_mask="r" fsuid=1001 ouid=1001 [Tue Apr 4 16:18:34 2017] type=1400 audit(1491322715.132:27): apparmor="DENIED" operation="open" profile="/usr/sbin/mysqld" name="/sys/devices/system/node/" pid=26633 comm="mysqld" requested_mask="r" denied_mask="r" fsuid=1001 ouid=0 [Tue Apr 4 16:18:34 2017] type=1400 audit(1491322715.132:28): apparmor="DENIED" operation="open" profile="/usr/sbin/mysqld" name="/proc/26633/status" pid=26633 comm="mysqld" requested_mask="r" denied_mask="r" fsuid=1001 ouid=1001 [Tue Apr 4 16:18:51 2017] type=1400 audit(1491322732.504:29): apparmor="DENIED" operation="open" profile="/usr/sbin/mysqld" name="/proc/26762/status" pid=26762 comm="mysqld" requested_mask="r" denied_mask="r" fsuid=0 ouid=0 [Tue Apr 4 16:18:51 2017] type=1400 audit(1491322732.504:30): apparmor="DENIED" operation="open" profile="/usr/sbin/mysqld" name="/sys/devices/system/node/" pid=26762 comm="mysqld" requested_mask="r" denied_mask="r" fsuid=0 ouid=0 [Tue Apr 4 16:18:51 2017] type=1400 audit(1491322732.504:31): apparmor="DENIED" operation="open" profile="/usr/sbin/mysqld" name="/proc/26762/status" pid=26762 comm="mysqld" requested_mask="r" denied_mask="r" fsuid=0 ouid=0 [Tue Apr 4 16:18:51 2017] type=1400 audit(1491322732.508:32): apparmor="DENIED" operation="mknod" profile="/usr/sbin/mysqld" name="/var/lib/mysql/server-02.lower-test" pid=26762 comm="mysqld" requested_mask="c" denied_mask="c" fsuid=0 ouid=0 [Tue Apr 4 16:18:51 2017] type=1400 audit(1491322732.508:33): apparmor="DENIED" operation="mknod" profile="/usr/sbin/mysqld" name="/var/lib/mysql/server-02.lower-test" pid=26762 comm="mysqld" requested_mask="c" denied_mask="c" fsuid=0 ouid=0 [Tue Apr 4 16:18:51 2017] type=1400 audit(1491322732.524:34): apparmor="DENIED" operation="mknod" profile="/usr/sbin/mysqld" name="/var/lib/mysql/ibdata1" pid=26762 comm="mysqld" requested_mask="c" denied_mask="c" fsuid=104 ouid=104 [Tue Apr 4 17:34:32 2017] type=1400 audit(1491327273.464:35): apparmor="DENIED" operation="open" profile="/usr/sbin/mysqld" name="/proc/6929/status" pid=6929 comm="mysqld" requested_mask="r" denied_mask="r" fsuid=0 ouid=0 [Tue Apr 4 17:34:32 2017] type=1400 audit(1491327273.464:36): apparmor="DENIED" operation="open" profile="/usr/sbin/mysqld" name="/sys/devices/system/node/" pid=6929 comm="mysqld" requested_mask="r" denied_mask="r" fsuid=0 ouid=0 [Tue Apr 4 17:34:32 2017] type=1400 audit(1491327273.464:37): apparmor="DENIED" operation="open" profile="/usr/sbin/mysqld" name="/proc/6929/status" pid=6929 comm="mysqld" requested_mask="r" denied_mask="r" fsuid=0 ouid=0 [Tue Apr 4 17:34:32 2017] type=1400 audit(1491327273.468:38): apparmor="DENIED" operation="mknod" profile="/usr/sbin/mysqld" name="/var/lib/mysql/server-02.lower-test" pid=6929 comm="mysqld" requested_mask="c" denied_mask="c" fsuid=0 ouid=0 [Tue Apr 4 17:34:32 2017] type=1400 audit(1491327273.468:39): apparmor="DENIED" operation="mknod" profile="/usr/sbin/mysqld" name="/var/lib/mysql/server-02.lower-test" pid=6929 comm="mysqld" requested_mask="c" denied_mask="c" fsuid=0 ouid=0 [Tue Apr 4 17:34:32 2017] type=1400 audit(1491327273.484:40): apparmor="DENIED" operation="mknod" profile="/usr/sbin/mysqld" name="/var/lib/mysql/ibdata1" pid=6929 comm="mysqld" requested_mask="c" denied_mask="c" fsuid=104 ouid=104 nexargi@server-02:/var/lib$ ===========
[4 Apr 2017 17:56]
Lars Tangvald
No worries. It's a bit annoying I can't spot exactly what the issue is, but try: * Delete /var/lib/mysql again * apt-get remove mysql-common (this should remove all the mysql-packages from repo.mysql.com) * apt-get install mysql-server And if _that_ doesn't work: Am I right in understanding you don't have anything of your own in the config either (since you let the 5.6 install overwrite your custom config). If that's the case, you could: * apt-get purge mysql-common * rm -rf /etc/mysql && rm -rf /var/lib/mysql * apt-get install mysql-server That should give you a completely fresh install, and you can the reconfigure to use the database you have in a different location.
[4 Apr 2017 18:23]
Purvez Desai
Hi Lars the first set of commands didn't work so I tried the second. Here's the output after apt-get install mysql-server ============ nexargi@server-02:/$ sudo apt-get install mysql-server Reading package lists... Done Building dependency tree Reading state information... Done The following packages were automatically installed and are no longer required: libdbi-perl libterm-readkey-perl mysql-client-core-5.5 Use 'apt-get autoremove' to remove them. The following extra packages will be installed: mysql-common mysql-community-server Recommended packages: mysql-client The following NEW packages will be installed: mysql-common mysql-community-server mysql-server 0 upgraded, 3 newly installed, 0 to remove and 0 not upgraded. Need to get 0 B/14.0 MB of archives. After this operation, 94.2 MB of additional disk space will be used. Do you want to continue? [Y/n] Y Preconfiguring packages ... Selecting previously unselected package mysql-common. (Reading database ... 62270 files and directories currently installed.) Preparing to unpack .../mysql-common_5.6.35-1ubuntu14.04_amd64.deb ... Unpacking mysql-common (5.6.35-1ubuntu14.04) ... Selecting previously unselected package mysql-community-server. Preparing to unpack .../mysql-community-server_5.6.35-1ubuntu14.04_amd64.deb ... Unpacking mysql-community-server (5.6.35-1ubuntu14.04) ... Selecting previously unselected package mysql-server. Preparing to unpack .../mysql-server_5.6.35-1ubuntu14.04_amd64.deb ... Unpacking mysql-server (5.6.35-1ubuntu14.04) ... Processing triggers for man-db (2.6.7.1-1ubuntu1) ... Processing triggers for ureadahead (0.100.0-16) ... Setting up mysql-common (5.6.35-1ubuntu14.04) ... Setting up mysql-community-server (5.6.35-1ubuntu14.04) ... 2017-04-04 18:20:45 0 [Note] Ignoring --secure-file-priv value as server is running with --bootstrap. 2017-04-04 18:20:45 0 [Note] /usr/sbin/mysqld (mysqld 5.6.35) starting as process 16655 ... 2017-04-04 18:20:45 16655 [Warning] Can't create test file /var/lib/mysql/server-02.lower-test 2017-04-04 18:20:45 16655 [Warning] Can't create test file /var/lib/mysql/server-02.lower-test dpkg: error processing package mysql-community-server (--configure): subprocess installed post-installation script returned error exit status 141 dpkg: dependency problems prevent configuration of mysql-server: mysql-server depends on mysql-community-server (= 5.6.35-1ubuntu14.04); however: Package mysql-community-server is not configured yet. dpkg: error processing package mysql-server (--configure): dependency problems - leaving unconfigured Processing triggers for ureadahead (0.100.0-16) ... Errors were encountered while processing: mysql-community-server mysql-server E: Sub-process /usr/bin/dpkg returned an error code (1) nexargi@server-02:/$ ============ Persistent little thing this one eh?!! ;-) I don't know whether to laugh or cry....but being the optimistic sort I'm doing the former. Very much appreciate your patience with this one.
[4 Apr 2017 20:15]
Lars Tangvald
Argh. Run these two commands and see what they say dmesg -T | grep mysql sudo service apparmor reload
[4 Apr 2017 21:58]
Purvez Desai
the dmesg -T gave........a message that I'm going to have to attach as a file as I'm told that the comment is too long by your system. and the apparmor reload gave === nexargi@server-02:/$ sudo service apparmor reload [sudo] password for nexargi: * Reloading AppArmor profiles Skipping profile in /etc/apparmor.d/disable: usr.sbin.rsyslogd [ OK ] nexargi@server-02:/$ === Good night speak to you tomorrow. Thanks again for all your help and support.
[4 Apr 2017 22:01]
Purvez Desai
Output from dmesg -T | grep mysql as requested
Attachment: OutputFromDmesg (application/octet-stream, text), 7.91 KiB.
[5 Apr 2017 5:30]
Lars Tangvald
Regretting saying nevermind to my very first question, which was to run dmesg :) [Tue Apr 4 18:20:45 2017] type=1400 audit(1491330045.944:46): apparmor="DENIED" operation="mknod" profile="/usr/sbin/mysqld" name="/var/lib/mysql/ibdata1" pid=16655 comm="mysqld" requested_mask="c" denied_mask="c" fsuid=104 ouid=104 My best guess is that the install didn't actually overwrite the apparmor profile. When you changed it originally to move datadir, did you add the new location, or change /var/lib/mysql in the profile to the new one? /etc/apparmor.d/usr.sbin.mysqld must contain the following lines: /var/lib/mysql/ r, /var/lib/mysql/** rwk, If it doesn't, please add them and then run /etc/init.d/apparmor reload Then delete the contents of /var/lib/mysql (as root) and try apt-get -f install again. Generally, the apparmor profile should look like this for 5.6.35: https://github.com/mysql/mysql-server/blob/5.6/packaging/deb-trusty/extra/apparmor-profile
[5 Apr 2017 8:10]
Purvez Desai
Hi Lars The usr.sbin.mysqld file in /etc/apparmor.d did have those lines in. It had no lines for the actual data directory. Anyway I reloaded apparmor and then ran sudo apt-get -f install Here is the output from that: (sadly no change) ============= nexargi@server-02:/$ sudo apt-get -f install Reading package lists... Done Building dependency tree Reading state information... Done The following packages were automatically installed and are no longer required: libdbi-perl libterm-readkey-perl mysql-client-core-5.5 Use 'apt-get autoremove' to remove them. 0 upgraded, 0 newly installed, 0 to remove and 0 not upgraded. 2 not fully installed or removed. After this operation, 0 B of additional disk space will be used. Setting up mysql-community-server (5.6.35-1ubuntu14.04) ... mkdir: cannot create directory ‘/var/lib/mysql/mysql’: No such file or directory dpkg: error processing package mysql-community-server (--configure): subprocess installed post-installation script returned error exit status 1 dpkg: dependency problems prevent configuration of mysql-server: mysql-server depends on mysql-community-server (= 5.6.35-1ubuntu14.04); however: Package mysql-community-server is not configured yet. dpkg: error processing package mysql-server (--configure): dependency problems - leaving unconfigured Errors were encountered while processing: mysql-community-server mysql-server E: Sub-process /usr/bin/dpkg returned an error code (1) nexargi@server-02:/$ ================ I was just wondering if I ran a sudo su command and then ran the full upgrade from there, then would that make a difference? Looking through the web I have found some references to the fact that running something as sudo su provides different environments to just the sudo command, although not privileges....which is where our problem seems to lie.
[5 Apr 2017 8:18]
Purvez Desai
Just to confirm that the usr.sbin.mysqld file in apparmor.d is identical to the link you gave.
[5 Apr 2017 8:30]
Purvez Desai
Hi Lars Looking at the output of the apt-get -f install it was complaining that it couldn't create the /var/lib/mysql/mysql directory. I then remembered you saying that that was because the pre script created /var/lib/mysql and the post script only tried to create the additional mysql dir. So I went back and created the /var/lib/mysql and gave it the proper ownership and permissions and then ran sudo apt-get -f install again.....and guess what!! We have SUCCESS!!!! Woooo Hoooo!! Excuse my exuberance.
[5 Apr 2017 8:42]
Purvez Desai
Although I'm ecstatic about getting the server working I still need to configure it point to where my real data is. So here are some questions relating to that: 1. Do I simply shut down the server, change the datadir variable in /etc/mysql/my.cnf and restart? 2. Because the server created a new install under /var/lib/mysql/ the original data has not been upgraded. So should I run mysql-upgrade once I've changed the datadir? 3. I read somewhere that the first time mysql is started where the data directory is from an older version then I need to use skip-grant-tables. Do I still need to do that here? 4. Is there anything else I need to know at this stage for reconfiguring to access my original data? Again many many thanks for your help here. I'd have never made it this far. Would be useful to understand why it failed though. Was it just that although the usr.sbin.mysqld file was correctly overwritten apparmor was not reloaded? Is that something that needs to happen in your scripts?
[5 Apr 2017 8:48]
Purvez Desai
Lars I need to push my luck a bit further. I really need to get to mysql5.7. I can take an image of my server at this point and then dpkg -i on the deb file from your repo and choose to receive 5.7 next. Then run the upgrade. That way I'm still doing it with the datadir pointing to /var/lib/mysql. Once I have a working mysql5.7 then I can WITH YOUR HELP. move the datadir. Would that be a better thing to do or should I first change the datadir whilst still under 5.6 and then try the 5.7 upgrade. I'll be guided by you on this one please.
[5 Apr 2017 9:07]
Lars Tangvald
Woohoo! About changing datadir: You should be able to do it the same way as you did for 5.5. But note that our current packages have pretty poor support for custom data locations. This only really affects upgrades and systemd systems and is improved in the next release, so I don't think it should affect you. About mysql_upgrade: The major different between native Debian/Ubuntu packages and the ones on repo.mysql.com is that our packages do not automatically run mysql_upgrade, so you will always need to do that manually. The reason is that mysql_upgrade is a client, which requires login access (in native packaging this is handled by creating a special debian-sys-maint user with admin access, which is used by the packaging). We recommend running mysql_upgrade on your actual data to get it upgraded to 5.6 before moving on to 5.7, but 5.7 should also be able to handle upgrading from the 5.5 data (Ubuntu 14 has 5.5 as default while 16 moves them directly to 5.7). I guess the absolutely _safest_ way to handle this would be: 1. Backup your data to be sure 2. Change your 5.6 install to point to your data 3. Run mysql_upgrade 4. Switch back to default config 5. Upgrade to 5.7 6. Change the 5.7 install to point to your data 7. Run mysql_upgrade As I mentioned, skipping 2-4 should work, but we only test that with very simple databases.
[5 Apr 2017 9:36]
Lars Tangvald
Didn't fully answer your questions: 1: You also need to update the apparmor profile to allow the same access to your datadir as it does for /var/lib/mysql 2: Yes, as mentioned above: 3: _Almost_ certainly not. This has been the case if the auth used for the root user is very old (pre-5.5, at least), so might depend on when your database was first created. If you start 5.6/5.7 and are unable to log in you might need to do this. 4: I don't think so As for the failure, it seems apparmor didn't correctly reload the profile, yes. This should happen automatically, so we'll need to look into that some more. Was the failure the same when trying to upgrade to the native 5.6 packages in Ubuntu 14?
[5 Apr 2017 10:36]
Purvez Desai
Lars very glad you share my enthusiasm ;-)!! I like your belts and braces approach to getting to 5.7. However I'll fall on the first hurdle. I can't remember how I moved the datadir in 5.5. Any links please? Looking at the current LIVE production system (still at ubuntu12.04 & mysql5.5) it's usr.sbin.mysqld does have entries for the data directory. Also the /var/lib/mysql entries there have been commented out AND there is no mysql directory at all under /var/lib/. Little wonder things fell apart so dramatically although if apparmor had reloaded we would never have found out...or wasted so much time! LOL!! My data directory is at: /data/mysqldata The /data has FULL access to everyone. The following is the mysqldata directory info: drwx------ 6 mysql mysql 4096 May 14 2015 mysqldata I have a copy of the my.cnf file before 5.6 overwrote it. I'm going to go through and compare the 2 to see what is different. One thing that I am aware of is that I had to manually change 5.5 from a single INNODB file per database to multiple INNODB files per table in each database. I understand that 5.6 now defaults to the files per table so will I still need to do anything within my.cnf for this to continue to work? Ok so I guess the main thing right now is for me to understand how I point to my own data. I'll google around but if you have something handy then please post it and I'd rather work with that. Thanks and I can see the light at the end of this tunnel.....its NOT a train. Haha.
[5 Apr 2017 10:39]
Purvez Desai
Sorry you asked whether the failure using ubuntu's repo was the same. I honestly don't remember the details because I hadn't fully understood the role of apparmor at the time. I am guessing however that it was. Unfortunately Canonical hasn't even acknowledged the bug that I reported as yet. Clearly they must have BIGGER problems!!
[5 Apr 2017 10:57]
Lars Tangvald
Generally you should just need to repeat the same access as the default profile has for var/lib/mysql: /var/lib/mysql/ r, /var/lib/mysql/** rwk, So adding the following: /data/mysqldata/ r, /data/mysqldata/** rwk, in the profile file should to it, then run sudo service apparmor reload, then change mysql config to datadir=/data/mysqldata, and run sudo service mysql restart, and hopefully you should be good to go. About canonical, we actually help them out with the MySQL bug reports, but the volume tends to much higher than what we get here, so it's mostly focused on 5.7 (particularly 5.6 doesn't get as much attention as it should since it's not the default version in 14.04)
[5 Apr 2017 11:05]
Lars Tangvald
Oh, and yes you should also put in the other config options you changed in 5.5. I don't think the innodb-file-per-table option should affect the upgrade in any way, but there are some 5.5 options that are removed in 5.7, which might cause issues. There's a simple way to check if the server is in a working state, by running: mysqld --verbose --help 2>&1 --not-working > /dev/null If you see something along the lines of «[ERROR] unknown option '--key_buffer'» you may have some obsolete options in your config. Particularly these two: key_buffer and myisam_recover. They are in the default 5.5 config in Ubuntu, and will fail for 5.7, but they were simply renamed to key_buffer_size and myisam_recover_options
[5 Apr 2017 11:30]
Lars Tangvald
That --not-working shouldn't be there, so: mysqld --verbose --help 2>&1 >/dev/null
[5 Apr 2017 11:57]
Purvez Desai
Lars I have a confession to make.....which leads to some GOOD NEWS so I hope you'll forgive my transgression: Once I got the 5.6 working it got me thinking. ALL of this had started with me trying to upgrade my ubuntu14.04 to ubuntu16.04 and falling down with the mysql upgrade to 5.7. So..... with my new found MENTOR (aka YOU) and the knowledge you gave me about apparmor and dmesg -T etc I thought .....I wonder....if I revert back to a clean ubuntu14.04 with mysql5.5 and then the do-release-upgrade AND accept the revised usr.sbin.mysqld and let it fail. THEN I run /etc/init.d/apparmor reload and then run sudo apt-get -f install, what would happen. Well it failed when trying to run /etc/init.d/apparmor reload APPARENTLY in ubuntu16.04 the command is : sudo start apparmor ACTION=reload AfteR doing that I ran sudo apt-get -f install again and well.... it failed AGAIN but using the new dmesg -T knowledge I found out that it couldn't access the /data/mysqldata directory. SO I edited IN the relevant permission for the /data/mysqldata directory in usr.sbin.mysqld and then ran sudo apt-get -f install again and GUESS WHAT !!! It's working with MY data!! I know this is 'all in a day's work' for you guys but for me it's a BIG DEAL and the ONLY reason I could get here was because of YOUR PERSISTENCE. A MILLION THANKS. I'm going to go and post this on Ask Ubuntu and Stackoverflow at a minimum so that some other poor S.O.D. doesn't get lumbered with what you and I had to go through. Is there a MySQL forum where I should post this too or can you arrange that. Yes I KNOW I have a BUNCH of testing to do before I can release this into production but I am just SOOOOO HAPPY right now. I often wonder whether OpenSource is more of a hindrance than a help. Control of everything through a single source has its benefits....as long as I'm the one in control of the single source!! LOL!!
[5 Apr 2017 12:52]
Lars Tangvald
Great that you got it working! Issues like this can be really frustrating, but also very satisfying when it finally works :) We have forums at https://forums.mysql.com/ where it would probably be great to have this sort of thing documented.
[5 Apr 2017 13:57]
Purvez Desai
Lars it has been a PLEASURE having you as my support. You've been both patient whilst things didn't go right AND also a mentor in explaining 'how' things worked. Very few people in my experience have that ability to teach whilst still trying to solve the original problem. KUDOS to you. Thanks again....and I hope our paths cross again but in more pleasant circumstances. Purvez
[7 Apr 2017 6:49]
Lars Tangvald
Original title: Attempting to upgrade from mysql5.5 to mysql5.6 using mysql repos but failed. --------------- If the user has customized their datadir by _changing_ the allowed datadir location in the apparmor profile (not just adding the new one), changing both cnf and apparmor profile, then agrees to overwrite custom config when upgrading (to repo.mysql.com packages. From doesn't seem to matter), then the apparmor profile will not be correctly replaced and reloaded, causing installation to fail when the server tries to write to the default datadir. Reproduce: * Install mysql 5.6.36, * Run service mysql stop * Rename /var/lib/mysql to /var/lib/mysql2 * Change /var/lib/mysql to /var/lib/mysql2 in /etc/mysql/mysql.conf.d/mysqld.cnf and /etc/apparmor.d/usr.sbin.mysqld * Run service apparmor reload and service mysql start * Upgrade to 5.7.18 Tested on Ubuntu 14.04 and 16.04 Choosing to keep the custom config will work (as long as it doesn't have options removed in the new version).
[7 Apr 2017 6:50]
Lars Tangvald
While the workaround itself is quite simple (fix apparmor profile and reload it), I think we've demonstrated fairly well it's not simple to find if you don't know exactly what to look for :)
[7 Apr 2017 12:02]
Purvez Desai
Lar, apologies. I got so far behind with everything that I had to do so I haven't had a chance to write it up on any forum. However I think you are absolutely SPOT ON when you say that the remedy is simple....ONCE YOU KNOW WHAT TO DO. I think currently any upgrade from 5.6 to 5.7 where the datadir default location has been changed WILL FAIL. To remedy that you need to add the same permissions in apparmor for the new dir locations as the default ones and then run: sudo apt-get -f install I don't know how easy or difficult the following is but if the scripts can do this then there would be no failure (DUE TO THIS ISSUE). 1. Script checks in my.cnf if datadir is not /var/lib/mysql. 2. If it is not then on installing usr.sbin.mysqld in /etc/apparmor it can add those 2 lines. 3. Reload apparmor profiles 4. Continue with rest of install.
[7 Apr 2017 12:07]
Purvez Desai
Lars, you stated that 'choosing to keep custom configs would work'. However I've been thinking about that. Because 5.7 adds a new directory /etc/mysql/mysql.conf.d then that directory would not be in the old custom config and it fails again. To my mind the simplest answer for manual correction is to accept the replacement of usr.sbin.mysqld and then add in the 2 entries for the changed datadir. Reload the apparmor profile and continue with sudo apt-get -f install. Thanks again for all your help with this one.