Follow Techotopia on Twitter

On-line Guides
All Guides
eBook Store
iOS / Android
Linux for Beginners
Office Productivity
Linux Installation
Linux Security
Linux Utilities
Linux Virtualization
Linux Kernel
System/Network Admin
Programming
Scripting Languages
Development Tools
Web Development
GUI Toolkits/Desktop
Databases
Mail Systems
openSolaris
Eclipse Documentation
Techotopia.com
Virtuatopia.com

How To Guides
Virtualization
General System Admin
Linux Security
Linux Filesystems
Web Servers
Graphics & Desktop
PC Hardware
Windows
Problem Solutions

  




 

 

2.3.15. MySQL on Windows Compared to MySQL on Unix

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 shouldn't 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() system calls to be able to mix INSERT and SELECT. Currently, we use mutexes to emulate pread() and pwrite(). We intend to replace the file level interface with a virtual interface in the future 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 5.1 can use to 2,048, which means that you cannot run as many concurrent threads on Windows 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 not possible 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 in the future.

  • ALTER TABLE

    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.

  • DROP TABLE

    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 dropping files that are open, you first must flush all MERGE tables (with FLUSH TABLES) or drop the MERGE table before dropping the table.

  • DATA DIRECTORY and INDEX DIRECTORY

    The DATA DIRECTORY and INDEX DIRECTORY options for CREATE TABLE are ignored on Windows, because Windows doesn't support symbolic links. These options also are ignored on systems that have a non-functional realpath() call.

  • DROP DATABASE

    You cannot drop a database that is in use by some 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.

  • Case-insensitive names

    Filenames 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 9.2.2, “Identifier Case Sensitivity”.

  • The ‘\’ pathname separator character

    Pathname 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 filenames 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 mainly a problem when you try to apply a binary log as follows:

    C:\> 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:

    C:\> mysqlbinlog binary_log_file --result-file=/tmp/bin.sql
    C:\> mysql --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 hostname 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
    

Here are some open issues for anyone who might want to help us improve MySQL on Windows:

  • Add macros to use the faster thread-safe increment/decrement methods provided by Windows.


 
 
  Published under the terms of the GNU General Public License Design by Interspire