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:Microsoft Windows (W2KPRO SP4)
Assigned to: Daniel Fischer CPU Architecture:Any
Tags: Contribution, qc

[22 May 2007 23:28] Peter Brawley
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.
[23 May 2007 4:34] Valeriy Kravchuk
Thank you for a problem report. Please, send more details: my.ini file used in older installation, exact Windows version, results of:

dir F:
[23 May 2007 5:30] Peter Brawley
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
[23 May 2007 7:51] Sveta Smirnova
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.
[23 May 2007 9:10] Peter Brawley
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'
[4 Jun 2007 23:10] Tom Donovan
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");
-------------------------------------------
[19 Jun 2007 6:32] Sergei Golubchik
Contributed patch:  http://lists.mysql.com/internals/34701
[22 Jun 2007 11:58] Sveta Smirnova
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.
[22 Jun 2007 13:23] Tom Donovan
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-
[25 Jun 2007 10:44] Sveta Smirnova
Status changed as feedback has been provided.
[26 Jun 2007 19:50] Sveta Smirnova
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.
[29 Jun 2007 14:57] Iggy Galarza
This is a duplicate of bug#28774, which is currently patch pending after applying the contributed patch linked above.
[29 Apr 2009 23:50] Dave Armour
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******
[30 Apr 2009 1:47] Peter Brawley
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:).