Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
5.5(EOL), 10.0(EOL)
-
CentOS 6.2,
Distrib 10.0.14-MariaDB ,
x86_64
-
10.0.25, 10.0.26
Description
set global userstat=1; |
create table just_a_test(id int,first_name varchar(10),last_name varchar(10),address varchar(100),phone bigint,email varchar(30)) engine =innodb; |
insert into just_a_test values(1,'fa','la','china_a',11111111,'fa_la@163.com'), |
(2,'fb','lb','china_b',22222222,'fb_lb@163.com'), |
(3,'fc','lc','china_c',33333333,'fc_lc@163.com'), |
(4,'fd','ld','china_d',44444444,'fd_ld@163.com'), |
(5,'fe','le','china_e',55555555,'fe_le@163.com'); |
alter table just_a_test add primary key (id); |
alter table just_a_test add key IND_just_a_test_first_name_last_name(first_name,last_name); |
select * from information_schema.index_statistics; |
# Empty set (0.00 sec) |
|
|
select count(*) from just_a_test where first_name='fc' and last_name='lc'; |
# +----------+ |
# | count(*) | |
# +----------+ |
# | 1 |
|
# +----------+ |
# 1 row in set (0.03 sec) |
|
|
select * from information_schema.index_statistics; |
# +--------------+-------------+--------------------------------------+-----------+ |
# | TABLE_SCHEMA | TABLE_NAME | INDEX_NAME | ROWS_READ |
|
# +--------------+-------------+--------------------------------------+-----------+ |
# | test | just_a_test | IND_just_a_test_first_name_last_name | 1 |
|
# +--------------+-------------+--------------------------------------+-----------+ |
|
Step X |
alter table just_a_test drop key IND_just_a_test_first_name_last_name; |
|
|
select * from information_schema.index_statistics where table_schema='test' and table_name='just_a_test'; |
# +--------------+-------------+--------------------------------------+-----------+ |
# | TABLE_SCHEMA | TABLE_NAME | INDEX_NAME | ROWS_READ |
|
# +--------------+-------------+--------------------------------------+-----------+ |
# | test | just_a_test | IND_just_a_test_first_name_last_name | 1 |
|
# +--------------+-------------+--------------------------------------+-----------+ |
# 1 row in set (0.00 sec) |
|
Step Y |
drop table test.just_a_test; |
|
|
select * from information_schema.index_statistics where table_schema='test' and table_name='just_a_test'; |
# +--------------+-------------+--------------------------------------+-----------+ |
# | TABLE_SCHEMA | TABLE_NAME | INDEX_NAME | ROWS_READ |
|
# +--------------+-------------+--------------------------------------+-----------+ |
# | test | just_a_test | IND_just_a_test_first_name_last_name | 1 |
|
# +--------------+-------------+--------------------------------------+-----------+ |
# 1 row in set (0.00 sec) |
Why could step X show informations about index that couldn't existed?
Why could step Y show informations about table that couldn't existed?
Attachments
Issue Links
Activity
| Field | Original Value | New Value |
|---|---|---|
| Description |
1.set global userstat=1; 2.create table just_a_test(id int,first_name varchar(10),last_name varchar(10),address varchar(100),phone bigint,email varchar(30)) engine =innodb; 3.insert into just_a_test values(1,'fa','la','china_a',11111111,'fa_la@163.com'), (2,'fb','lb','china_b',22222222,'fb_lb@163.com'), (3,'fc','lc','china_c',33333333,'fc_lc@163.com'), (4,'fd','ld','china_d',44444444,'fd_ld@163.com'), (5,'fe','le','china_e',55555555,'fe_le@163.com'); 4.alter table just_a_test add primary key (id); 5.alter table just_a_test add key IND_just_a_test_first_name_last_name(first_name,last_name); 6.MariaDB [test]> select * from information_schema.index_statistics; Empty set (0.00 sec) 7.MariaDB [test]> select count(*) from just_a_test where first_name='fc' and last_name='lc'; +----------+ | count(*) | +----------+ | 1 | +----------+ 1 row in set (0.03 sec) 8.MariaDB [test]> select * from information_schema.index_statistics; +--------------+-------------+--------------------------------------+-----------+ | TABLE_SCHEMA | TABLE_NAME | INDEX_NAME | ROWS_READ | +--------------+-------------+--------------------------------------+-----------+ | test | just_a_test | IND_just_a_test_first_name_last_name | 1 | +--------------+-------------+--------------------------------------+-----------+ 9.MariaDB [test]> alter table just_a_test drop key IND_just_a_test_first_name_last_name; Query OK, 0 rows affected (0.06 sec) Records: 0 Duplicates: 0 Warnings: 0 10.MariaDB [test]> select * from information_schema.index_statistics where table_schema='test' and table_name='just_a_test'; +--------------+-------------+--------------------------------------+-----------+ | TABLE_SCHEMA | TABLE_NAME | INDEX_NAME | ROWS_READ | +--------------+-------------+--------------------------------------+-----------+ | test | just_a_test | IND_just_a_test_first_name_last_name | 1 | +--------------+-------------+--------------------------------------+-----------+ 1 row in set (0.00 sec) 11.MariaDB [test]> drop table test.just_a_test; Query OK, 0 rows affected (0.04 sec) 12.MariaDB [test]> select * from information_schema.index_statistics where table_schema='test' and table_name='just_a_test'; +--------------+-------------+--------------------------------------+-----------+ | TABLE_SCHEMA | TABLE_NAME | INDEX_NAME | ROWS_READ | +--------------+-------------+--------------------------------------+-----------+ | test | just_a_test | IND_just_a_test_first_name_last_name | 1 | +--------------+-------------+--------------------------------------+-----------+ 1 row in set (0.00 sec) Why could step 10 show informations about index that couldn't existed? Why could step 10 show informations about table that couldn't existed? |
{code:sql} set global userstat=1; create table just_a_test(id int,first_name varchar(10),last_name varchar(10),address varchar(100),phone bigint,email varchar(30)) engine =innodb; insert into just_a_test values(1,'fa','la','china_a',11111111,'fa_la@163.com'), (2,'fb','lb','china_b',22222222,'fb_lb@163.com'), (3,'fc','lc','china_c',33333333,'fc_lc@163.com'), (4,'fd','ld','china_d',44444444,'fd_ld@163.com'), (5,'fe','le','china_e',55555555,'fe_le@163.com'); alter table just_a_test add primary key (id); alter table just_a_test add key IND_just_a_test_first_name_last_name(first_name,last_name); {code} {code:sql} select * from information_schema.index_statistics; Empty set (0.00 sec) select count(*) from just_a_test where first_name='fc' and last_name='lc'; +----------+ | count(*) | +----------+ | 1 | +----------+ 1 row in set (0.03 sec) select * from information_schema.index_statistics; +--------------+-------------+--------------------------------------+-----------+ | TABLE_SCHEMA | TABLE_NAME | INDEX_NAME | ROWS_READ | +--------------+-------------+--------------------------------------+-----------+ | test | just_a_test | IND_just_a_test_first_name_last_name | 1 | +--------------+-------------+--------------------------------------+-----------+ {code} {code:sql|title=Step X} alter table just_a_test drop key IND_just_a_test_first_name_last_name; select * from information_schema.index_statistics where table_schema='test' and table_name='just_a_test'; +--------------+-------------+--------------------------------------+-----------+ | TABLE_SCHEMA | TABLE_NAME | INDEX_NAME | ROWS_READ | +--------------+-------------+--------------------------------------+-----------+ | test | just_a_test | IND_just_a_test_first_name_last_name | 1 | +--------------+-------------+--------------------------------------+-----------+ 1 row in set (0.00 sec) {code} {code:sql|title=Step Y} drop table test.just_a_test; select * from information_schema.index_statistics where table_schema='test' and table_name='just_a_test'; +--------------+-------------+--------------------------------------+-----------+ | TABLE_SCHEMA | TABLE_NAME | INDEX_NAME | ROWS_READ | +--------------+-------------+--------------------------------------+-----------+ | test | just_a_test | IND_just_a_test_first_name_last_name | 1 | +--------------+-------------+--------------------------------------+-----------+ 1 row in set (0.00 sec) {code} Why could step X show informations about index that couldn't existed? Why could step Y show informations about table that couldn't existed? |
| Description |
{code:sql} set global userstat=1; create table just_a_test(id int,first_name varchar(10),last_name varchar(10),address varchar(100),phone bigint,email varchar(30)) engine =innodb; insert into just_a_test values(1,'fa','la','china_a',11111111,'fa_la@163.com'), (2,'fb','lb','china_b',22222222,'fb_lb@163.com'), (3,'fc','lc','china_c',33333333,'fc_lc@163.com'), (4,'fd','ld','china_d',44444444,'fd_ld@163.com'), (5,'fe','le','china_e',55555555,'fe_le@163.com'); alter table just_a_test add primary key (id); alter table just_a_test add key IND_just_a_test_first_name_last_name(first_name,last_name); {code} {code:sql} select * from information_schema.index_statistics; Empty set (0.00 sec) select count(*) from just_a_test where first_name='fc' and last_name='lc'; +----------+ | count(*) | +----------+ | 1 | +----------+ 1 row in set (0.03 sec) select * from information_schema.index_statistics; +--------------+-------------+--------------------------------------+-----------+ | TABLE_SCHEMA | TABLE_NAME | INDEX_NAME | ROWS_READ | +--------------+-------------+--------------------------------------+-----------+ | test | just_a_test | IND_just_a_test_first_name_last_name | 1 | +--------------+-------------+--------------------------------------+-----------+ {code} {code:sql|title=Step X} alter table just_a_test drop key IND_just_a_test_first_name_last_name; select * from information_schema.index_statistics where table_schema='test' and table_name='just_a_test'; +--------------+-------------+--------------------------------------+-----------+ | TABLE_SCHEMA | TABLE_NAME | INDEX_NAME | ROWS_READ | +--------------+-------------+--------------------------------------+-----------+ | test | just_a_test | IND_just_a_test_first_name_last_name | 1 | +--------------+-------------+--------------------------------------+-----------+ 1 row in set (0.00 sec) {code} {code:sql|title=Step Y} drop table test.just_a_test; select * from information_schema.index_statistics where table_schema='test' and table_name='just_a_test'; +--------------+-------------+--------------------------------------+-----------+ | TABLE_SCHEMA | TABLE_NAME | INDEX_NAME | ROWS_READ | +--------------+-------------+--------------------------------------+-----------+ | test | just_a_test | IND_just_a_test_first_name_last_name | 1 | +--------------+-------------+--------------------------------------+-----------+ 1 row in set (0.00 sec) {code} Why could step X show informations about index that couldn't existed? Why could step Y show informations about table that couldn't existed? |
{code:sql} set global userstat=1; create table just_a_test(id int,first_name varchar(10),last_name varchar(10),address varchar(100),phone bigint,email varchar(30)) engine =innodb; insert into just_a_test values(1,'fa','la','china_a',11111111,'fa_la@163.com'), (2,'fb','lb','china_b',22222222,'fb_lb@163.com'), (3,'fc','lc','china_c',33333333,'fc_lc@163.com'), (4,'fd','ld','china_d',44444444,'fd_ld@163.com'), (5,'fe','le','china_e',55555555,'fe_le@163.com'); alter table just_a_test add primary key (id); alter table just_a_test add key IND_just_a_test_first_name_last_name(first_name,last_name); {code} {code:sql} select * from information_schema.index_statistics; # Empty set (0.00 sec) select count(*) from just_a_test where first_name='fc' and last_name='lc'; # +----------+ # | count(*) | # +----------+ # | 1 | # +----------+ # 1 row in set (0.03 sec) select * from information_schema.index_statistics; # +--------------+-------------+--------------------------------------+-----------+ # | TABLE_SCHEMA | TABLE_NAME | INDEX_NAME | ROWS_READ | # +--------------+-------------+--------------------------------------+-----------+ # | test | just_a_test | IND_just_a_test_first_name_last_name | 1 | # +--------------+-------------+--------------------------------------+-----------+ {code} {code:sql|title=Step X} alter table just_a_test drop key IND_just_a_test_first_name_last_name; select * from information_schema.index_statistics where table_schema='test' and table_name='just_a_test'; # +--------------+-------------+--------------------------------------+-----------+ # | TABLE_SCHEMA | TABLE_NAME | INDEX_NAME | ROWS_READ | # +--------------+-------------+--------------------------------------+-----------+ # | test | just_a_test | IND_just_a_test_first_name_last_name | 1 | # +--------------+-------------+--------------------------------------+-----------+ # 1 row in set (0.00 sec) {code} {code:sql|title=Step Y} drop table test.just_a_test; select * from information_schema.index_statistics where table_schema='test' and table_name='just_a_test'; # +--------------+-------------+--------------------------------------+-----------+ # | TABLE_SCHEMA | TABLE_NAME | INDEX_NAME | ROWS_READ | # +--------------+-------------+--------------------------------------+-----------+ # | test | just_a_test | IND_just_a_test_first_name_last_name | 1 | # +--------------+-------------+--------------------------------------+-----------+ # 1 row in set (0.00 sec) {code} Why could step X show informations about index that couldn't existed? Why could step Y show informations about table that couldn't existed? |
| Status | Open [ 1 ] | Confirmed [ 10101 ] |
| Fix Version/s | 10.0 [ 16000 ] | |
| Affects Version/s | 10.0 [ 16000 ] | |
| Affects Version/s | 5.5 [ 15800 ] | |
| Affects Version/s | 10.0.15 [ 17300 ] | |
| Affects Version/s | 10.0.14 [ 17101 ] | |
| Labels | userstat | upstream userstat |
| Remote Link | This issue links to "#1449440 - when drop a table, TABLE_STATISTICS not clean it (Web Link)" [ 24501 ] |
| Assignee | Jan Lindström [ jplindst ] |
| Sprint | 10.0.25 [ 50 ] |
| Rank | Ranked lower |
| Fix Version/s | 10.0.25 [ 21701 ] | |
| Fix Version/s | 10.0 [ 16000 ] |
| Status | Confirmed [ 10101 ] | In Progress [ 3 ] |
http://lists.askmonty.org/pipermail/commits/2016-April/009316.html
http://lists.askmonty.org/pipermail/commits/2016-April/009318.html
I will combine these before push.
| Assignee | Jan Lindström [ jplindst ] | Sergei Golubchik [ serg ] |
| Status | In Progress [ 3 ] | In Review [ 10002 ] |
| Fix Version/s | 10.0 [ 16000 ] | |
| Fix Version/s | 10.0.25 [ 21701 ] |
| Sprint | 10.0.25 [ 50 ] | 10.0.25, 10.0.26 [ 50, 73 ] |
| Rank | Ranked lower |
| Assignee | Sergei Golubchik [ serg ] | Jan Lindström [ jplindst ] |
| Status | In Review [ 10002 ] | Stalled [ 10000 ] |
| Status | Stalled [ 10000 ] | In Progress [ 3 ] |
commit 70ad689b11bfbd8a30a777f4893a5384628c00e7
Author: Jan Lindström <jan.lindstrom@mariadb.com>
Date: Mon Jun 20 09:58:31 2016 +0300
MDEV-8633: information_schema.index_statistics doesn't delete
item when drop table indexes or drop table;
Problem was that table and index statistics is removed from
persistent tables but not from memory cache. Added functions
to remove table and index statistics from memory cache
| issue.field.resolutiondate | 2016-06-20 07:20:41.0 | 2016-06-20 07:20:41.448 |
| Fix Version/s | 10.0.26 [ 22016 ] | |
| Fix Version/s | 10.0 [ 16000 ] | |
| Resolution | Fixed [ 1 ] | |
| Status | In Progress [ 3 ] | Closed [ 6 ] |
| Workflow | MariaDB v3 [ 71101 ] | MariaDB v4 [ 149483 ] |
Thanks for the report.
Same in Percona 5.5/5.6, see https://bugs.launchpad.net/percona-server/+bug/1449440.