MySQL Bugs: #9551: SHOW commands fail

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:	Windows (WinXP)
Assigned to:	Alexey Botchkov	CPU Architecture:	Any

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

Same here on Linux (RHEL 4)

show databases -->

ERROR 13 (HY000): Can't get stat of './mysqlmanager.sock'

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.

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

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.

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

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.

I verified the below key is the cause of the error:

exit-info=65535

Excellent! Perhaps the docs need a note that exit-info breaks SHOW. Thank you.

I wasn't able to repeat the issue on Linux.  Will test 5.0.4 Windows server.

Tested with Windows 5.0.4 and looks to be Windows specific issue
with exit-info=65535.

Somebody did report the bug on Linux so it might be a good idea to check it out with that user.

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

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

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

Please reverify.

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>

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

Noted in 5.0.16 changelog.

Looking bug: http://bugs.mysql.com/bug.php?id=18225 I verified
that now the server crash. Please see that bug for back trace.

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.

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.

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

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.

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.

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!