INDEX_STATISTICS can not STATISTICS partition table

Bug #625045 reported by limohao
22
This bug affects 4 people
Affects Status Importance Assigned to Milestone
Percona Server moved to https://jira.percona.com/projects/PS
Status tracked in 5.7
5.1
Won't Fix
Low
Unassigned
5.5
Triaged
Low
Unassigned
5.6
Triaged
Low
Unassigned
5.7
Triaged
Low
Unassigned

Bug Description

using script.txt create table and proc

 table part_date3 is a partitioning table

  table part_date3 is a normal table

then load data

call load_data3()
call load_data4()

open user statistics

set global userstat_running =1

then run

SELECT * FROM part_date3 p;

and

SELECT * FROM part_date4 p;

output information_schema.INDEX_STATISTICS

SELECT * FROM information_schema.INDEX_STATISTICS I;

'test', 'part_date4', 'PRIMARY', 12999
'mysql', 'proc', 'PRIMARY', 4

 part_date3 index has not STATISTIC.

Tags: userstat
Revision history for this message
limohao (limohao) wrote :
Changed in percona-server:
status: New → Confirmed
Stewart Smith (stewart)
Changed in percona-server:
importance: Undecided → Low
status: Confirmed → Triaged
Revision history for this message
Trey Raymond (wraymond) wrote :

This also affects table_statistics. what version is this fixed in?

Revision history for this message
Przemek (pmalkowski) wrote :

I can confirm this bug still exists in Percona Server 5.5.32:

mysql [localhost] {msandbox} (test) > CREATE TABLE `t1` (
    -> `id` int(11) DEFAULT NULL,
    -> KEY `id` (`id`)
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.01 sec)

mysql [localhost] {msandbox} (test) > CREATE TABLE `p1` (
    -> `id` int(11) DEFAULT NULL,
    -> KEY `id` (`id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1
    -> /*!50100 PARTITION BY HASH (id)
    -> PARTITIONS 8 */;
Query OK, 0 rows affected (0.02 sec)

mysql [localhost] {msandbox} (test) > insert into p1 values (2),(33),(3),(100),(4);
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0

mysql [localhost] {msandbox} (test) > insert into t1 values (2),(33),(3),(100),(4);
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0

mysql [localhost] {msandbox} (test) > set global userstat=1;
Query OK, 0 rows affected (0.00 sec)

mysql [localhost] {msandbox} (test) > select * from t1;
+------+
| id |
+------+
| 2 |
| 3 |
| 4 |
| 33 |
| 100 |
+------+
5 rows in set (0.00 sec)

mysql [localhost] {msandbox} (test) > select * from p1;
+------+
| id |
+------+
| 2 |
| 3 |
| 4 |
| 33 |
| 100 |
+------+
5 rows in set (0.00 sec)

mysql [localhost] {msandbox} (test) > select * from INFORMATION_SCHEMA.INDEX_STATISTICS;
+--------------+------------+------------+-----------+
| TABLE_SCHEMA | TABLE_NAME | INDEX_NAME | ROWS_READ |
+--------------+------------+------------+-----------+
| test | t1 | id | 5 |
+--------------+------------+------------+-----------+
1 row in set (0.00 sec)

mysql [localhost] {msandbox} (test) > select * from INFORMATION_SCHEMA.TABLE_STATISTICS;
+--------------+------------+-----------+--------------+------------------------+
| TABLE_SCHEMA | TABLE_NAME | ROWS_READ | ROWS_CHANGED | ROWS_CHANGED_X_INDEXES |
+--------------+------------+-----------+--------------+------------------------+
| test | t1 | 5 | 5 | 5 |
+--------------+------------+-----------+--------------+------------------------+
1 row in set (0.00 sec)

While INFORMATION_SCHEMA.CLIENT_STATISTICS and INFORMATION_SCHEMA.THREAD_STATISTICS are not affected.

Revision history for this message
Valerii Kravchuk (valerii-kravchuk) wrote :

PS 5.1 is also affected:

[openxs@chief p5.1]$ bin/mysql --no-defaults -uroot 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 MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.68 Source distribution

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create table t1 (id int primary key) engine=InnoDB;
Query OK, 0 rows affected (0.07 sec)

mysql> create table p1 (id int primary key) engine=InnoDB partition by hash(id) partitions 8;
Query OK, 0 rows affected (0.11 sec)

mysql> insert into t1 values (2), (33), (3), (100), (4);
Query OK, 5 rows affected (0.01 sec)
Records: 5 Duplicates: 0 Warnings: 0

mysql> insert into p1 values (2), (33), (3), (100), (4);
Query OK, 5 rows affected (0.01 sec)
Records: 5 Duplicates: 0 Warnings: 0

mysql> set global userstat=1;
ERROR 1193 (HY000): Unknown system variable 'userstat'
mysql> set global userstat_running=1;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t1;
+-----+
| id |
+-----+
| 2 |
| 3 |
| 4 |
| 33 |
| 100 |
+-----+
5 rows in set (0.00 sec)

mysql> select * from p1;
+-----+
| id |
+-----+
| 2 |
| 3 |
| 4 |
| 33 |
| 100 |
+-----+
5 rows in set (0.00 sec)

mysql> select * from INFORMATION_SCHEMA.INDEX_STATISTICS;
+--------------+------------+------------+-----------+
| TABLE_SCHEMA | TABLE_NAME | INDEX_NAME | ROWS_READ |
+--------------+------------+------------+-----------+
| test | t1 | PRIMARY | 5 |
+--------------+------------+------------+-----------+
1 row in set (0.00 sec)

mysql> select * from INFORMATION_SCHEMA.TABLE_STATISTICS;
+--------------+------------+-----------+--------------+------------------------+
| TABLE_SCHEMA | TABLE_NAME | ROWS_READ | ROWS_CHANGED | ROWS_CHANGED_X_INDEXES |
+--------------+------------+-----------+--------------+------------------------+
| test | t1 | 5 | 0 | 0 |
+--------------+------------+-----------+--------------+------------------------+
1 row in set (0.00 sec)

tags: added: userstat
Revision history for this message
Shahriyar Rzayev (rzayev-sehriyar) wrote :

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

To post a comment you must log in.
This report contains Public information  
Everyone can see this information.

Other bug subscribers

Bug attachments

Remote bug watches

Bug watches keep track of this bug in other bug trackers.