MySQL Bugs: #48787: Built-In InnoDB plugin won't load or initialize

Bug #48787	Built-In InnoDB plugin won't load or initialize
Submitted:	16 Nov 2009 2:56	Modified:	20 Nov 2009 10:46
Reporter:	Fred Zirdung	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: InnoDB Plugin storage engine	Severity:	S3 (Non-critical)
Version:	5.1.40, 5.1.41	OS:	MacOS (10.6.2)
Assigned to:		CPU Architecture:	Any
Tags:	innodb, osx

Description:
The built-in InnoDB plugin doesn't load in Mac OS X 10.6.2 (Snow Leopard) using the x86_64 version of MySQL. Because of this, the storage engine is not available. 

I originally found the problem in 5.1.39. After upgrading to 5.1.40, I found the problem had not been resolved.

How to repeat:
Install MySQL 5.1.40 x86_64 on Snow Leopard, run mysql client, run show engines.

mysql> show engines;
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine     | Support | Comment                                                        | Transactions | XA   | Savepoints |
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
| MyISAM     | DEFAULT | Default engine as of MySQL 3.23 with great performance         | NO           | NO   | NO         |
| MRG_MYISAM | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| BLACKHOLE  | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| CSV        | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| MEMORY     | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| FEDERATED  | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| ARCHIVE    | YES     | Archive storage engine                                         | NO           | NO   | NO         |
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
7 rows in set (0.00 sec)

mysql> SHOW VARIABLES LIKE 'plugin_dir';
+---------------+---------------------------------------------------+
| Variable_name | Value                                             |
+---------------+---------------------------------------------------+
| plugin_dir    | /usr/local/mysql-5.1.40-osx10.5-x86_64/lib/plugin |
+---------------+---------------------------------------------------+
1 row in set (0.00 sec)

mysql> INSTALL PLUGIN INNODB SONAME 'ha_innodb_plugin.so';
ERROR 1123 (HY000): Can't initialize function 'INNODB'; Plugin initialization function failed.

# my.cnf --------------------------------------------------------
# The MySQL server
[mysqld]
port		= 3306
socket		= /tmp/mysql.sock
innodb_file_per_table
skip-locking
key_buffer_size = 256M
max_allowed_packet = 1M
table_open_cache = 256
sort_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size= 16M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 4

server-id	= 1

# Uncomment the following if you are using InnoDB tables
innodb_data_home_dir = /usr/local/mysql/data/
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /usr/local/mysql/data/
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
innodb_buffer_pool_size = 256M
innodb_additional_mem_pool_size = 20M
# Set .._log_file_size to 25 % of buffer pool size
innodb_log_file_size = 64M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50

Please, check if the same problem happens with MySQL 5.1.41.

5.1.41 doesn't fix the problem. Same results.

Can't repeat with
* package mysql-advanced-gpl-5.1.39-osx10.5-x86_64
* on MacOS X 10.6.2:

(Will try with 5.1.41/community in a few, it's still downloading)

Breakdown:

$ file bin/mysqld
bin/mysqld: Mach-O 64-bit executable x86_64

Starting --ignore-builtin-innodb:
 [mysqld]
 ignore_builtin_innodb
 plugin-load=innodb=ha_innodb_plugin.so;innodb_trx=ha_innodb_plugin.so;innodb_locks=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

Doing it first without the option --plugin-load set
in the option file, I can load it fine:

mysql> SHOW ENGINES;
+------------+---------+------------------------
| Engine     | Support | Comment                
+------------+---------+-------------------------
| MyISAM     | DEFAULT | Default engine as of MyS
| MRG_MYISAM | YES     | Collection of identical 
| BLACKHOLE  | YES     | /dev/null storage engine
| CSV        | YES     | CSV storage engine      
| MEMORY     | YES     | Hash based, stored in me
| FEDERATED  | NO      | Federated MySQL storage 
| ARCHIVE    | YES     | Archive storage engine  
+------------+---------+-------------------------

mysql> INSTALL PLUGIN INNODB SONAME 'ha_innodb_plugin.so';
Query OK, 0 rows affected (0.24 sec)

mysql> SELECT ENGINE,SUPPORT FROM INFORMATION_SCHEMA.ENGINES;
+------------+---------+
| ENGINE     | SUPPORT |
+------------+---------+
| MyISAM     | DEFAULT |
| InnoDB     | YES     |
| BLACKHOLE  | YES     |
| CSV        | YES     |
| MEMORY     | YES     |
| FEDERATED  | NO      |
| ARCHIVE    | YES     |
| MRG_MYISAM | YES     |
+------------+---------+

On restart with the --plugin-load option set in the option file, I get this:

mysql> SELECT PLUGIN_NAME FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE 'InnoDB%';
+---------------------+
| PLUGIN_NAME         |
+---------------------+
| InnoDB              |
| INNODB_TRX          |
| INNODB_LOCKS        |
| INNODB_CMP          |
| INNODB_CMP_RESET    |
| INNODB_CMPMEM       |
| INNODB_CMPMEM_RESET |
+---------------------+

I discovered the problem. The log file didn't match what was in the cnf file:

091120 02:23:42 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data
091120  2:23:42 [Warning] Setting lower_case_table_names=2 because file system for /usr/local/mysql/data/ is case insensitive
091120  2:23:42 [Note] Plugin 'FEDERATED' is disabled.
InnoDB: Error: log file /usr/local/mysql/data/ib_logfile0 is of different size 0 5242880 bytes
InnoDB: than specified in the .cnf file 0 67108864 bytes!
091120  2:23:43 [ERROR] Plugin 'InnoDB' init function returned error.
091120  2:23:43 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
091120  2:23:43 [Note] Event Scheduler: Loaded 0 events
091120  2:23:43 [Note] /usr/local/mysql/bin/mysqld: ready for connections.
Version: '5.1.39'  socket: '/tmp/mysql.sock'  port: 3306  MySQL Community Server (GPL)
091120  2:24:47 [Note] /usr/local/mysql/bin/mysqld: Normal shutdown

I forgot about one step that I performed during installation, which was not in my problem description.  That step was to copy the data dir from a previous installation. By redoing the install from a clean tar.gz file I was not able to reproduce the failure.

Sorry...

The workaround I found is to delete the two log files and let InnoDB enter recovery mode, where it regens the logfiles. After that, everything works as expected.

So, this problem was not a result of any bug in MySQL code. Workaround is described, to some extent, in http://dev.mysql.com/doc/refman/5.1/en/adding-and-removing.html.