MySQL Bugs: #14325: show command fails with errcode 17 - can't create/write to file ... MYD or MYI

Bug #14325	show command fails with errcode 17 - can't create/write to file ... MYD or MYI
Submitted:	26 Oct 2005 10:17	Modified:	26 Oct 2005 14:14
Reporter:	Christian Belz	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.15-nt	OS:	Windows (Windows Server 2003)
Assigned to:		CPU Architecture:	Any

Description:
After installing MySQL 5.0.15-nt on my Windows Server 2003 i tried to view with MySQL Administrator 1.1.4 to view the existing Schematas.
This worked a few times but then i get the Error:

Error while fetching Schemata Table Status.
The following error occured: Can't create/write to file
'C:\WINDOWS\TEMP\#sql_c34_0.MYI' (Errcode: 17) (1)

And sometimes the same Message but then a .MYD file.

Sometimes i get a other message:

Error while fetching Schema Table Status.
The following error occured: Table 'COLUMNS' is read only (1036)

In http://bugs.mysql.com/bug.php?id=9551 is a solution to a similar problem. But i don't have the setting in my my.ini.

Tried to view the schema with the console, too: 
show full column from mysql;

Same Error messages appear.

How to repeat:
Happens directly after installing and switching schematas in the Administrator 5-10 times (slowly).

Thank you for a problem report. What is the result of the following command

show variables like 'tmpdir%';

on your system?

As for how to repeat:

"Happens directly after installing and switching schematas in the Administrator
5-10 times (slowly)."

I installed 5.0.15-nt this morning, and now keep switching schemata in Administrator 1.1.4, and still can not repeat. So, please, describe the exact sequence of actions I should perform to repeat this incorrect behaviour of the server. Better if you simply list commands to execute in mysql command line client, but how many times to switch schemata and what do you mean by "slowly" (how many seconds to wait), is OK too.

I Just reinstalled again (with deleting the data folder).

Here the result of the requested command:

mysql> show variables like 'tmpdir%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| tmpdir        |       |
+---------------+-------+
1 row in set (0.00 sec)

mysql>

Works fine!

Now i tried to reproduce the error in the command line:

mysql> SHOW tables FROM mysql;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| func                      |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| host                      |
| proc                      |
| procs_priv                |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+
17 rows in set (0.00 sec)

mysql> SHOW FULL COLUMNS FROM mysql;
ERROR 1146 (42S02): Table 'mysql.mysql' doesn't exist

Strange, this got the same error as the MyAdmin before.

Now i will try to use the myAdmin:

i switch to schema "information_schema"		=> worked
about 15 seconds later to "mysql"		=> worked
again 15 seconds later to "test"		=> worked / is empty
10 seconds later to "mysql"			=> Worked
10 seconds later to "information_schema"	=> Worked
10 seconds later to "mysql"			=> Worked
10 seconds later to "information_schema"	=> Worked
10 seconds later to "mysql"			=> Worked
10 seconds later to "information_schema"	=> Worked
5 seconds later to "mysql"			=> Worked
5 seconds later to "information_schema"		=> Worked
5 seconds later to "mysql"			=> Worked
5 seconds later to "information_schema"		=> Worked
5 seconds later to "mysql"			=> Worked
5 seconds later to "information_schema"		=> Worked
5 seconds later to "mysql"			=> Worked
1 seconds later to "information_schema"		=> Worked
1 seconds later to "mysql"			=> Worked
1 seconds later to "information_schema"		=> Worked
1 seconds later to "mysql"			=> Worked
1 seconds later to "information_schema"		=> Worked
1 seconds later to "mysql"			=> Worked

Ok this is working so far.

Now i created a new User with All Global Privileges in MyAdmin and deleted it again, in remind to use the console... .
To check if everything is still okey i checked the schemas again and NOW i get the error after 3 times switching from "mysql" to "information_schema" with 1-2 seconds delay.

Maybe a problem with rights?

mysql> show variables like 'tmpdir%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| tmpdir        |       |
+---------------+-------+
1 row in set (0.00 sec)

mysql>

Still workes fine.

Ok, it has nothing to do with the user!

I just stoped the service, Deleted the Data Folder and installed with a Repair installation again.
Now i started the Service again and waited 5 mins for the Tables to create.

Started again like before and now i got the error after 2 switches!
"mysql"  => OK
"mysql" => "Information_schema"  => OK
"Information_schema" => "mysql"  => Error!

Thank you for keep trying to produce a repeatable test case.

As for the SHOW command giving error message, this is not a bug. Please, read the manual (http://dev.mysql.com/doc/refman/5.0/en/show-columns.html). The correct syntax is:

SHOW [FULL] COLUMNS FROM tbl_name [FROM db_name] [LIKE 'pattern']

There is no mysql table in your current database (mysql), so you got an error message.

As for your test with Administrator, I tried to create user, add him privileges to each table in each schema, and drop user, and still keep switching without aby error messages (what was it, by the way)? I'll try to install into the fresh directory tomorrow (*mine left from 5.0.13-rc), but I do not think it is the right way to make a repeatable test case... We are trying to find a server bug, in any case. So, please, try to repeat using mysql client and inform about the results.

And more testing:
I deinstalled the server again. Deleted the Data folder.
Installed the server. Waited for the Data folder to be fully created.

Tried again to switch between the Schemas. Worked in about 95% the tries.
When i am to fast in switching i get one of the same error as in first post.

Now i created a new SA-User via Console:
mysql> GRANT ALL PRIVILEGES ON *.* TO 'remrot'@'%'
    -> IDENTIFIED BY 'some_pass' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)

In MyAdmin (still open and connected as root) i switched again a while. Works so far.

Started on a client the MyAdmin and connected to the server with the new SA-User. Switched again and it worked.

Now i closed the Myadmin on the Server.

Switched again on the client.
ERROR! ERROR! ERROR! ERROR! 
95% Errors and arround. 5% working switches... .

In console:
mysql> show full columns from host like 'host';
ERROR 1 (HY000): Can't create/write to file 'C:\WINDOWS\TEMP\#sql_7dc_0.MYI' (Errcode: 17)

To My Server System:
Its a Microsoft Small Business Server 2003 with Exchange.
Xeon 3Ghz
2 GB RAM

I will install MySQL5 on a Win 2000 Machine and try to reproduce the error.

I think found the problem!

Norman Antivirus was scanning all files and blocked them while scanning.
Now after deactivating Norman i can't reproduce the Error.

Sorry, don't know it was running on the Server, too.

Hi,

  The problem is the permission of the temporary folder. If you don't have specified any temporary folder please edit the my.cnf and spcify "tmp_dir = /tmp" if you run a linux server. Then restart the mysql service.

  If the problem persists look if any program is locking this folder.

Usually I dismiss out off hand any forum response of the kind "it's probably your anti-virus software" (along with those of the kind "you've probably got a virus"), but in this case it really was the on-access scanner of my client's McAfee anti-virus scanner conflicting with MySQL's attempts to read/write temporary files.

My suspicion was aroused because of the error code 17 ("File exists") despite the file clearly not existing.  I imagine McAfee was locking the file as soon as it was created, and then deleting it.