MySQL Bugs: #33990: Lost connection to MySQL server when using date > or

Bug #33990	Lost connection to MySQL server when using date > or < in where clause
Submitted:	22 Jan 2008 18:57	Modified:	24 Apr 2008 23:40
Reporter:	Dave Schultz	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.45 SUSE MySQL RPM	OS:	Other (SUSE)
Assigned to:		CPU Architecture:	Any

Description:
THIS WORKS:
mysql> select elementType, switch, eNum, eSubNum as sector, pageHeader as Page,
    -> day as Day, date_sub(curdate(), interval 30 day) as month, hour as Hour, record
    -> FROM pagelog2
    -> WHERE switch = 'WB2'
    -> and day = '2008-01-22'
    -> AND tech = 'cdma'
    -> ORDER BY day desc, hour desc limit 10;
+-------------+--------+------+--------+---------------------------------------------+------------+------------+------+--------+
| elementType | switch | eNum | sector | Page                                        | Day        | month      | Hour | record |
+-------------+--------+------+--------+---------------------------------------------+------------+------------+------+--------+
| CELL        | WB2    |  209 |      0 | CELL_209(ALRM 1ITEM  13:25)TKT:13183 on WB2 | 2008-01-22 | 2007-12-23 |   13 |   9218 |
| CELL        | WB2    |  204 |      0 | CELL_204(ALRM 1ITEM  13:40)TKT:12916 on WB2 | 2008-01-22 | 2007-12-23 |   13 |   9251 |
| CELL        | WB2    |  143 |      0 | CELL_143(ALRM 1ITEM  13:10)TKT:13169 on WB2 | 2008-01-22 | 2007-12-23 |   13 |   9194 |
| CELL        | WB2    |  141 |      0 | CELL_141(ALRM 1ITEM  13:25)TKT:13027 on WB2 | 2008-01-22 | 2007-12-23 |   13 |   9219 |
| CELL        | WB2    |  193 |      0 | CELL_193(ALRM 1ITEM  12:10)TKT:13084 on WB2 | 2008-01-22 | 2007-12-23 |   12 |   9072 |
| CELL        | WB2    |  122 |      0 | CELL_122(ALRM 1ITEM  12:40)TKT:13120 on WB2 | 2008-01-22 | 2007-12-23 |   12 |   9124 |
| CELL        | WB2    |  273 |      0 | CELL_273(ALRM 1ITEM  11:25)TKT:13025 on WB2 | 2008-01-22 | 2007-12-23 |   11 |   8950 |
| CELL        | WB2    |  204 |      0 | CELL_204(ALRM 1ITEM  11:40)TKT:12916 on WB2 | 2008-01-22 | 2007-12-23 |   11 |   8995 |
| CELL        | WB2    |  187 |      0 | CELL_187(ALRM 1ITEM  11:25)TKT:13026 on WB2 | 2008-01-22 | 2007-12-23 |   11 |   8951 |
| CELL        | WB2    |  152 |      0 | CELL_152(ALRM 1ITEM  11:40)TKT:12915 on WB2 | 2008-01-22 | 2007-12-23 |   11 |   8994 |
+-------------+--------+------+--------+---------------------------------------------+------------+------------+------+--------+
10 rows in set (0.00 sec)

THIS BREAKS:
mysql> select elementType, switch, eNum, eSubNum as sector, pageHeader as Page,
    -> day as Day, date_sub(curdate(), interval 30 day) as month, hour as Hour, record
    -> FROM pagelog2
    -> WHERE switch = 'WB2'
    -> and day >= '2008-01-22'
    -> AND tech = 'cdma'
    -> ORDER BY day desc, hour desc limit 10;
ERROR 2013 (HY000): Lost connection to MySQL server during query

mysql> describe pagelog2;
+--------------+--------------+------+-----+---------+----------------+
| Field        | Type         | Null | Key | Default | Extra          |
+--------------+--------------+------+-----+---------+----------------+
| record       | int(11)      | NO   | PRI | NULL    | auto_increment |
| day          | date         | YES  | MUL | NULL    |                |
| hour         | int(11)      | YES  |     | NULL    |                |
| min          | int(11)      | YES  |     | NULL    |                |
| tech         | varchar(10)  | YES  |     | NULL    |                |
| class        | varchar(20)  | YES  |     | NULL    |                |
| nwTime       | datetime     | YES  | MUL | NULL    |                |
| type         | varchar(20)  | YES  |     | NULL    |                |
| switch       | varchar(20)  | YES  |     | NULL    |                |
| elementType  | varchar(20)  | YES  |     | NULL    |                |
| eNum         | int(11)      | YES  |     | NULL    |                |
| eSubnum      | int(11)      | YES  |     | NULL    |                |
| cluster1     | int(11)      | YES  |     | NULL    |                |
| cluster2     | int(11)      | YES  |     | NULL    |                |
| cluster3     | int(11)      | YES  |     | NULL    |                |
| cluster4     | int(11)      | YES  |     | NULL    |                |
| cluster5     | int(11)      | YES  |     | NULL    |                |
| cluster6     | int(11)      | YES  |     | NULL    |                |
| priority     | int(11)      | YES  |     | NULL    |                |
| pageHeader   | varchar(255) | YES  |     | NULL    |                |
| pageDetail   | blob         | YES  |     | NULL    |                |
| ticketID     | int(11)      | YES  | MUL | NULL    |                |
| pageCriteria | blob         | YES  |     | NULL    |                |
| reminder     | int(11)      | YES  |     | NULL    |                |
+--------------+--------------+------+-----+---------+----------------+
24 rows in set (0.00 sec)

mysql> show index from pagelog2;
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table    | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| pagelog2 |          0 | PRIMARY  |            1 | record      | A         |        7727 |     NULL | NULL   |      | BTREE      |         |
| pagelog2 |          1 | time     |            1 | nwTime      | A         |        NULL |     NULL | NULL   | YES  | BTREE      |         |
| pagelog2 |          1 | day      |            1 | day         | A         |        NULL |     NULL | NULL   | YES  | BTREE      |         |
| pagelog2 |          1 | day      |            2 | hour        | A         |        NULL |     NULL | NULL   | YES  | BTREE      |         |
| pagelog2 |          1 | day      |            3 | elementType | A         |        NULL |     NULL | NULL   | YES  | BTREE      |         |
| pagelog2 |          1 | day      |            4 | eNum        | A         |        NULL |     NULL | NULL   | YES  | BTREE      |         |
| pagelog2 |          1 | day      |            5 | eSubnum     | A         |        NULL |     NULL | NULL   | YES  | BTREE      |         |
| pagelog2 |          1 | ticket   |            1 | ticketID    | A         |        NULL |     NULL | NULL   | YES  | BTREE      |         |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
8 rows in set (0.00 sec)

How to repeat:
THIS BREAKS:
mysql> select elementType, switch, eNum, eSubNum as sector, pageHeader as Page,
    -> day as Day, date_sub(curdate(), interval 30 day) as month, hour as Hour, record
    -> FROM pagelog2
    -> WHERE switch = 'WB2'
    -> and day >= '2008-01-22'
    -> AND tech = 'cdma'
    -> ORDER BY day desc, hour desc limit 10;
ERROR 2013 (HY000): Lost connection to MySQL server during query

mysql> describe pagelog2;
+--------------+--------------+------+-----+---------+----------------+
| Field        | Type         | Null | Key | Default | Extra          |
+--------------+--------------+------+-----+---------+----------------+
| record       | int(11)      | NO   | PRI | NULL    | auto_increment |
| day          | date         | YES  | MUL | NULL    |                |
| hour         | int(11)      | YES  |     | NULL    |                |
| min          | int(11)      | YES  |     | NULL    |                |
| tech         | varchar(10)  | YES  |     | NULL    |                |
| class        | varchar(20)  | YES  |     | NULL    |                |
| nwTime       | datetime     | YES  | MUL | NULL    |                |
| type         | varchar(20)  | YES  |     | NULL    |                |
| switch       | varchar(20)  | YES  |     | NULL    |                |
| elementType  | varchar(20)  | YES  |     | NULL    |                |
| eNum         | int(11)      | YES  |     | NULL    |                |
| eSubnum      | int(11)      | YES  |     | NULL    |                |
| cluster1     | int(11)      | YES  |     | NULL    |                |
| cluster2     | int(11)      | YES  |     | NULL    |                |
| cluster3     | int(11)      | YES  |     | NULL    |                |
| cluster4     | int(11)      | YES  |     | NULL    |                |
| cluster5     | int(11)      | YES  |     | NULL    |                |
| cluster6     | int(11)      | YES  |     | NULL    |                |
| priority     | int(11)      | YES  |     | NULL    |                |
| pageHeader   | varchar(255) | YES  |     | NULL    |                |
| pageDetail   | blob         | YES  |     | NULL    |                |
| ticketID     | int(11)      | YES  | MUL | NULL    |                |
| pageCriteria | blob         | YES  |     | NULL    |                |
| reminder     | int(11)      | YES  |     | NULL    |                |
+--------------+--------------+------+-----+---------+----------------+
24 rows in set (0.00 sec)

mysql> show index from pagelog2;
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table    | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| pagelog2 |          0 | PRIMARY  |            1 | record      | A         |        7727 |     NULL | NULL   |      | BTREE      |         |
| pagelog2 |          1 | time     |            1 | nwTime      | A         |        NULL |     NULL | NULL   | YES  | BTREE      |         |
| pagelog2 |          1 | day      |            1 | day         | A         |        NULL |     NULL | NULL   | YES  | BTREE      |         |
| pagelog2 |          1 | day      |            2 | hour        | A         |        NULL |     NULL | NULL   | YES  | BTREE      |         |
| pagelog2 |          1 | day      |            3 | elementType | A         |        NULL |     NULL | NULL   | YES  | BTREE      |         |
| pagelog2 |          1 | day      |            4 | eNum        | A         |        NULL |     NULL | NULL   | YES  | BTREE      |         |
| pagelog2 |          1 | day      |            5 | eSubnum     | A         |        NULL |     NULL | NULL   | YES  | BTREE      |         |
| pagelog2 |          1 | ticket   |            1 | ticketID    | A         |        NULL |     NULL | NULL   | YES  | BTREE      |         |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
8 rows in set (0.00 sec)

THIS ALSO WORKS (removed the 'desc' from the order by clause):

select elementType, switch, eNum, eSubNum as sector, pageHeader as Page,
day as Day, date_sub(curdate(), interval 30 day) as month, hour as Hour, record 
FROM pagelog2 
WHERE switch = 'WB2'
and day >= '2008-01-22'
AND tech = 'cdma'
ORDER BY day, hour desc limit 10;

THE PROBLEM SEEMS TO BE THE COMBINATION OF < or > ON A DATE FIELD IN THE WHERE CLAUSE, COMBINED WITH A 'desc' IN THE ORDER BY CLAUSE ON THAT DATE COLUMN.

THIS ALSO WORKS (descended order using a concat of the date field):

select elementType, switch, eNum, eSubNum as sector, pageHeader as Page,
day as Day, date_sub(curdate(), interval 30 day) as month, hour as Hour, record 
FROM pagelog2 
WHERE switch = 'WB2'
and day >= '2008-01-22'
AND tech = 'cdma'
ORDER BY concat(day,'-') desc, hour desc limit 10;

Thank you for a problem report. Please, try to repeat with a newer version, 5.0.51 at least, and inform about the results.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

We still need feedback if this also occurs on version 5.0.51a

Sorry - no way for me to do that now.

Thank you for the feedback.

I was not able to repeat described behavior with test data. Could you please provide dump of table pagelog2 and your configuration file?

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".