| Bug #86317 | reorganize partition set information_schema.PARTITIONS.table_rows to 0 | ||
|---|---|---|---|
| Submitted: | 15 May 2017 3:30 | Modified: | 20 May 2017 3:42 |
| Reporter: | Rob Burrowes | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | Ver 14.14 Distrib 5.7.17 | OS: | FreeBSD (11.0-RELEASE-p2) |
| Assigned to: | MySQL Verification Team | CPU Architecture: | Any |
| Tags: | partition, reorganize | ||
[20 May 2017 3:42]
MySQL Verification Team
Hi Rob, I don't see this as a bug. The statistics for that table is just not "calculated" yet, it should show after a while. The same thing was with 5.6 (the old values are no more correct then the zeroes you are getting with 5.7). Have you tried running analyze after reorganize? for e.g. ALTER TABLE `wikk`.`log_summary` ANALYZE PARTITION p2009_04; ALTER TABLE `wikk`.`log_summary` ANALYZE PARTITION p2009_05; and after the analyze you check the statistics: SELECT PARTITION_NAME, TABLE_ROWS, PARTITION_DESCRIPTION FROM information_schema.PARTITIONS WHERE TABLE_SCHEMA = 'wikk' AND TABLE_NAME = 'log_summary'; all best Bogdan
Description: information_schema_partitions table_rows is set to 0 on a reorganize used to rename the table partitions. The data is still there, as can be seen using a count(*) on the table (and by queries on the data). Same change to a "mysql Ver 14.14 Distrib 5.6.32, for FreeBSD8.4 (i386)" system left the table_rows column as it was (though it doesn't match a count(*) on the table, and didn't before the reorganize). How to repeat: mysql> SELECT PARTITION_NAME, TABLE_ROWS, PARTITION_METHOD FROM information_schema.PARTITIONS WHERE TABLE_SCHEMA = 'wikk' AND TABLE_NAME = 'log_summary'; +----------------+------------+------------------+ | PARTITION_NAME | TABLE_ROWS | PARTITION_METHOD | +----------------+------------+------------------+ | p2009_05 | 676416 | RANGE COLUMNS | | p2009_06 | 2301457 | RANGE COLUMNS | ... mysql> ALTER TABLE log_summary REORGANIZE PARTITION p2009_05 INTO ( -> PARTITION p2009_04 VALUES LESS THAN ('2009-05-01')); Query OK, 0 rows affected (16.53 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE log_summary REORGANIZE PARTITION p2009_06 INTO ( -> PARTITION p2009_05 VALUES LESS THAN ('2009-06-01')); Query OK, 0 rows affected (49.97 sec) Records: 0 Duplicates: 0 Warnings: 0 ... mysql> SELECT PARTITION_NAME, TABLE_ROWS, PARTITION_DESCRIPTION FROM information_schema.PARTITIONS WHERE TABLE_SCHEMA = 'wikk' AND TABLE_NAME = 'log_summary'; +----------------+------------+-----------------------+ | PARTITION_NAME | TABLE_ROWS | PARTITION_DESCRIPTION | +----------------+------------+-----------------------+ | p2009_04 | 0 | '2009-05-01' | | p2009_05 | 0 | '2009-06-01' | ... mysql> select Year(log_timestamp) as year, Month(log_timestamp) as month, count(*) from log_summary group by year,month; +------+-------+----------+ | year | month | count(*) | +------+-------+----------+ | 2009 | 4 | 678571 | | 2009 | 5 | 2307612 | | 2009 | 6 | 1961981 | ...