Bug #33990 Lost connection to MySQL server when using date > or < in where clause
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)
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.
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)