MySQL for Windows has proven itself to be very stable. The Windows version of MySQL has the same features as the corresponding Unix version, with the following exceptions:
Windows 95 and threads
Windows 95 leaks about 200 bytes of main memory for each thread creation. Each connection in MySQL creates a new thread, so you should not run mysqld for an extended time on Windows 95 if your server handles many connections! Newer versions of Windows don't suffer from this bug.
Limited number of ports
Windows systems have about 4,000 ports available for client connections, and after a connection on a port closes, it takes two to four minutes before the port can be reused. In situations where clients connect to and disconnect from the server at a high rate, it is possible for all available ports to be used up before closed ports become available again. If this happens, the MySQL server appears to be unresponsive even though it is running. Note that ports may be used by other applications running on the machine as well, in which case the number of ports available to MySQL is lower.
For more information about this problem, see http://support.microsoft.com/default.aspx?scid=kb;en-us;196271.
Concurrent reads
MySQL depends on the pread()
and
pwrite()
calls to be able to mix
INSERT
and
SELECT
. Currently, we use
mutexes to emulate
pread()
/pwrite()
. We
will, in the long run, replace the file level interface with
a virtual interface so that we can use the
readfile()
/writefile()
interface on NT, 2000, and XP to get more speed. The current
implementation limits the number of open files that MySQL
can use to 2,048 (1,024 before MySQL 4.0.19), which means
that you cannot run as many concurrent threads on NT, 2000,
XP, and 2003 as on Unix.
Blocking read
MySQL uses a blocking read for each connection. That has the following implications if named-pipe connections are enabled:
A connection is not disconnected automatically after eight hours, as happens with the Unix version of MySQL.
If a connection hangs, it is impossible to break it without killing MySQL.
mysqladmin kill does not work on a sleeping connection.
mysqladmin shutdown cannot abort as long as there are sleeping connections.
We plan to fix this problem when our Windows developers have figured out a workaround.
While you are executing an ALTER
TABLE
statement, the table is locked from being
used by other threads. This has to do with the fact that on
Windows, you cannot delete a file that is in use by another
thread. In the future, we may find some way to work around
this problem.
DROP TABLE
on a table that is
in use by a MERGE
table does not work on
Windows because the MERGE
handler does
the table mapping hidden from the upper layer of MySQL.
Because Windows does not allow you to drop files that are
open, you first must flush all MERGE
tables (with FLUSH
TABLES
) or drop the MERGE
table
before dropping the table. We will fix this at the same time
we introduce views.
DATA DIRECTORY
and
INDEX DIRECTORY
The DATA DIRECTORY
and INDEX
DIRECTORY
options for CREATE
TABLE
are ignored on Windows, because Windows does
not support symbolic links. These options also are ignored
on systems that have a nonfunctional
realpath()
call.
You cannot drop a database that is in use by a thread.
Killing MySQL from the Task Manager
On Windows 95, you cannot kill MySQL from the Task Manager
or with the shutdown utility. You must stop it with
mysqladmin shutdown or the NET
STOP ...
command.
Case-insensitive names
File names are not case sensitive on Windows, so MySQL database and table names are also not case sensitive on Windows. The only restriction is that database and table names must be specified using the same case throughout a given statement. See Section 8.2.2, “Identifier Case Sensitivity”.
Directory and file names
On Windows, MySQL Server supports only directory and file names that are compatible with the current ANSI code pages. For example, the following Japanese directory name will not work in the Western locale (code page 1252):
datadir="C:/私たちのプロジェクトのデータ"
The same limitation applies to directory and file names
referred to in SQL statements, such as the data file path
name in LOAD DATA
INFILE
.
The
“\
” path name separator
character
Path name components in Windows are separated by the
“\
” character, which is also
the escape character in MySQL. If you are using
LOAD DATA
INFILE
or
SELECT ... INTO
OUTFILE
, use Unix-style file names with
“/
” characters:
mysql>LOAD DATA INFILE 'C:/tmp/skr.txt' INTO TABLE skr;
mysql>SELECT * INTO OUTFILE 'C:/tmp/skr.txt' FROM skr;
Alternatively, you must double the
“\
” character:
mysql>LOAD DATA INFILE 'C:\\tmp\\skr.txt' INTO TABLE skr;
mysql>SELECT * INTO OUTFILE 'C:\\tmp\\skr.txt' FROM skr;
Problems with pipes
Pipes do not work reliably from the Windows command-line
prompt. If the pipe includes the character
^Z
/ CHAR(24)
, Windows
thinks that it has encountered end-of-file and aborts the
program.
This is a problem mainly when you try to apply a binary log as follows:
shell> mysqlbinlog binary_log_file
| mysql --user=root
If you have a problem applying the log and suspect that it
is because of a ^Z
/
CHAR(24)
character, you can use the
following workaround:
shell>mysqlbinlog
shell>binary_log_file
--result-file=/tmp/bin.sqlmysql --user=root --execute "source /tmp/bin.sql"
The latter command also can be used to reliably read in any SQL file that may contain binary data.
Access denied for
user
error
If MySQL cannot resolve your host name properly, you may get the following error when you attempt to run a MySQL client program to connect to a server running on the same machine:
Access denied for user 'some_user
'@'unknown'
to database 'mysql'
To fix this problem, you should create a file named
\windows\hosts
containing the following
information:
127.0.0.1 localhost
User Comments
Quote: "While you are executing an ALTER TABLE statement, the table is locked from being used by other threads. This has to do with the fact that on Windows, you can't delete a file that is in use by another thread. In the future, we may find some way to work around this problem."
Windows does allow files to be deleted while still in use if all currently open handles are opened with FILE_SHARE_DELETE.
There is usually no reason to omit this flag when opening a file. If your code relies on the Unix behavior, which appears to be similar to always specifying FILE_SHARE_DELETE, then just change all your CreateFile() calls to use this flag, and you will get the desired behavior.
Add your own comment.