| Bug #20222 | server cant write to temporary file when running query on table with lot of rec | ||
|---|---|---|---|
| Submitted: | 2 Jun 2006 10:26 | Modified: | 2 Jun 2006 10:52 |
| Reporter: | Marino Simic | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server | Severity: | S2 (Serious) |
| Version: | 5.0.19-nt-max 64 bit | OS: | Windows (Windows Xp 64 bit) |
| Assigned to: | CPU Architecture: | Any | |
[2 Jun 2006 10:29]
Valeriy Kravchuk
Thank you for a problem report. Looks like you just have not enough free space on disk d:. Please, send dir D:\ command results.
[2 Jun 2006 10:31]
Marino Simic
you must add 'MAISTRA' under ID_PARTNERA, 'V.R. room' as ID_OBJEKTA and have some dates fall in august, GODINA '2006'. other fields are not important
[2 Jun 2006 10:33]
Marino Simic
Directory of D:\Program Files (x86)\Microsoft Visual Studio 8\VC
24.05.2006 23:11 <DIR> .
24.05.2006 23:11 <DIR> ..
24.05.2006 23:07 <DIR> atlmfc
24.05.2006 23:11 <DIR> bin
24.05.2006 23:09 <DIR> ce
24.05.2006 23:07 <DIR> crt
24.05.2006 23:11 <DIR> include
24.05.2006 23:10 <DIR> lib
24.05.2006 23:07 <DIR> PlatformSDK
24.05.2006 23:09 <DIR> redist
24.05.2006 23:11 <DIR> VCAddClass
24.05.2006 23:11 <DIR> VCContextItems
23.09.2005 03:56 159 vcEmptyTestProject.vsz
24.05.2006 23:11 <DIR> VCNewItems
24.05.2006 23:11 <DIR> vcpackages
24.05.2006 23:11 <DIR> VCProjectDefaults
24.05.2006 23:11 <DIR> vcprojectitems
24.05.2006 23:11 <DIR> vcprojects
24.05.2006 23:10 <DIR> VCResourceTemplates
24.05.2006 23:09 <DIR> vcsmartdeviceprojectdefaults
23.09.2005 09:20 1.247 vcvarsall.bat
24.05.2006 23:11 <DIR> VCWizards
2 File(s) 1.406 bytes
20 Dir(s) 3.050.283.008 bytes free
D:\Program Files (x86)\Microsoft Visual Studio 8\VC>
I have 3Gb free... 38k records that make 3gb? hm maybe... but i dunno...
[2 Jun 2006 10:52]
Marino Simic
Changed location of the Temp directory on the raid drive with more space available and now works! Withdrawing the bug submission and apologizing! Cheers
Description: Running the following query causes this error: Error writing file 'D:\WINDOWS\TEMP\MY22' (Errcode: 28) (123,359 sec) (every time i run it... and didnt happen when there were fewer records (now there are 38k)) set @a:=0; set @b:=0; select xx.g1, xx.idr1, xx.idp1, xx.ido1, xx.tp1, xx.od1, xx.do1, count(xx.idr2) as lvl, xx.do1 - xx.od1 as duzina from (select * from (select @a := @a + 1 as rn1, r.godina g1, r.id_rezervacije idr1, r.datum_od od1, r.datum_do do1, r.id_partnera idp1, r.id_objekta ido1, r.id_tipa_smj_jed tp1 from rezervacija_glava r where r.id_partnera = 'MAISTRA' and r.id_objekta = 'V.R. room' and ((date (r.datum_od) >= '2006-08-01' and date (r.datum_od) <= '2006-08-31') or (date (r.datum_od) < '2006-08-01' and date (r.datum_do) > '2006-08-01')) order BY r.DATUM_OD, r.datum_do - r.datum_od desc, @a) rez1 left outer join (select @b := @b + 1 as rn2, r2.godina g2, r2.id_rezervacije idr2, r2.datum_od od2, r2.datum_do do2, r2.id_partnera idp2, r2.id_objekta ido2, r2.id_tipa_smj_jed tp2 from rezervacija_glava r2 where r2.id_partnera = 'MAISTRA' and r2.id_objekta = 'V.R. room' and ((date (r2.datum_od) >= '2006-08-01' and date (r2.datum_od) <= '2006-08-31') or (date (r2.datum_od) < '2006-08-01' and date (r2.datum_do) > '2006-08-01')) order by r2.DATUM_OD, r2.datum_do - r2.datum_od desc, @b) rez2 on rez1.rn1 > rez2.rn2 and rez1.g1 = rez2.g2 and rez2.do2 > rez1.od1 and rez1.idp1 = rez2.idp2 and rez1.ido1 = rez2.ido2 and rez1.tp1 = rez2.tp2 order by rez1.tp1, rez1.od1, rez1.idr1) xx group by xx.g1, xx.idp1, xx.ido1, xx.tp1, xx.idr1, xx.od1, xx.do1 order by xx.g1, xx.idp1, xx.ido1, xx.tp1, xx.od1, xx.idr1, xx.do1 How to repeat: Create a following table and add arounf 40k records, then run the query: CREATE TABLE `rezervacija_glava` ( `ID_REZERVACIJE` bigint(20) NOT NULL auto_increment, `GODINA` int(11) NOT NULL, `ID_KUP_CJENIKA` varchar(50) NOT NULL, `ID_PROD_CJENIKA` varchar(50) NOT NULL, `ID_PARTNERA` varchar(10) NOT NULL, `ID_OBJEKTA` varchar(20) NOT NULL, `ID_TIPA_SMJ_JED` varchar(10) NOT NULL, `ID_TIPA_SMJ_JED_KNJ` varchar(10) NOT NULL, `ID_KUPCA` varchar(10) NOT NULL, `ID_TIPA_REZERVACIJE` varchar(1) NOT NULL, `OPIS` varchar(25) default NULL, `DATUM_DO` date NOT NULL, `DATUM_OD` date NOT NULL, `BROJ_DANA_ZA_NAPLATU` int(11) default NULL, `NAZIV_KONTAKTA` varchar(35) default NULL, `PREZIME_KONTAKTA` varchar(25) default NULL, `ID_DRZAVE` varchar(3) default NULL, `POSTANSKI_BROJ` varchar(8) default NULL, `GRAD` varchar(20) default NULL, `ADRESA` varchar(25) default NULL, `TELEFON` varchar(15) default NULL, `KOMENTAR` varchar(255) default NULL, `INDIVIDUALAC` tinyint(1) default '0', `STATUS` varchar(2) default '0Z', `DATUM_REZERVACIJE` date default NULL, `DATUM_IZMJENE` datetime default NULL, `KORISNIK_KREIRAO` varchar(20) default NULL, `KORISNIK_IZMJENIO` varchar(20) default NULL, PRIMARY KEY (`ID_REZERVACIJE`,`GODINA`), KEY `DATUM_DO` (`DATUM_DO`,`DATUM_OD`), KEY `ID_PARTNERA` (`ID_PARTNERA`,`ID_OBJEKTA`,`ID_TIPA_SMJ_JED`), KEY `GODINA_ID` (`GODINA`,`ID_REZERVACIJE`), KEY `GODINA` (`GODINA`), KEY `ID_PARTNERA_ONLY` (`ID_PARTNERA`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; Suggested fix: ? tried to tweak some server parameters but no go...