Bug #9551 SHOW commands fail
Submitted: 1 Apr 2005 5:50 Modified: 10 May 2006 15:33
Reporter: Peter Brawley (Basic Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S1 (Critical)
Version:5.03/5.0.15-rc OS:Microsoft Windows (WinXP)
Assigned to: Alexey Botchkov

[1 Apr 2005 5:50] Peter Brawley
Description:
my.ini has the entry
  tmpdir=c:/temp
and the directory exists, but show database | tables | variables etc commands fail with:

"ERROR 1 (HY000): Can't create/write to file 'c:\temp\#sql_150_0.MYI' (Errcode: 17)"

The odd thing is: the file which the error message says MySQL can't write or create is there.

How to repeat:
With the server running
  run mysql client
or
  run mysqladministrator
or
  run sqlyog
or
  run phpmyadmin
[1 Apr 2005 16:44] Michael Kofler
Same here on Linux (RHEL 4)

show databases -->

ERROR 13 (HY000): Can't get stat of './mysqlmanager.sock'
[1 Apr 2005 17:00] Miguel Solorzano
I wasn't able to repeat:

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

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

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| parallax_kernel    |
| sqlbug             |
| test               |
+--------------------+
5 rows in set (0.00 sec)

mysql> show variables like "tmpdir";
+---------------+---------+
| Variable_name | Value   |
+---------------+---------+
| tmpdir        | c:/temp |
+---------------+---------+
1 row in set (0.00 sec)

mysql>

and the error you got is:

c:\mysql\bin>perror 17
OS error code  17:  File exists

and Windows not permits to write over existing file, then please
stop the server, delete that file in c:\temp and try again.

Thanks in advance.
[1 Apr 2005 17:19] Peter Brawley
Miguel,

>c:\mysql\bin>perror 17
>OS error code  17:  File exists

>and Windows not permits to write over existing file, then please
>stop the server, delete that file in c:\temp and try again.

Yes of course, one can stop the server, delete the file, restart, then the error recurs. If one adds more paths to the tmpdir variable and reboots the server, a couple of SHOW ... commands will execute, then the error will recur.

Since the behaviour can be temporarily changed by adding tmpdirs, and since all this occurs only with 5.03, not with previous MySQL releases, it is reasonable to assume that it has something to do with the 5.03 enhancement described under Change History in the 5.03 manual...

Security improvement: The server creates `.frm', `.MYD', `.MYI', `.MRG', `.ISD', and `.ISM' table files only if a file with the same name does not already exist. (Thanks to Stefano Di Paola stefano.dipaola@wisec.it for finding and informing us about this issue.)

... since it has been reported by a Linux user, it cannot be just a Windows setting, 

...and since it occurs on this box and not on yours, it is reasonable to assume that the bug may depend on another MySQL setting. But which one?

PB
[1 Apr 2005 17:27] Miguel Solorzano
Yes something in your settings can be the cause, could you please
provide your my.ini file for I try on my side ?

Thanks in advance.
[1 Apr 2005 17:35] Peter Brawley
Here is the my.ini file

# Example mysql config file.
# Copy this file to c:\my.cnf to set global options
# 
# One can use all long options that the program supports.
# Run the program with --help to get a list of available options

# ALL mysql clients
[client]
#password=my_password
port=3306
#socket=MySQL

# MySQL server
[mysqld]
port=3306
server_id=1
log_slow_queries=c:\mysql\data\slow.log
big_tables
exit-info=65535
init-rpl-role=master
log=myqrylog.txt
warnings
tmpdir=c:/temp;c:/tmp
log-bin=toshnb-bin

########## TEST SETTINGS ##########
bulk_insert_buffer_size=1000000
# disconnect-slave-event-count ONLY IF REPLICATION IS RUNNING

###################################

# RECENTLY ADDED
myisam_recover=BACKUP,FORCE
new

# Uncomment the following rows if you move the MySQL distribution to another
# location
#basedir = c:/mysql/
#datadir = c:/mysql/data/

# Uncomment the following if you are NOT using BDB tables
skip-bdb

# Uncomment the following if you are using Innobase tables
innodb_data_file_path = ibdata1:100M
innodb_data_home_dir = c:\mysql\data\innodb
innodb_log_group_home_dir = c:\mysql\data\innodb
innodb_log_arch_dir = c:\mysql\data\innodb
innodb_thread_concurrency=2
innodb_mirrored_log_groups=1
innodb_log_files_in_group=3
innodb_log_file_size=10M
innodb_log_buffer_size=1M
innodb_flush_log_at_trx_commit=1
innodb_log_archive=0
innodb_buffer_pool_size=8M
innodb_additional_mem_pool_size=2M
innodb_file_io_threads=4
innodb_lock_wait_timeout=50

[mysqldump]
quick
max_allowed_packet=16M
quote_names=0

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

#NO WORKEE
#sql_big_selects=0

[isamchk]
key_buffer=8M
sort_buffer=8M

[myisamchk]
key_buffer=8M
sort_buffer=8M

[mysqlhotcopy]
interactive-timeout

[WinMySqlAdmin]
Server=C:/mysql501/bin/mysqld-max.exe
#QueryInterval=10
[1 Apr 2005 18:02] Miguel Solorzano
I was able to repeat, however in your my.ini file you had the InnoDB
tablespace and log files inside a directory called InnoDB into the
data directory since MySQL handles directories in data directory
as databases I recommend you to change this. Anyway I changed
that setting to \mysql\data and continues the error, so I need to
investigate which setting is the cause.

Thank you for the feedback.
[1 Apr 2005 18:17] Miguel Solorzano
I verified the below key is the cause of the error:

exit-info=65535
[1 Apr 2005 18:42] Peter Brawley
Excellent! Perhaps the docs need a note that exit-info breaks SHOW. Thank you.
[1 Apr 2005 19:57] Miguel Solorzano
I wasn't able to repeat the issue on Linux.  Will test 5.0.4 Windows server.
[1 Apr 2005 20:23] Miguel Solorzano
Tested with Windows 5.0.4 and looks to be Windows specific issue
with exit-info=65535.
[1 Apr 2005 21:48] Peter Brawley
Somebody did report the bug on Linux so it might be a good idea to check it out with that user.
[4 Apr 2005 9:01] Michael Kofler
SHOW + Linux + SELinux

ok, I played around again, and the problem seems to be unrelated to the Windows problem

however, there seems to be a problem with SELinux instead; in detail:

my /etc/my.cnf is empty

mysql 5.0.3 + SHOW works without problems on SUSE 9.3 beta

it fails on Red Hat Enterprise Linux 4 ES

it works on Red Hat Enterprise Linux 4 ES if you 
switch off SELinux for mysqld:
  system-config-securitylevel
  panel SELinux
  disable option 'Enforcing'

another strange detail: MySQL 5.0.2 works on RHEL 4 ES even if SELinux is active; so some detail in 5.0.3 must have changed which conflicts with the SELinux rules of Red Hat

dmesg reports this error (one line) after trying to execute SHOW DATABASES:

audit(1112605037.621:0): avc:  denied  { getattr } for  
  pid=4462 exe=/usr/sbin/mysqld path=/var/lib/mysql/mysqlmanager.sock 
  dev=hdb9 ino=132310 scontext=root:system_r:mysqld_t 
  tcontext=root:object_r:mysqld_db_t tclass=sock_file
[4 Apr 2005 9:04] Michael Kofler
it is not neccessary to switch of SELinux entirely as shown in the previous comment; it suffices to switch of SELinux only for mysqld:

system-config-security
  dialog page 'SELinux'
  listbox 'Modify SELinux Policy'
  enable option
    'SELinux Service Protection|Disable SELinux protection fo mysqld daemon'

mysql must be restarted afterwards
[19 May 2005 18:21] Moshe Itah
The problem has nothing to do with "exit-info=65535".  I never used this option, and my application "causes" this error 8 time out of 10.  At startup my application issues over 100 SQL of the form "SHOW FULL COLUMNS FROM ...." and as stated above 8 out of 10 times, I get the error:

  Can't create/write to file D:\Temp\#sql_c38_0.MYI' (Errcode: 13)

I cannot use 5.0.3 or 5.0.4 !!
[4 Oct 2005 13:29] Mark Matthews
Please reverify.
[4 Oct 2005 16:00] Miguel Solorzano
Still only happens on Windows:

C:\mysql\bin>mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 5.0.15-rc-nt-log

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

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| test               |
+--------------------+
3 rows in set (0.00 sec)

mysql> show variables like "tmpdir";
ERROR 1 (HY000): Can't create/write to file 'c:\temp\#sql_8a0_0.MYI' (Errcode: 17)
mysql> exit
Bye

OS error code  17:  File exists

C:\mysql\bin>dir c:\temp
 O volume na unidade C não tem nome.
 O número de série do volume é 0465-CD10

 Pasta de c:\temp

04/10/2005  12:49    <DIR>          .
04/10/2005  12:49    <DIR>          ..
04/10/2005  12:49            10.968 #sql_8a0_0.MYD
04/10/2005  12:49             1.024 #sql_8a0_0.MYI
04/10/2005  12:46                 0 ib2
04/10/2005  12:46                 0 ib3
04/10/2005  12:46                 0 ib4
               5 arquivo(s)         11.992 bytes
               2 pasta(s) 20.054.859.776 bytes disponíveis

and commenting #exit-info=65535 on my.ini:

C:\mysql\bin>mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.15-rc-nt-log

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

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| test               |
+--------------------+
3 rows in set (0.00 sec)

mysql> show variables like "tmpdir";
+---------------+---------+
| Variable_name | Value   |
+---------------+---------+
| tmpdir        | c:/temp |
+---------------+---------+
1 row in set (0.02 sec)

mysql>
[28 Oct 2005 7:01] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/31595
[20 Nov 2005 2:57] Paul Dubois
Noted in 5.0.16 changelog.
[15 Mar 2006 16:35] Miguel Solorzano
Looking bug: http://bugs.mysql.com/bug.php?id=18225 I verified
that now the server crash. Please see that bug for back trace.
[10 May 2006 15:33] Trudy Pelzer
Reviewing related bug #18225, I see that it has been marked
"can't repeat". Since that bug caused this one to be re-opened,
I'm closing this bug again. If the problem still persists, please 
file an updated bug report. Thank you.
[24 May 2006 18:39] SHAWN HOGAN
This actually just happened to me as well on a non-Windows machine.

Mac OS X Server 10.3.8 with MySQL 5.0.21...

Can't create/write to file '/tmp/#sql_17a3_0.MYD' (Errcode: 17)

x2:/tmp root# ls -l
total 0
-rw-rw----  1 mysql  wheel    0 24 May 11:03 #sql_17a3_0.MYD

It seemed to be specific to one table being accessed.  I deleted the existing file in the /tmp folder (didn't restart mysqld or anything), and it was fine.
[24 Feb 2007 9:35] Adnan Haider
The problem for me was McCaffee enterprise. Uninstall it and it works fine.

More help on the following link:

http://bugs.mysql.com/bug.php?id=25872
[12 Apr 2007 15:39] Ben J
For whatever it's worth at this point, McAfee VirusScan (specifically, "VirusScan Enterprise + AntiSpyware Enterprise 8.5.0i") was causing this problem for me on a Windows XP machine, i586. As soon as I disabled the On-Access Scanner (OAS), by right-clicking the tray icon and choosing "Disable On-Access Scan", the problem ceased to occur.
[12 Apr 2007 16:25] Ben J
In case anybody else runs into this problem, disabling McAfee obviously isn't the best solution, as the system will be put at risk. The proper workaround is to move MySQL's temp folder (by adding/changing the "tempdir" directive in my.ini) to an arbitrary directory OTHER THAN C:\WINDOWS\TEMP, and then exclude that directory from within McAfee's On-Access Scanner options.

To do this, right-click the McAfee tray icon and choose "On-Access Scan Properties". In the ensuing dialog, choose "All Processes" from the left column, select the "Detection" tab at the top of the dialog, and then press the "Exclusions" button at the bottom of that tab. The rest is self-explanatory.

Good luck.
[24 May 2008 10:27] Gerard Nijboer
I had the problem with Errcode:17, too.
As I changed the tempdir and security settings of it, nothing worked.
After finding out the problem is caused by McAfee and its scanner, I did what is described above and excluded my tempdir.
This worked out well perfectly and I can now run big queries again.
Thanks!