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:
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

[8 May 2008 9:45] Zunil John
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 :
"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
[8 May 2008 9:47] Zunil John
Also, in the same sequence I am executing lot of queries having other kinds of conditions. Only this one throws error
[8 May 2008 9:51] MySQL Verification Team
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.
[8 May 2008 9:54] Zunil John
My MySQL server my.ini file

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

[8 May 2008 10:04] Zunil John
Screen shot of  properties of temp directory of MySQL

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

[8 May 2008 10:37] Zunil John
Another screen shot of MySQL temp directory permissions

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

[8 May 2008 10:46] Zunil John
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
[8 May 2008 11:25] MySQL Verification Team
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.
[8 Jun 2008 23:00] Bugs System
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".