| 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: | |
| 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: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.
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.