[MDEV-8633] information_schema.index_statistics doesn't delete item when drop table indexes or drop table; - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: 5.5(EOL), 10.0(EOL)
Fix Version/s: 10.0.26
Component/s: Plugins
Labels:
- upstream
- userstat
Environment:
CentOS 6.2,
Distrib 10.0.14-MariaDB ,
x86_64

Sprint:
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

links to

#1449440 - when drop a table, TABLE_STATISTICS not clean it

Activity

Ascending order - Click to sort in descending order

QFY created issue - 2015-08-17 05:08

Elena Stepanova made changes - 2015-08-19 00:37

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?

Elena Stepanova made changes - 2015-08-19 00:44

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?

Elena Stepanova made changes - 2015-08-19 00:57

Status

Open [ 1 ]

Confirmed [ 10101 ]

Elena Stepanova made changes - 2015-08-19 01:01

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

Elena Stepanova made changes - 2015-08-19 01:01

Remote Link

This issue links to "#1449440 - when drop a table, TABLE_STATISTICS not clean it (Web Link)" [ 24501 ]

Jan Lindström (Inactive) made changes - 2016-04-22 09:09

Assignee

Jan Lindström [ jplindst ]

Jan Lindström (Inactive) made changes - 2016-04-22 09:12

Sprint

10.0.25 [ 50 ]

Jan Lindström (Inactive) made changes - 2016-04-22 09:12

Rank

Ranked lower

Jan Lindström (Inactive) made changes - 2016-04-22 09:16

Fix Version/s		10.0.25 [ 21701 ]
Fix Version/s	10.0 [ 16000 ]

Jan Lindström (Inactive) made changes - 2016-04-25 06:08

Status

Confirmed [ 10101 ]

In Progress [ 3 ]

Jan Lindström (Inactive) made changes - 2016-04-26 12:58

Assignee	Jan Lindström [ jplindst ]	Sergei Golubchik [ serg ]
Status	In Progress [ 3 ]	In Review [ 10002 ]

Sergei Golubchik made changes - 2016-05-03 14:42

Fix Version/s		10.0 [ 16000 ]
Fix Version/s	10.0.25 [ 21701 ]

Sergei Golubchik made changes - 2016-06-14 12:12

Sprint

10.0.25 [ 50 ]

10.0.25, 10.0.26 [ 50, 73 ]

Sergei Golubchik made changes - 2016-06-14 12:12

Rank

Ranked lower

Sergei Golubchik made changes - 2016-06-15 08:26

Assignee	Sergei Golubchik [ serg ]	Jan Lindström [ jplindst ]
Status	In Review [ 10002 ]	Stalled [ 10000 ]

Jan Lindström (Inactive) made changes - 2016-06-20 06:22

Status

Stalled [ 10000 ]

In Progress [ 3 ]

Jan Lindström (Inactive) made changes - 2016-06-20 07:20

issue.field.resolutiondate

2016-06-20 07:20:41.0

2016-06-20 07:20:41.448

Jan Lindström (Inactive) made changes - 2016-06-20 07:20

Fix Version/s		10.0.26 [ 22016 ]
Fix Version/s	10.0 [ 16000 ]
Resolution		Fixed [ 1 ]
Status	In Progress [ 3 ]	Closed [ 6 ]

Sergei Golubchik made changes - 2021-12-06 21:41

Workflow

MariaDB v3 [ 71101 ]

MariaDB v4 [ 149483 ]

People

Assignee:: Jan Lindström (Inactive)

Reporter:: QFY

Votes:: 0 Vote for this issue

Watchers:: 4 Start watching this issue

Dates

Created:: 2015-08-17 05:08

Updated:: 2016-06-20 07:20

Resolved:: 2016-06-20 07:20

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server

Details

Description

Attachments

Issue Links

Activity

People

Dates

Git Integration