MySQL Bugs: #4845: Query failed (select count(id) cnt,title from udata where anketa=3 and qid=33 )

Bug #4845	Query failed (select count(id) cnt,title from udata where anketa=3 and qid=33 )
Submitted:	1 Aug 2004 11:44	Modified:	1 Aug 2004 21:34
Reporter:	Eugene Suprun	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: MyISAM storage engine	Severity:	S1 (Critical)
Version:	4.1.3 beta	OS:	Windows (Windows 2003 server)
Assigned to:		CPU Architecture:	Any

Description:
While executing query:

select count(id) cnt,title from udata where anketa=3 and qid=33  group by title;

on table:

mysql> describe udata;
+-----------+--------------+------+-----+---------+----------------+
| Field     | Type         | Null | Key | Default | Extra          |
+-----------+--------------+------+-----+---------+----------------+
| id        | int(3)       |      | PRI | NULL    | auto_increment |
| anketa_id | int(3)       | YES  |     | NULL    |                |
| title     | varchar(255) | YES  |     | NULL    |                |
| qid       | int(3)       | YES  |     | NULL    |                |
| val       | int(3)       | YES  |     | NULL    |                |
| anketa    | int(3)       | YES  |     | NULL    |                |
| SID       | int(3)       | YES  |     | NULL    |                |
+-----------+--------------+------+-----+---------+----------------+
7 rows in set (0.00 sec)
get an error:

ERROR 1 (HY000): Can't create/write to file 'd:\windows\temp\#sql_5cc_0.MYI' (Er
rcode: 2)

How to fix this bug? Plaese, send the suggestions to suprun@mail.zp.ua!

How to repeat:
Always %-((((

Suggested fix:
Unknown

table

Attachment: udata.zip (application/x-zip-compressed, text), 40.54 KiB.

Mysqld_nt uses directory %windir%/temp/ for its temporary files, but it's not always correct, 'cause of some of "clean-upers" deletes all contents of this diectory and also %windir%/temp. More efficiently is to use GetTempPath() API function to get temp location instead. 

So, I've solved this BUG on my system.

Some clarifications how the MySQL server handles the temporary directory:

The server try to use the temporary directory that belong the below
environment variables:

  /* Get default temporary directory */
  opt_mysql_tmpdir=getenv("TMPDIR");	/* Use this if possible */
#if defined( __WIN__) || defined(OS2)
  if (!opt_mysql_tmpdir)
    opt_mysql_tmpdir=getenv("TEMP");
  if (!opt_mysql_tmpdir)
    opt_mysql_tmpdir=getenv("TMP");
#endif

Then in your case the OS has the  %windir%/temp/ pointing to any of
the above variables that should be the same with GetTempPath() with
the exception that GetTempPath() doesn't verify the TMPDIR directory.

You can define the MySQL server's temporary directory how showed below:

c:\mysql\bin>mkdir d:\mytemp

c:\mysql\bin>mysqld-nt --tmpdir=d:\mytemp

c:\mysql\bin>mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.0.20a-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show variables like "tmpdir";
+---------------+------------+
| Variable_name | Value      |
+---------------+------------+
| tmpdir        | d:\mytemp\ |
+---------------+------------+
1 row in set (0.00 sec)

mysql>