Monday, February 29, 2016

Exploring Metadata Locks with gdb - Studying Simple Case on Percona Server 5.7

I had forgotten the topic of studying metadata locks with gdb for quite a some time. Now that I've upgraded to Percona Server 5.7 and enjoy the remaining of my last free day as an independent MySQL Support Engineer doing nothing but some blogging, I think it's time to get back to gdb interactive sessions and try to answer the question I've got from customer back in Percona but had no chance to answer.

In a bit rewritten way, it sounded as follows:
"Do ALTER TABLE ... STATS_AUTO_RECALC=1" and the reverse ALTER
TABLE...STATS_AUTO_RECALC=default block select, update, delete, and insert on the table?
"
There are two ways to answer this question. First, we can just try to set up a simple test with 3 sessions and simple table, like this:
mysql> create table ti(id int primary key, c1 int, key(c1)) engine=InnoDB stats_auto_recalc=default;Query OK, 0 rows affected (0.16 sec)

mysql> insert into ti values (1,1), (2,2);
Query OK, 2 rows affected (0.09 sec)
Records: 2  Duplicates: 0  Warnings: 0
Now, in one session we can SELECT from the table in transaction that is not completed:
mysql> start transaction;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from ti;
+----+------+
| id | c1   |
+----+------+
|  1 |    1 |
|  2 |    2 |
+----+------+
2 rows in set (0.00 sec)
and then in another session run the ALTER TABLE in question:
mysql> alter table ti stats_auto_recalc=1;
to find out in the first session that it actually hangs waiting on some metadata lock:
mysql> show processlist;
+----+------+-----------+------+---------+------+---------------------------------+------------------------------------+-----------+---------------+
| Id | User | Host      | db   | Command | Time | State                           | Info                               | Rows_sent | Rows_examined |
+----+------+-----------+------+---------+------+---------------------------------+------------------------------------+-----------+---------------+
|  3 | root | localhost | test | Query   |   15 | Waiting for table metadata lock | alter table ti stats_auto_recalc=1 |         0 |             0 ||  4 | root | localhost | test | Query   |    0 | starting                        | show processlist                   |         0 |             0 |
+----+------+-----------+------+---------+------+---------------------------------+------------------------------------+-----------+---------------+
2 rows in set (0.00 sec)
So, one of metadata locks set by ALTER TABLE even in this very simple case (when we try to modify the way of statistics recalculation for InnoDB table) even in 5.7 is too strict, so that it conflicts with one of metadata locks set by simple SELECT for the duration of the entire transaction. What's more interesting, now in third session we can try to run whatever statements we want to check (those that were "compatible" with SELECT at metadata locks level) and see them also blocked, like this:
mysql> show processlist;
+----+------+-----------+------+---------+------+---------------------------------+------------------------------------+-----------+---------------+
| Id | User | Host      | db   | Command | Time | State                           | Info                               | Rows_sent | Rows_examined |
+----+------+-----------+------+---------+------+---------------------------------+------------------------------------+-----------+---------------+
|  3 | root | localhost | test | Query   |   52 | Waiting for table metadata lock | alter table ti stats_auto_recalc=1 |         0 |             0 |
|  4 | root | localhost | test | Query   |    0 | starting                        | show processlist                   |         0 |             0 |
|  5 | root | localhost | test | Query   |    4 | Waiting for table metadata lock | select * from ti                   |         0 |             0 |+----+------+-----------+------+---------+------+---------------------------------+------------------------------------+-----------+---------------+
3 rows in set (0.00 sec)
This way we can already conclude that ALTER TABLE of this kind does block even SELECT from the table! Good that it runs fast (there is no need to make a table copy), so will not block for a long time, but if ALTER itself is blocked by some transaction (like in my case) - we are still in trouble!

That simple test would give the answer to customer that he could be satisfied with. But I know that ALTER TABLE sets many metadata locks at different stages and while for this trivial one it may not matter much, I still wanted to find some time to attach gdb and see what locks are requested and when/in what order.

I had not done this with 5.7, so tried the same approach as in the initial post in this series (please, check it carefully for the details from the source code, like types of the locks etc), setting breakpoint on MDL_request::init, but it seems this method does NOT exist in MySQL 5.7. So, I ended up with the following breakpoints set:
(gdb) b MDL_context::acquire_lockBreakpoint 1 at 0xbf9083: file /usr/src/debug/percona-server-5.7.10-3/percona-server-5.7.10-3/sql/mdl.cc, line 3506.
(gdb) b lock_rec_lock
Breakpoint 2 at 0xf24ffd: file /usr/src/debug/percona-server-5.7.10-3/percona-server-5.7.10-3/storage/innobase/lock/lock0lock.cc, line 2343.
(gdb) c
Continuing.
With these breakpoints in place, I've executed alter table ti stats_auto_recalc=1; and ended up with the following in gdb:
Breakpoint 1, MDL_context::acquire_lock (this=0x7fc1e27ec0c8,
    mdl_request=0x7fc1b2df95b0, lock_wait_timeout=31536000)
    at /usr/src/debug/percona-server-5.7.10-3/percona-server-5.7.10-3/sql/mdl.cc:3506
3506      set_timespec(&abs_timeout, lock_wait_timeout);
(gdb) p mdl_request
$1 = (MDL_request *) 0x7fc1b2df95b0
(gdb) p *mdl_request
$2 = {type = MDL_INTENTION_EXCLUSIVE, duration = MDL_STATEMENT,
  next_in_list = 0x7fc1e27f7f70, prev_in_list = 0x7fc1b2df9778, ticket = 0x0,
  key = {m_length = 3, m_db_name_length = 0,
    m_ptr = "\000\000\000\000L\274q\324\301...
We see something not that much useful immediately, some metadata lock request at the weakest possible level for the duration of the statement. Just to see how we can see all of requests in the same "batch", let's do the following in gdb:
(gdb) p *(mdl_request->next_in_list)
$5 = {type = MDL_INTENTION_EXCLUSIVE, duration = MDL_TRANSACTION,
  next_in_list = 0x7fc1e27f7d70, prev_in_list = 0x7fc1b2df95b8, ticket = 0x0,
  key = {m_length = 7, m_db_name_length = 4,
    m_ptr = "\002test", '\000' <repeats 175 times>"\377, \377\377\037\000\000\000\000\001", '\000' <repeats 15 times>, "\001", '\000' <repeats 31 times>, "\a", '\000' <repeats 15 times>, "\004\000\000\000\000\000\000\000\002\000\000\000\000\000\000\000\001\001\000\001", '\000' <repeats 44 times>, "\001", '\000' <repeats 69 times>, static m_namespace_to_wait_state_name = {{m_key = 111,
...
So, the next one is also the weakest, but is set for the duration of transaction at the database (named "test") level. We can see the next after it as well:
(gdb) p *(mdl_request->next_in_list->next_in_list)
$6 = {type = MDL_SHARED_UPGRADABLE, duration = MDL_TRANSACTION,
  next_in_list = 0x0, prev_in_list = 0x7fc1e27f7f78, ticket = 0x0, key = {
    m_length = 9, m_db_name_length = 4,
    m_ptr = "\003test\000ti", '\000' <repeats 378 times>,
    static m_namespace_to_wait_state_name = {{m_key = 111,
...

(gdb) c
Continuing.
Here we can see a stronger metadata lock request for the duration of transaction on the test.ti table. The next_in_list pointer is NULL, so we are done with this "batch" of requests. If we continue, we'll see the same locks requested one by one while hitting breakpoint:
Breakpoint 1, MDL_context::acquire_lock (this=0x7fc1e27ec0c8,
    mdl_request=0x7fc1e27f7f70, lock_wait_timeout=31536000)
    at /usr/src/debug/percona-server-5.7.10-3/percona-server-5.7.10-3/sql/mdl.cc:3506
3506      set_timespec(&abs_timeout, lock_wait_timeout);

(gdb) p *mdl_request
$7 = {type = MDL_INTENTION_EXCLUSIVE, duration = MDL_TRANSACTION,
  next_in_list = 0x7fc1e27f7d70, prev_in_list = 0x7fc1b2df95b8, ticket = 0x0,
  key = {m_length = 7, m_db_name_length = 4,
    m_ptr = "\002test", '\000' <repeats 175 times>"\377, \377\377\037\000\000\000\000\001", '\000' <repeats 15 times>, "\001", '\000' <repeats 31 times>, "\a",
...

(gdb) p *(mdl_request->next_in_list)
$8 = {type = MDL_SHARED_UPGRADABLE, duration = MDL_TRANSACTION,
  next_in_list = 0x0, prev_in_list = 0x7fc1e27f7f78, ticket = 0x0, key = {
    m_length = 9, m_db_name_length = 4,
    m_ptr = "\003test\000ti", '\000' <repeats 378 times>,
    static m_namespace_to_wait_state_name = {{m_key = 111,
...

(gdb) c
Continuing.

Breakpoint 1, MDL_context::acquire_lock (this=0x7fc1e27ec0c8,
    mdl_request=0x7fc1e27f7d70, lock_wait_timeout=31536000)
    at /usr/src/debug/percona-server-5.7.10-3/percona-server-5.7.10-3/sql/mdl.cc:3506
3506      set_timespec(&abs_timeout, lock_wait_timeout);

(gdb) p *mdl_request
$9 = {type = MDL_SHARED_UPGRADABLE, duration = MDL_TRANSACTION,
  next_in_list = 0x0, prev_in_list = 0x7fc1e27f7f78, ticket = 0x0, key = {
    m_length = 9, m_db_name_length = 4,
    m_ptr = "\003test\000ti", '\000' <repeats 378 times>,
    static m_namespace_to_wait_state_name = {{m_key = 111,
...

(gdb) c
Continuing.
So, we have two ways to study the locks and new one in this post is to check the list of the lock requests in a single mdl_request list of pointers.

Eventually we also get the following breakpoint hit:
(gdb) c
Continuing.

Breakpoint 1, MDL_context::acquire_lock (this=0x7fc1e27ec0c8,
    mdl_request=0x7fc1b2df98a0, lock_wait_timeout=31536000)
    at /usr/src/debug/percona-server-5.7.10-3/percona-server-5.7.10-3/sql/mdl.cc:3506
3506      set_timespec(&abs_timeout, lock_wait_timeout);
(gdb) p *mdl_request
$15 = {type = MDL_EXCLUSIVE, duration = MDL_TRANSACTION,
  next_in_list = 0xd044f7, prev_in_list = 0x7fc1b2dfa914, ticket = 0x0, key = {
    m_length = 9, m_db_name_length = 4,
    m_ptr = "\003test\000ti\000_3\000\001\000\000\000\003\000\000\000<\240V\001\
...
Now, this is a real problem for any concurrent activity with the table, it's the strongest MDL_EXCLUSIVE lock requested on test.ti table that is hold (when obtained) till the end of ALTER statement...

To summarize this post:
  1. We see that we still can use gdb to study metadata locks in MySQL (or Percona Server) 5.7.x, but the breakpoint should be set on MDL_context::acquire_lock (at least until I'll find time to study changes in the source code for any alternatives).
  2. When breakpoint is hit you can study the content of structure pointed by the mdl_request parameter to find out what kind of lock is requested.
  3. At table level ALTER TABLE ... STATS_AUTO_RECALC=1 in MySQL 5.7 starts with weak enough MDL_SHARED_UPGRADABLE lock, but then eventually we end up with the request for the strongest MDL_EXCLUSIVE lock that is either blocked by any concurrent access to the table until transaction there is completed, and/or blocks everything (including SELECT from the table) until ALTER finally completes.
Next time in this series I plan to check what happens with metadata locks for partitioned tables and how much maintenance of individual partitions may affect other partitions. Other idea would be to check what metadata locks are set when statistics for the InnoDB table is recalculated automatically when 10% of data changes. Stay tuned!

Friday, February 26, 2016

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-24
0 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.

Thursday, February 25, 2016

On Upgrades to Percona Server 5.7 GA: Building from Git Source on Fedora

While writing my first post in this series I was bulding Percona Server 5.7 from Git sources on my wife's Fedora Core 23 box based on this manual page, for my collection of test instances. As I build MySQL and related software from sources on different platforms almost every day for 11 years already, the process was completed successfully without much troubles, but there are still some details I'd like to highlight.

Manual says one has to do the following:
$ git clone https://github.com/percona/percona-server.git
$ cd percona-server
$ git checkout 5.7
$ git submodule init
$ git submodule update
I had source code clonned already for regular 5.6.x builds, so I did this instead:
[openxs@fc23 percona-server]$ git pull
Already up-to-date.
[openxs@fc23 percona-server]$ git checkout 5.7
Checking out files: 100% (20321/20321), done.
M       plugin/tokudb-backup-plugin/Percona-TokuBackup
M       storage/tokudb/PerconaFT
Branch 5.7 set up to track remote branch 5.7 from origin.
Switched to a new branch '5.7'
[openxs@fc23 percona-server]$ git submodule init
[openxs@fc23 percona-server]$ git submodule update
On this FC 23 box I've already built MySQL 5.7, MariaDB 10.1, Facebook's MySQL 5.6 and Percona Server 5.6 from Git sources more than once, and I've updated all packages today and even built Facebook's 5.6 code (for testing MyRocks further) after that successfully, so I expect everything should work really well.

I put my test instances into dbs subdirectory usually:
[openxs@fc23 percona-server]$ ls ~/dbs
5.7  fb56  maria10.1  p5.6
So, I've modified cmake command line a bit comparing to the one recommended in the manual:
cmake . -DCMAKE_BUILD_TYPE=RelWithDebInfo -DBUILD_CONFIG=mysql_release -DFEATURE_SET=community -DWITH_EMBEDDED_SERVER=OFF
as you can see below:
[openxs@fc23 percona-server]$ cmake . -DCMAKE_BUILD_TYPE=RelWithDebInfo -DBUILD_CONFIG=mysql_release -DFEATURE_SET=community -DWITH_EMBEDDED_SERVER=OFF -DCMAKE_INSTALL_PREFIX=/home/openxs/dbs/p5.7
-- Running cmake version 3.4.1
-- Configuring with MAX_INDEXES = 64
-- Looking for SHM_HUGETLB
-- Looking for SHM_HUGETLB - found
-- SIZEOF_VOIDP 8
-- MySQL 5.7.10-3
-- Packaging as: percona-server-5.7.10-3-Linux-x86_64
-- Looked for boost/version.hpp in  and
-- BOOST_INCLUDE_DIR BOOST_INCLUDE_DIR-NOTFOUND
-- LOCAL_BOOST_DIR
-- LOCAL_BOOST_ZIP
-- Could not find (the correct version of) boost.
-- MySQL currently requires boost_1_59_0

CMake Error at cmake/boost.cmake:76 (MESSAGE):
  You can download it with -DDOWNLOAD_BOOST=1 -DWITH_BOOST=<directory>


  This CMake script will look for boost in <directory>.  If it is not there,
  it will download and unpack it (in that directory) for you.

  If you are inside a firewall, you may need to use an http proxy:

  export http_proxy=http://example.com:80

Call Stack (most recent call first):
  cmake/boost.cmake:228 (COULD_NOT_FIND_BOOST)
  CMakeLists.txt:439 (INCLUDE)


-- Configuring incomplete, errors occurred!
See also "/home/openxs/git/percona-server/CMakeFiles/CMakeOutput.log".
See also "/home/openxs/git/percona-server/CMakeFiles/CMakeError.log".
As you can see, manual obviously misses some details, like adding options related to Boost library. We see nice hints on what to do, and I've already downloaded Boost once into ~/boost while building MySQL 5.7. If Percona Server 5.7 was based on 5.7.11, we could use -DWITH_BOOST=system, see the release notes:
"A value of system is now permitted for the WITH_BOOST CMake option. If this option is not set or is set to system, it is assumed that the correct version of Boost is installed on the compilation host in the standard location. In this case, the installed version of Boost is used rather than any version included with a MySQL source distribution. (Bug #22224313)"
But it's based on 5.7.10, so I've modified my command like as follows:
[openxs@fc23 percona-server]$ cmake . -DCMAKE_BUILD_TYPE=RelWithDebInfo -DBUILD_CONFIG=mysql_release -DFEATURE_SET=community -DWITH_EMBEDDED_SERVER=OFF -DDOWNLOAD_BOOST=1 -DWITH_BOOST=/home/openxs/boost -DCMAKE_INSTALL_PREFIX=/home/openxs/dbs/p5.7
...
-- Check if the system is big endian - little endian
CMake Error at /usr/share/cmake/Modules/FindPackageHandleStandardArgs.cmake:148 (message):
  Could NOT find ZLIB (missing: ZLIB_INCLUDE_DIR)
Call Stack (most recent call first):
  /usr/share/cmake/Modules/FindPackageHandleStandardArgs.cmake:388 (_FPHSA_FAILURE_MESSAGE)
  /usr/share/cmake/Modules/FindZLIB.cmake:124 (FIND_PACKAGE_HANDLE_STANDARD_ARGS)
  libbinlogstandalone/CMakeLists.txt:30 (FIND_PACKAGE)


-- Configuring incomplete, errors occurred!
See also "/home/openxs/git/percona-server/CMakeFiles/CMakeOutput.log".
See also "/home/openxs/git/percona-server/CMakeFiles/CMakeError.log".
to end up with another error. I was surprised a bit, as I do have zlib-devel installed:
[openxs@fc23 percona-server]$ rpm -qa | grep zlib
zlib-1.2.8-9.fc23.x86_64
zlib-devel-1.2.8-9.fc23.x86_64
To not waste time on zlib now I've used the "trick" from Facebook's wiki this time:
[openxs@fc23 percona-server]$ cmake . -DCMAKE_BUILD_TYPE=RelWithDebInfo -DBUILD_CONFIG=mysql_release -DFEATURE_SET=community -DWITH_EMBEDDED_SERVER=OFF -DDOWNLOAD_BOOST=1 -DWITH_BOOST=/home/openxs/boost -DWITH_ZLIB=bundled -DCMAKE_INSTALL_PREFIX=/home/openxs/dbs/p5.7
...
-- MySQL 5.7.10-3
-- Packaging as: percona-server-5.7.10-3-Linux-x86_64
-- Local boost dir /home/openxs/boost/boost_1_59_0
-- Local boost zip /home/openxs/boost/boost_1_59_0.tar.gz

...
-- Library perconaserverclient depends on OSLIBS -lpthread;m;rt;/usr/lib64/libssl.so;/usr/lib64/libcrypto.so;dl
-- Performing Test HAVE_NO_UNUSED_TYPEDEFS
-- Performing Test HAVE_NO_UNUSED_TYPEDEFS - Success
-- Performing Test HAVE_NO_LOGICAL_OP
-- Performing Test HAVE_NO_LOGICAL_OP - Success
-- INSTALL perconaserverclient.pc lib/pkgconfig
-- CMAKE_BUILD_TYPE: RelWithDebInfo
-- COMPILE_DEFINITIONS: _GNU_SOURCE;_FILE_OFFSET_BITS=64;HAVE_CONFIG_H
-- CMAKE_C_FLAGS:  -Wall -Wextra -Wformat-security -Wvla -Wwrite-strings -Wdeclaration-after-statement
-- CMAKE_CXX_FLAGS:  -Wall -Wextra -Wformat-security -Wvla -Woverloaded-virtual -Wno-unused-parameter
-- CMAKE_C_FLAGS_RELWITHDEBINFO: -O3 -D_FORTIFY_SOURCE=2 -g -fabi-version=2 -fno-omit-frame-pointer -fno-strict-aliasing -DDBUG_OFF
-- CMAKE_CXX_FLAGS_RELWITHDEBINFO: -O3 -D_FORTIFY_SOURCE=2 -g -fabi-version=2 -fno-omit-frame-pointer -fno-strict-aliasing -DDBUG_OFF
-- Configuring done
CMake Warning (dev) at storage/tokudb/PerconaFT/portability/CMakeLists.txt:22 (add_dependencies):
  Policy CMP0046 is not set: Error on non-existent dependency in
  add_dependencies.  Run "cmake --help-policy CMP0046" for policy details.
  Use the cmake_policy command to set the policy and suppress this warning.

  The dependency target "build_jemalloc" of target
  "tokuportability_static_conv" does not exist.
This warning is for project developers.  Use -Wno-dev to suppress it.

-- Generating done
-- Build files have been written to: /home/openxs/git/percona-server
to end up with cmake command completed sucessfully. Now, time to make it (my FC23 box I've assembled myself back in 2011 has single QuadCore CPU):
[openxs@fc23 percona-server]$ time make -j 4
...
[100%] Built target mysqld
Scanning dependencies of target udf_example
[100%] Building CXX object sql/CMakeFiles/udf_example.dir/udf_example.cc.o
[100%] Built target pfs_connect_attr-t
[100%] Linking CXX shared module udf_example.so
[100%] Built target udf_example

real    10m40.254s
user    36m18.994s
sys     3m9.644s

[openxs@fc23 percona-server]$ make install && make clean
...
-- Installing: /home/openxs/dbs/p5.7/share/aclocal/mysql.m4
-- Installing: /home/openxs/dbs/p5.7/support-files/mysql.server
It took reasonable time to build and, some warnings aside, it worked well.

Percona manual says nothing on what to do next, but if it's a new test instance we need to initialize the database directory for it. Let's do it new way (mysql_install_db is NOT only in the bin subdirectory now in 5.7 instead of scripts, but is deprecated now):
[openxs@fc23 percona-server]$ cd ~/dbs/p5.7
[openxs@fc23 p5.7]$ bin/mysqld --initialize-insecure
2016-02-25T10:14:19.910929Z 0 [Warning] Changed limits: max_open_files: 1024 (requested 5000)
2016-02-25T10:14:19.911033Z 0 [Warning] Changed limits: table_open_cache: 431 (requested 2000)
2016-02-25T10:14:19.911197Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2016-02-25T10:14:19.927376Z 0 [ERROR] --initialize specified but the data directory has files in it. Aborting.
2016-02-25T10:14:19.927448Z 0 [ERROR] Aborting
 At this moment I've suspected that I probably have my.cnf or .my.cnf somewhere pointing to some other datadir that can be non-empty, so I tried to add --no-defaults, as usual:
 [openxs@fc23 p5.7]$ bin/mysqld --no-defaults --initialize-insecure
2016-02-25T10:14:51.318896Z 0 [Warning] Changed limits: max_open_files: 1024 (requested 5000)
2016-02-25T10:14:51.319006Z 0 [Warning] Changed limits: table_open_cache: 431 (requested 2000)
2016-02-25T10:14:51.319199Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2016-02-25T10:14:51.321754Z 0 [ERROR] --initialize specified but the data directory has files in it. Aborting.
2016-02-25T10:14:51.321823Z 0 [ERROR] Aborting

[openxs@fc23 p5.7]$ ls -l data
ls: cannot access data: No such file or directory
[openxs@fc23 p5.7]$ ls -l
total 128
drwxrwxr-x.  2 openxs openxs  4096 Feb 25 12:06 bin
-rw-r--r--.  1 openxs openxs 17987 Feb  9 09:44 COPYING
-rw-r--r--.  1 openxs openxs 34520 Feb  9 13:04 COPYING.AGPLv3
-rw-r--r--.  1 openxs openxs 17987 Feb  9 13:04 COPYING.GPLv2
drwxr-xr-x.  2 openxs openxs  4096 Feb 25 12:06 docs
drwxrwxr-x.  3 openxs openxs  4096 Feb 25 12:06 include
-rw-r--r--.  1 openxs openxs   301 Feb 25 11:44 INSTALL-BINARY
drwxrwxr-x.  4 openxs openxs  4096 Feb 25 12:06 lib
drwxrwxr-x.  4 openxs openxs  4096 Feb 25 12:06 man
drwxrwxr-x. 10 openxs openxs  4096 Feb 25 12:07 mysql-test
-rw-r--r--.  1 openxs openxs  2211 Feb  9 13:04 PATENTS
-rw-r--r--.  1 openxs openxs  4442 Feb  9 13:04 README.md
-rw-r--r--.  1 openxs openxs  2478 Feb 25 11:44 README.MySQL
drwxrwxr-x. 28 openxs openxs  4096 Feb 25 12:07 share
drwxrwxr-x.  2 openxs openxs  4096 Feb 25 12:07 support-files

[openxs@fc23 p5.7]$ pwd
/home/openxs/dbs/p5.7
 Usually for the Git source by default the datadir is supposed to be in the basedir (current directory in my case). But surely I can specify it explicitly (and I should, see Bug #73845 and the manual):
[openxs@fc23 p5.7]$ bin/mysqld --no-defaults --datadir=/home/openxs/dbs/p5.7/data --initialize-insecure
2016-02-25T10:19:37.946463Z 0 [Warning] Changed limits: max_open_files: 1024 (requested 5000)
2016-02-25T10:19:37.946558Z 0 [Warning] Changed limits: table_open_cache: 431 (requested 2000)
2016-02-25T10:19:37.946723Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2016-02-25T10:19:39.784840Z 0 [Warning] InnoDB: New log files created, LSN=45790
2016-02-25T10:19:40.201625Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2016-02-25T10:19:40.513883Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 47cb6f30-dba9-11e5-9c78-485b3952834f.
2016-02-25T10:19:40.546366Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2016-02-25T10:19:41.073896Z 0 [Warning] CA certificate ca.pem is self signed.
2016-02-25T10:19:41.225334Z 1 [Warning] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.
[openxs@fc23 p5.7]$
It seems everything worked well with --datadir specified explicitly, so we can try to use this new instance:
[openxs@fc23 p5.7]$ ps aux | grep mysqld
openxs   12123  0.0  0.0 116996  2316 pts/1    S+   12:21   0:00 grep --color=auto mysqld

[openxs@fc23 p5.7]$ bin/mysqld_safe --no-defaults &
[1] 12125
[openxs@fc23 p5.7]$ 160225 12:25:06 mysqld_safe Logging to '/home/openxs/dbs/p5.7/data/fc23.err'.
160225 12:25:06 mysqld_safe Starting mysqld daemon with databases from /home/openxs/dbs/p5.7/data

[openxs@fc23 p5.7]$ bin/mysql -uroot test
ERROR 1049 (42000): Unknown database 'test'
[openxs@fc23 p5.7]$ bin/mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.10-3 MySQL Community Server (GPL)

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> show variables like '%version%';
+-------------------------+------------------------------+
| Variable_name           | Value                        |
+-------------------------+------------------------------+
| innodb_version          | 5.7.10-3                     |
| protocol_version        | 10                           |
| slave_type_conversions  |                              |
| tls_version             | TLSv1,TLSv1.1,TLSv1.2        |
| version                 | 5.7.10-3                     |
| version_comment         | MySQL Community Server (GPL) |
| version_compile_machine | x86_64                       |
| version_compile_os      | Linux                        |
+-------------------------+------------------------------+
8 rows in set (0.00 sec)
That's it. MySQL 5.7.x (and thus Percona Server 5.7) is "secure by default", and in frames of that effort test database is not created.

As a last step, let's check the size of the mysqld binary:
[openxs@fc23 p5.7]$ ls -l bin/mysqld
-rwxr-xr-x. 1 openxs openxs 211932312 Feb 25 12:05 bin/mysqld
[openxs@fc23 p5.7]$ file bin/mysqld
bin/mysqld: ELF 64-bit LSB executable, x86-64, version 1 (GNU/Linux), dynamically linked, interpreter /lib64/ld-linux-x86-64.so.2, for GNU/Linux 2.6.32, BuildID[sha1]=f0ba086eb3639e927529cfdc4699c0ac3e748319, not stripped
 I'll try to build on Ubuntu 14.04 later tonight. I do not expect any problems other than zlib related and --datadir required for mysql_install_db or mysqld --initialize, but who knows...

On Upgrades to Percona Server 5.7 GA: CentOS 6 Case

As you probably noted, Percona had finally released Percona Server 5.7 GA two days ago. I had not paid much attention to this branch during last month or so, but as it's GA now I decided to check what's there, how it works and install/upgrade different boxes and VMs to use it.

For testing purposes and for this series of blog posts I am going to build it from Git source (at least on Fedora Core 23), upgrade to it on 32-bit Ubuntu 15.04 VM, upgrade to it on 64-bit Ubuntu 14.04 laptop, upgrade to it on 64-bit CentOS 6.7 VM and maybe more. If any of the attempts to install/upgrade ends up with something worth sharing, I am going to write about the experience, problems (if any) and solutions.

Yesterday I've spent some time upgrading on Ubuntu 15.04 VM and some observations are discussed on Facebook already. Then I tried to upgrade on my main CentOS 6.7 VM, where I had a "full stack" of Percona software for Percona Server 5.6.28, including TokuDB. The idea was to make sure everything I need works as expected after the upgrade. In this post I am going to share the observations from that experince.

Smart user should definitely start with reading the manual, in this case - "Percona Server In-Place Upgrading Guide: From 5.6 to 5.7"? as this is what I planned to do, in-place upgrade. Reading and following the directions there can save you notable time, but I am goign to show what happens when one tries naive "trial and failure" approach.

So, without reading anything, I just logged in as root, run yum update and then checked what Percona software I had there:
[root@centos ~]# yum update
...
No Packages marked for Update
[root@centos ~]# rpm -qa | grep -i percona
Percona-Server-shared-56-5.6.28-rel76.1.el6.x86_64
Percona-Server-test-56-5.6.28-rel76.1.el6.x86_64
Percona-Server-tokudb-56-5.6.28-rel76.1.el6.x86_64
Percona-Server-56-debuginfo-5.6.28-rel76.1.el6.x86_64
percona-release-0.1-3.noarch
Percona-Server-shared-51-5.1.73-rel14.12.625.rhel6.x86_64
Percona-Server-server-56-5.6.28-rel76.1.el6.x86_64
percona-nagios-plugins-1.1.6-1.noarch
percona-toolkit-2.2.16-1.noarch
percona-xtrabackup-2.3.3-1.el6.x86_64
Percona-Server-client-56-5.6.28-rel76.1.el6.x86_64
Percona-Server-devel-56-5.6.28-rel76.1.el6.x86_64
Great, so automatic upgrade to 5.7 GA doesd NOT happen. First thing to take into account for naive user like me. Manual mentioned above wisely suggests, as a next step, to stop MySQL server and then remove all Percona Server software without dependecies:
$ rpm -qa | grep Percona-Server | xargs rpm -e --nodeps
But I do not read the manuals (as many users) and previously upgrades worked for me without removing everything, so I just proceeded in a straighforward manner:
[root@centos ~]# yum install Percona-Server-server-57
...
--> Finished Dependency Resolution
Error: Percona-Server-client-57 conflicts with Percona-Server-client-56-5.6.28-rel76.1.el6.x86_64
Error: Percona-Server-server-57 conflicts with Percona-Server-server-56-5.6.28-rel76.1.el6.x86_64
Error: Percona-Server-shared-51 conflicts with Percona-Server-shared-57-5.7.10-3.1.el6.x86_64
 You could try using --skip-broken to work around the problem
** Found 1 pre-existing rpmdb problem(s), 'yum check' output follows:
Percona-Server-shared-51-5.1.73-rel14.12.625.rhel6.x86_64 has installed conflicts mysql-libs: Percona-Server-shared-51-5.1.73-rel14.12.625.rhel6.x86_64
So, removing seems unavoidable and I proceeded step by step (surely I had not cared about dependencies, who do, yum will tell us anyway or fix it for us, right?):
[root@centos ~]# yum remove Percona-Server-server-56
...
Removed:
  Percona-Server-server-56.x86_64 0:5.6.28-rel76.1.el6

Dependency Removed:
  Percona-Server-tokudb-56.x86_64 0:5.6.28-rel76.1.el6

Complete!
Let's remember that we need to install TokuDB for 5.7 separately, and proceed:
[root@centos ~]# yum remove Percona-Server-client-56 Percona-Server-shared-51
...
Transaction Summary
================================================================================
Remove        8 Package(s)

Installed size: 369 M
...
Removed:
  Percona-Server-client-56.x86_64 0:5.6.28-rel76.1.el6
  Percona-Server-shared-51.x86_64 0:5.1.73-rel14.12.625.rhel6

Dependency Removed:
  Percona-Server-test-56.x86_64 0:5.6.28-rel76.1.el6
  percona-toolkit.noarch 0:2.2.16-1
  percona-xtrabackup.x86_64 0:2.3.3-1.el6
  perl-DBD-MySQL.x86_64 0:4.013-3.el6
  php-mysql.x86_64 0:5.3.3-46.el6_7.1
  sysbench.x86_64 0:0.5-6.el6

Complete!
As we had not cared much about dependencies, we've got a lot of software removed that depended on old shared libraries. Let's save the list to install it back later. Now, we can try to install Percona Server 5.7 again:
[root@centos ~]# yum install Percona-Server-server-57
...
Dependencies Resolved

================================================================================
 Package                  Arch   Version           Repository              Size
================================================================================
Installing:
 Percona-Server-server-57 x86_64 5.7.10-3.1.el6    percona-release-x86_64  40 M
Installing for dependencies:
 Percona-Server-client-57 x86_64 5.7.10-3.1.el6    percona-release-x86_64 8.7 M
 Percona-Server-shared-57 x86_64 5.7.10-3.1.el6    percona-release-x86_64 902 k

Transaction Summary
================================================================================
Install       3 Package(s)
...

Transaction Check Error:
  file /usr/bin/mysql_config from install of Percona-Server-client-57-5.7.10-3.1.el6.x86_64 conflicts with file from package Percona-Server-devel-56-5.6.28-rel76.1.el6.x86_64

Error Summary
-------------
Great, so we also have a conflict with Percona-Server-devel-56 package that yum had not informed us about initially (Percona manual was probably right, remove them all, Percona-Srerver-* pacvkages first!). Let's remember to install it back for 5.7, remove it and install Percona Server 5.7 finally:
[root@centos ~]# yum remove Percona-Server-devel-56
...
Removed:
  Percona-Server-devel-56.x86_64 0:5.6.28-rel76.1.el6

Complete!
[root@centos ~]# yum install Percona-Server-server-57
...
Dependencies Resolved

================================================================================
 Package                  Arch   Version           Repository              Size
================================================================================
Installing:
 Percona-Server-server-57 x86_64 5.7.10-3.1.el6    percona-release-x86_64  40 M
Installing for dependencies:
 Percona-Server-client-57 x86_64 5.7.10-3.1.el6    percona-release-x86_64 8.7 M
 Percona-Server-shared-57 x86_64 5.7.10-3.1.el6    percona-release-x86_64 902 k

Transaction Summary
================================================================================
Install       3 Package(s)
...
Installed:
  Percona-Server-server-57.x86_64 0:5.7.10-3.1.el6

Dependency Installed:
  Percona-Server-client-57.x86_64 0:5.7.10-3.1.el6
  Percona-Server-shared-57.x86_64 0:5.7.10-3.1.el6

Complete!
Now, let's try to use it:
[root@centos ~]# mysql -uroot test
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)
[root@centos ~]# ps aux | grep mysql
root      2665  0.0  0.0 103304   884 pts/0    S+   21:09   0:00 grep mysql
So, it is NOT started, as we had NOT upgraded it, we removed Percona Server 5.6.x first! Let's start the server now (at this step on recent Ubuntu etc you may find yourself in troubles):
[root@centos ~]# service mysql start
Starting mysqld:                                           [  OK  ]
[root@centos ~]# mysql -uroot  test
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-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>
So, I've got it up and running. Now let me install some software back to replace those older packages I've removed:
[root@centos ~]# yum install Percona-Server-devel-57 Percona-Server-test-57 Percona-Server-tokudb-57
...

Installed:
  Percona-Server-devel-57.x86_64 0:5.7.10-3.1.el6
  Percona-Server-test-57.x86_64 0:5.7.10-3.1.el6
  Percona-Server-tokudb-57.x86_64 0:5.7.10-3.1.el6

Complete!
So, we have missing Percona Server packages for 5.7, now let me try to install dependecies back:
[root@centos ~]# yum install percona-toolkit percona-xtrabackup perl-DBD-MySQL php-mysql sysbench
...
Installed:
  percona-toolkit.noarch 0:2.2.16-1    percona-xtrabackup.x86_64 0:2.3.3-1.el6
  perl-DBD-MySQL.x86_64 0:4.013-3.el6  php-mysql.x86_64 0:5.3.3-46.el6_7.1
  sysbench.x86_64 0:0.5-6.el6

Dependency Installed:
  Percona-Server-shared-51.x86_64 0:5.1.73-rel14.12.625.rhel6

Complete!
Works well! Now, let me check if "full stack" works as expected. I had xtrabackup and toolkit installed, so do we have xtrabackup capable to work with 5.7 GA as result? Let's check:
[root@centos ~]# xtrabackup --version
xtrabackup version 2.3.3 based on MySQL server 5.6.24 Linux (x86_64) (revision id: 525ca7d)
[root@centos ~]# xtrabackup --backup
160224 21:28:08  version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;mysql_socket=/var/lib/mysql/mysql.sock' as 'mysql'  (using password: NO).
160224 21:28:08  version_check Connected to MySQL server
160224 21:28:08  version_check Executing a version check against the server...
DBD::mysql::db selectall_hashref failed: Table 'performance_schema.session_variables' doesn't exist at - line 1221.
160224 21:28:10  version_check Done.
160224 21:28:10 Connecting to MySQL server host: localhost, user: mysql, password: not set, port: 0, socket: /var/lib/mysql/mysql.sock
Error: failed to execute query SHOW VARIABLES: Table 'performance_schema.session_variables' doesn't exist
Well, I was not sure (let's assume) if xtrabackup 2.3.3 is supposed to work with 5.7 (it was released recently, no upgrade happened, why not?), so I tried to use it. But it does not work as we do not have that performance_schema.session_variables table. Surely it's not there, it does not exist in MySQL 5.6. We missed important step that manual clearly mentiones, running mysql_upgrade and restart of service:
[root@centos ~]# mysql_upgrade
Checking if update is needed.
Checking server version.
Running queries to upgrade MySQL server.
Checking system database.
mysql.columns_priv                                 OK
...
Upgrade process completed successfully.
Checking if update is needed.
[root@centos ~]# service mysql restart
Stopping mysqld:                                           [  OK  ]
Starting mysqld:                                           [  OK  ]
Let's try again now:
[root@centos ~]# xtrabackup --backup
160224 21:31:00  version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;mysql_socket=/var/lib/mysql/mysql.sock' as 'mysql'  (using password: NO).
160224 21:31:00  version_check Connected to MySQL server
160224 21:31:00  version_check Executing a version check against the server...
160224 21:31:00  version_check Done.
160224 21:31:00 Connecting to MySQL server host: localhost, user: mysql, password: not set, port: 0, socket: /var/lib/mysql/mysql.sock
Error: Unsupported server version: '5.7.10-3-log'. Please report a bug at https://bugs.launchpad.net/percona-xtrabackup
No luck. Indeed, as anyone can find out after some reading, only Percona Xtrabackup 2.4 (that was also released as GA on February 23) is supposed to work with Percona Server or MySQL 5.7. We have to install it explicitly as "it was decided" that Percona does not want it to be used by default, yet:
[root@centos ~]# yum install percona-xtrabackup-24
...
--> Processing Conflict: percona-xtrabackup-24-2.4.1-1.el6.x86_64 conflicts percona-xtrabackup
--> Finished Dependency Resolution
Error: percona-xtrabackup-24 conflicts with percona-xtrabackup-2.3.3-1.el6.x86_64
 You could try using --skip-broken to work around the problem
 You could try running: rpm -Va --nofiles --nodigest
[root@centos ~]# yum remove percona-xtrabackup
...
Removed:
  percona-xtrabackup.x86_64 0:2.3.3-1.el6

Complete!
[root@centos ~]# yum install percona-xtrabackup-24
...
Running Transaction
  Installing : percona-xtrabackup-24-2.4.1-1.el6.x86_64                     1/1
  Verifying  : percona-xtrabackup-24-2.4.1-1.el6.x86_64                     1/1

Installed:
  percona-xtrabackup-24.x86_64 0:2.4.1-1.el6

Complete!
So, one has to explicitly remove percona-xtrabackup package and then install percona-xtrabackup-24. Now we can finally backup our new Percona Server 5.7 online (after exp;licitly specifying root user for backup, I wonder why, and doing some cleanup from the previous failed attempt):
[root@centos ~]# xtrabackup --backup
160224 21:35:46  version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;mysql_socket=/var/lib/mysql/mysql.sock' as 'mysql'  (using password: NO).
160224 21:35:46  version_check Connected to MySQL server
160224 21:35:46  version_check Executing a version check against the server...
160224 21:35:46  version_check Done.
160224 21:35:46 Connecting to MySQL server host: localhost, user: mysql, password: not set, port: 0, socket: /var/lib/mysql/mysql.sock
Using server version 5.7.10-3-log
xtrabackup version 2.4.1 based on MySQL server 5.7.10 Linux (x86_64) (revision id: a2dc9d4)
xtrabackup: uses posix_fadvise().
...
160224 21:35:53 Executing LOCK TABLES FOR BACKUP...
Error: failed to execute query LOCK TABLES FOR BACKUP: Access denied; you need (at least one of) the RELOAD privilege(s) for this operation
[root@centos ~]# xtrabackup --user=root --backup
...
xtrabackup: Can't create/write to file '/root/xtrabackup_backupfiles/xtrabackup_logfile' (Errcode: 17 - File exists)
xtrabackup: error: failed to open the target stream for 'xtrabackup_logfile'.
[root@centos ~]# rm -rf xtrabackup_backupfiles/
[root@centos ~]# xtrabackup --user=root --backup
...
160224 21:37:40 Backup created in directory '/root/xtrabackup_backupfiles/'
160224 21:37:40 [00] Writing backup-my.cnf
160224 21:37:40 [00]        ...done
160224 21:37:40 [00] Writing xtrabackup_info
160224 21:37:40 [00]        ...done
xtrabackup: Transaction log of lsn (1018105090) to (1018105141) was copied.
160224 21:37:40 completed OK!
Now the last step for this post, let's try if magic tools from Percona Toolkit works with 5.7, at least pt-mysql-summary is something to test for sure:
[root@centos ~]# pt-mysql-summary
# Percona Toolkit MySQL Summary Report #######################
              System time | 2016-02-24 19:38:09 UTC (local TZ: EET +0200)
# Instances ##################################################
  Port  Data Directory             Nice OOM Socket
  ===== ========================== ==== === ======
        /var/lib/mysql             0    0   /var/lib/mysql/mysql.sock
# MySQL Executable ###########################################
       Path to executable | /usr/sbin/mysqld
              Has symbols | No
# Report On Port 3306 ########################################
                     User | root@localhost
                     Time | 2016-02-24 21:38:09 (EET)
                 Hostname | centos
                  Version | 5.7.10-3-log Percona Server (GPL), Release 3, Revision 63dafaf
                 Built On | Linux x86_64
                  Started | 2016-02-24 21:30 (up 0+00:07:22)
                Databases | 6
                  Datadir | /var/lib/mysql/
                Processes | 1 connected, 1 running
              Replication | Is not a slave, has 0 slaves connected
                  Pidfile | /var/run/mysqld/mysqld.pid (exists)
...
So, it works and recongnizes the version properly, but at this moment I've noted I miss debug symbols for 5.7 (and I'll need them as soon as I attach gdb to running server, something I do on a regular basis). So, I have to install them:
[root@centos ~]# yum install Percona-Server-57-debuginfo
...
  file /usr/lib/debug/usr/share/mysql-test/lib/My/SafeProcess/my_safe_process.debug from install of Percona-Server-57-debuginfo-5.7.10-3.1.el6.x86_64 conflicts with file from package Percona-Server-56-debuginfo-5.6.28-rel76.1.el6.x86_64

Error Summary
-------------
[root@centos ~]# yum remove Percona-Server-56-debuginfo
Removed:
  Percona-Server-56-debuginfo.x86_64 0:5.6.28-rel76.1.el6

Complete!
[root@centos ~]# yum install Percona-Server-57-debuginfo
...
Installed:
  Percona-Server-57-debuginfo.x86_64 0:5.7.10-3.1.el6

Complete!
[root@centos ~]# rpm -qa | grep -i percona
Percona-Server-shared-56-5.6.28-rel76.1.el6.x86_64
Percona-Server-shared-57-5.7.10-3.1.el6.x86_64
Percona-Server-test-57-5.7.10-3.1.el6.x86_64
percona-toolkit-2.2.16-1.noarch
percona-xtrabackup-24-2.4.1-1.el6.x86_64
Percona-Server-tokudb-57-5.7.10-3.1.el6.x86_64
percona-release-0.1-3.noarch
percona-nagios-plugins-1.1.6-1.noarch
Percona-Server-server-57-5.7.10-3.1.el6.x86_64
Percona-Server-shared-51-5.1.73-rel14.12.625.rhel6.x86_64
Percona-Server-client-57-5.7.10-3.1.el6.x86_64
Percona-Server-devel-57-5.7.10-3.1.el6.x86_64
Percona-Server-57-debuginfo-5.7.10-3.1.el6.x86_64
So, after dealing with one more conflict I seem to have a "full stack" of Percona tools that are ready to work with Percona Server 5.7 GA.

To summarize my experience of a naive user, one should note the following while upgrading to Percona Server 5.7 on CentOS 6.x:
  1. Read the manual about upgrade carefully and don't ignore it. You'll have to remove all Percona-Server-* packages first, deal with dependencies somehow and then install all Percona-Server-57-* packages you need.
  2. mysql_upgrade is NOT executed automatically upon installation when there is some older datadir from the previous version. You have to execute it explicitly, check the outputs and then restart mysql service before any further use.
  3. If you need to use xtrabackup with Percona Server 5.7, you have to remove percona-xtrabackup package and then install percona-xtrabackup-24 explicitly.
I hope this step by step guide will be useful to some of you. I'll try to upgrade "full stack" of Percona software on 64-bit Ubuntu 14.04 laptop tonight and carefully note all steps. I may end up with the next blog post in this series after that, depending on the results, so stay tuned!