On Upgrades to Percona Server 5.7 GA: Ubuntu 14.04 Case

After upgrading Percona Server 5.6.28 (and "full stack" of Percona software) to 5.7.10-3 GA on CentOS 6 and building it from Git Source on Fedora Core 23 yesterday, I've decided to spend some time upgrading Percona Server on my netbook that plays a role of "home build/test/VMs server" and runs under Ubuntu 14.04:

openxs@ao756:~$ uname -a
Linux ao756 3.13.0-77-generic #121-Ubuntu SMP Wed Jan 20 10:50:42 UTC 2016 x86_64 x86_64 x86_64 GNU/Linux
openxs@ao756:~$ cat /etc/issue
Ubuntu 14.04.4 LTS \n \l

It had the following set of Percona packages (as a result of some tests with Percona XtraDB Cluster I had to use sometime while working there) and mysqld process running after OS startup:

openxs@ao756:~$ dpkg -l | grep -i percona | grep ii
ii  libmysqlclient18                                      1:5.5.35-rel33.0-611.raring                         amd64        Percona Server database client library
ii  libmysqlclient18-dev                                  1:5.5.35-rel33.0-611.raring                         amd64        Percona Server database development files
ii  libperconaserverclient18                              5.5.47-rel37.7-1.trusty                             amd64        Percona Server database client library
ii  libperconaserverclient18.1                            5.6.28-76.1-1.trusty                                amd64        Percona Server database client library
ii  percona-server-5.6-dbg                                5.6.28-76.1-1.trusty                                amd64        Debugging package for Percona Server
ii  percona-server-client-5.6                             5.6.28-76.1-1.trusty                                amd64        Percona Server database client binaries
ii  percona-server-common-5.5                             5.5.47-rel37.7-1.trusty                             amd64        Percona Server database common files (e.g. /etc/mysql/my.cnf)
ii  percona-server-common-5.6                             5.6.28-76.1-1.trusty                                amd64        Percona Server database common files (e.g. /etc/mysql/my.cnf)
ii  percona-server-server-5.6                             5.6.28-76.1-1.trusty                                amd64        Percona Server database server binaries
ii  percona-toolkit                                       2.2.16-1                                            all          Advanced MySQL and system command-line tools
ii  percona-xtrabackup                                    2.3.3-1.trusty                                      amd64        Open source backup tool for InnoDB and XtraDB
ii  percona-xtradb-cluster-common-5.6                     5.6.28-25.14-1.trusty                               amd64        Percona XtraDB Cluster database common files (e.g. /etc/mysql/my.cnf)
ii  percona-xtradb-cluster-galera-3.x                     3.14-1.trusty                                       amd64        Galera components of Percona XtraDB Cluster

openxs@ao756:~$ ps aux | grep mysqld
root      1283  0.0  0.0   4440   756 ?        S    10:35   0:00 /bin/sh /usr/bin/mysqld_safe
mysql     1505  0.1 13.0 895592 505516 ?       Sl   10:35   0:01 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql --log-error=/var/lib/mysql/ao756.err --pid-file=/var/lib/mysql/ao756.pid
openxs    2938  0.0  0.0  14640   972 pts/4    S+   10:55   0:00 grep --color=auto mysqld

 This time I decided to follow the manual closely, so I checked for all usual/possible locations of configuration files, stopped MySQL server first and then installed the packages for 5.7 (I've noted TokuDB package was NOT installed, so I've added it to the list, as TokuDB storage engine is one of the reasons to use Percona Server 5.7 instead of well tested and known upstream MySQL 5.7):

openxs@ao756:~$ ls -l /etc/mysql/
total 16
drwxr-xr-x 2 root root 4096 тра 22  2014 conf.d
-rw------- 1 root root  333 тра 22  2014 debian.cnf
-rwxr-xr-x 1 root root 1285 січ  9 02:24 debian-start
-rw-r--r-- 1 root root  120 січ 12 13:03 my.cnf

openxs@ao756:~$ ls -l /etc/mysql/conf.d/
total 0

openxs@ao756:~$ ls -l /etc/my.cnf
-rw-r--r-- 1 root root 180 лис 17 14:22 /etc/my.cnf

openxs@ao756:~$ sudo service mysql stop
[sudo] password for openxs:
 * Stopping MySQL (Percona Server) mysqld                                [ OK ]
openxs@ao756:~$ ps aux | grep mysqld
openxs    3102  0.0  0.0  14636   968 pts/4    S+   11:01   0:00 grep --color=auto mysqld

openxs@ao756:~$ sudo apt-get install percona-server-server-5.7 percona-server-tokudb-5.7Reading package lists... Done
Building dependency tree
Reading state information... Done
The following packages were automatically installed and are no longer required:
  libperconaserverclient18.1 percona-server-common-5.6
  percona-xtradb-cluster-galera-3.x socat
Use 'apt-get autoremove' to remove them.
The following extra packages will be installed:
  libmecab2 percona-server-client-5.7 percona-server-common-5.7
The following packages will be REMOVED:
  percona-server-5.6-dbg percona-server-client-5.6 percona-server-server-5.6
The following NEW packages will be installed:
  libmecab2 percona-server-client-5.7 percona-server-common-5.7
  percona-server-server-5.7 percona-server-tokudb-5.7
0 upgraded, 5 newly installed, 3 to remove and 3 not upgraded.
Need to get 21,6 MB of archives.
After this operation, 166 MB disk space will be freed.
...
Setting up libmecab2 (0.996-1.1) ...
Setting up percona-server-server-5.7 (5.7.10-3-1.trusty) ...
Installing new version of config file /etc/default/mysql ...
Installing new version of config file /etc/init.d/mysql ...
...
.................... * Percona Server 5.7.10-3 did not start. Please check logs for more details.
Processing triggers for ureadahead (0.100.0-16) ...
Setting up percona-server-tokudb-5.7 (5.7.10-3-1.trusty) ...

 * This release of Percona Server is distributed with TokuDB storage engine.
 * Run the following script to enable the TokuDB storage engine in Percona Server:

        ps_tokudb_admin --enable -u <mysql_admin_user> -p[mysql_admin_pass] [-S <socket>] [-h <host> -P <port>]

 * See http://www.percona.com/doc/percona-server/5.7/tokudb/tokudb_installation.html for more installation details

 * See http://www.percona.com/doc/percona-server/5.7/tokudb/tokudb_intro.html for an introduction to TokuDB


Processing triggers for libc-bin (2.19-0ubuntu6.7) ...
openxs@ao756:~$ ps aux | grep mysqldopenxs   17729  0.0  0.0  14636   972 pts/4    S+   11:20   0:00 grep --color=auto mysqld

So, even though the server was stopped (by the manual) at the moment of installation, there was an attempt to start server in the process that failed after painful 900 seconds of waiting... I've noted that new /etc/init.d/mysql script was installed and decided to check it (starting it manually under bash -x etc, reading and so on). While 900 seconds startup and 300 second shutdown default timeouts were visible in the code, it was not clear what's wrong. I suspected Percona Server Bug #1490897 again (it was a problem for me on Ubuntu 15.04), but here usual init.d approach to startup was used and none of the configuration files I checked had a single pid_file setting. Attempts to start using the script directly also ended up with 900 dots printed:

openxs@ao756:~$ sudo /etc/init.d/mysql start
No directory, logging in with HOME=/
...

Getting a bit tired of all these, I've checked that at least one way to start server up works, without defaults:

openxs@ao756:~$ sudo mysqld_safe --no-defaults &
[1] 21048
openxs@ao756:~$ 160226 11:51:32 mysqld_safe Adding '/usr/lib/x86_64-linux-gnu/libjemalloc.so.1' to LD_PRELOAD for mysqld
160226 11:51:32 mysqld_safe Logging to '/var/lib/mysql/ao756.err'.
160226 11:51:32 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql

openxs@ao756:~$ ps aux | grep mysqld
root     21048  0.0  0.0  74060  2204 pts/4    S    11:51   0:00 sudo mysqld_safe --no-defaults
root     21049  0.0  0.0   4440   784 pts/4    S    11:51   0:00 /bin/sh /usr/bin/mysqld_safe --no-defaults
mysql    21124  1.8 14.8 1042020 573708 pts/4  Sl   11:51   0:00 /usr/sbin/mysqld --no-defaults --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql --log-error=/var/lib/mysql/ao756.err --pid-file=/var/lib/mysql/ao756.pid
openxs   21154  0.0  0.0  14636   972 pts/4    S+   11:51   0:00 grep --color=auto mysqld

openxs@ao756:~$ mysql -uroot -proot test
mysql: [Warning] Using a password on the command line interface can be insecure.
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.10-3 Percona Server (GPL), Release '3', Revision '63dafaf'

Copyright (c) 2009-2015 Percona LLC and/or its affiliates
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

 So, the problem was surely with some settings in the configuration files and/or init.d script. One of the problems was actually something to expect from 5.7, based on the messages in the error log:

2016-02-26T09:54:47.927969Z 0 [ERROR] You have enabled the binary log, but you haven't provided the mandatory server-id. Please refer to the proper server start-up parameters documentation
2016-02-26T09:54:47.928010Z 0 [ERROR] Aborting

2016-02-26T09:54:47.928052Z 0 [Note] Binlog end
2016-02-26T09:54:47.928131Z 0 [Note] /usr/sbin/mysqld: Shutdown complete

and taking into account the content of /etc/my.cnf at the moment:

openxs@ao756:~$ cat /etc/my.cnf[mysqld_safe]
pid_file=/var/lib/mysql/ao756.pid
[mysql]
connect_timeout=2
[mysqld]
innodb_file_format=Barracuda
table_open_cache=1156
table_open_cache_instances=1
log_bin
gtid_mode=ON
enforce_gtid_consistency
log_slave_updates

 So, I've added mandatory (for my case) server_id=N setting to /etc/my.cnf and tried to start again, to find out error messages that I've seen too many times recently and this year:

2016-02-26T09:56:47.576196Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2016-02-26T09:56:47.666859Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
2016-02-26T09:56:47.668162Z 0 [Warning] InnoDB: Retry attempts for reading partial data failed.
2016-02-26T09:56:47.668218Z 0 [ERROR] InnoDB: Tried to read 2048 bytes at offset 0 was only able to read02016-02-26T09:56:47.668238Z 0 [ERROR] InnoDB: Operating system error number 22 in a file operation.
2016-02-26T09:56:47.668263Z 0 [ERROR] InnoDB: Error number 22 means 'Invalid argument'
2016-02-26T09:56:47.668274Z 0 [Note] InnoDB: Some operating system error numbers are described at http://dev.mysql.com/doc/refman/5.7/en/operating-system-error-codes.html
2016-02-26T09:56:47.668287Z 0 [ERROR] InnoDB: File (unknown): 'read' returned OS error 122. Cannot continue operation2016-02-26T09:56:47.668298Z 0 [ERROR] InnoDB: Cannot continue operation.

I know these messages by heart already thanks to Percona Server Bug #1131949. One of my configuration files had to have innodb_flush_method set to ALL_O_DIRECT:

openxs@ao756:~$ cat /etc/mysql/my.cnf
[mysqld]
innodb_flush_method = ALL_O_DIRECT
innodb_track_changed_pages = 1
innodb_log_file_size                    = 2G

After replacing it with usual O_DIRECT I was able to finally start the server as expected and execute mysql_upgrade:

openxs@ao756:~$ sudo mysqld_safe &
[1] 21711
openxs@ao756:~$ 160226 11:58:33 mysqld_safe Adding '/usr/lib/x86_64-linux-gnu/libjemalloc.so.1' to LD_PRELOAD for mysqld
160226 11:58:33 mysqld_safe Logging to '/var/lib/mysql/ao756.err'.
160226 11:58:33 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql

openxs@ao756:~$ sudo mysql_upgrade -uroot -proot
mysql_upgrade: [Warning] Using a password on the command line interface can be insecure.
Checking if update is needed.
Checking server version.
Running queries to upgrade MySQL server.
...
Repairing tables
mysql.proxies_priv                                 OK
Upgrade process completed successfully.
Checking if update is needed.

openxs@ao756:~$ ps aux | grep mysqld
root     21711  0.0  0.0  74060  2200 pts/4    S    11:58   0:00 sudo mysqld_safe
root     21712  0.0  0.0   4440   760 pts/4    S    11:58   0:00 /bin/sh /usr/bin/mysqld_safe
mysql    21938  6.8 15.8 1066860 610576 pts/4  Sl   11:58   0:12 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql --log-error=/var/lib/mysql/ao756.err --pid-file=/var/lib/mysql/ao756.pid
openxs   22292  0.0  0.0  14636   972 pts/4    S+   12:01   0:00 grep --color=auto mysqld

Moreover, now it can be started as a service also (even though the output looks unusual):

openxs@ao756:~$ sudo service mysql start
No directory, logging in with HOME=/
....
 * Percona Server 5.7.10-3 is started

Next step was to enable TokuDB based on instructions I've already seen:

openxs@ao756:~$ sudo ps_tokudb_admin --enable -uroot -proot
Checking if Percona Server is running with jemalloc enabled...
INFO: Percona Server is running with jemalloc enabled.

Checking transparent huge pages status on the system...
INFO: Transparent huge pages are enabled (should be disabled).

Checking if thp-setting=never option is already set in config file...
INFO: Option thp-setting=never is not set in the config file.
      (needed only if THP is not disabled permanently on the system)

Checking TokuDB engine plugin status...
INFO: TokuDB engine plugin is not installed.

Disabling transparent huge pages for the current session...
INFO: Successfully disabled transparent huge pages for this session.

Adding thp-setting=never option into /etc/my.cnf
INFO: Successfully added thp-setting=never option into /etc/my.cnf

Installing TokuDB engine...
INFO: Successfully installed TokuDB engine plugin.

and create my first TokuDB table on Percona Server 5.7:

openxs@ao756:~$ mysql -uroot -proot testmysql: [Warning] Using a password on the command line interface can be insecure.
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.10-3-log Percona Server (GPL), Release '3', Revision '63dafaf'

Copyright (c) 2009-2015 Percona LLC and/or its affiliates
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create table tt(id int primary key) engine=tokudb;
Query OK, 0 rows affected (0.40 sec)

mysql> show create table tt\G
*************************** 1. row ***************************
       Table: tt
Create Table: CREATE TABLE `tt` (
  `id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=TokuDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

As I want to check "full stack", I definitely need xtrabackup version 2.4 that is not installed by default (note the unusual package name for .deb packages, server has dot in the name before minor version):

openxs@ao756:~$ sudo apt-get install percona-xtrabackup-24
Reading package lists... Done
Building dependency tree
Reading state information... Done
The following packages were automatically installed and are no longer required:
  libperconaserverclient18.1 percona-server-common-5.6
  percona-xtradb-cluster-galera-3.x socat
Use 'apt-get autoremove' to remove them.
The following packages will be REMOVED:
  percona-xtrabackup
The following NEW packages will be installed:
  percona-xtrabackup-240 upgraded, 1 newly installed, 1 to remove and 3 not upgraded.
Need to get 6 084 kB of archives.
After this operation, 10,3 MB of additional disk space will be used.
Do you want to continue? [Y/n]
Get:1 http://repo.percona.com/apt/ trusty/main percona-xtrabackup-24 amd64 2.4.1-1.trusty [6 084 kB]
Fetched 6 084 kB in 8s (691 kB/s)
(Reading database ... 801961 files and directories currently installed.)
Removing percona-xtrabackup (2.3.3-1.trusty) ...
Processing triggers for man-db (2.6.7.1-1ubuntu1) ...
Selecting previously unselected package percona-xtrabackup-24.
(Reading database ... 801947 files and directories currently installed.)
Preparing to unpack .../percona-xtrabackup-24_2.4.1-1.trusty_amd64.deb ...
Unpacking percona-xtrabackup-24 (2.4.1-1.trusty) ...
Processing triggers for man-db (2.6.7.1-1ubuntu1) ...
Setting up percona-xtrabackup-24 (2.4.1-1.trusty) ...

openxs@ao756:~$ xtrabackup --version
xtrabackup version 2.4.1 based on MySQL server 5.7.10 Linux (x86_64) (revision id: a2dc9d4)

It seems at least in my case xtrabackup was not able to find the socket by default, but eventually it worked:

openxs@ao756:~$ sudo xtrabackup --user=root --password=root --backup
160226 12:08:45  version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup' as 'root'  (using password: YES).
160226 12:08:45  version_check Connected to MySQL server
160226 12:08:45  version_check Executing a version check against the server...
160226 12:08:47  version_check Done.
160226 12:08:47 Connecting to MySQL server host: localhost, user: root, password: set, port: 0, socket: (null)
Failed to connect to MySQL server: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2).

openxs@ao756:~$ mysql -uroot -proot test -e "show variables like 'socket%'"
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------+-----------------------------+
| Variable_name | Value                       |
+---------------+-----------------------------+
| socket        | /var/run/mysqld/mysqld.sock |
+---------------+-----------------------------+

openxs@ao756:~$ sudo xtrabackup --user=root --password=root --socket=/var/run/mysqld/mysqld.sock  --backup
...
160226 12:11:43 Finished backing up non-InnoDB tables and files
160226 12:11:43 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
xtrabackup: The latest check point (for incremental): '7481732856'
xtrabackup: Stopping log copying thread.
.160226 12:11:43 >> log scanned up to (7481732865)

160226 12:11:43 Executing UNLOCK TABLES
160226 12:11:43 All tables unlocked
160226 12:11:43 [00] Copying ib_buffer_pool to /home/openxs/xtrabackup_backupfiles/ib_buffer_pool
160226 12:11:43 [00]        ...done
160226 12:11:43 Backup created in directory '/home/openxs/xtrabackup_backupfiles/'
160226 12:11:43 [00] Writing backup-my.cnf
160226 12:11:43 [00]        ...done
160226 12:11:43 [00] Writing xtrabackup_info
160226 12:11:43 [00]        ...done
xtrabackup: Transaction log of lsn (7481732815) to (7481732865) was copied.
160226 12:11:44 completed OK!

I've also installed debug package to have symbols while working with gdb, and checked that pt-mysql-summary works:

openxs@ao756:~$ sudo apt-get install percona-server-5.7-dbg
...
Fetched 160 MB in 59s (2 702 kB/s)
Selecting previously unselected package percona-server-5.7-dbg.
(Reading database ... 801962 files and directories currently installed.)
Preparing to unpack .../percona-server-5.7-dbg_5.7.10-3-1.trusty_amd64.deb ...
Unpacking percona-server-5.7-dbg (5.7.10-3-1.trusty) ...
Setting up percona-server-5.7-dbg (5.7.10-3-1.trusty) ...
openxs@ao756:~$ pt-mysql-summary
# Percona Toolkit MySQL Summary Report #######################
              System time | 2016-02-26 10:17:09 UTC (local TZ: EET +0200)
# Instances ##################################################
  Port  Data Directory             Nice OOM Socket
  ===== ========================== ==== === ======
        /var/lib/mysql             0    0
# MySQL Executable ###########################################
       Path to executable | /usr/sbin/mysqld
              Has symbols | No
# Report On Port 3306 ########################################
                     User | @localhost
                     Time | 2016-02-26 12:17:08 (EET)
                 Hostname | ao756
                  Version | 5.7.10-3-log Percona Server (GPL), Release '3', Revision '63dafaf'
                 Built On | debian-linux-gnu x86_64
                  Started | 2016-02-26 12:02 (up 0+00:14:11)
                Databases | 2
                  Datadir | /var/lib/mysql/
                Processes | 1 connected, 1 running
              Replication | Is not a slave, has 0 slaves connected
                  Pidfile | /var/lib/mysql/ao756.pid (does not exist)...

As a last note, service can not be stopped properly:

openxs@ao756:~$ sudo service mysql stop
 * Percona Server 5.7.10-3 is already stopped
openxs@ao756:~$ ps aux | grep mysqld
mysql    24382  0.1  0.0   4440   760 ?        S    12:21   0:00 /bin/sh /usr/bin/mysqld_safe
mysql    24643  2.9  5.6 1234144 218252 ?      Sl   12:21   0:00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --log-error=/var/lib/mysql/ao756.err --pid-file=/var/lib/mysql/ao756.pid
openxs   24756  0.0  0.0  14636   972 pts/4    S+   12:21   0:00 grep --color=auto mysqld

so one has to use mysqladmin:

openxs@ao756:~$ sudo mysqladmin -uroot -proot shutdownmysqladmin: [Warning] Using a password on the command line interface can be insecure.

openxs@ao756:~$ ps aux | grep mysqld
openxs   24765  0.0  0.0  14636   972 pts/4    S+   12:21   0:00 grep --color=auto mysqld

To summarize my experience of a less naive user of Ubuntu 14.04 (the one who already upgraded to Percona Server 5.7.10-3 on other Linux versions), one should note the following:

1. Please, check all your configuration files carefully for any possible incompatibility with 5.7 before attempting upgrade.
2. Make sure ALL_O_DIRECT value is NOT used for innodb_flush_method. Check this my Facebook post for more details and discussion with Laurynas about the bugs involved (Percona Server Bug #1529885 and Percona Server Bug #1550404) and manual statements related.
3. If you need to use xtrabackup with Percona Server 5.7, you have to install percona-xtrabackup-24 explicitly and may have to specify --socket option explicitly.
4. I think /etc/init.d/mysql script from Percona Server 5.7.10-3 is somewhat broken on Ubuntu 14.04. Something to study in more details later.

I hope this step by step guide will help some of you to upgrade faster and provide some troubleshooting hints in case of problems.