Bug #16336 Lost connection to MySQL server during query
Submitted: 10 Jan 2006 12:10 Modified: 12 Jan 2006 8:00
Reporter: Eugen Schülter Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.0.16-standard-log OS:Linux (SUSE Linux 9.3)
Assigned to: CPU Architecture:Any

[10 Jan 2006 12:10] Eugen Schülter
Description:
I get a ERROR 2013 (HY000): Lost connection to MySQL server during query
after selecting data from a view. (There is a already a similar bug found with debian).

Here are the tables and view:
CREATE TABLE compounds (
  comp			CHAR (4)		NOT NULL PRIMARY KEY,	#compound abbreviation
  class			CHAR (6)		NOT NULL,		#class e.g. PI
  target		CHAR (6)		NOT NULL,		#target	e.g. PRO

  updID			SMALLINT UNSIGNED	NOT NULL,
  updated		TIMESTAMP,
  insID			SMALLINT UNSIGNED	NOT NULL,
  inserted		DATETIME		NOT NULL

) Engine=InnoDB COMMENT='List of ART compounds e.g. APV';
CREATE TABLE comp2drugs (
  comp			CHAR (4)		NOT NULL,		#references compounds
  drugID		SMALLINT UNSIGNED	NOT NULL,		#references drugs

  updID			SMALLINT UNSIGNED	NOT NULL,
  updated		TIMESTAMP,
  insID			SMALLINT UNSIGNED	NOT NULL,
  inserted		DATETIME		NOT NULL,

  PRIMARY KEY			(comp,drugID),
  KEY		I_Comp		(comp),
  KEY		I_Drug		(drugID)
) Engine=InnoDB COMMENT='Relation compounds to drugs';
CREATE TABLE drugs (	
  drugID		SMALLINT UNSIGNED	NOT NULL PRIMARY KEY,
  brand_name		VARCHAR (40)		NOT NULL DEFAULT '',	#comercial name e.g. Combivir
  generic		VARCHAR (40)		NOT NULL DEFAULT '',	#e.g. zidovudine+lamivudine
  abbrev		VARCHAR (25)		NOT NULL DEFAULT '',	#e.g. ZDV+3TC
  exp_code		VARCHAR (40)		NOT NULL DEFAULT '',	#experimental code
  producer		VARCHAR (40)		NOT NULL DEFAULT '',	#producer e.g. GlaxoSmithKline
  combi			ENUM('N','Y')		NOT NULL DEFAULT 'N',	#Y if consists of more then one compound
  aprvoved		ENUM('N','Y')		NOT NULL DEFAULT 'Y',	#Y if aproved
  aprv_date		DATE			NULL,

  updID			SMALLINT UNSIGNED	NOT NULL,
  updated		TIMESTAMP,
  insID			SMALLINT UNSIGNED	NOT NULL,
  inserted		DATETIME		NOT NULL
) Engine=InnoDB COMMENT='List of ART drugs e.g. Kivexa';

DROP VIEW IF EXISTS vCompDrugsSingle;
CREATE VIEW vCompDrugsSingle AS SELECT
	a.comp, c.generic, c.brand_name, c.abbrev, c.producer, a.target, a.class
FROM
	compounds a, comp2drugs b, drugs c
WHERE
	a.comp=b.comp AND b.drugID=c.drugID AND c.combi='N'
ORDER BY
	a.target, a.comp
;

How to repeat:
select * from vCompDrugsSingle;
Alternating gives the data and the Error 2013.
[10 Jan 2006 12:37] Valeriy Kravchuk
Thank you for a problem report. Sorry, but I was not able to repeat it with latest 5.0.19-BK:

mysql> CREATE TABLE compounds (
    ->   comp   CHAR (4)  NOT NULL PRIMARY KEY, #compound abbreviation
    ->   class   CHAR (6)  NOT NULL,  #class e.g. PI
    ->   target  CHAR (6)  NOT NULL,  #target e.g. PRO
    ->   updID   SMALLINT UNSIGNED NOT NULL,
    ->   updated  TIMESTAMP,
    ->   insID   SMALLINT UNSIGNED NOT NULL,
    ->   inserted  DATETIME  NOT NULL
    -> ) Engine=InnoDB COMMENT='List of ART compounds e.g. APV';
Query OK, 0 rows affected (0.29 sec)

mysql> CREATE TABLE comp2drugs (
    ->   comp   CHAR (4)  NOT NULL,  #references compounds
    ->   drugID  SMALLINT UNSIGNED NOT NULL,  #references drugs
    ->   updID   SMALLINT UNSIGNED NOT NULL,
    ->   updated  TIMESTAMP,
    ->   insID   SMALLINT UNSIGNED NOT NULL,
    ->   inserted  DATETIME  NOT NULL,
    ->   PRIMARY KEY   (comp,drugID),
    ->   KEY  I_Comp  (comp),
    ->   KEY  I_Drug  (drugID)
    -> ) Engine=InnoDB COMMENT='Relation compounds to drugs';
Query OK, 0 rows affected (0.14 sec)

mysql> CREATE TABLE drugs (
    ->   drugID  SMALLINT UNSIGNED NOT NULL PRIMARY KEY,
    ->   brand_name  VARCHAR (40)  NOT NULL DEFAULT '', #comercial name e.g. Combivir
    ->   generic  VARCHAR (40)  NOT NULL DEFAULT '', #e.g. zidovudine+lamivudine
    ->   abbrev  VARCHAR (25)  NOT NULL DEFAULT '', #e.g. ZDV+3TC
    ->   exp_code  VARCHAR (40)  NOT NULL DEFAULT '', #experimental code
    ->   producer  VARCHAR (40)  NOT NULL DEFAULT '', #producer e.g. GlaxoSmithKline
    ->   combi   ENUM('N','Y')  NOT NULL DEFAULT 'N', #Y if consists of more th
en one compound
    ->   aprvoved  ENUM('N','Y')  NOT NULL DEFAULT 'Y', #Y if aproved
    ->   aprv_date  DATE   NULL,
    ->   updID   SMALLINT UNSIGNED NOT NULL,
    ->   updated  TIMESTAMP,
    ->   insID   SMALLINT UNSIGNED NOT NULL,
    ->   inserted  DATETIME  NOT NULL
    -> ) Engine=InnoDB COMMENT='List of ART drugs e.g. Kivexa';
Query OK, 0 rows affected (0.12 sec)

mysql> DROP VIEW IF EXISTS vCompDrugsSingle;
CQuery OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE VIEW vCompDrugsSingle AS SELECT
    ->  a.comp, c.generic, c.brand_name, c.abbrev, c.producer, a.target, a.class
    -> FROM
    ->  compounds a, comp2drugs b, drugs c
    -> WHERE
    ->  a.comp=b.comp AND b.drugID=c.drugID AND c.combi='N'
    -> ORDER BY
    ->  a.target, a.comp;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from vCompDrugsSingle;
Empty set (0.02 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.0.19    |
+-----------+
1 row in set (0.00 sec)

So, please, try to repeat with the newer version, 5.0.18, now generally available, and inform about the results.
[10 Jan 2006 16:49] Heikki Tuuri
Eugen,

does mysqld print anything to the .err log?

Regards,

Heikki
[11 Jan 2006 12:48] Eugen Schülter
OK. I've tried the version 5.0.18 and wasn't able to reproduce the error with this version. Unfortunately I have no log files from the 5.0.16 version but I think it's not a issue since 5.0.18 seems to work fine!

Great job!

P.S. Will we have database aliases (synonyms) in a future version?
[11 Jan 2006 13:00] Eugen Schülter
I've overseen the following lines in the error log (5.0.16) that are written after

mysql> select * from vCompDrugsMulti;
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql>                                                                

mysql_err.log:
Number of processes running now: 0
060111 13:55:11  mysqld restarted

But as stated before with 5.0.18 no problems..
[12 Jan 2006 8:00] Valeriy Kravchuk
The problem is solved in 5.0.18 now generally available.