Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
10.5.13, 10.5.18, 10.6.14
-
None
Description
MDEV-15101 is supposed to be fixed in 10.5.4+, but if engine-independent statistics is collected by the ANALYZE TABLE we still have to wait for the table flush if there was some long running query in progress when such ANALYZE was executed, thus causing the same problems. Consider this simple table:
MariaDB [test]> show create table t1\G
|
*************************** 1. row ***************************
|
Table: t1
|
Create Table: CREATE TABLE `t1` (
|
`id` int(11) DEFAULT NULL,
|
`c1` int(11) DEFAULT NULL
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1
|
1 row in set (0.005 sec)
|
|
MariaDB [test]> select * from t1;
|
+------+------+
|
| id | c1 |
|
+------+------+
|
| 1 | 0 |
|
| 2 | 0 |
|
| 3 | 0 |
|
| 4 | 0 |
|
+------+------+
|
4 rows in set (0.002 sec)
|
In one connection run a slow query against it, like this:
MariaDB [test]> select sleep(100) from t1;
Now in another one try this:
MariaDB [test]> select @@use_stat_tables;
|
+------------------------+
|
| @@use_stat_tables |
|
+------------------------+
|
| PREFERABLY_FOR_QUERIES |
|
+------------------------+
|
1 row in set (0.000 sec)
|
|
MariaDB [test]> analyze table t1;
|
+---------+---------+----------+----------+
|
| Table | Op | Msg_type | Msg_text |
|
+---------+---------+----------+----------+
|
| test.t1 | analyze | status | OK |
|
+---------+---------+----------+----------+
|
1 row in set (0.044 sec)
|
|
MariaDB [test]> select count(*) from t1;
|
+----------+
|
| count(*) |
|
+----------+
|
| 4 |
|
+----------+
|
1 row in set (0.001 sec)
|
No problem so far, but if we with to 'preferable' to force engine-independent statistics collection even by default:
MariaDB [test]> set session use_stat_tables = 'preferably';
|
Query OK, 0 rows affected (0.005 sec)
|
|
MariaDB [test]> analyze table t1;
|
+---------+---------+----------+-----------------------------------------+
|
| Table | Op | Msg_type | Msg_text |
|
+---------+---------+----------+-----------------------------------------+
|
| test.t1 | analyze | status | Engine-independent statistics collected |
|
| test.t1 | analyze | status | OK |
|
+---------+---------+----------+-----------------------------------------+
|
2 rows in set (0.053 sec)
|
|
MariaDB [test]> select count(*) from t1;
|
...
|
we are forced to wait for the query in the first connection to complete, this way:
Yuliyas-Air:maria10.5 Valerii$ bin/mysql 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 MariaDB monitor. Commands end with ; or \g.
|
Your MariaDB connection id is 21
|
Server version: 10.5.18-MariaDB MariaDB Server
|
|
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
|
|
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
|
|
MariaDB [test]> show processlist;
|
+----+---------+-----------+------+---------+------+-------------------------+---------------------------+----------+
|
| Id | User | Host | db | Command | Time | State | Info | Progress |
|
+----+---------+-----------+------+---------+------+-------------------------+---------------------------+----------+
|
| 19 | Valerii | localhost | test | Query | 114 | User sleep | select sleep(100) from t1 | 0.000 |
|
| 20 | Valerii | localhost | test | Query | 6 | Waiting for table flush | select count(*) from t1 | 0.000 |
|
| 21 | Valerii | localhost | test | Query | 0 | starting | show processlist | 0.000 |
|
+----+---------+-----------+------+---------+------+-------------------------+---------------------------+----------+
|
3 rows in set (0.003 sec)
|
So, the fix for MDEV-15101 had not taken the engine-independent table statistics collection case into account. I think it's a bug.
Attachments
Issue Links
- relates to
-
MDEV-19061 table_share used for reading statistical tables is not protected
-
- Closed
-
-
MDEV-31957 Concurrent ALTER and ANALYZE collecting statistics can result in stale statistical data
-
- Closed
-
Run with --repeat=N if it doesn't fail right away.
--source include/have_sequence.inc
--connect (con1,localhost,root,,)
--send
--connection default
--connection con1
--reap
bb-10.6-monty 901cf96446816cae944e73df62bbdf6aa4ff2414
Direct leak of 32 byte(s) in 1 object(s) allocated from:
#0 0x7f88fb0b89cf in __interceptor_malloc ../../../../src/libsanitizer/asan/asan_malloc_linux.cpp:69
#1 0x5561915952fd in my_malloc /data/src/bb-10.6-monty/mysys/my_malloc.c:91
#2 0x55618f2b8637 in Binary_string::real_alloc(unsigned long) /data/src/bb-10.6-monty/sql/sql_string.cc:45
#3 0x55618ec286b0 in Binary_string::alloc(unsigned long) /data/src/bb-10.6-monty/sql/sql_string.h:792
#4 0x55618fa546a7 in Field_blob::store(char const*, unsigned long, charset_info_st const*) /data/src/bb-10.6-monty/sql/field.cc:8668
#5 0x55618f4dc89c in Field::store_text(char const*, unsigned long, charset_info_st const*) /data/src/bb-10.6-monty/sql/field.h:950
#6 0x55618fa01060 in Field_blob::store_from_statistical_minmax_field(Field*, String*, st_mem_root*) /data/src/bb-10.6-monty/sql/field.cc:2024
#7 0x55618f2b062f in Column_stat::get_stat_values(Column_statistics*, st_mem_root*, bool) /data/src/bb-10.6-monty/sql/sql_statistics.cc:1147
#8 0x55618f2a057a in read_statistics_for_table /data/src/bb-10.6-monty/sql/sql_statistics.cc:2883
#9 0x55618f2a2a67 in read_statistics_for_tables(THD*, TABLE_LIST*, bool) /data/src/bb-10.6-monty/sql/sql_statistics.cc:3137
#10 0x55618f58237e in mysql_admin_table /data/src/bb-10.6-monty/sql/sql_admin.cc:1315
#11 0x55618f58405b in Sql_cmd_analyze_table::execute(THD*) /data/src/bb-10.6-monty/sql/sql_admin.cc:1492
#12 0x55618efd02d2 in mysql_execute_command(THD*, bool) /data/src/bb-10.6-monty/sql/sql_parse.cc:6024
#13 0x55618efe1a0f in mysql_parse(THD*, char*, unsigned int, Parser_state*) /data/src/bb-10.6-monty/sql/sql_parse.cc:8053
#14 0x55618efa49ed in dispatch_command(enum_server_command, THD*, char*, unsigned int, bool) /data/src/bb-10.6-monty/sql/sql_parse.cc:1896
#15 0x55618efa0bea in do_command(THD*, bool) /data/src/bb-10.6-monty/sql/sql_parse.cc:1409
#16 0x55618f52f4a1 in do_handle_one_connection(CONNECT*, bool) /data/src/bb-10.6-monty/sql/sql_connect.cc:1416
#17 0x55618f52eb4c in handle_one_connection /data/src/bb-10.6-monty/sql/sql_connect.cc:1318
#18 0x5561904b0c02 in pfs_spawn_thread /data/src/bb-10.6-monty/storage/perfschema/pfs.cc:2201
#19 0x7f88fa4a7fd3 in start_thread nptl/pthread_create.c:442