| Bug #54305 | MySQL 5.5.4-m3 server | ||
|---|---|---|---|
| Submitted: | 7 Jun 2010 19:15 | Modified: | 11 Jun 2010 6:26 |
| Reporter: | steven tang | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | MySQL Server | Severity: | S1 (Critical) |
| Version: | 5.5.4-m3-log | OS: | Linux (CentOS 5.4 64-bits) |
| Assigned to: | CPU Architecture: | Any | |
[7 Jun 2010 20:17]
Sveta Smirnova
Thank you for the report.
Have you inserted some rows in test table? If yes please provide us dump: I can not repeat described behavior with following test data.
insert into t2 (dob) values('1982-06-23'), ('1992-06-23'), ('1988-06-23'), ('1972-06-23'), ('1962-06-23'), ('1992-06-23'), ('2002-06-23'), ('2007-06-23'), ('2012-06-23'), ('1982-06-23');
Warnings:
Warning 1364 Field 'fname' doesn't have a default value
Warning 1364 Field 'lname' doesn't have a default value
Warning 1364 Field 'region_code' doesn't have a default value
explain partitions SELECT * FROM t2 WHERE dob = '1982-06-23';
id select_type table partitions type possible_keys key key_len ref rows Extra
1 SIMPLE t2 d3 ALL NULL NULL NULL NULL 2 Using where
explain partitions SELECT * FROM t2 WHERE dob BETWEEN '1991-02-15' AND '1997-04-25';
id select_type table partitions type possible_keys key key_len ref rows Extra
1 SIMPLE t2 d5 ALL NULL NULL NULL NULL 4 Using where
explain partitions SELECT * FROM t2 WHERE dob >= '1984-06-21' AND dob <= '1999-06-21';
id select_type table partitions type possible_keys key key_len ref rows Extra
1 SIMPLE t2 d3,d4,d5 ALL NULL NULL NULL NULL 5 Using where
[7 Jun 2010 20:37]
steven tang
The MySQL dump file for this Bug.
Attachment: t2.dump (application/octet-stream, text), 2.68 KiB.
[7 Jun 2010 20:39]
steven tang
Yes I just inserted some records and re-ran the queries and I got the same disappointing results: ---------------------- mysql> select * from t2; +-------+-------+-------------+------------+ | fname | lname | region_code | dob | +-------+-------+-------------+------------+ | fn5 | ln5 | 5 | 1962-06-23 | | fn4 | ln4 | 4 | 1972-06-23 | | fn1 | ln1 | 1 | 1982-06-23 | | fn10 | ln10 | 5 | 1982-06-23 | | fn3 | ln3 | 3 | 1988-06-23 | | fn2 | ln2 | 2 | 1992-06-23 | | fn6 | ln6 | 1 | 1992-06-23 | | fn7 | ln7 | 2 | 2002-06-23 | | fn8 | ln8 | 3 | 2007-06-23 | | fn9 | ln9 | 4 | 2012-06-23 | +-------+-------+-------------+------------+ 10 rows in set (0.01 sec) mysql> explain partitions SELECT * FROM t2 WHERE dob BETWEEN '1991-02-15' AND '1997-04-25'; +----+-------------+-------+-------------------------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------------------------+------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | t2 | d0,d1,d2,d3,d4,d5,d6,d7 | ALL | NULL | NULL | NULL | NULL | 11 | Using where | +----+-------------+-------+-------------------------+------+---------------+------+---------+------+------+-------------+ 1 row in set (0.00 sec) mysql> explain partitions SELECT * FROM t2 WHERE dob = '1982-06-23'; +----+-------------+-------+-------------------------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------------------------+------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | t2 | d0,d1,d2,d3,d4,d5,d6,d7 | ALL | NULL | NULL | NULL | NULL | 11 | Using where | +----+-------------+-------+-------------------------+------+---------------+------+---------+------+------+-------------+ 1 row in set (0.00 sec) mysql> explain partitions SELECT * FROM t2 WHERE dob >= '1984-06-21' AND dob <= '1999-06-21'; +----+-------------+-------+-------------------------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------------------------+------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | t2 | d0,d1,d2,d3,d4,d5,d6,d7 | ALL | NULL | NULL | NULL | NULL | 11 | Using where | +----+-------------+-------+-------------------------+------+---------------+------+---------+------+------+-------------+ 1 row in set (0.01 sec) -------------- I already submitted the MySQL dump of table t2.
[8 Jun 2010 5:58]
Sveta Smirnova
Thank you for the feedback. I still can not repeat described behavior. Please indicate exact package name you are using (file name you downloaded) and send us configuration file.
[8 Jun 2010 14:32]
steven tang
The file I downloaded and installed is: mysql-5.5.4-m3-linux2.6-x86_64.tar.gz Thanks, Steven
[9 Jun 2010 6:13]
Sveta Smirnova
Thank you for the data. I still can not repeat described behavior. Do you have error messages in the error log? What CHECK TABLE prints?
[9 Jun 2010 14:36]
steven tang
mysql> check table t2; ERROR 2006 (HY000): MySQL server has gone away No connection. Trying to reconnect... Connection id: 28 Current database: quadrant +-------------+-------+----------+----------+ | Table | Op | Msg_type | Msg_text | +-------------+-------+----------+----------+ | quadrant.t2 | check | status | OK | +-------------+-------+----------+----------+ 1 row in set (1.15 sec) mysql> Error log: --------------- 100604 16:46:35 [Note] /usr/local/mysql/bin/mysqld: Normal shutdown 100604 16:46:35 [Note] Event Scheduler: Purging the queue. 0 events 100604 16:46:37 [Warning] /usr/local/mysql/bin/mysqld: Forcing close of thread 18 user: 'root' 100604 16:46:37 InnoDB: Starting shutdown... 100604 16:46:43 InnoDB: Shutdown completed; log sequence number 54647400 100604 16:46:43 [Note] /usr/local/mysql/bin/mysqld: Shutdown complete 100604 16:46:43 mysqld_safe mysqld from pid file /opt/mysql/data/dev-db1-1.webtech.local.pid ended 100607 10:39:58 mysqld_safe Starting mysqld daemon with databases from /opt/mysql/data 100607 10:39:59 [Note] Buffered information: Performance schema disabled (reason: start parameters). 100607 10:39:59 [Note] Plugin 'FEDERATED' is disabled. InnoDB: The InnoDB memory heap is disabled InnoDB: Mutexes and rw_locks use GCC atomic builtins InnoDB: Compressed tables use zlib 1.2.3 100607 10:39:59 InnoDB: Using Linux native AIO 100607 10:40:01 InnoDB: highest supported file format is Barracuda. 100607 10:40:07 InnoDB 1.1.0 started; log sequence number 54647400 100607 10:40:07 [Note] Event Scheduler: Loaded 0 events 100607 10:40:07 [Note] /usr/local/mysql/bin/mysqld: ready for connections. Version: '5.5.4-m3-log' socket: '/opt/mysql/tmp/mysql.sock' port: 3306 MySQL Community Server (GPL) 100607 10:44:55 [Warning] Access denied for user 'reporter'@'192.168.169.77' (using password: YES) 100607 10:45:00 [Warning] Access denied for user 'reporter'@'192.168.169.77' (using password: YES) 100607 10:45:47 [Warning] Aborted connection 6 to db: 'unconnected' user: 'root' host: '192.168.169.42' (Got timeout reading communication packets) 100607 10:46:03 [Warning] Access denied for user 'reporter'@'192.168.169.77' (using password: YES) 100607 11:27:09 [Warning] Aborted connection 12 to db: 'quadrant' user: 'root' host: '192.168.169.42' (Got timeout reading communication packets) 100607 11:59:17 [Warning] Aborted connection 16 to db: 'quadrant' user: 'root' host: '192.168.169.42' (Got timeout reading communication packets) 100607 12:19:16 [Warning] Aborted connection 18 to db: 'quadrant' user: 'root' host: '192.168.169.79' (Got timeout reading communication packets) 100607 13:34:48 [Warning] Aborted connection 19 to db: 'quadrant' user: 'root' host: '192.168.169.42' (Got timeout reading communication packets) 100607 13:49:12 [Warning] Aborted connection 5 to db: 'unconnected' user: 'root' host: '192.168.169.42' (Got timeout reading communication packets) 100607 13:54:56 [Warning] Aborted connection 20 to db: 'quadrant' user: 'root' host: '192.168.169.79' (Got timeout reading communication packets) 100607 15:11:14 [Warning] Aborted connection 24 to db: 'unconnected' user: 'root' host: '192.168.169.42' (Got timeout reading communication packets) 100607 15:15:47 [Warning] Aborted connection 25 to db: 'quadrant' user: 'root' host: '192.168.169.42' (Got timeout reading communication packets) 100607 15:15:47 [Warning] Aborted connection 22 to db: 'unconnected' user: 'root' host: '192.168.169.42' (Got timeout reading communication packets) 100608 15:57:07 [Warning] Aborted connection 27 to db: 'quadrant' user: 'root' host: '192.168.169.79' (Got timeout reading communication packets)
[9 Jun 2010 16:28]
steven tang
I just installed a new server with CentOS 5.4 and MySQL 5.5.4-m3 and loaded the t2 tables and I got the exactly the same result. Please let me know if there is anything I could do. What MySQL version or binary are you testing? We are trying to do data partitioning and using partition pruning. If MySQL 5.5 partition pruning does not work, we will have to stop our development. So please help for this critical issue ASAP. Thanks Steven
[10 Jun 2010 6:42]
Sveta Smirnova
Thank you for the feedback. I tested with exactly same package as you indicated: mysql-5.5.4-m3-linux2.6-x86_64.tar.gz, but on RedHat 5.
[10 Jun 2010 14:50]
steven tang
CentOS 5.4 is RedHat 5. If your test is good on RedHat 5, then there must be something different on maybe configuration? Have you verified the my.cnf file I sent to you? Are you able to test it on CentOS 5.4 with the my.cnf? It would be great help if you can as that would be the real support. Please let me know if you could test my scenario? Thanks, Steven
[10 Jun 2010 17:45]
steven tang
The good news is, after I installed binary: mysql-5.5.3-m3-linux2.6-x86_64-icc.tar.gz on the same server, this time the explains give the correct results. Conclusion: the 5.5.4-m3 binary does not work and MySQL does not test out the main feature bug. If you could let me know when MySQL fixed the bug, that will be helpful so I do not have to have this type of problem. Thanks for your time. Steven
[10 Jun 2010 17:46]
steven tang
Updated the OS.
[10 Jun 2010 20:28]
steven tang
Well for the new MySQL 5.5.3-m3 server, I have following results that one is expected and the other is not expected.
------------------------------------------------------------------------------
mysql> show create table rd\G
*************************** 1. row ***************************
Table: rd
Create Table: CREATE TABLE `rd` (
`full_name` varchar(60) COLLATE latin1_general_ci DEFAULT NULL COMMENT 'Column full_name is the street name of the road',
`l_f_add` int(11) DEFAULT NULL COMMENT 'Column l_f_add left from address, carried from ESRI shp spec',
`l_t_add` int(11) DEFAULT NULL COMMENT 'Column l_t_add is left to address, carried from ESRI shp spec',
`r_f_add` int(11) DEFAULT NULL COMMENT 'Column r_f_add is right from address, carried from ESRI shp spec',
`r_t_add` int(11) DEFAULT NULL COMMENT 'Column r_t_add is right to address, carried from ESRI shp spec',
`cityl` varchar(60) COLLATE latin1_general_ci DEFAULT NULL COMMENT 'Column cityl is city name',
`zipl` varchar(10) COLLATE latin1_general_ci DEFAULT NULL COMMENT 'Column zipl is zip code to the left, carried from ESRI shp spec',
`zipr` varchar(10) COLLATE latin1_general_ci DEFAULT NULL COMMENT 'Column zipr is zip code to the right, carried from ESRI shp spec',
`state_abbr` varchar(32) COLLATE latin1_general_ci DEFAULT NULL COMMENT 'Column state_abbr is the abbreviation of the state or province the road located',
`shapeid` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'Column shapeid is a sequence number generated by database as primary key (PK) for each record',
`speedkm` int(11) DEFAULT NULL COMMENT 'Column speedkm is the speed limit in KM of the road',
`speedtype` char(1) COLLATE latin1_general_ci NOT NULL DEFAULT 'N' COMMENT 'Column speedtype is used to indicate the speed limit type. P: posted; C: category; N: not-in-use',
`writetime` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'Column writetime is the insertion time stamp of the record',
PRIMARY KEY (`shapeid`,`writetime`)
) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci COMMENT='See schema_dictionary table for details'
/*!50100 PARTITION BY RANGE (year (writetime))
SUBPARTITION BY HASH (month(writetime))
SUBPARTITIONS 12
(PARTITION p0 VALUES LESS THAN (2008) ENGINE = InnoDB,
PARTITION p1 VALUES LESS THAN (2009) ENGINE = InnoDB,
PARTITION p2 VALUES LESS THAN (2010) ENGINE = InnoDB,
PARTITION p3 VALUES LESS THAN (2011) ENGINE = InnoDB,
PARTITION p4 VALUES LESS THAN (2012) ENGINE = InnoDB,
PARTITION pM VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
1 row in set (0.00 sec)
mysql> explain partitions select * from rd where writetime = '2009-12-01';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | rd | p2_p2sp0 | ALL | NULL | NULL | NULL | NULL | 77 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
mysql> explain partitions select * from rd where writetime >= '2009-02-01' and writetime <= '2009-05-28';
+----+-------------+-------+---------------------------------------------------------------------------------------------------------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+---------------------------------------------------------------------------------------------------------------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | rd | p2_p2sp0,p2_p2sp1,p2_p2sp2,p2_p2sp3,p2_p2sp4,p2_p2sp5,p2_p2sp6,p2_p2sp7,p2_p2sp8,p2_p2sp9,p2_p2sp10,p2_p2sp11 | ALL | NULL | NULL | NULL | NULL | 77 | Using where |
+----+-------------+-------+---------------------------------------------------------------------------------------------------------------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
The first explain gives the correct partition p2_p2sp0, but the second one gives the wrong partitions (it s the whole 12 partitions of the 2009 year), the correct result should be p2_p2sp2,p2_p2sp3,p2_p2sp4,p2_p2sp5.
Could you please explain why?
I will submit the table dump to you.
Thanks,
Steven
[11 Jun 2010 6:26]
Sveta Smirnova
Thank you for the feedback. You use function MONTH, but according to http://dev.mysql.com/doc/refman/5.1/en/partitioning-pruning.html: Pruning can also be applied for tables partitioned on a DATE or DATETIME column when the partitioning expression uses the YEAR() or TO_DAYS() function. Note We plan to add pruning support in a future MySQL release for additional functions that act on a DATE or DATETIME value, return an integer, and are increasing or decreasing. So last case is not a bug. Report closed as "Can't repeat", because I can not repeat it with exactly same settings and package which you used and because problem is not repeatable for you with icc package.
Description: I am testing MySQL 5.5 partition pruning according to MySQL server 5.5 document: 17.4. Partition Pruning. --- Suppose that table t2, defined as shown here, is partitioned on a DATE column: CREATE TABLE t2 ( fname VARCHAR(50) NOT NULL, lname VARCHAR(50) NOT NULL, region_code TINYINT UNSIGNED NOT NULL, dob DATE NOT NULL ) PARTITION BY RANGE( YEAR(dob) ) ( PARTITION d0 VALUES LESS THAN (1970), PARTITION d1 VALUES LESS THAN (1975), PARTITION d2 VALUES LESS THAN (1980), PARTITION d3 VALUES LESS THAN (1985), PARTITION d4 VALUES LESS THAN (1990), PARTITION d5 VALUES LESS THAN (2000), PARTITION d6 VALUES LESS THAN (2005), PARTITION d7 VALUES LESS THAN MAXVALUE ); The following queries on t2 can make of use partition pruning: SELECT * FROM t2 WHERE dob = '1982-06-23'; SELECT * FROM t2 WHERE dob BETWEEN '1991-02-15' AND '1997-04-25'; SELECT * FROM t2 WHERE dob >= '1984-06-21' AND dob <= '1999-06-21' In the case of the last query, the optimizer can also act as follows: 1. Find the partition containing the low end of the range. YEAR('1984-06-21') yields the value 1984, which is found in partition d3. 2. Find the partition containing the high end of the range. YEAR('1999-06-21') evaluates to 1999, which is found in partition d5. 3. Scan only these two partitions and any partitions that may lie between them. In this case, this means that only partitions d3, d4, and d5 are scanned. The remaining partitions may be safely ignored (and are ignored). ---- I followed exactly the document steps (copy & paste) and wished I could get the stated partition returned, however very disappointingly, the results are totally different from the document. Could MySQL experts give me a explanation? Thanks, Steven stang@webtechwireless.com How to repeat: [root@dev-db1-1 ~]# mysql -uroot -p -hamdb1 quadrant Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 18 Server version: 5.5.4-m3-log MySQL Community Server (GPL) Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> mysql> CREATE TABLE t2 ( -> fname VARCHAR(50) NOT NULL, -> lname VARCHAR(50) NOT NULL, -> region_code TINYINT UNSIGNED NOT NULL, -> dob DATE NOT NULL -> ) -> PARTITION BY RANGE( YEAR(dob) ) ( -> PARTITION d0 VALUES LESS THAN (1970), -> PARTITION d1 VALUES LESS THAN (1975), PARTITION d3 VALUES LESS THAN (1985), PARTITION d4 VALUES LESS THAN (1990), -> PARTITION d2 VALUES LESS THAN (1980), -> PARTITION d3 VALUES LESS THAN (1985), -> PARTITION d4 VALUES LESS THAN (1990), -> PARTITION d5 VALUES LESS THAN (2000), -> PARTITION d6 VALUES LESS THAN (2005), -> PARTITION d7 VALUES LESS THAN MAXVALUE -> ); Query OK, 0 rows affected (0.52 sec) mysql> explain partitions SELECT * FROM t2 WHERE dob = '1982-06-23'; +----+-------------+-------+-------------------------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------------------------+------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | t2 | d0,d1,d2,d3,d4,d5,d6,d7 | ALL | NULL | NULL | NULL | NULL | 8 | Using where | +----+-------------+-------+-------------------------+------+---------------+------+---------+------+------+-------------+ 1 row in set (0.00 sec) mysql> explain partitions SELECT * FROM t2 WHERE dob BETWEEN '1991-02-15' AND '1997-04-25'; +----+-------------+-------+-------------------------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------------------------+------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | t2 | d0,d1,d2,d3,d4,d5,d6,d7 | ALL | NULL | NULL | NULL | NULL | 8 | Using where | +----+-------------+-------+-------------------------+------+---------------+------+---------+------+------+-------------+ 1 row in set (0.00 sec) mysql> explain partitions SELECT * FROM t2 WHERE dob = '1982-06-23'; +----+-------------+-------+-------------------------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------------------------+------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | t2 | d0,d1,d2,d3,d4,d5,d6,d7 | ALL | NULL | NULL | NULL | NULL | 8 | Using where | +----+-------------+-------+-------------------------+------+---------------+------+---------+------+------+-------------+ 1 row in set (0.00 sec)