Summary:
If you are using innodb_flush_method = O_DIRECT (which is highly recommended for a bunch of reasons) and innodb_track_changed_pages the instance will crash if you query any tables related to that feature.
So innodb_track_changed_pages is a Percona system variable which is used to make incremental backups much faster.
So if you are using Percona’s great backup and recovery software suite and running at least MySQL 5.5.27 you might have enabled this variable to make your incremental backups run faster.
You may want to reconsider your choice and turn it off … continue to read why…
Background:
I was working on a client a while back (12 months ago) and I was looking to improve their incremental backup durations.
When I turned on the feature and restarted the MySQL instance, as soon as I queried the table the MySQL instance crashed.
After a couple of tests, the change was hastily rolled back and I raised this bug ticket with Percona.
https://bugs.launchpad.net/percona-server/+bug/1538403
Testing, preparing and getting a bug raised took time, but apart from Rick Pizzi also posting that he had a similar crash, there has been no activity on this. It is like the crickets chirping in the Simpsons.
http://tvtropes.org/pmwiki/pmwiki.php/Main/ChirpingCrickets
Zero activity, zero response, the bug is Status is new and unassigned since Jan 27th 2016.
Yes this is an older version MySQL Percona 5.5, but it also affects versions up to 5.7.
How many installations are running between 5.5 and 5.7? I would have thought the bulk of the installed base.
Goal of this post:
Hopefully, this article will prompt someone to take a look at the bug.
If it seems like some of my recent blog posts have been raising issues with Percona, it is because I use, support and recommend Percona MySQL databases and tools every day even though I don’t work at Percona. I love that Percona provide the tools and software they do. Having a crash bug open for more than 12 months is disappointing.
The bug in action:
mysql> select @@version;
+-----------------+
| @@version |
+-----------------+
| 5.5.54-38.6-log | <== The latest 5.5 version
+-----------------+
1 row in set (0.00 sec)
mysql> show global variables like 'innodb%track%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| innodb_track_changed_pages | OFF |
+----------------------------+-------+
1 row in set (0.00 sec)
-- restart after adding variable to .cnf file
mysql> show global variables like 'innodb%track%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| innodb_track_changed_pages | ON |
+----------------------------+-------+
1 row in set (0.00 sec)
mysql> select count(*) from INFORMATION_SCHEMA.INNODB_CHANGED_PAGES;
+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row in set (0.00 sec)
-- it works because there is nothing in the bitmap file
root@db1:~# ls -l /var/lib/mysql/
total 28704
-rw-r--r-- 1 root root 0 Feb 18 03:01 debian-5.5.flag
-rw-rw---- 1 mysql mysql 18874368 Feb 18 03:06 ibdata1
-rw-rw---- 1 mysql mysql 5242880 Feb 18 03:06 ib_logfile0
-rw-rw---- 1 mysql mysql 5242880 Feb 18 03:01 ib_logfile1
-rw-rw---- 1 mysql mysql 0 Feb 18 03:06 ib_modified_log_1_0.xdb <=== BITMAP FILE
drwx------ 2 mysql root 4096 Feb 18 03:01 mysql
-rw-rw---- 1 mysql mysql 126 Feb 18 03:05 mysql-bin.000001
-rw-rw---- 1 mysql mysql 126 Feb 18 03:06 mysql-bin.000002
-rw-rw---- 1 mysql mysql 107 Feb 18 03:06 mysql-bin.000003
-rw-rw---- 1 mysql mysql 96 Feb 18 03:06 mysql-bin.index
-rw------- 1 root root 11 Feb 18 03:01 mysql_upgrade_info
drwx------ 2 mysql mysql 4096 Feb 18 03:01 performance_schema
drwx------ 2 mysql root 4096 Feb 18 03:01 test
-- nothing wrong at this point... now change some rows
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.00 sec)
mysql> use test;
Database changed
mysql> show tables;
Empty set (0.00 sec)
-- Don't use column names like this at home folks...
mysql> create table test (blah varchar(10));
Query OK, 0 rows affected (0.02 sec)
-- Fred the world renowned tester
mysql> insert into test values ('fred');
Query OK, 1 row affected (0.00 sec)
mysql> exit
Bye
root@db1:~# ls -l /var/lib/mysql/
total 28708
-rw-r--r-- 1 root root 0 Feb 18 03:01 debian-5.5.flag
-rw-rw---- 1 mysql mysql 18874368 Feb 18 03:08 ibdata1
-rw-rw---- 1 mysql mysql 5242880 Feb 18 03:08 ib_logfile0
-rw-rw---- 1 mysql mysql 5242880 Feb 18 03:01 ib_logfile1
-rw-rw---- 1 mysql mysql 4096 Feb 18 03:08 ib_modified_log_1_0.xdb <=== BITMAP FILE
drwx------ 2 mysql root 4096 Feb 18 03:01 mysql
-rw-rw---- 1 mysql mysql 126 Feb 18 03:05 mysql-bin.000001
-rw-rw---- 1 mysql mysql 126 Feb 18 03:06 mysql-bin.000002
-rw-rw---- 1 mysql mysql 396 Feb 18 03:08 mysql-bin.000003
-rw-rw---- 1 mysql mysql 96 Feb 18 03:06 mysql-bin.index
-rw------- 1 root root 11 Feb 18 03:01 mysql_upgrade_info
drwx------ 2 mysql mysql 4096 Feb 18 03:01 performance_schema
drwx------ 2 mysql root 4096 Feb 18 03:08 test
-- still works as innodb_flush_method isn't set
mysql> show global variables like 'innodb_flush_method';
+---------------------+-------+
| Variable_name | Value |
+---------------------+-------+
| innodb_flush_method | |
+---------------------+-------+
1 row in set (0.00 sec)
mysql> select count(*) from INFORMATION_SCHEMA.INNODB_CHANGED_PAGES;
+----------+
| count(*) |
+----------+
| 17 |
+----------+
1 row in set (0.01 sec)
-- OK now turn on O_DIRECT
root@db1:~# vi /etc/mysql/my.cnf
root@db1:~# service mysql restart
* Stopping MySQL (Percona Server) mysqld [ OK ]
* Starting MySQL (Percona Server) database server mysqld [ OK ]
* Checking for corrupt, not cleanly closed and upgrade needing tables.
mysql> show global variables like 'innodb_flush_method';
+---------------------+----------+
| Variable_name | Value |
+---------------------+----------+
| innodb_flush_method | O_DIRECT |
+---------------------+----------+
1 row in set (0.00 sec)
-- Bingo! Houston we have a problem.
mysql> select count(*) from INFORMATION_SCHEMA.INNODB_CHANGED_PAGES;
ERROR 2013 (HY000): Lost connection to MySQL server during query
DBA Dojo 