Chapter 9. Installing the InnoDB Plugin

Table of Contents

9.1. Overview of Installing the InnoDB Plugin
9.2. Checking for Compatible Version Levels
9.3. Installing the Precompiled InnoDB Plugin as a Shared Library
9.3.1. Installing the InnoDB Plugin as a Shared Library on Unix or Linux
9.3.2. Installing the Binary InnoDB Plugin as a Shared Library on Microsoft Windows
9.3.3. Errors When Installing the InnoDB Plugin on Microsoft Windows
9.4. Building the InnoDB Plugin from Source Code
9.4.1. Building the InnoDB Plugin on Linux or Unix
9.4.2. Building the InnoDB Plugin on Microsoft Windows
9.5. Configuring the InnoDB Plugin
9.6. Frequently Asked Questions about Plugin Installation
9.6.1. Should I use the InnoDB-supplied plugin or the one that is included with MySQL 5.1.38 or higher?
9.6.2. Why doesn't the MySQL service on Windows start after the replacement?
9.6.3. The Plugin is installed... now what?
9.6.4. Once the Plugin is installed, is it permanent?

9.1. Overview of Installing the InnoDB Plugin

You can acquire the plugin in these formats:

  • As a platform-specific executable binary file that is dynamically linked or plugged in to the MySQL server.

  • In source code form, available under the GNU General Public License (GPL), version 2.

Notes:

While it is possible to use the source code to build a special version of MySQL containing the InnoDB Plugin, we recommend you install the binary shared library for the InnoDB Plugin instead, without building from source. Replacing the shared library is simpler and much less error prone than building from source.

The InnoDB Plugin is included in the MySQL distribution, starting from MySQL 5.1.38. From MySQL 5.1.46 and up, this is the only download location for the InnoDB Plugin; it is not available from the InnoDB web site. If you used any scripts or configuration files with the earlier InnoDB Plugin from the InnoDB web site, be aware that the filename of the shared library as supplied by MySQL is ha_innodb_plugin.so or ha_innodb_plugin.dll, as opposed to ha_innodb.so or ha_innodb.dll in the older Plugin downloaded from the InnoDB web site. You might need to change the applicable file names in your startup or configuration scripts.

This discussion pertains to using the InnoDB Plugin with the MySQL Community Edition, whether source or binary. Except for download locations for MySQL software, the procedures documented here should work without change when you use MySQL Enterprise.

Whether you dynamically install the binary InnoDB Plugin or build from source, configure MySQL by editing the configuration file to use InnoDB as the default engine (if desired) and set appropriate configuration parameters to enable use of new InnoDB Plugin features, as described in Section 9.5, “Configuring the InnoDB Plugin”.

At this time, the InnoDB Plugin has not been compiled or tested with the Intel C Compiler (icc), so you should use a version of MySQL compiled with the GNU Compiler Collection (gcc).

9.2. Checking for Compatible Version Levels

Use the following table to confirm that the version of the InnoDB Plugin (whether source or binary) is compatible with your platform, hardware type (including 32-bit vs 64-bit) and with your version of MySQL. In general, a specific release of the InnoDB Plugin is designed to be compatible with a range of MySQL versions, but this may vary, so check the information on the download page.

When building MySQL from source, you can generally use the source for the InnoDB Plugin in place of the source for the built-in InnoDB. However, due to limitations of MySQL, a given binary version of the InnoDB Plugin is compatible only with a specific version of MySQL, as follows.

Table 9.1. InnoDB Plugin Compatibility

InnoDB Plugin ReleaseMySQL Release (Binary Compatibility)MySQL Release (Source Compatibility)
1.0.05.1.235.1.23 or newer
1.0.15.1.245.1.24 or newer
1.0.25.1.305.1.24 or newer
1.0.35.1.30 (not 5.1.31)5.1.24 or newer
1.0.45.1.375.1.24 or newer
1.0.55.1.415.1.24 or newer
1.0.65.1.415.1.24 or newer
1.0.7 and higherIncorporated into the applicable 5.1.x MySQL release; not separately downloadable.N/A

Note: MySQL Bug #42610 prevents using the binary InnoDB Plugin with MySQL 5.1.31 or 5.1.32. There is no binary InnoDB Plugin for MySQL 5.1.33. The only way to use InnoDB Plugin with MySQL 5.1.31 through 5.1.35 is by building from source. This issue was resolved in MySQL 5.1.37 and InnoDB Plugin 1.0.4.

9.3. Installing the Precompiled InnoDB Plugin as a Shared Library

The simplest way to install the InnoDB Plugin is to use a precompiled (binary) shared library file, when one is available. The procedures are similar for installing the InnoDB Plugin using the binary on different hardware and operating systems platforms, but the specific details differ between Unix or Linux and Microsoft Windows. See below for notes specific to your platform.

Note that due to MySQL Bug #42610, the procedure of installing the binary InnoDB Plugin changed in MySQL 5.1.33. If your version of MySQL is older than 5.1.33, refer to Appendix B, Using the InnoDB Plugin with MySQL 5.1.30 or Earlier.

The steps for installing the InnoDB Plugin as a shared library are as follows:

  • Download, extract and install the suitable MySQL executable for your platform.

  • Make sure the MySQL server is not running. If you have to shut down the database server, you use a special slow shutdown procedure, described later.

  • On database startup, make MySQL ignore the builtin InnoDB, and load the InnoDB Plugin and all new InnoDB Information Schema tables implemented in the InnoDB Plugin, using one of these alternatives:

    • Edit the option file (my.cnf, or my.ini) to contain the necessary options.

    • Specify equivalent options on the MySQL command line.

    • Edit the option file to disable InnoDB, then use INSTALL statements on the MySQL command line after startup.

    These procedures are described in detail in the following sections.

  • Set appropriate configuration parameters to enable new InnoDB Plugin features.

  • Start MySQL, and verify the installation of the plugins.

The following sections detail these steps for Unix or Linux systems, and for Microsoft Windows.

9.3.1. Installing the InnoDB Plugin as a Shared Library on Unix or Linux

For Unix and Linux systems, use the following procedure to install the InnoDB Plugin as a shared library:

  1. Download, extract and install the suitable MySQL executable for your server platform and operating system from the MySQL download section for MySQL Database Server 5.1. Be sure to use a 32-bit or 64-bit version as appropriate for your hardware and operating system.

  2. Make sure the MySQL server is not running. If the server is running, do a slow shutdown by issuing the following command before performing the shutdown:

    SET GLOBAL innodb_fast_shutdown=0;

    Then finish the shutdown process, as described in The Shutdown Process in the MySQL documentation. This option setting performs a full purge and an insert buffer merge before the shutdown, which can typically take minutes, or even hours for very large and busy databases.

  3. The InnoDB Plugin shared library is already installed in the directory lib/plugin as part of the MySQL installation.

  4. Edit the option file (my.cnf) to ignore the builtin InnoDB, and load the InnoDB Plugin and all Information Schema tables implemented in the InnoDB Plugin when the server starts:

    ignore_builtin_innodb
    plugin-load=innodb=ha_innodb_plugin.so;innodb_trx=ha_innodb_plugin.so;
      innodb_locks=ha_innodb_plugin.so;innodb_lock_waits=ha_innodb_plugin.so;
      innodb_cmp=ha_innodb_plugin.so;innodb_cmp_reset=ha_innodb_plugin.so;
      innodb_cmpmem=ha_innodb_plugin.so;innodb_cmpmem_reset=ha_innodb_plugin.so

    Note that all plugins for plugin-load should be on the same line in the option file.

    Alternatively, you can use the equivalent options on the MySQL command line:

    mysqld --ignore-builtin-innodb --plugin-load=innodb=ha_innodb_plugin.so;
      innodb_trx=ha_innodb_plugin.so;innodb_locks=ha_innodb_plugin.so;
      innodb_lock_waits=ha_innodb_plugin.so;innodb_cmp=ha_innodb_plugin.so;
      innodb_cmp_reset=ha_innodb_plugin.so;innodb_cmpmem=ha_innodb_plugin.so;
      innodb_cmpmem_reset=ha_innodb_plugin.so

    You can also install the InnoDB Plugin and the new InnoDB Information Schema tables implemented in ha_innodb_plugin.so with INSTALL commands:

    INSTALL PLUGIN INNODB SONAME 'ha_innodb_plugin.so';
    INSTALL PLUGIN INNODB_TRX SONAME 'ha_innodb_plugin.so';
    INSTALL PLUGIN INNODB_LOCKS SONAME 'ha_innodb_plugin.so';
    INSTALL PLUGIN INNODB_LOCK_WAITS SONAME 'ha_innodb_plugin.so';
    INSTALL PLUGIN INNODB_CMP SONAME 'ha_innodb_plugin.so';
    INSTALL PLUGIN INNODB_CMP_RESET SONAME 'ha_innodb_plugin.so';
    INSTALL PLUGIN INNODB_CMPMEM SONAME 'ha_innodb_plugin.so';
    INSTALL PLUGIN INNODB_CMPMEM_RESET SONAME 'ha_innodb_plugin.so';

    If you use INSTALL PLUGIN statement to install the InnoDB Plugin and the Information Schema tables, ensure the following conditions are set up:

    • In the mysqld command line or my.cnf option file, prepend each InnoDB option with loose_, so that MySQL will start even when InnoDB is unavailable. For example, write loose_innodb_file_per_table instead of innodb_file_per_table.

    • Start the MySQL server while it is configured to skip loading the built-in InnoDB and to make MyISAM the default storage engine. This can be done by editing the option file my.cnf to contain these two lines:

      ignore_builtin_innodb
      default_storage_engine=MyISAM

      Or, you can use the equivalent options on the MySQL command line:

      mysqld --ignore-builtin-innodb --default-storage-engine=MyISAM …

    See the MySQL Manual section on INSTALL PLUGIN Syntax for information on how these commands work.

  5. Edit the option file my.cnf to use InnoDB as the default engine (if desired) and set appropriate configuration parameters to enable use of new InnoDB Plugin features, as described in Section 9.5, “Configuring the InnoDB Plugin”. In particular, we recommend that you set the following specific parameters as follows:

    default-storage-engine=InnoDB
    innodb_file_per_table=1
    innodb_file_format=barracuda
    innodb_strict_mode=1
    IMPORTANT:

    The MySQL server always must be started with the option ignore_builtin_innodb, as long as you want to use the InnoDB Plugin as a shared library. Also, remember that the startup option skip_grant_tables prevents MySQL from loading any plugins.

  6. Verify the installation of the plugins with the MySQL command SHOW PLUGINS, which should produce the following output:

    NameStatusTypeLibraryLicense
    binlogACTIVESTORAGE ENGINENULLGPL
    CSVACTIVESTORAGE ENGINENULLGPL
    MEMORYACTIVESTORAGE ENGINENULLGPL
    InnoDBACTIVESTORAGE ENGINEha_innodb_plugin.soGPL
    INNODB_TRXACTIVEINFORMATION SCHEMAha_innodb_plugin.soGPL
    INNODB_LOCKSACTIVEINFORMATION SCHEMAha_innodb_plugin.soGPL
    INNODB_LOCK_WAITSACTIVEINFORMATION SCHEMAha_innodb_plugin.soGPL
    INNODB_CMPACTIVEINFORMATION SCHEMAha_innodb_plugin.soGPL
    INNODB_CMP_RESETACTIVEINFORMATION SCHEMAha_innodb_plugin.soGPL
    INNODB_CMPMEMACTIVEINFORMATION SCHEMAha_innodb_plugin.soGPL
    INNODB_CMPMEM_RESETACTIVEINFORMATION SCHEMAha_innodb_plugin.soGPL
    MRG_MYISAMACTIVESTORAGE ENGINENULLGPL
    MyISAMACTIVESTORAGE ENGINENULLGPL

    If the plugins fail to load properly, see Section 9.3.1.1, “Errors When Installing the InnoDB Plugin on Unix or Linux” for possible causes and corrections.

    After verifying that the Plugin is recognized by MySQL, create an InnoDB table as another confirmation of success.

9.3.1.1. Errors When Installing the InnoDB Plugin on Unix or Linux

If MySQL or its associated daemon process cannot start, or a post-startup INSTALL PLUGIN statement fails, look at the MySQL error log (usually named machine_name.err and located in the MySQL data directory) for the detailed error message. The log is in chronological order, so look at the end of the file. Try to resolve the problem based on other information in the message.

Error Condition or MessagePossible Solution
Can't open shared library library_nameDiagnose the cause from the following message details.
API version for STORAGE ENGINE plugin is too differentThe version of the Plugin is not compatible with the version of the MySQL server. Consult the compatibility chart.
No such file or directoryCheck that the file ha_innodb_plugin.so or .dll was copied to the correct location. Confirm that you specified the right file name (ha_innodb_plugin.so or .dll for the library from the InnoDB web site; ha_innodb_plugin.so or .dll for the library supplied along with MySQL 5.1.38 and up.)
Permission deniedCheck that the directory and file access permissions are set properly, or change them using chmod on Unix-like systems . The mysqld process must have permission to read (r) the file ha_innodb_plugin.so and to access files (x) in the plugin directory.
wrong ELF class or any other messageEnsure that ha_innodb_plugin.so is for the same system platform as mysqld. In particular, note that a 32-bit mysqld is unable to load a 64-bit plugin, and vice versa. Be sure to download an InnoDB Plugin that is compatible with your platform.
Note

The Information Schema tables are themselves plugins to the MySQL server, but they depend on having the InnoDB storage engine plugin installed as well. These tables will appear to be empty if the storage engine is not installed.

9.3.2. Installing the Binary InnoDB Plugin as a Shared Library on Microsoft Windows

The InnoDB Plugin is supported on any of the Windows operating system versions supported by MySQL. In particular, this includes Microsoft Windows 2008 Server, Windows Vista, Windows 2003 Server and Windows XP. Note that on Vista certain special procedures must be followed that are not documented here.

Use the following procedure to dynamically install the InnoDB Plugin on Microsoft Windows.

  1. Download, extract and install the suitable MySQL executable for your server platform and operating system from the MySQL download section for MySQL Database Server 5.1. Be sure to use a 32-bit or 64-bit version as appropriate for your hardware and Windows version.

  2. Make sure the MySQL server is not running. You do a slow shutdown by issuing the following command before performing the shutdown:

    SET GLOBAL innodb_fast_shutdown=0;

    Then finish the shutdown process, as described in The Shutdown Process in the MySQL documentation. This option setting performs a full purge and an insert buffer merge before the shutdown, which can typically take minutes, or even hours for very large and busy databases.

  3. The InnoDB Plugin shared library is already installed in the directory lib\plugin as part of the MySQL installation.

  4. Edit the option file (my.ini) to ignore the builtin InnoDB, and load the InnoDB Plugin and all Information Schema tables implemented in the InnoDB Plugin when the server starts:

    ignore_builtin_innodb
    plugin-load=innodb=ha_innodb_plugin.dll;innodb_trx=ha_innodb_plugin.dll;
      innodb_locks=ha_innodb_plugin.dll;innodb_lock_waits=ha_innodb_plugin.dll;
      innodb_cmp=ha_innodb_plugin.dll;innodb_cmp_reset=ha_innodb_plugin.dll;
      innodb_cmpmem=ha_innodb_plugin.dll;innodb_cmpmem_reset=ha_innodb_plugin.dll
    Note

    All plugins for plugin-load should be on the same line in the option file. Be careful when copying and pasting that the line does not split.

    Alternatively, you can use the equivalent options on the MySQL command line:

    mysqld --ignore-builtin-innodb --plugin-load=
      innodb=ha_innodb_plugin.dll;
      innodb_trx=ha_innodb_plugin.dll;innodb_locks=ha_innodb_plugin.dll;
      innodb_lock_waits=ha_innodb_plugin.dll;innodb_cmp=ha_innodb_plugin.dll;
      innodb_cmp_reset=ha_innodb_plugin.dll;innodb_cmpmem=ha_innodb_plugin.dll;
      innodb_cmpmem_reset=ha_innodb_plugin.dll

    You can also install the InnoDB Plugin and the new InnoDB Information Schema tables implemented in ha_innodb_plugin.so with INSTALL commands, as follows:

    INSTALL PLUGIN INNODB SONAME 'ha_innodb_plugin.dll';
    INSTALL PLUGIN INNODB_TRX SONAME 'ha_innodb_plugin.dll';
    INSTALL PLUGIN INNODB_LOCKS SONAME 'ha_innodb_plugin.dll';
    INSTALL PLUGIN INNODB_LOCK_WAITS SONAME 'ha_innodb_plugin.dll';
    INSTALL PLUGIN INNODB_CMP SONAME 'ha_innodb_plugin.dll';
    INSTALL PLUGIN INNODB_CMP_RESET SONAME 'ha_innodb_plugin.dll';
    INSTALL PLUGIN INNODB_CMPMEM SONAME 'ha_innodb_plugin.dll';
    INSTALL PLUGIN INNODB_CMPMEM_RESET SONAME 'ha_innodb_plugin.dll';

    If you use INSTALL PLUGIN statements to install the InnoDB Plugin and the Information Schema tables, ensure the following conditions are set up:

    • In the mysqld command line or my.ini option file, prepend each InnoDB option with loose_, so that MySQL will start even when InnoDB is unavailable. For example, write loose_innodb_file_per_table instead of innodb_file_per_table.

    • Start the MySQL server while it is configured to skip loading the built-in InnoDB and to make MyISAM the default storage engine. This can be done by editing the option file my.cnf to contain these two lines:

      ignore_builtin_innodb
      default_storage_engine=MyISAM

      Or, you can use the equivalent options on the MySQL command line:

      mysqld --ignore-builtin-innodb --default-storage-engine=MyISAM …

    See the MySQL Manual section on INSTALL PLUGIN Syntax for information on how these commands work.

  5. Edit the option file my.ini to use InnoDB as the default engine (if desired) and set appropriate configuration parameters to enable use of new InnoDB Plugin features, as described in Section 9.5, “Configuring the InnoDB Plugin”. In particular, we recommend that you set the following specific parameters as follows:

    default-storage-engine=InnoDB
    innodb_file_per_table=1
    innodb_file_format=barracuda
    innodb_strict_mode=1

    IMPORTANT: The MySQL server always must be started with the option ignore_builtin_innodb, as long as you want to use the dynamic InnoDB Plugin. Also, remember that the startup option skip_grant_tables prevents MySQL from loading any plugins.

  6. Verify the installation of the plugins with the MySQL command SHOW PLUGINS, which should produce the following output:

    NameStatusTypeLibraryLicense
    binlogACTIVESTORAGE ENGINENULLGPL
    CSVACTIVESTORAGE ENGINENULLGPL
    MEMORYACTIVESTORAGE ENGINENULLGPL
    InnoDBACTIVESTORAGE ENGINEha_innodb_plugin.dllGPL
    INNODB_TRXACTIVEINFORMATION SCHEMAha_innodb_plugin.dllGPL
    INNODB_LOCKSACTIVEINFORMATION SCHEMAha_innodb_plugin.dllGPL
    INNODB_LOCK_WAITSACTIVEINFORMATION SCHEMAha_innodb_plugin.dllGPL
    INNODB_CMPACTIVEINFORMATION SCHEMAha_innodb_plugin.dllGPL
    INNODB_CMP_RESETACTIVEINFORMATION SCHEMAha_innodb_plugin.dllGPL
    INNODB_CMPMEMACTIVEINFORMATION SCHEMAha_innodb_plugin.dllGPL
    INNODB_CMPMEM_RESETACTIVEINFORMATION SCHEMAha_innodb_plugin.dllGPL
    MRG_MYISAMACTIVESTORAGE ENGINENULLGPL
    MyISAMACTIVESTORAGE ENGINENULLGPL

    If the plugins fail to load properly, see Section 9.3.3, “Errors When Installing the InnoDB Plugin on Microsoft Windows” for possible causes and corrections.

    After verifying that the Plugin is recognized by MySQL, create an InnoDB table as another confirmation of success.

9.3.3. Errors When Installing the InnoDB Plugin on Microsoft Windows

If MySQL or the associated Windows service can not start, or a post-startup INSTALL PLUGIN statement fails, look at the MySQL error log (usually named machine_name.err and located in the MySQL data directory) for the detailed error message. The log is in chronological order, so look at the end of the file. Try to resolve the problem based on other information in the message.

Error Condition or MessagePossible Solution
Can't open shared library library_nameDiagnose the cause from the following message details.
API version for STORAGE ENGINE plugin is too differentThe version of the Plugin is not compatible with the version of the MySQL server. Consult the compatibility chart.
No such file or directoryCheck that the file ha_innodb_plugin.so or .dll was copied to the correct location. Confirm that you specified the right file name (ha_innodb_plugin.so or .dll for the library from the InnoDB web site; ha_innodb_plugin.so or .dll for the library supplied along with MySQL 5.1.38 and up.)
Permission deniedCheck that the folder and file access permissions are set properly. The mysqld process must have permission to read the file ha_innodb_plugin.dll and to read files in the plugin folder. On Windows XP, file permissions can be seen or changed by right-clicking a file and pressing Properties, and then the Security Tab. To see the Security Tab, you may need to adjust the Folder Options on the Control Panel to turn off Use Simple File Sharing.
Can't open shared library 'ha_innodb_plugin.dll' (errno: 0)

Ensure that ha_innodb_plugin.dll is for the same system platform as mysqld. In particular, note that a 32-bit mysqld is unable to load a 64-bit plugin, and vice versa.

Note: The Information Schema tables are themselves plugins to the MySQL server, but they depend on having the InnoDB storage engine plugin installed as well. These tables will appear to be empty if the storage engine is not installed.

9.4. Building the InnoDB Plugin from Source Code

Sometimes, you may wish to build the plugin from the source code using special compilation options, or there might be no binary plugin available for your server platform. With the resulting special version of MySQL containing the new InnoDB functionality, it is not necessary to INSTALL any plugins or be concerned about startup parameters that preclude loading plugins.

To build the InnoDB Plugin from the source code, you also need the MySQL source code and some software tools. You should become familiar with the MySQL manual section on MySQL Installation Using a Source Distribution.

The general steps for building MySQL from source, containing the InnoDB Plugin in place of the standard built-in InnoDB, are as follows:

  • Download the MySQL source code.

  • Download the InnoDB Plugin source code.

  • Replace the source code for the built-in InnoDB with the InnoDB Plugin source tree.

  • Compile MySQL as usual, generating a new mysqld executable file.

  • Configure the MySQL server by editing the configuration file to use InnoDB as the default engine (if desired) and set appropriate configuration parameters to enable use of new InnoDB Plugin features.

The following sections detail these steps for Linux or Unix systems, and for Microsoft Windows.

9.4.1. Building the InnoDB Plugin on Linux or Unix

  1. Download the MySQL source code, version 5.1.24 or later from http://dev.mysql.com/downloads/mysql/5.1.html#source and extract it.

  2. Download the InnoDB Plugin source code from http://dev.mysql.com/downloads/.

  3. Replace the contents of the storage/innobase directory in the MySQL source tree with the InnoDB Plugin source tree.

    Note

    In MySQL 5.1.38 and up, the MySQL source tree also contains a storage/innodb_plugin directory, but that does not affect this procedure. The source that you download from the InnoDB web site may contain additional changes and fixes.

  4. Compile and build MySQL. Instead of building a dynamic InnoDB Plugin, it is advisable to build a version of MySQL that contains the InnoDB Plugin. This is because a dynamic InnoDB Plugin must be built with exactly the same tools and options as the mysqld executable, or spurious errors may occur. Example:

    % wget ftp://ftp.easynet.be/mysql/Downloads/MySQL-5.1/mysql-5.1.37.tar.gz
    % tar -zxf mysql-5.1.37.tar.gz
    % cd mysql-5.1.37/storage
    % wget http://dev.mysql.com/downloads/innodb_plugin/innodb_plugin-1.0.8.tar.gz
    % tar -zxf innodb-1.0.8.tar.gz
    % rm -fr innobase
    % mv innodb-1.0.8 innobase
    % cd ..
    % ./configure --with-plugins=innobase
    % make
  5. Reconfigure the MySQL server by editing the my.cnf option file to use InnoDB as the default engine (if desired) and set appropriate configuration parameters to enable use of new InnoDB Plugin features, as described in section Section 9.5, “Configuring the InnoDB Plugin”. In particular, we recommend that you set the following specific parameters as follows:

    default_storage_engine=InnoDB
    innodb_file_per_table=1
    innodb_file_format=barracuda
    innodb_strict_mode=1
  6. If you build a version of MySQL that contains the InnoDB Plugin (--with-plugins=innobase), you do not have to tell MySQL to specify ignore_builtin_innodb or specify plugin-load, or issue any INSTALL PLUGIN statements. The mysqld executable that you compiled will contain the new InnoDB Plugin features.

Note: To fully exploit the performance improvements discussed in Section 7.2, “Faster Locking for Improved Scalability”, the InnoDB Plugin source code and build process makes some compile-time tests of platform capabilities to automatically use instructions for atomic memory access where available. If this logic fails, you may need to perform some additional steps as described in a Support Tip on the InnoDB website.

9.4.2. Building the InnoDB Plugin on Microsoft Windows

The MySQL website includes some information about building from source on Windows. The following discussion is specifically focused on building a version of MySQL containing the InnoDB Plugin.

You need the following tools:

  • A compiler environment, one of the following:

    • Microsoft Visual C++ 2003

    • Microsoft Visual C++ 2005

    • Microsoft Visual C++ 2008 (Note: for building MySQL 5.1.31 or later)

    • Microsoft Visual C++ 2005 Express Edition (free of charge)

    • Microsoft Visual C++ 2008 Express Edition (free of charge, for building MySQL 5.1.31 or later)

  • GNU Bison for Windows, a general-purpose parser generator that is largely compatible with Berkeley Yacc. This tool is used automatically as part of compiling and building MySQL. For most users, it is sufficient to download and run the complete package to install GNU Bison.

  • CMake 2.6.0 or later, a cross-platform make system that can generate MSVC project files.

In addition to installing these tools, you must also set CScript as the default Windows script host by executing the following command in the Command Prompt:

cscript //H:CScript

After you have installed and configured all the required tools, you may proceed with the compilation.

  1. Download the MySQL source code, version 5.1.24 or later from the MySQL website and extract the source files.

  2. Download the InnoDB plugin source code from the MySQL download site.

  3. Extract the files from the source code archives.

  4. Replace the contents of the storage\innobase folder in the MySQL source tree with the InnoDB plugin source tree.

    In MySQL 5.1.38 and up, the MySQL source tree also contains a storage\innodb_plugin directory, but that does not affect this procedure. The source that you download from the InnoDB web site may contain additional changes and fixes.

  5. Compile and build MySQL under the Microsoft Visual Studio Command Prompt as follows:

    Visual Studio 2003:
    win\configure WITH_INNOBASE_STORAGE_ENGINE __NT__
    win\build-vs7.bat
    devenv mysql /build release /project ALL_BUILD
    Visual Studio 2005:
    win\configure WITH_INNOBASE_STORAGE_ENGINE __NT__
    win\build-vs8.bat
    devenv mysql /build release /project ALL_BUILD
    Visual Studio 2008:
    win\configure WITH_INNOBASE_STORAGE_ENGINE __NT__
    win\build-vs9.bat
    vcbuild mysql.sln "Release"

    For the 64-bit version, use win\build-vsN_x64.bat instead of win\build-vsN.bat.

  6. Install the compiled mysqld.exe from the sql\release folder of the source tree by doing one of the following:

    1. Copy the mysqld.exe to the bin folder of an earlier MySQL 5.1 installation.

    2. Make a distribution package and unpack it to the folder where MySQL will be installed. See the MySQL manual section on make_win_bin_dist—Package MySQL Distribution as Zip Archive. Note that scripts\make_win_bin_dist requires the Cygwin environment.

  7. Reconfigure the MySQL server by editing the my.cnf or my.ini option file to use InnoDB as the default engine (if desired) and set appropriate configuration parameters to enable use of new InnoDB Plugin features, as described in section Section 9.5, “Configuring the InnoDB Plugin”. In particular, we recommend that you set the following specific parameters as follows:

    default_storage_engine=InnoDB
    innodb_file_per_table=1
    innodb_file_format=barracuda
    innodb_strict_mode=1
  8. Since you built a version of MySQL that contains the InnoDB Plugin, you do not have to specify ignore_builtin_innodb or specify plugin-load, or issue any INSTALL PLUGIN statements. The mysqld.exe that you compiled contains the new InnoDB Plugin features.

9.5. Configuring the InnoDB Plugin

Because the MySQL server as distributed by MySQL includes a built-in copy of InnoDB, if you are using the dynamic InnoDB Plugin and have INSTALLed it into the MySQL server, you must always start the server with the option ignore_builtin_innodb, either in the option file or on the mysqld command line. Also, remember that the startup option skip_grant_tables prevents MySQL from loading any plugins. Neither of these options is needed when using a specialized version of MySQL that you build from source.

By default, the InnoDB Plugin does not create tables in a format that is incompatible with the built-in InnoDB in MySQL. Tables in the new format may be compressed, and they may store portions of long columns off-page, outside the B-tree nodes. You may wish to enable the creation of tables in the new format, using one of these techniques:

  • Include innodb_file_per_table=1 and innodb_file_format=barracuda in the [mysqld] section of the MySQL option file.

  • Add --innodb_file_per_table=1 and --innodb_file_format=barracuda to the mysqld command line.

  • Issue the statements:

    SET GLOBAL innodb_file_format=barracuda;
    SET GLOBAL innodb_file_per_table=ON;

    in the MySQL client when running with SUPER privileges.

You may also want to enable the new InnoDB strict mode, which guards SQL or certain operational errors that otherwise generate warnings and possible unintended consequences of ignored or incorrect SQL commands or parameters. As described in Section 8.5, “InnoDB Strict Mode”, the GLOBAL parameter innodb_strict_mode can be set ON or OFF in the same way as the parameters just mentioned. You can also use the command SET SESSION innodb_strict_mode=mode (where mode is ON or OFF) to enable or disable InnoDB strict mode on a per-session basis.

Take care when using new InnoDB configuration parameters or values that apply only when using the InnoDB Plugin. When the MySQL server encounters an unknown option, it fails to start and returns an error: unknown variable. This happens, for example, if you include the new parameter innodb_file_format when you start the MySQL server with the built-in InnoDB rather than the plugin. This can cause a problem if you accidentally use the built-in InnoDB after a system crash, because InnoDB crash recovery runs before MySQL checks the startup parameters. See Section 11.4, “Possible Problems” why this can be a problem. One safeguard is to specify the prefix loose_ before the names of new options, so that if they are not recognized on startup, the server gives a warning instead of a fatal error.

9.6. Frequently Asked Questions about Plugin Installation

9.6.1. Should I use the InnoDB-supplied plugin or the one that is included with MySQL 5.1.38 or higher?

The Plugin that you download from the InnoDB web site should always be at the same level or newer than the shared library that is included with the MySQL distribution starting with version 5.1.38. To pick up the very latest fixes, download from the InnoDB site.

9.6.2. Why doesn't the MySQL service on Windows start after the replacement?

For the types of errors and how to diagnose them, see Section 9.3.3, “Errors When Installing the InnoDB Plugin on Microsoft Windows”. Be especially careful that the plugin-load line in the option file does not get split across lines when you copy and paste from the README or this manual, which can produce an unrecognized option error in the error log.

9.6.3. The Plugin is installed... now what?

You automatically benefit from the fast index creation feature for every index you create on a large InnoDB table. If you switch to the Barracuda file format using the innodb_file_format option in combination with the innodb_file_per_table option, you can take advantage of other features such as table compression. For the full list of features, refer to Section 1.2, “Features of the InnoDB Plugin”.

9.6.4. Once the Plugin is installed, is it permanent?

The Plugin must loaded whenever the MySQL database server is started. As we saw earlier, there are several ways to configure MySQL to use the Plugin rather than the built-in InnoDB: in the option file, with mysqld command-line options, or with INSTALL statements after the server starts.

To ensure that you do not accidentally revert to the older InnoDB, be careful to carry any configuration file, command-line options, or post-startup commands forward in the future, such as when transitioning from a development system to a test system, setting up a replication slave, or when writing new mysqld startup scripts.