MySQL Bugs: #49637: mysqld won't start as Windows service, it works directly from command line

Bug #49637	mysqld won't start as Windows service, it works directly from command line
Submitted:	12 Dec 2009 2:55	Modified:	16 Dec 2009 17:05
Reporter:	Robert Reinhardt	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Windows	Severity:	S3 (Non-critical)
Version:	5.1.41-community	OS:	Windows (XP SP3)
Assigned to:		CPU Architecture:	Any
Tags:	temporary file, windows service

Description:
In out-of-the-box installation of mysql the MySQLInstanceConfig hung in starting the windows service with log entry

091212 3:26:46 [Note] Plugin 'FEDERATED' is disabled.
091212 3:26:46 InnoDB: Error: unable to create temporary file; errno: 2
091212 3:26:46 [ERROR] Plugin 'InnoDB' init function returned error.
091212 3:26:46 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
091212 3:26:46 [ERROR] MySQL: unknown variable 'tempdir=c:/nobackup/temp'
091212 3:26:46 [ERROR] Aborting

I managed to start mysqld manually and configure the database, using mysql_upgrade (datafiles were from V5.0) everything seemed to be fine.

However the service stil would not start with the above errorlog.

After some experimentation checking for the most obvious ACLs and similar and including searching here I used some file activity monitoring to find out that the service tries to open temporary directory as defined in the windows environment variable TEMP but without translation. I have TEMP = "%SystemDevice%\nobackup\temp" and the service tried to open temporary directory as
C:\mysql\%SYSTE~1\nobackup\temp
whereas the directly started mysqld opens it with translated %SystemDevide% (maybe command shell does the translation).

The problem was solved by defining tmpdir in my.ini. I assume it would also be solved by defining system variable TEMP (or TMP) as translated path.

This may solve some other similar problems with MySQLInstanceConfig described here.

How to repeat:
Define environment variable TEMP (and/or TMP) to use %SystemDevice% or any other similar symbol and restart mysqld as a service.

I believe that out-of-the-box windows setting for TEMP was %SystemRoot%\temp or something similar.

Suggested fix:
Do the symbol translation after evaluating environment varaibles.

Robert you have a typo in my.ini:

091212  3:26:46 [ERROR] MySQL: unknown variable 'tempdir=c:/nobackup/temp'

It should have been:

tmpdir=c:/nobackup/temp

Then innodb might have started up and the service itself would startup.

> Robert you have a typo in my.ini:
> 091212  3:26:46 [ERROR] MySQL: unknown variable 'tempdir=c:/nobackup/temp'
> It should have been:
> tmpdir=c:/nobackup/temp
> Then innodb might have started up and the service itself would startup.

Yes, I noticed and the service starts OK after the tmpdir is defined properly. I have copied a wrong paragraph from the errorlog, the right one is:

091212  3:25:55 [Note] Plugin 'FEDERATED' is disabled.
091212  3:25:55  InnoDB: Error: unable to create temporary file; errno: 2
091212  3:25:55 [ERROR] Plugin 'InnoDB' init function returned error.
091212  3:25:55 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
091212  3:25:55 [ERROR] Unknown/unsupported table type: INNODB
091212  3:25:55 [ERROR] Aborting

The main point was that mysqld should have started even with no definition of tmpdir in my.ini - it does so if started from the command line but it does not if started as a service.

When tmpdir is not explicitly defined in my.ini it gets defined from the environment variable TEMP or TMP. They were both set to %SystemDevice%\something and the symbol %SystemDevice% is properly translated in command line environment and it is not translated in the service.

I still claim this is a bug or an undocumented feature. I may accept the fact that the execution environment for command line programs and service programs is not the same (which is not a nice feature of Windows), but mysqld has been made to run as a service so it should take care of such differences.

Thank you for the feedback. Sorry but this isn't a bug just an issue with a unknown variable, the server in this case can't link the wrong syntax with the tmpdir variable.