Bug #15762 losing connection on queries after creating view
Submitted: 14 Dec 2005 23:34 Modified: 18 Jun 2006 21:25
Reporter: Martin Lučka Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.0.16-1 OS:Linux (debian, unstable)
Assigned to: CPU Architecture:Any

[14 Dec 2005 23:34] Martin Lučka
Description:
create or replace view ObsahKosika as (select Tovar.Nazov, Tovar.Cena ,PrvokKosika.Mnozstvo*Tovar.Cena, PrvokKosika.Tovar_ID from PrvokKosika join Tovar on PrvokKosika.Tovar_ID = Tovar.ID group by Tovar_ID)

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

immediately again...

mysql> select * from obsah_kosika;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    2
Current database: ebizmod

+----------+---------------------------------------+----------+-------+--------------+
| Tovar_ID | Nazov                                 | Mnozstvo | Cena  | Cena_Celkova |
+----------+---------------------------------------+----------+-------+--------------+
|        2 | PC Seven Student                      |        1 | 11540 |        11540 |
|       18 | Barebone ASUS PUNDIT - P2 - AE2       |        4 |  6150 |        24600 |
|       20 | Server ASUS AP130 - E1 AI4            |        3 | 12510 |        37530 |
|       23 | Server ASUS AP140R - E1 AA2           |        1 | 17170 |        17170 |
|       24 | Server ASUS R120 - E3 - PA2           |        2 | 19210 |        38420 |
|      551 | GENIUS MaxFire G-08XU USB             |        2 |   168 |          336 |
|      884 | PCIe Serial ATA II Card               |        3 |   943 |         2829 |
|      886 | STLabs PCI card  SATA RAID 4porty     |        4 |  1273 |         5092 |
|      888 | Manli 1394 PCI card 3+1               |        7 |   349 |         2443 |
|      901 | STLabs PCI card 4serial 1paralel port |       35 |   943 |        33005 |
+----------+---------------------------------------+----------+-------+--------------+
10 rows in set (0.05 sec)

and again...

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

view on this 2 tables : 

CREATE TABLE PrvokKosika (
  Kosik_Zakaznik_login VARCHAR(10) NOT NULL,
  Tovar_ID INTEGER UNSIGNED NOT NULL,
  Mnozstvo FLOAT NOT NULL,
  PRIMARY KEY(Kosik_Zakaznik_login),
  INDEX PrvokKosika_FKIndex1(Tovar_ID),
  INDEX PrvokKosika_FKIndex2(Kosik_Zakaznik_login),
  FOREIGN KEY(Tovar_ID)
    REFERENCES Tovar(ID)
      ON DELETE NO ACTION
      ON UPDATE NO ACTION,
  FOREIGN KEY(Kosik_Zakaznik_login)
    REFERENCES Kosik(Zakaznik_login)
      ON DELETE NO ACTION
      ON UPDATE NO ACTION
);

CREATE TABLE Tovar (
  ID INTEGER UNSIGNED NOT NULL,
  Kategoria_ID INTEGER UNSIGNED NOT NULL,
  Nazov VARCHAR(50) NULL,
  Cena INTEGER UNSIGNED NULL,
  Popis VARCHAR(500) NULL,
  Jednotka VARCHAR(10) NULL,
  Obrazok VARCHAR(255) NULL,
  Zaruka INTEGER UNSIGNED NULL,
  DatumVyroby DATE NOT NULL,
  PRIMARY KEY(ID),
  INDEX Tovar_FKIndex1(Kategoria_ID),
  FOREIGN KEY(Kategoria_ID)
    REFERENCES Kategoria(ID)
      ON DELETE NO ACTION
      ON UPDATE NO ACTION
);

How to repeat:
try to create a similar view and then select from this view
[15 Dec 2005 11:46] Aleksey Kishkin
Hi! wasn't able to reproduce it:

mysql>  select * from ObsahKosika;
Empty set (0.00 sec)

mysql>  select * from ObsahKosika;
Empty set (0.00 sec)

mysql>  select * from ObsahKosika;
Empty set (0.00 sec)

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

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

Did you install mysql from mysql.com or from debian distributive?
[16 Jan 2006 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".
[18 May 2006 21:25] Valeriy Kravchuk
Please, try to repeat with a newer version, 5.0.21, using MySQL binaries, and inform about the results.
[18 Jun 2006 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".