MySQL Bugs: #28606: 5.1.18 mysql_upgrade fails on Windows

Bug #28606	5.1.18 mysql_upgrade fails on Windows
Submitted:	22 May 2007 23:28	Modified:	30 Apr 2009 1:46
Reporter:	Peter Brawley (Basic Quality Contributor)	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Installing	Severity:	S3 (Non-critical)
Version:	5.1.18. 5.1.19	OS:	Windows (W2KPRO SP4)
Assigned to:	Daniel Fischer	CPU Architecture:	Any
Tags:	Contribution, qc

Description:
mysql_upgrade.exe fails:

F:\Program Files\MySQL\MySQL Server 5.1\bin>mysql_upgrade
Looking for 'mysql.exe' in: F:\Program Files\MySQL\MySQL Server 5.1\bin\mysql.ex
e
Looking for 'mysqlcheck.exe' in: F:\Program Files\MySQL\MySQL Server 5.1\bin\mys
qlcheck.exe
FATAL ERROR: Failed to create temporary file for defaults

How to repeat:
Invoke mysql_upgrade.exe

It is hard to tell if running mysql_fix_privilege_tables.sql is an effective workaround: that script is dated 26 Feb 2007, so the user has no way of determining if the script is up-to-date.

Thank you for a problem report. Please, send more details: my.ini file used in older installation, exact Windows version, results of:

dir F:

W2KPRO SP4, %windir%=f:\winnt

my.ini:

# Example mysql config file.
# Copy this file to f:\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]
port=3306

# MySQL server
[mysqld]
basedir = f:/Program Files/MySQL/MySQL Server 5.1/
datadir = d:/MySQL/data/
port=3306
server-id=1
init-rpl-role=master
log=myqrylog.txt
log_warnings
tmpdir=f:/temp;f:/tmp
log-bin=bigmutha-bin
local-infile
myisam_recover=BACKUP,FORCE

# LET VS2005 SERVER EXPLORER USE MYSQL
# sql_mode=ANSI_QUOTES

# event-scheduler 5.1.11 SERVER MISINTERPRETS FOLLOWING LINE AS EVENT SCHEDULER ARGUMENT!
# disconnect-slave-event-count ONLY IF REPLICATION IS RUNNING

# INNODB
innodb_data_file_path = ibdata1:100M
innodb_data_home_dir = d:\mysql\data
innodb_log_group_home_dir = d:\mysql\data
innodb_log_arch_dir = d:\mysql\data
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              5.1.18 WILL NOT LOAD WITH THIS
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
prompt=\h.\d>
no-auto-rehash

[myisamchk]
key_buffer=8M
sort_buffer=8M

[mysqlhotcopy]
interactive-timeout

Thank you for the feedback.

Please also check location of system temporary directory. To do it, please, click Start,
then right-click My Computer, then find Advanced tab and click Environment
button.

Please paste value of TMP and TEMP for system user.

Also check user MySQL running as. You can find it in Control
Panel->Administartion->Services

Also try to add tmpdir setting into [client] section of my.ini file and say us if it solves the problem.

TEMP %USERPROFILE%\Local Settings\Temp
TMP  %USERPROFILE%\Local Settings\Temp

%USERPROFILE% is F:\Documents and Settings\Administrator.
%USERPROFILE%\Local Settings\Temp exists.

There is no "Running As" column in Administration Services; the LogonAs column shows 'LocalSystem'.

Adding a tmp dir setting in the client setting of my.ini elicits this error report: mysql_upgrade: unknown variable 'tmpdir=f:/temp;f:/tmp'

The problem appears to be that the run_query function in client/mysql_upgrade.c:438 passes a NULL as the 2nd argument (temp directory) to create_temp_file.

This works OK on Unix, but will fail on Windows.  
The problem exists with MySQL 5.0.42 too.

This corrects the problem in 5.0.42 on Windows (client/mysql_upgrade.c line 436)
-------------------------------------------
#ifdef __WIN__
  char tmp_dir[FN_REFLEN];
  GetTempPath(sizeof(tmp_dir), tmp_dir);
#else
  char *tmp_dir = NULL;
#endif
  DBUG_ENTER("run_query");
  DBUG_PRINT("enter", ("query: %s", query));
  if ((fd= create_temp_file(query_file_path, tmp_dir,
                            "sql", O_CREAT | O_SHARE | O_RDWR,
                            MYF(MY_WME))) < 0)
    die("Failed to create temporary file for defaults");
-------------------------------------------

Contributed patch:  http://lists.mysql.com/internals/34701

Thank you for the feedback.

I could not repeat described behaviour with 5.1.19. Please upgrade, try with it and inform us if bug still exists or not.

mysql_upgrade.exe still fails on Windows (Win2k) with 5.1.19-beta from the Community Download site:

> mysql_upgrade.exe
Looking for 'mysql.exe' in: C:\MySQL\mysql-5.1.19-beta-win32\bin\mysql.exe
Looking for 'mysqlcheck.exe' in: C:\MySQL\mysql-5.1.19-beta-win32\bin\mysqlcheck.exe
FATAL ERROR: Failed to create temporary file for defaults

Examining the 5.1.19-beta source for client/mysql_upgrade.c shows it is not been changed. It still passes a NULL as the 2nd argument (temp directory) to create_temp_file.  This causes mysys/mf_tempfile.c to call the Windows function GetTempFileName with a NULL lpPathName.  
See the Microsoft docs at http://msdn2.microsoft.com/en-us/library/aa364991.aspx 
"If this parameter is NULL, the function fails".

-tom-

Status changed as feedback has been provided.

Thank you for the feedback.

Verified as described.

create_temp_file takes second argument as const char *, so GetTempFileName takes not NULL, but const char *, so it doesn't fail and creates temporary files in C:\ (in my environment)

To repeat: start mysql_upgrade as user who hasn't permissions to write to C:\

Additionally mysql_upgrade doesn't delete temporary files if it can create them.

This is a duplicate of bug#28774, which is currently patch pending after applying the contributed patch linked above.

For what its worth, I was having exactly the same problem as Peter Brawley with mysql_upgrade on my W2KPRO box. After many attempts I successfully tried using a tmpdir command line parameter:
I:\Program Files\MySQL\Server 5.1\bin>mysql_upgrade --tmpdir=I: -p******

Apropos Dave Armour's comment "For what its worth, I was having exactly the same problem as Peter Brawley with mysql_upgrade on my W2KPRO box. After many attempts I successfully tried using a tmpdir command line parameter:
I:\Program Files\MySQL\Server 5.1\bin>mysql_upgrade --tmpdir=I: -p******":

This works, which appears to indicate that the problem lies in mysql_upgrade.exe making unwarranted assumptions about the drive setup (in the one installation of ours where mysql_upgrade fails consistently without Dave's fix, drive C: is essentially a dummy and the system drive is F:).