You are here

Home » Blogs » Shinguz's blog

How can I find what InnoDB version I am using?

Sat, 2011-01-01 12:05 — Shinguz

In the old days everything was simpler. We had one maker of our favourite database management system and possibly the choice between different Storage Engines. Mostly the decision has to be taken between MyISAM and InnoDB. When you care about your data integrity you have chosen InnoDB.

Nowadays it is more complex. We have several different makers of our favourite database management system: Oracle/MySQL, Monty Program AB and Percona with their products: MySQL, MariaDB and Percona Server.

We have different performant and reliable transactional Storage Engines: InnoDB, XtraDB and PBXT and to make it more difficult we can have InnoDB even as built-in and as plugin in the same release (5.1 only).

XtraDB is a fork of InnoDB and claims to be a drop-in replacement for InnoDB. They look quite similar but have partly different features. When you come to a new customer it makes sense to find out which version of InnoDB/XtraDB they are running. When you ask them sometimes they do not know because they did not care (then it is typically the default plugin) or because the installation was done by somebody else (hoster, system administrator, DBA, etc.).

So we should find out what Fork (InnoDB/XtraDB), Version (old, 1.0 or 1.1) or Type (built-in or plugin) we are dealing with.

This writing should help to guide you through how to find what version of InnoDB you are using:

MySQL 5.1.x built-in InnoDB

MySQL Error Log

110101 10:41:36  InnoDB: Started; log sequence number 2 1904218532

INFORMATION_SCHEMA PLUGINS table

mysql> SELECT PLUGIN_NAME, PLUGIN_VERSION, PLUGIN_TYPE_VERSION, PLUGIN_LIBRARY
     , PLUGIN_LIBRARY_VERSION, PLUGIN_AUTHOR
  FROM information_schema.PLUGINS
 WHERE PLUGIN_NAME = 'innodb';
+-------------+----------------+---------------------+----------------+------------------------+---------------+
| PLUGIN_NAME | PLUGIN_VERSION | PLUGIN_TYPE_VERSION | PLUGIN_LIBRARY | PLUGIN_LIBRARY_VERSION | PLUGIN_AUTHOR |
+-------------+----------------+---------------------+----------------+------------------------+---------------+
| InnoDB      | 1.0            | 50150.0             | NULL           | NULL                   | Innobase OY   |
+-------------+----------------+---------------------+----------------+------------------------+---------------+

MySQL 5.1.x plugin InnoDB

MySQL Error Log

InnoDB: The InnoDB memory heap is disabled
InnoDB: Mutexes and rw_locks use GCC atomic builtins
InnoDB: Compressed tables use zlib 1.2.3
110101 10:35:44  InnoDB: highest supported file format is Barracuda.
110101 10:35:44  InnoDB: Warning: allocated tablespace 50, old maximum was 0
110101 10:35:44 InnoDB Plugin 1.0.11 started; log sequence number 10494153124

Version in GLOBAL VARIABLES

mysql> SHOW GLOBAL VARIABLES LIKE 'innodb_ver%';
+----------------+--------+
| Variable_name  | Value  |
+----------------+--------+
| innodb_version | 1.0.11 |
+----------------+--------+

INFORMATION_SCHEMA PLUGINS table

mysql> SELECT PLUGIN_NAME, PLUGIN_VERSION, PLUGIN_TYPE_VERSION, PLUGIN_LIBRARY
     , PLUGIN_LIBRARY_VERSION, PLUGIN_AUTHOR
  FROM information_schema.PLUGINS
 WHERE PLUGIN_NAME = 'innodb';
+---------------------+----------------+---------------------+---------------------+------------------------+---------------+
| PLUGIN_NAME         | PLUGIN_VERSION | PLUGIN_TYPE_VERSION | PLUGIN_LIBRARY      | PLUGIN_LIBRARY_VERSION | PLUGIN_AUTHOR |
+---------------------+----------------+---------------------+---------------------+------------------------+---------------+
| InnoDB              | 1.0            | 50150.0             | ha_innodb_plugin.so | 1.0                    | Innobase Oy   |
+---------------------+----------------+---------------------+---------------------+------------------------+---------------+

INFORMATION_SCHEMA add-ons

mysql> SHOW TABLES FROM information_schema LIKE 'INNODB%';
+----------------------------------------+
| Tables_in_information_schema (INNODB%) |
+----------------------------------------+
| INNODB_CMP_RESET                       |
| INNODB_TRX                             |
| INNODB_CMPMEM_RESET                    |
| INNODB_LOCK_WAITS                      |
| INNODB_CMPMEM                          |
| INNODB_CMP                             |
| INNODB_LOCKS                           |
+----------------------------------------+

MySQL 5.5.x built-in InnoDB

MySQL Error Log

InnoDB: The InnoDB memory heap is disabled
InnoDB: Mutexes and rw_locks use InnoDB's own implementation
InnoDB: Compressed tables use zlib 1.2.3
110101 10:42:32  InnoDB: Using Linux native AIO
110101 10:42:32  InnoDB: Initializing buffer pool, size = 128.0M
110101 10:42:33  InnoDB: Completed initialization of buffer pool
110101 10:42:33  InnoDB: highest supported file format is Barracuda.
110101 10:42:33  InnoDB: 1.1.3 started; log sequence number 1600165

Version in GLOBAL VARIABLES

mysql> SHOW GLOBAL VARIABLES LIKE 'innodb_ver%';
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| innodb_version | 1.1.3 |
+----------------+-------+

INFORMATION_SCHEMA PLUGINS table

mysql> SELECT PLUGIN_NAME, PLUGIN_VERSION, PLUGIN_TYPE_VERSION, PLUGIN_LIBRARY
     , PLUGIN_LIBRARY_VERSION, PLUGIN_AUTHOR
  FROM information_schema.PLUGINS
 WHERE PLUGIN_NAME = 'innodb';
+---------------------+----------------+---------------------+----------------+------------------------+---------------+
| PLUGIN_NAME         | PLUGIN_VERSION | PLUGIN_TYPE_VERSION | PLUGIN_LIBRARY | PLUGIN_LIBRARY_VERSION | PLUGIN_AUTHOR |
+---------------------+----------------+---------------------+----------------+------------------------+---------------+
| InnoDB              | 1.1            | 50507.0             | NULL           | NULL                   | Innobase Oy   |
+---------------------+----------------+---------------------+----------------+------------------------+---------------+

INFORMATION_SCHEMA add-ons

mysql> SHOW TABLES FROM information_schema LIKE 'INNODB%';
+----------------------------------------+
| Tables_in_information_schema (INNODB%) |
+----------------------------------------+
| INNODB_CMP_RESET                       |
| INNODB_TRX                             |
| INNODB_CMPMEM_RESET                    |
| INNODB_LOCK_WAITS                      |
| INNODB_CMPMEM                          |
| INNODB_CMP                             |
| INNODB_LOCKS                           |
+----------------------------------------+

MariaDB 5.1.x built-in XtraDB and Percona Server

MySQL Error Log

InnoDB: The InnoDB memory heap is disabled
InnoDB: Mutexes and rw_locks use GCC atomic builtins
110101 10:47:49  InnoDB: highest supported file format is Barracuda.
110101 10:47:49 InnoDB Plugin 1.0.6-9 started; log sequence number 178248

Version in GLOBAL VARIABLES

mysql> SHOW GLOBAL VARIABLES LIKE 'innodb_ver%';
+----------------+---------+
| Variable_name  | Value   |
+----------------+---------+
| innodb_version | 1.0.6-9 |
+----------------+---------+

INFORMATION_SCHEMA PLUGINS table

mysql> SELECT PLUGIN_NAME, PLUGIN_VERSION, PLUGIN_TYPE_VERSION, PLUGIN_LIBRARY
     , PLUGIN_LIBRARY_VERSION, PLUGIN_AUTHOR
  FROM information_schema.PLUGINS
 WHERE PLUGIN_NAME = 'innodb';
+--------------------------------+----------------+---------------------+----------------+------------------------+---------------+
| PLUGIN_NAME                    | PLUGIN_VERSION | PLUGIN_TYPE_VERSION | PLUGIN_LIBRARY | PLUGIN_LIBRARY_VERSION | PLUGIN_AUTHOR |
+--------------------------------+----------------+---------------------+----------------+------------------------+---------------+
| InnoDB                         | 1.0            | 50144.0             | NULL           | NULL                   | Innobase Oy   |
+--------------------------------+----------------+---------------------+----------------+------------------------+---------------+

INFORMATION_SCHEMA add-ons

 mysql> SHOW TABLES FROM information_schema LIKE '____DB%';
+----------------------------------------+
| Tables_in_information_schema (____DB%) |
+----------------------------------------+
| INNODB_BUFFER_POOL_PAGES               |
| INNODB_CMP                             |
| INNODB_RSEG                            |
| INNODB_BUFFER_POOL_PAGES_INDEX         |
| INNODB_TRX                             |
| INNODB_CMP_RESET                       |
| INNODB_LOCK_WAITS                      |
| INNODB_CMPMEM_RESET                    |
| INNODB_LOCKS                           |
| INNODB_CMPMEM                          |
| INNODB_TABLE_STATS                     |
| INNODB_BUFFER_POOL_PAGES_BLOB          |
| INNODB_INDEX_STATS                     |
| XTRADB_ENHANCEMENTS                    |
| XTRADB_ADMIN_COMMAND                   |
+----------------------------------------+

MariaDB 5.2.x built-in XtraDB and Percona Server

MySQL Error Log

InnoDB: The InnoDB memory heap is disabled
InnoDB: Mutexes and rw_locks use GCC atomic builtins
InnoDB: Compressed tables use zlib 1.2.3
110101 10:52:54  InnoDB: highest supported file format is Barracuda.
110101 10:52:54 Percona XtraDB (http://www.percona.com) 1.0.13-11.6 started; log sequence number 45356

Version in GLOBAL VARIABLES

mysql> SHOW GLOBAL VARIABLES LIKE 'innodb_ver%';
+----------------+-------------+
| Variable_name  | Value       |
+----------------+-------------+
| innodb_version | 1.0.13-11.6 |
+----------------+-------------+

INFORMATION_SCHEMA PLUGINS table

mysql> SELECT PLUGIN_NAME, PLUGIN_VERSION, PLUGIN_TYPE_VERSION, PLUGIN_LIBRARY
     , PLUGIN_LIBRARY_VERSION, PLUGIN_AUTHOR
  FROM information_schema.PLUGINS
 WHERE PLUGIN_NAME = 'innodb';
+--------------------------------+----------------+---------------------+----------------+------------------------+---------------+
| PLUGIN_NAME                    | PLUGIN_VERSION | PLUGIN_TYPE_VERSION | PLUGIN_LIBRARY | PLUGIN_LIBRARY_VERSION | PLUGIN_AUTHOR |
+--------------------------------+----------------+---------------------+----------------+------------------------+---------------+
| InnoDB                         | 1.0            | 50204.0             | NULL           | NULL                   | Percona       |
+--------------------------------+----------------+---------------------+----------------+------------------------+---------------+

INFORMATION_SCHEMA add-ons

mysql> SHOW TABLES FROM information_schema LIKE '____DB%';
+----------------------------------------+
| Tables_in_information_schema (____B%) |
+----------------------------------------+
| INNODB_BUFFER_POOL_PAGES               |
| INNODB_CMP                             |
| INNODB_RSEG                            |
| INNODB_BUFFER_POOL_PAGES_INDEX         |
| INNODB_TRX                             |
| INNODB_SYS_TABLES                      |
| INNODB_LOCK_WAITS                      |
| INNODB_SYS_STATS                       |
| INNODB_LOCKS                           |
| INNODB_CMPMEM                          |
| INNODB_TABLE_STATS                     |
| INNODB_SYS_INDEXES                     |
| INNODB_CMP_RESET                       |
| INNODB_BUFFER_POOL_PAGES_BLOB          |
| INNODB_CMPMEM_RESET                    |
| INNODB_INDEX_STATS                     |
| XTRADB_ENHANCEMENTS                    |
| XTRADB_ADMIN_COMMAND                   |
+----------------------------------------+
Taxonomy upgrade extras: 
english
storage engine
innodb
plugin
pbxt
version
xtradb
builtin