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

[1 Aug 2004 11:44] Eugene Suprun
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
[1 Aug 2004 11:46] Eugene Suprun
table

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

[1 Aug 2004 12:15] Eugene Suprun
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.
[1 Aug 2004 21:34] MySQL Verification Team
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>