Release lock on table statistics after query plan created | Stop ANALYZE TABLE from flushing table definition cache

Experimental 5.6 fix (applies only to non-partitioned InnoDB tables) at https://github.com/laurynas-biveinis/percona-server/tree/bug1704195-5.6-trunk

Build fails for me with

[ 28%] Building CXX object storage/innobase/CMakeFiles/innobase.dir/api/api0api.cc.o
In file included from /home/sveta/src/percona-server-laurynas/storage/innobase/include/dict0dict.ic:30:0,
                 from /home/sveta/src/percona-server-laurynas/storage/innobase/include/dict0dict.h:1846,
                 from /home/sveta/src/percona-server-laurynas/storage/innobase/include/rem0rec.ic:28,
                 from /home/sveta/src/percona-server-laurynas/storage/innobase/include/rem0rec.h:985,
                 from /home/sveta/src/percona-server-laurynas/storage/innobase/include/btr0sea.h:31,
                 from /home/sveta/src/percona-server-laurynas/storage/innobase/include/que0que.h:32,
                 from /home/sveta/src/percona-server-laurynas/storage/innobase/include/api0misc.h:32,
                 from /home/sveta/src/percona-server-laurynas/storage/innobase/api/api0api.cc:38:
/home/sveta/src/percona-server-laurynas/storage/innobase/include/fsp0fsp.ic: In function ‘bool fsp_flags_is_valid(ulint)’:
/home/sveta/src/percona-server-laurynas/storage/innobase/include/fsp0fsp.h:44:39: error: left shift of negative value [-Werror=shift-negative-value]
 #define FSP_FLAGS_WIDTH_POST_ANTELOPE 1
                                       ^
/home/sveta/src/percona-server-laurynas/storage/innobase/include/fsp0fsp.h:86:13: note: in expansion of macro ‘FSP_FLAGS_WIDTH_POST_ANTELOPE’
   ((~(~0 << FSP_FLAGS_WIDTH_POST_ANTELOPE)) \
             ^~~~~~~~~~~~~~~~~~~~~~~~~~~~~
/home/sveta/src/percona-server-laurynas/storage/innobase/include/fsp0fsp.h:107:13: note: in expansion of macro ‘FSP_FLAGS_MASK_POST_ANTELOPE’
   ((flags & FSP_FLAGS_MASK_POST_ANTELOPE) \
             ^~~~~~~~~~~~~~~~~~~~~~~~~~~~
/home/sveta/src/percona-server-laurynas/storage/innobase/include/fsp0fsp.ic:61:24: note: in expansion of macro ‘FSP_FLAGS_GET_POST_ANTELOPE’
  ulint post_antelope = FSP_FLAGS_GET_POST_ANTELOPE(flags);
                        ^~~~~~~~~~~~~~~~~~~~~~~~~~~
/home/sveta/src/percona-server-laurynas/storage/innobase/include/fsp0fsp.h:46:35: error: left shift of negative value [-Werror=shift-negative-value]
 #define FSP_FLAGS_WIDTH_ZIP_SSIZE 4
                                   ^
...

Cmake options:

cmake /home/sveta/src/percona-server-laurynas -DCMAKE_INSTALL_PREFIX=$HOME/build/ps-5.7-george -DWITH_DEBUG=1 -DDOWNLOAD_BOOST=1 -DDOWNLOAD_BOOST_TIMEOUT=1800 -DWITH_BOOST=./boost -DENABLE_DTRACE=0 -DWITH_ROCKSDB=0 -DWITH_RAPID=0

$ gcc --version
gcc (Ubuntu/Linaro 6.3.0-18ubuntu2~16.04) 6.3.0 20170519
Copyright (C) 2016 Free Software Foundation, Inc.
This is free software; see the source for copying conditions. There is NO
warranty; not even for MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.

The error is not related to the branch; please try with an older compiler

With gcc 5.4.1-2:

[ 54%] Building CXX object sql/CMakeFiles/sql.dir/gcalc_tools.cc.o
/home/sveta/src/percona-server-laurynas/sql/gcalc_tools.cc: In member function ‘int Gcalc_function::count_internal()’:
/home/sveta/src/percona-server-laurynas/sql/gcalc_tools.cc:225:18: error: suggest parentheses around operand of ‘!’ or change ‘&’ to ‘&&’ or ‘!’ to ‘~’ [-Werror=parentheses]
         result= !result & next_res;
                  ^
cc1plus: all warnings being treated as errors
sql/CMakeFiles/sql.dir/build.make:385: recipe for target 'sql/CMakeFiles/sql.dir/gcalc_tools.cc.o' failed
make[2]: *** [sql/CMakeFiles/sql.dir/gcalc_tools.cc.o] Error 1
CMakeFiles/Makefile2:5638: recipe for target 'sql/CMakeFiles/sql.dir/all' failed
make[1]: *** [sql/CMakeFiles/sql.dir/all] Error 2
Makefile:160: recipe for target 'all' failed
make: *** [all] Error 2

Compiled with -DMYSQL_MAINTAINER_MODE=OFF, but still seeing the issue:

mysql> show processlist;
+----+------+-----------------+------+------------+------+-------------------------+--------------------------------------------+-----------+---------------+
| Id | User | Host | db | Command | Time | State | Info | Rows_sent | Rows_examined |
+----+------+-----------------+------+------------+------+-------------------------+--------------------------------------------+-----------+---------------+
| 2 | root | localhost:59258 | test | Query | 0 | init | show processlist | 0 | 0 |
| 3 | root | localhost:59262 | test | Query | 20 | User sleep | select sleep(1) from goods limit 1000, 300 | 20 | 0 |
| 4 | root | localhost:59264 | test | Field List | 11 | Waiting for table flush | | 0 | 0 |
+----+------+-----------------+------+------------+------+-------------------------+--------------------------------------------+-----------+---------------+
3 rows in set (0,01 sec)

Test case I use:

1. Load dump from the link which will be provided in separate comment.
2. In session 1: select sleep(1) from goods limit 1000, 300;
3. In session 2: analyze table goods;
4. In session 3: either connect to the database or select * from goods order by id limit 10;

I suspect that you built the default branch after "git clone" instead of the bugfix one, this could also explain the previous compilation issues. Please confirm that you have checked out "bug1704195-5.6-trunk" before building (5eee06f372389168c37941dc075f161f43cd5c0f)

My bad! Fix works fine. Thought I had to compile with option -DWITH_TOKUDB=0 (not related to the fix)

Dump file for the reference: https://drive.google.com/a/percona.com/file/d/0B67lYkmv0Zcsel9YNGxrYnpWNUU/view?usp=sharing

The experimental fix at https://github.com/laurynas-biveinis/percona-server/tree/bug1704195-5.6-trunk has been extended for partitioned InnoDB tables too.

Enabling this for TokuDB exposes https://jira.percona.com/browse/TDB-83

https://github.com/percona/percona-server/pull/1977
https://github.com/percona/percona-server/pull/1978

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-2503