Bug #5237 select query causes ERROR 2013 but only if queried table is empty

• Submitted: 26 Aug 2004 17:50
• Modified: 27 Aug 2004 14:59
• Reporter: Desmond Campbell
• Status: Can't repeat
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: Ver 11.16 Distrib 3.23.49, for unknown-l
• OS: Linux (linux - debian woody)
• CPU Architecture: Any

[26 Aug 2004 17:50] Desmond Campbell

Description:
I have a select query which causes
  ERROR 2013: Lost connection to MySQL server during query
This doesn't cause mysql to fall over.
Any other query subsequently run is fine albeit with a
  ERROR 2006: MySQL server has gone away
  No connection. Trying to reconnect...
reported before the query results.

Yet when one dummy record is put in the queried table the same select query runs without fault.

$ mysql -V
mysql  Ver 11.16 Distrib 3.23.49, for unknown-linux-gnu (alphaev6)

How to repeat:
Below is mysql output demonstrating this.
At the end of this I have included the 
  show create table
  and table description.

mysql> select * from answers;
Empty set (0.01 sec)

mysql> select * from questionnaires;
+--------------+-----------+-----------+------------+---------------------+
| instrumentId | subjectId | studyWeek | pageId     | dtInsert            |
+--------------+-----------+-----------+------------+---------------------+
| DR           | ADV-90099 |         1 | 1093530708 | 2004-08-26 15:31:58 |
| GAS          | ADV-90099 |         1 | 1093530686 | 2004-08-26 15:31:36 |
+--------------+-----------+-----------+------------+---------------------+
2 rows in set (0.00 sec)

mysql> select * from answers where instrumentId = 'DR' and subjectId = 'ADV-90099' and studyWeek = 1 and pageId = 1093530727;
ERROR 2013: Lost connection to MySQL server during query

mysql> select * from questionnaires;
ERROR 2006: MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    1
Current database: gendep

+--------------+-----------+-----------+------------+---------------------+
| instrumentId | subjectId | studyWeek | pageId     | dtInsert            |
+--------------+-----------+-----------+------------+---------------------+
| DR           | ADV-90099 |         1 | 1093530708 | 2004-08-26 15:31:58 |
| GAS          | ADV-90099 |         1 | 1093530686 | 2004-08-26 15:31:36 |
+--------------+-----------+-----------+------------+---------------------+
2 rows in set (0.19 sec)

mysql> select * from answers;
Empty set (0.01 sec)

mysql> insert into answers (questionId, instrumentId, subjectId) values('dummy', 'dummy', 'dummy' );
Query OK, 1 row affected (0.04 sec)

mysql> select * from answers;
+--------+------------+--------------+-----------+-----------+------------+--------+---------+------------+
| pageId | questionId | instrumentId | subjectId | studyWeek | unanswered | answer | inputer | whenInput  |
+--------+------------+--------------+-----------+-----------+------------+--------+---------+------------+
|      0 | dummy      | dummy        | dummy     |         0 | True       |        |         | 0000-00-00 |
+--------+------------+--------------+-----------+-----------+------------+--------+---------+------------+
1 row in set (0.00 sec)

mysql> select * from answers where instrumentId = 'DR' and subjectId = 'ADV-90099' and studyWeek = 1 and pageId = 1093530727;
Empty set (0.06 sec)

mysql> select * from questionnaires;
+--------------+-----------+-----------+------------+---------------------+
| instrumentId | subjectId | studyWeek | pageId     | dtInsert            |
+--------------+-----------+-----------+------------+---------------------+
| DR           | ADV-90099 |         1 | 1093530708 | 2004-08-26 15:31:58 |
| GAS          | ADV-90099 |         1 | 1093530686 | 2004-08-26 15:31:36 |
+--------------+-----------+-----------+------------+---------------------+
2 rows in set (0.00 sec)

mysql> show create table answers;
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            |
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| answers | CREATE TABLE `answers` (
  `pageId` bigint(20) NOT NULL default '0',
  `questionId` varchar(20) NOT NULL default '',
  `instrumentId` varchar(10) NOT NULL default '',
  `subjectId` varchar(9) NOT NULL default '',
  `studyWeek` int(11) NOT NULL default '0',
  `unanswered` enum('True','False') NOT NULL default 'True',
  `answer` varchar(20) NOT NULL default '',
  `inputer` varchar(50) NOT NULL default '',
  `whenInput` date NOT NULL default '0000-00-00',
  PRIMARY KEY  (`pageId`,`questionId`,`instrumentId`,`subjectId`,`studyWeek`)
) TYPE=BerkeleyDB |
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.03 sec)

mysql>
mysql> desc answers;
+--------------+----------------------+------+-----+------------+-------+
| Field        | Type                 | Null | Key | Default    | Extra |
+--------------+----------------------+------+-----+------------+-------+
| pageId       | bigint(20)           |      | PRI | 0          |       |
| questionId   | varchar(20)          |      | PRI |            |       |
| instrumentId | varchar(10)          |      | PRI |            |       |
| subjectId    | varchar(9)           |      | PRI |            |       |
| studyWeek    | int(11)              |      | PRI | 0          |       |
| unanswered   | enum('True','False') |      |     | True       |       |
| answer       | varchar(20)          |      |     |            |       |
| inputer      | varchar(50)          |      |     |            |       |
| whenInput    | date                 |      |     | 0000-00-00 |       |
+--------------+----------------------+------+-----+------------+-------+
9 rows in set (0.00 sec)

This is the command I actually used to create the table:-
CREATE TABLE answers (pageId BIGINT NOT NULL, questionId VARCHAR(20) NOT NULL, instrumentId VARCHAR(10) NOT NULL, subjectId CHAR(9) NOT NULL, studyWeek INT NOT NULL, unanswered ENUM( 'True', 'False' ) NOT NULL, answer VARCHAR(20) NOT NULL, inputer VARCHAR(50) NOT NULL, whenInput DATE NOT NULL, PRIMARY KEY ( pageId, questionId, instrumentId, subjectId, studyWeek ) ) TYPE=BDB;

[26 Aug 2004 20:17] Hartmut Holzgraefe

May i ask you to add the create statement for the questionaire table, too?

[27 Aug 2004 11:34] Desmond Campbell

As requested here is the create questionnaire table info. I include the 
  the command I actually used
  show create table

Further to this bug. I discovered it not through the command line mysql interface prompt but as a result of a php page invoking the query as part of web page generation. It returned ERROR 2013. 
It affected any other mysql clients as well though, because the next query run at a command line mysql interface prompt returned 
 ERROR 2006: MySQL server has gone away
 No connection. Trying to reconnect...
before executing the query perfectly normally.

The create table command I actually used
CREATE TABLE questionnaires (instrumentId VARCHAR(10) NOT NULL, subjectId CHAR(9) NOT NULL, studyWeek INT NOT NULL, pageId BIGINT NOT NULL, dtInsert DATETIME NOT NULL, PRIMARY KEY ( instrumentId, subjectId, studyWeek ) ) TYPE=BDB;

mysql> show create table questionnaires;
+----------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table          | Create Table                                                                                                                                                                                                                                                                                                                                                    |
+----------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| questionnaires | CREATE TABLE `questionnaires` (
  `instrumentId` varchar(10) NOT NULL default '',
  `subjectId` varchar(9) NOT NULL default '',
  `studyWeek` int(11) NOT NULL default '0',
  `pageId` bigint(20) NOT NULL default '0',
  `dtInsert` datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (`instrumentId`,`subjectId`,`studyWeek`)
) TYPE=BerkeleyDB |
+----------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>

regards

Desmond

[27 Aug 2004 14:59] Hartmut Holzgraefe

I can't repeat this with 3.23.57 on linux
with neither PHP nor the command line client.