Bug #1604 Creating/dropping an index causes a ERROR 2013: Lost connection to MySQL server
Submitted: 20 Oct 2003 4:34 Modified: 28 Oct 2003 20:04
Reporter: [ name withheld ] Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server Severity:S1 (Critical)
Version:4.0.15 OS:Advanced Windows 2000
Assigned to: CPU Architecture:Any

[20 Oct 2003 4:34] [ name withheld ]
Description:
We have an OLAP application currently runs on MSSQL. The applications main purpose is to generate profit reports about the clients of a bank. Monthly updates takes too much time and I wanted to test the stability and speed of MySQL vs. MSSQL.

The table creation script (direct port from MSSQL):
CREATE TABLE QUANTIS_MKR (
	SUBE decimal(4, 0) NOT NULL ,
	MUSTERI_NO decimal(8, 0) NOT NULL ,
	MKR_AY decimal(2, 0) NOT NULL ,

	GRUP_KODU decimal(5, 0) NOT NULL ,
	SEKTOR_KODU char (2) ,
	MUSTERI_ADI char (15) ,
	AKTIF_KODU char (1) ,
	PERSONEL_KODU char (1) ,
	MUST_TEMSILCISI decimal(5, 0) NOT NULL ,
	TL_KREDI decimal(9, 0) NOT NULL ,
	YP_KREDI decimal(9, 0) NOT NULL ,
	CM_KREDI decimal(9, 0) NOT NULL ,
	KSVD_KREDI decimal(9, 0) NOT NULL ,
	ORVD_KREDI decimal(9, 0) NOT NULL ,
	PRB_KREDI decimal(9, 0) NOT NULL ,
	TCMB_REES decimal(9, 0) NOT NULL ,
	EXIM decimal(9, 0) NOT NULL ,
	KASA_KOLAYLIGI decimal(9, 0) NOT NULL ,
	DVZ_END_KREDI decimal(9, 0) NOT NULL ,
	TL_VADELI decimal(9, 0) NOT NULL ,
	TL_VADESIZ decimal(9, 0) NOT NULL ,
	YP_VADELI decimal(9, 0) NOT NULL ,
	YP_VADESIZ decimal(9, 0) NOT NULL ,
	YP_SWAP decimal(9, 0) NOT NULL ,
	GNK_RISK decimal(9, 0) NOT NULL ,
	KOMISYON decimal(9, 0) NOT NULL ,
	KAMBIYO decimal(9, 0) NOT NULL ,
	EMLAK_KREDISI decimal(9, 0) NOT NULL ,
	OTOMOBIL_KREDISI decimal(9, 0) NOT NULL ,
	TAKAS_CIK_BNK_CEK decimal(9, 0) NOT NULL ,
	TUKETICI_KREDISI decimal(9, 0) NOT NULL ,
	DIGER_GELIR decimal(9, 0) NOT NULL ,
	MASRAF decimal(9, 0) NOT NULL ,
	TL_MAL_KAY decimal(9, 0) NOT NULL ,
	YP_MAL_KAY decimal(9, 0) NOT NULL ,
	TL_NETFAIZ decimal(9, 0) NOT NULL ,
	YP_NETFAIZ decimal(9, 0) NOT NULL ,
	TOPLAM_NETFAIZ decimal(9, 0) NOT NULL ,
	MUSTERI_KARI decimal(18, 0) NOT NULL ,
	KAR_YUZDESI decimal(7, 2) NOT NULL ,
	IT_KKVM_DOS decimal(9, 0) NOT NULL ,
	IT_KKVM_TRS decimal(9, 0) NOT NULL ,
	IT_KKVM_AVAL_DOS decimal(9, 0) NOT NULL ,
	IT_KKVM_AVAL_TRS decimal(9, 0) NOT NULL ,
	IT_VM_DOS decimal(9, 0) NOT NULL ,
	IT_VM_TRS decimal(9, 0) NOT NULL ,
	IT_KKMM_DOS decimal(9, 0) NOT NULL ,
	IT_KKMM_TRS decimal(9, 0) NOT NULL ,
	IT_KKMM_AVAL_DOS decimal(9, 0) NOT NULL ,
	IT_KKMM_AVAL_TRS decimal(9, 0) NOT NULL ,
	IT_MM_DOS decimal(9, 0) NOT NULL ,
	IT_MM_TRS decimal(9, 0) NOT NULL ,
	IT_KKLC_DOS decimal(9, 0) NOT NULL ,
	IT_KKLC_TRS decimal(9, 0) NOT NULL ,
	IT_VADELI_LC_DOS decimal(9, 0) NOT NULL ,
	IT_VADELI_LC_TRS decimal(9, 0) NOT NULL ,
	IT_SIGHT_LC_DOS decimal(9, 0) NOT NULL ,
	IT_SIGHT_LC_TRS decimal(9, 0) NOT NULL ,
	IH_VM_DOS decimal(9, 0) NOT NULL ,
	IH_VM_TRS decimal(9, 0) NOT NULL ,
	IH_MM_DOS decimal(18, 0) NOT NULL ,
	IH_MM_TRS decimal(9, 0) NOT NULL ,
	IH_PESIN_ODEME_DOS decimal(9, 0) NOT NULL ,
	IH_PESIN_ODEME_TRS decimal(9, 0) NOT NULL ,
	IH_LC_DOS decimal(9, 0) NOT NULL ,
	IH_LC_TRS decimal(9, 0) NOT NULL ,
	IH_TEYITLI_LC_DOS decimal(9, 0) NOT NULL ,
	IH_TEYITLI_LC_TRS decimal(9, 0) NOT NULL ,
	GK_CIKTI decimal(9, 0) NOT NULL ,
	GK_GIRDI decimal(9, 0) NOT NULL ,
	YP_KISA_VADELI decimal(9, 0) NOT NULL ,
	YP_ORTA_UZUN_VADELI decimal(9, 0) NOT NULL ,
	YP_EXIM decimal(9, 0) NOT NULL ,
	YP_DEK decimal(9, 0) NOT NULL ,
	TL_TEMINAT decimal(9, 0) NOT NULL ,
	YP_TEMINAT decimal(9, 0) NOT NULL ,
	YP_VADELI_USD decimal(9, 0) NOT NULL ,
	YP_VADESIZ_USD decimal(9, 0) NOT NULL ,
	TL_BLOKE decimal(9, 0) NOT NULL ,
	YP_BLOKE decimal(9, 0) NOT NULL ,
	COLLECTION decimal(9, 0) NOT NULL ,
	DISBURSEMENT decimal(9, 0) NOT NULL ,
	FAALIYET decimal(3, 0) NOT NULL ,
	RATING decimal(1, 0) NOT NULL ,
	IT_PESIN_ODEME_DOS decimal(9, 0) NOT NULL ,
	IT_PESIN_ODEME_TRS decimal(9, 0) NOT NULL ,
	DEK_EMLAK decimal(9, 0) NOT NULL ,
	DEK_OTOMOBIL decimal(9, 0) NOT NULL ,
	DEK_TUKETICI decimal(9, 0) NOT NULL ,
	MASRAF_KRDP decimal(9, 0) NOT NULL ,
	DIREKT_SATIS_TEMSILCISI decimal(5, 0) NOT NULL ,
	LC_RISK decimal(9, 0) NOT NULL ,
	KABUL_AVAL_RISK decimal(9, 0) NOT NULL ,
	YP_TEM_MEK_RISK decimal(9, 0) NOT NULL ,
	OZEL_HESAP_KREDISI decimal(9, 0) NOT NULL ,
	KIK_TL_OTO decimal(9, 0) NOT NULL ,
	KIK_TL_EMLAK decimal(9, 0) NOT NULL ,
	KIK_TL_TUKETICI decimal(9, 0) NOT NULL ,
	KIK_DEK_OTO decimal(9, 0) NOT NULL ,
	KIK_DEK_EMLAK decimal(9, 0) NOT NULL ,
	KIK_DEK_TUKETICI decimal(9, 0) NOT NULL ,
	HISSE_SENEDI_KREDISI decimal(9, 0) NOT NULL ,
	KREDI_KARTI_HACIM decimal(9, 0) NOT NULL ,
	KREDI_KARTI_KAR decimal(9, 0) NOT NULL ,
	AKTIF_MUS char (1) ,
	AYLIK_BRUT_KAR decimal(9, 0) NOT NULL 
);

(Table type is MyISAM)

The main table for reports (QUANTIS_MKR) has more than 100 fields and currently holds 4 million records. 

I modified the input file of a month for MySQL LOAD DATA statement. After running LOAD DATA I consistently got "server connection lost" errors. Later I changed the my.ini file with the my-large. With this way I ran LOAD DATA successfully. Then I created the composite index for the first three rows. I got disconnected from the server again. But the index seems to be created. I tried to create index specifically for MUSTERI_NO. I again disconnected, but again index seems to be created. Now anytime I create an index I get a "server connection lost" but the index seems to be created. The same applies for dropping an index too.

How to repeat:
DROP INDEX MUSTERI_IND ON QUANTIS_MKR;

-- or --

CREATE INDEX MUSTERI_IND ON QUANTIS_MKR(MUSTERI_NO);
[28 Oct 2003 11:12] Dean Ellis
I cannot repeat this problem using the latest release of MySQL 4.0 (4.0.16).

Some timeouts for Windows clients were changed in 4.0.16.  Consider upgrading and see if the problem persists.

Thank you
[28 Oct 2003 20:04] MySQL Verification Team
The advice of Dean for to upgrade for the latest 4.0.16 release
is right, in that version (4.0.15) was introduced for the Windows
version the socket timeout and the default ones for net_read_timeout
and net_write_timeout are respectively (30/60 seconds) which makes
the client to disconnect when the server takes more than these times
for to send the retrieve data. This bug was fixed in the 4.0.16
release. Also you have the option for to use prior clients than
4.0.15 for to avoid the disconnect issue.

This behavior was reported through the bug:

http://bugs.mysql.com/bug.php?id=1370
[31 Oct 2003 1:39] [ name withheld ]
Miguel is right. The problem is related with time out values. 
MySQL client 4.0.16 runs without a problem.

Thank you.