| Bug #67241 | Partition pruning not working utf8 / skip-character-set-client-handshake options | ||
|---|---|---|---|
| Submitted: | 15 Oct 2012 20:03 | Modified: | 17 Oct 2012 13:38 |
| Reporter: | Nicolas Brousse | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Partitions | Severity: | S3 (Non-critical) |
| Version: | 5.5 | OS: | Linux |
| Assigned to: | CPU Architecture: | Any | |
[15 Oct 2012 20:03]
Nicolas Brousse
[15 Oct 2012 20:32]
MySQL Verification Team
Please provide the complete test case (sql script), with real results and expected result. Thanks.
[15 Oct 2012 22:51]
Nicolas Brousse
Simply create a table this way: create table test_nico ( cid char(20) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, `time_id` datetime NOT NULL, PRIMARY KEY (`cid`, `time_id`), KEY `time_id` (`time_id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci /*!50100 PARTITION BY RANGE (to_days(time_id)) (PARTITION PM_2010_01_31 VALUES LESS THAN (734169) ENGINE = InnoDB, PARTITION PM_2010_02_28 VALUES LESS THAN (734197) ENGINE = InnoDB, PARTITION PM_2010_03_31 VALUES LESS THAN (734228) ENGINE = InnoDB, PARTITION PM_2010_04_30 VALUES LESS THAN (734258) ENGINE = InnoDB, PARTITION PM_2010_05_31 VALUES LESS THAN (734289) ENGINE = InnoDB, PARTITION PM_2010_06_30 VALUES LESS THAN (734319) ENGINE = InnoDB, PARTITION PM_2010_07_31 VALUES LESS THAN (734350) ENGINE = InnoDB, PARTITION PM_2010_08_31 VALUES LESS THAN (734381) ENGINE = InnoDB, PARTITION PM_2010_09_30 VALUES LESS THAN (734411) ENGINE = InnoDB, PARTITION PM_2010_10_31 VALUES LESS THAN (734442) ENGINE = InnoDB, PARTITION PM_2010_11_30 VALUES LESS THAN (734472) ENGINE = InnoDB, PARTITION PM_2010_12_31 VALUES LESS THAN (734503) ENGINE = InnoDB, PARTITION PM_2011_01_31 VALUES LESS THAN (734534) ENGINE = InnoDB, PARTITION PM_2011_02_28 VALUES LESS THAN (734562) ENGINE = InnoDB, PARTITION PM_2011_03_31 VALUES LESS THAN (734593) ENGINE = InnoDB, PARTITION PM_2011_04_30 VALUES LESS THAN (734623) ENGINE = InnoDB, PARTITION PM_2011_05_31 VALUES LESS THAN (734654) ENGINE = InnoDB, PARTITION PM_2011_06_30 VALUES LESS THAN (734684) ENGINE = InnoDB, PARTITION PM_2011_07_31 VALUES LESS THAN (734715) ENGINE = InnoDB, PARTITION PM_2011_08_31 VALUES LESS THAN (734746) ENGINE = InnoDB, PARTITION PM_2011_09_30 VALUES LESS THAN (734776) ENGINE = InnoDB, PARTITION PM_2011_10_31 VALUES LESS THAN (734807) ENGINE = InnoDB, PARTITION PM_2011_11_30 VALUES LESS THAN (734837) ENGINE = InnoDB, PARTITION PM_2011_12_31 VALUES LESS THAN (734868) ENGINE = InnoDB, PARTITION PM_2012_01_31 VALUES LESS THAN (734899) ENGINE = InnoDB, PARTITION PM_2012_02_29 VALUES LESS THAN (734928) ENGINE = InnoDB, PARTITION PM_2012_03_31 VALUES LESS THAN (734959) ENGINE = InnoDB, PARTITION PM_2012_04_30 VALUES LESS THAN (734989) ENGINE = InnoDB, PARTITION PM_2012_05_31 VALUES LESS THAN (735020) ENGINE = InnoDB, PARTITION PM_2012_06_30 VALUES LESS THAN (735050) ENGINE = InnoDB, PARTITION PM_2012_07_31 VALUES LESS THAN (735081) ENGINE = InnoDB, PARTITION PM_2012_08_31 VALUES LESS THAN (735112) ENGINE = InnoDB, PARTITION PM_2012_09_30 VALUES LESS THAN (735142) ENGINE = InnoDB, PARTITION PM_2012_10_31 VALUES LESS THAN (735173) ENGINE = InnoDB, PARTITION PM_2012_11_30 VALUES LESS THAN (735203) ENGINE = InnoDB, PARTITION PM_2012_12_31 VALUES LESS THAN (735234) ENGINE = InnoDB) */; Then, no need of data, run the query on MySQL 5.1: mysql> explain partitions SELECT * FROM test_nico WHERE time_id >= '2012-10-09 00:00:00' AND time_id < '2012-10-16 00:00:00' AND cid = 'TpqiCvy6S9af5KbEL5fQ'; +----+-------------+-----------+-----------------------------+------+-----------------+---------+---------+-------+------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-----------+-----------------------------+------+-----------------+---------+---------+-------+------+--------------------------+ | 1 | SIMPLE | test_nico | PM_2010_01_31,PM_2012_10_31 | ref | PRIMARY,time_id | PRIMARY | 20 | const | 20 | Using where; Using index | +----+-------------+-----------+-----------------------------+------+-----------------+---------+---------+-------+------+--------------------------+ 1 row in set (0.00 sec) Then on MySQL 5.5: mysql> explain partitions SELECT * FROM test_nico WHERE time_id >= '2012-10-09 00:00:00' AND time_id < '2012-10-16 00:00:00' AND cid = 'TpqiCvy6S9af5KbEL5fQ';+----+-------------+-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------+---------------+---------+---------+-------+------+--------------------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------+---------------+---------+---------+-------+------+--------------------------+| 1 | SIMPLE | test_nico | PM_2010_01_31,PM_2010_02_28,PM_2010_03_31,PM_2010_04_30,PM_2010_05_31,PM_2010_06_30,PM_2010_07_31,PM_2010_08_31,PM_2010_09_30,PM_2010_10_31,PM_2010_11_30,PM_2010_12_31,PM_2011_01_31,PM_2011_02_28,PM_2011_03_31,PM_2011_04_30,PM_2011_05_31,PM_2011_06_30,PM_2011_07_31,PM_2011_08_31,PM_2011_09_30,PM_2011_10_31,PM_2011_11_30,PM_2011_12_31,PM_2012_01_31,PM_2012_02_29,PM_2012_03_31,PM_2012_04_30,PM_2012_05_31,PM_2012_06_30,PM_2012_07_31,PM_2012_08_31,PM_2012_09_30,PM_2012_10_31,PM_2012_11_30,PM_2012_12_31 | ref | PRIMARY | PRIMARY | 20 | const | 36 | Using where; Using index | +----+-------------+-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------+---------------+---------+---------+-------+------+--------------------------+ 1 row in set (0.00 sec) MySQL 5.5 doesn't do proper pruning and use all partitions instead of the two concerned. It should only use PM_2010_01_31,PM_2012_10_31. Behavior changed between 5.1 and 5.5
[16 Oct 2012 14:32]
MySQL Verification Team
Thank you for the feedback. Which 5.5.XX server are you using on my side with most recent source I can't repeat on Windows:
d:\dbs>d:\dbs\5.5\bin\mysql -uroot --port=3541 --prompt="mysql 5.5 >"
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.5.29 Source distribution
Copyright (c) 2000, 2012, 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 5.5 >use test
Database changed
mysql 5.5 >explain partitions SELECT * FROM test_nico WHERE time_id >= '2012-10-09 00:00:00' AND time_id < '2012-10-16 00:00:00' AND cid = 'TpqiCvy6S9af5Kb
EL5fQ'
-> ;
+----+-------------+-----------+-----------------------------+------+-----------------+---------+---------+-------+------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+-----------------------------+------+-----------------+---------+---------+-------+------+--------------------------+
| 1 | SIMPLE | test_nico | PM_2010_01_31,PM_2012_10_31 | ref | PRIMARY,time_id | PRIMARY | 20 | const | 2 | Using where; Using index |
+----+-------------+-----------+-----------------------------+------+-----------------+---------+---------+-------+------+--------------------------+
1 row in set (0.01 sec)
mysql 5.5 >exit
Bye
d:\dbs>51c
d:\dbs>d:\dbs\5.1\bin\mysql -uroot --port=3510 --prompt="mysql 5.1 >"
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.67-Win X64-log Source distribution
Copyright (c) 2000, 2012, 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 5.1 >use test
Database changed
mysql 5.1 >explain partitions SELECT * FROM test_nico WHERE time_id >= '2012-10-09 00:00:00' AND time_id < '2012-10-16 00:00:00' AND cid = 'TpqiCvy6S9af5Kb
EL5fQ';
+----+-------------+-----------+-----------------------------+------+-----------------+---------+---------+-------+------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+-----------------------------+------+-----------------+---------+---------+-------+------+--------------------------+
| 1 | SIMPLE | test_nico | PM_2010_01_31,PM_2012_10_31 | ref | PRIMARY,time_id | PRIMARY | 20 | const | 20 | Using where; Using index |
+----+-------------+-----------+-----------------------------+------+-----------------+---------+---------+-------+------+--------------------------+
1 row in set (0.00 sec)
mysql 5.1 >
[16 Oct 2012 17:17]
Nicolas Brousse
Investigating, it seems due to one of my innodb config option. Still digging. innodb_flush_log_at_trx_commit=0 innodb_buffer_pool_size=4G innodb_log_file_size=256M innodb_additional_mem_pool_size = 16M innodb_data_file_path = ibdata1:10M:autoextend innodb_log_buffer_size = 8M innodb_lock_wait_timeout = 300 innodb_log_files_in_group = 2 #innodb_log_group_home_dir=/mysql/logs innodb_max_purge_lag = 5000000 innodb_max_dirty_pages_pct = 80
[16 Oct 2012 17:23]
Nicolas Brousse
So, it's failing due to our utf8 options: collation_server=utf8_unicode_ci character_set_server=utf8 skip-character-set-client-handshake removing those, fix the problem.
[16 Oct 2012 17:28]
Nicolas Brousse
That's the one breaking our partition pruning: skip-character-set-client-handshake
[16 Oct 2012 17:33]
Nicolas Brousse
Removing the skip-character-set-client-handshake fix our issue. Tho, still seems like a bug since it used to work in 5.1 and stopped working in 5.5. mysql> explain partitions SELECT sum(streams_viewed) FROM rollup_adlist_camp WHERE time_id between '2012-10-09 00:00:00' AND '2012-10-16 00:00:00'; +----+-------------+--------------------+-----------------------------+-------+---------------+---------+---------+------+------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------------------+-----------------------------+-------+---------------+---------+---------+------+------+-------------+ | 1 | SIMPLE | rollup_adlist_camp | PM_2010_01_31,PM_2012_10_31 | range | time_id | time_id | 8 | NULL | 2 | Using where | +----+-------------+--------------------+-----------------------------+-------+---------------+---------+---------+------+------+-------------+ 1 row in set (0.00 sec)
[17 Oct 2012 13:38]
MySQL Verification Team
Thank you for the feedback. Indeed with the utf8 and skip-character-set-client-handshake options the issue is repeatable then removing skip-character-set-client-handshake the issue not happens anymore.
d:\dbs>d:\dbs\5.5\bin\mysql -uroot --port=3541 --prompt="mysql 5.5 >"
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.29 Source distribution
Copyright (c) 2000, 2012, 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 5.5 >create database gh;
Query OK, 1 row affected (0.00 sec)
mysql 5.5 >use gh
Database changed
mysql 5.5 >create table test_nico
<cut>
mysql 5.5 >explain partitions SELECT * FROM test_nico WHERE time_id >= '2012-10-09 00:00:00' AND time_id < '2012-10-16 00:00:00' AND cid = 'TpqiCvy6S9
EL5fQ'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: test_nico
partitions: PM_2010_01_31,PM_2010_02_28,PM_2010_03_31,PM_2010_04_30,PM_2010_05_31,PM_2010_06_30,PM_2010_07_31,PM_2010_08_31,PM_2010_09_30,PM_2010_1
PM_2010_11_30,PM_2010_12_31,PM_2011_01_31,PM_2011_02_28,PM_2011_03_31,PM_2011_04_30,PM_2011_05_31,PM_2011_06_30,PM_2011_07_31,PM_2011_08_31,PM_2011_09
M_2011_10_31,PM_2011_11_30,PM_2011_12_31,PM_2012_01_31,PM_2012_02_29,PM_2012_03_31,PM_2012_04_30,PM_2012_05_31,PM_2012_06_30,PM_2012_07_31,PM_2012_08_
_2012_09_30,PM_2012_10_31,PM_2012_11_30,PM_2012_12_31
type: ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 20
ref: const
rows: 36
Extra: Using where; Using index
1 row in set (0.00 sec)
d:\dbs>d:\dbs\5.1\bin\mysql -uroot --port=3510 --prompt="mysql 5.1 >"
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.67-Win X64-log Source distribution
Copyright (c) 2000, 2012, 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 5.1 >create database th;
Query OK, 1 row affected (0.00 sec)
mysql 5.1 >use th
Database changed
mysql 5.1 >create table test_nico ( cid char(20)
<cut>
mysql 5.1 >explain partitions SELECT * FROM test_nico WHERE time_id >= '2012-10-09 00:00:00' AND time_id < '2012-10-16 00:00:00' AND cid = 'TpqiCvy6S9
EL5fQ'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: test_nico
partitions: PM_2010_01_31,PM_2012_10_31
type: ref
possible_keys: PRIMARY,time_id
key: PRIMARY
key_len: 20
ref: const
rows: 20
Extra: Using where; Using index
1 row in set (0.00 sec)
mysql 5.1 >
