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

[22 Jan 2008 18:57] Dave Schultz
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)
[22 Jan 2008 19:10] Dave Schultz
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;
[22 Jan 2008 19:33] Valeriy Kravchuk
Thank you for a problem report. Please, try to repeat with a newer version, 5.0.51 at least, and inform about the results.
[23 Feb 2008 0:00] Bugs System
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".
[4 Mar 2008 12:42] Susanne Ebrecht
We still need feedback if this also occurs on version 5.0.51a
[4 Mar 2008 19:19] Dave Schultz
Sorry - no way for me to do that now.
[24 Mar 2008 23:40] Sveta Smirnova
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?
[25 Apr 2008 23:00] Bugs System
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".

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.058 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.