MySQL Bugs: #36589: IN query with MySQL 5 throws error randomly

Bug #36589	IN query with MySQL 5 throws error randomly
Submitted:	8 May 2008 9:45	Modified:	8 Jun 2008 11:25
Reporter:	Zunil John	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.18-nt	OS:	Windows (Windows 2000 SP4)
Assigned to:		CPU Architecture:	Any
Tags:	Can't create/write to file, JConnector, Mysql 5

Description:
When I execute a query which has IN parameters in the where condition, randomly I am getting an error java.sql.SQLException: Can't create/write to file 'D:\mysql5temp\#sql_3f8_0.MYI' (Errcode: 13).

What all I tried
1. Based on some group messages, I changed the temperory directory from TMP folder to a specific directory created by me and give full permission to everyone
2. Checked Mcafee Virus scan logs to see whether virus scanner blocks it.

Environment I am using
1. OS : Windows 2000 server with SP4
2. MySQL server version 5.0.18-nt
3. MySQL J Connector mysql-connector-java-3.1.13-bin.jar
4. DB Type : InnoDB
5. Table structure :
CREATE TABLE "TEST_TABLE" (   
"BIGINT1" bigint(20) NOT NULL, "CHAR1" char(10) default NULL,  "VARCHAR1" varchar(50) default NULL,                      "NCHAR1" char(10) character set utf8 default NULL,  "NVARCHAR1" varchar(50) character set utf8 default NULL, PRIMARY KEY  ("BIGINT1")   )

6. Query :
SELECT * FROM "TEST_TABLE" WHERE "VARCHAR1" IN ('Test1','Test2','Test3') AND "BIGINT1" IN (1,2,3)

How to repeat:

1. Write a java program using JDBC which connects to a mysql server(5.0.18)
2. Executea  query as given above in the program
3. Randomly you will get this error

Also, in the same sequence I am executing lot of queries having other kinds of conditions. Only this one throws error

Zunil, please try disabling Antivirus software and retest.  Ultimately you should set tmpdir=c:\mysqltmp in my.ini and just restrict antivirus from scanning that directory (to avoid system from being vulnerable).

Then, 5.0.18 is too old, and you should consider using 5.0.51b and check if problem repeats itself.

My MySQL server my.ini file

Attachment: my.ini (application/octet-stream, text), 9.11 KiB.

Screen shot of properties of temp directory of MySQL

Attachment: sc1.JPG (image/pjpeg, text), 38.82 KiB.

Another screen shot of MySQL temp directory permissions

Attachment: sc2.JPG (image/pjpeg, text), 40.16 KiB.

Hi Shane Bester , before coming hee I did a search to find out the issue. Then I disabled virus scan and even changed the mysql 5 temp folder(eralier it was TMP) and give full permissions.
Virus scanner is not locking this folder. I checked virus scan logs
But still the issue is here

Also another observation is I am having a similiar query with IN parameters(same 2 in Parameters), but both are integers. That query never gives any issue. When ever I get error, only the abiove mentioned query throws error. In a test run I am executing around 100 queries with various kinds of parameters including LIKE nad Nested queries. But all that runs fine except this one.

I have more than 2 GB disk space available in that temp folder drive.

Presently upgrading to 5.0.51b is not that easy because we tested the entire product in this version of mysql. Without proper testing we cannot upgarde which may take quitea lot of effort

Could you please consider to upgrade or at least to test with the latest
released version because if actually there is a bug the fix will be done
in the current source. Thanks in advance.

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".