MySQL Bugs: #46794: Windows binary for 5.1.39 does not include InnoDB plugin

Bug #46794	Windows binary for 5.1.39 does not include InnoDB plugin
Submitted:	18 Aug 2009 19:20	Modified:	13 Sep 2009 14:11
Reporter:	Sheeri Cabral (Candidate Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Packaging	Severity:	S1 (Critical)
Version:	5.1.39-snapshot20090813-win-x86	OS:	Windows
Assigned to:	Jonathan Perkin	CPU Architecture:	Any
Tags:	InnoDB plugin, windows

Description:
Downloaded from labs.mysql.com -> MySQL 5.1 -> Windows 32-bit:

http://downloads.mysql.com/snapshots/pb/snapshot-5.1/tree-snapshot-win-x86-zip/mysql-5.1.3...

How to repeat:
Download from http://downloads.mysql.com/snapshots/pb/snapshot-5.1/tree-snapshot-win-x86-zip/mysql-5.1.3...

Extract the files

run mysqld.exe 

See that it doesn't start, has a problem binding to port 3306 even though there's nothing there

run mysqld.exe --verbose --help

See the default value for the plugin directory & load variable:

plugin-load                       (No default value)
plugin_dir                        C:\Documents and Settings\cabral\My Documents\
Downloads\mysql-5.1.39-snapshot20090813-win-x86\lib/plugin

Note the directory is /plugin, not \plugin

Now check to see if the directory is there:

C:\Documents and Settings\cabral\My Documents\Downloads\mysql-5.1.39-snapshot200
90813-win-x86\lib>dir
 Volume in drive C has no label.
 Volume Serial Number is 78E9-1042

 Directory of C:\Documents and Settings\cabral\My Documents\Downloads\mysql-5.1.
39-snapshot20090813-win-x86\lib

08/18/2009  03:05 PM    <DIR>          .
08/18/2009  03:05 PM    <DIR>          ..
08/18/2009  03:05 PM    <DIR>          Debug
08/18/2009  03:05 PM    <DIR>          opt
               0 File(s)              0 bytes
               4 Dir(s)  442,121,269,248 bytes free

it is not, in fact, there.

Then check to see if ha_innodb.dll is anywhere in the directory.  I used Windows search, and it's not there.

Suggested fix:
put in the plugin directory and the ha_innodb.dll (and license file) into the plugin directory.  Change the default plugin directory path to use all Windows path markers (ie, \ not /).

Er, my apologies, mysql does start, with both ignore_builtin_innodb on and off:

mysql> show variables like 'ignore%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| ignore_builtin_innodb | OFF   |
+-----------------------+-------+
1 row in set (0.00 sec)

mysql> exit
Bye

C:\Documents and Settings\cabral\My Documents\Downloads\mysql-5.1.39-snapshot200
90813-win-x86\bin>mysqladmin -u root shutdown

-------- now restart with --ignore-builtin-innodb

C:\Documents and Settings\cabral\My Documents\Downloads\mysql-5.1.39-snapshot200
90813-win-x86\bin>mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.39-snapshot20090813 Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show variables like 'ignore%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| ignore_builtin_innodb | ON    |
+-----------------------+-------+
1 row in set (0.00 sec)

mysql>

Thank you for the report.

But where did you find documentation about MySQL should have InnoDB Plugin by default in version 5.1? Could you please provide a link?

Please ignore previous comment: no feedback needed.

Missed this is snapshot and can include features not officially announced yet.

This is being addressed for the upcoming 5.1.38 release and the fix should make it into the main mysql-5.1 branch soon.

Bug #46870 was marked as duplicate of this one

not fixed in mysql-5.1.40-snapshot20090904

It seems that the plugin is included in 5.1.38 GA and 5.1.40 snapshot

$ unzip -l mysql-5.1.40-snapshot20090904-win-x86.zip |grep -i ha_innodb_plugin | perl -pe 's{.*?/}{}'
lib/plugin/Debug/ha_innodb_plugin.dll
lib/plugin/Debug/ha_innodb_plugin.pdb
lib/plugin/Debug/ha_innodb_plugin.lib
lib/plugin/ha_innodb_plugin.dll
lib/plugin/ha_innodb_plugin.pdb

$ unzip -l mysql-noinstall-5.1.38-win32.zip |grep -i ha_innodb_plugin | perl -pe 's{.*?/}{}'
lib/plugin/debug/ha_innodb_plugin.dll
lib/plugin/debug/ha_innodb_plugin.lib
lib/plugin/debug/ha_innodb_plugin.pdb
lib/plugin/ha_innodb_plugin.dll
lib/plugin/ha_innodb_plugin.pdb

Plugin is in 5.1.38 for windows and works fine.

However I am not able to start 5.1.40 snapshot 64 bit for windows (as a service), but I think I will have to check my my.ini again! It could very well be a a mistake of mine.

I used a functional my.ini of 5.1.38.  Edited datadir, basedir and port. No plugin specification in my.ini (I think the plugin should be default now?).  Verified the registry key defining service parameters. 'Net start mysqlsnap' returns 'system error 1067' after trying to start service for a few seconds.

Error log contains:
C:\Program Files\MySQL\mysql-snapshot\bin\mysqld: Too many arguments (first extra is 'C:\Program Files\MySQL\mysql-snapshot\my.ini').
..what is same as I experienced with 5.1.39 snapshot. 

Not totally sure that it is not my mistake somehow. So if somebody else can verify that 5.1.40 can be started on Windows please correct me! :-)

Peter,
if MySQL 5.1.40 doesn't start on Windows, please report a separate bug. This one deals with the plugin not included in the windows distribution.
Notice that the plugin is NOT on by default. For two reasons: (a) the plugin is still beta, and (b) MySQL 5.1 is GA, and we can't introduce such a huge change like an engine being replaced in this release. 
Please refer to the InnoDB documentation (or to my blog) for instructions on how to enable the plugin.

I used a functional my.ini of 5.1.38.  Edited datadir, basedir and port. No plugin specification in my.ini (I think the plugin should be default now?).  Verified the registry key defining service parameters. 'Net start mysqlsnap' returns 'system error 1067' after trying to start service for a few seconds.

Error log contains:
C:\Program Files\MySQL\mysql-snapshot\bin\mysqld: Too many arguments (first extra is 'C:\Program Files\MySQL\mysql-snapshot\my.ini').
..what is same as I experienced with 5.1.39 snapshot. 

my.ini:

[mysqld]

# The TCP/IP Port the MySQL Server will listen on
port=3302

#Path to installation directory. All paths are usually resolved relative to this.
basedir="C:\Program Files\MySQL\mysql-snapshot"

#Path to the database root
datadir="C:\Program Files\MySQL\mysql-snapshot\data"

# The default character set that will be used when a new schema or table is
# created and no character set is defined
default-character-set=utf8

# The default storage engine that will be used when create new tables when
default-storage-engine=INNODB

 -- etc

error log from last start attempt:

090912 23:42:07 [Note] Plugin 'FEDERATED' is disabled.
InnoDB: Error: log file .\ib_logfile0 is of different size 0 5242880 bytes
InnoDB: than specified in the .cnf file 0 56623104 bytes!
090912 23:42:07 [ERROR] Plugin 'InnoDB' init function returned error.
090912 23:42:07 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
C:\Program Files\MySQL\mysql-snapshot\bin\mysqld: Too many arguments (first extra is 'C:\Program Files\MySQL\mysql-snapshot\my.ini').
Use --verbose --help to get a list of available options
090912 23:42:07 [ERROR] Aborting

090912 23:42:07 [Warning] Forcing shutdown of 1 plugins
090912 23:42:07 [Note] C:\Program Files\MySQL\mysql-snapshot\bin\mysqld: Shutdown complete

.. I cannot see what is wrong!

This is wrong for the 5.1.39+ snapshots I believe: "Notice that the plugin is NOT on by default." (It is true for 5.1.38 however). I understand that from 5.1.39 the plugin is the default and only InnoDB implementation. I believe I am better updated than you here! ;-)

There is no plugin spec in configuration. The error log clearly tells that the plugin is attempted loaded.

I can start a new report for sure. Please confirm once more that I should the above information taken into considerations. Sveta will tell me that I should not create duplicates, but I don't mind discussing with Sveta! :-)

I will check tomorrow for reply (I'm so tired now that I am too prone to mistakes)

The manual clearly states that the plugin is beta and it is distributed in addition to the builtin one.
http://dev.mysql.com/doc/refman/5.1/en/news-5-1-38.html
What the manual says is the intended behavior.

"As of MySQL 5.1.38, the InnoDB Plugin is included in MySQL 5.1 releases, in addition to the built-in version of InnoDB that has been included in previous releases. This version of the InnoDB Plugin is 1.0.4 and is considered of Beta quality."

"To use the InnoDB Plugin, you must disable the built-in version of InnoDB that is also included and instruct the server to use InnoDB Plugin instead. "

One more thing. The built-in InnoDB engine *IS* a plugin, as are MyISAM, Memory, CSV, etc.

show variables like 'ignore%innodb';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| ignore_builtin_innodb | OFF   |
+-----------------------+-------+

select * from information_schema.plugins where plugin_name='innodb'\G
*************************** 1. row ***************************
           PLUGIN_NAME: InnoDB
        PLUGIN_VERSION: 1.0
         PLUGIN_STATUS: ACTIVE
           PLUGIN_TYPE: STORAGE ENGINE
   PLUGIN_TYPE_VERSION: 50140.0
        PLUGIN_LIBRARY: NULL
PLUGIN_LIBRARY_VERSION: NULL
         PLUGIN_AUTHOR: Innobase OY
    PLUGIN_DESCRIPTION: Supports transactions, row-level locking, and foreign keys
        PLUGIN_LICENSE: GPL

This is the built-in engine. Notice the version. 
select @@innodb_version;
ERROR 1193 (HY000): Unknown system variable 'innodb_version'
# this variable comes with the new plugin only.

The error you see in the error log appears because you changed the log file size and restarted the server. The plugin mentioned in the error log is the built-in one.

I edited the log file size. That removes one error. Error log now: 

090913  9:53:47 [Note] Plugin 'FEDERATED' is disabled.
C:\Program Files\MySQL\mysql-snapshot\bin\mysqld: Too many arguments (first extra is 'C:\Program Files\MySQL\mysql-snapshot\my.ini').
Use --verbose --help to get a list of available options
090913  9:53:48 [ERROR] Aborting

090913  9:53:50 [Warning] Forcing shutdown of 1 plugins
090913  9:53:50 [Note] C:\Program Files\MySQL\mysql-snapshot\bin\mysqld: Shutdown complete

Besides adding to configuration:
ignore-builtin-innodb
plugin-load=innodb=ha_innodb_plugin.dll

.. changes nothing. I have no problem with using the plugin with 5.1.38 (what you´probably noticed) but I cannot start 5.1.39 and 5.1.40 snapshot servers with same options. The error log records exactly the same with and without the above options. 

What about this:
Too many arguments (first extra is 'C:\Program Files\MySQL\mysql-snapshot\my.ini').

Now I also am uncertain if I should follow Sveta and use this post or I should open a new one (or reopen the one I posted that was marked as a duplicate)

@guiseppe .. I believe you are wrong about the 5.1.39 and 5.1.40 snapshots.  If I start server without parameters at all:

C:\Program Files\MySQL\mysql-snapshot\bin>mysqld

then "SHOW PLUGINS" returns 

Name        Status    Type            Library               License
----------  --------  --------------  --------------------  -------
binlog      ACTIVE    STORAGE ENGINE  (NULL)                GPL    
ARCHIVE     ACTIVE    STORAGE ENGINE  (NULL)                GPL    
BLACKHOLE   ACTIVE    STORAGE ENGINE  (NULL)                GPL    
CSV         ACTIVE    STORAGE ENGINE  (NULL)                GPL    
FEDERATED   DISABLED  STORAGE ENGINE  (NULL)                GPL    
MEMORY      ACTIVE    STORAGE ENGINE  (NULL)                GPL    
MyISAM      ACTIVE    STORAGE ENGINE  (NULL)                GPL    
MRG_MYISAM  ACTIVE    STORAGE ENGINE  (NULL)                GPL    
partition   ACTIVE    STORAGE ENGINE  (NULL)                GPL    
InnoDB      ACTIVE    STORAGE ENGINE  ha_innodb_plugin.dll  GPL    

.. what means that the (new) plugin is enabled as default.  I also read that somewhere.  Did you consider that the docs may not be updated for the 'Labs' releases?

Peter, what if you start it like this?  Same output ?

mysqld --no-defaults --console

This works:
C:\Program Files\MySQL\mysql-snapshot\bin>mysqld --defaults-file="c:\my.ini"

But this doesn't
C:\Program Files\MySQL\mysql-snapshot\bin>mysqld --install mysqlsnap --defaults-
file="c:\my.ini"
Service successfully installed.

C:\Program Files\MySQL\mysql-snapshot\bin>net start mysqlsnap
Tjenesten mysqlsnap starter..
Tjenesten mysqlsnap kunne ikke startes.

Der opstod en systemfejl.

Systemfejlen 1067 opstod.

Processen sluttede uventet.

C:\Program Files\MySQL\mysql-snapshot\bin>

error log for start attempt as a service: 

090913 11:37:28 [Note] Plugin 'FEDERATED' is disabled.
C:\Program Files\MySQL\mysql-snapshot\bin\mysqld: Too many arguments (first extra is 'c:\my.ini').
Use --verbose --help to get a list of available options
090913 11:37:29 [ERROR] Aborting

090913 11:37:30 [Warning] Forcing shutdown of 1 plugins
090913 11:37:30 [Note] C:\Program Files\MySQL\mysql-snapshot\bin\mysqld: Shutdown complete

So the won't start as a service ("Too many arguments (first extra is 'c:\my.ini')"). That is the problem. That was probably also the problem when I tried 5.1.39. So the bug report I posted should probably not have been marked as a duplicate of this one.

reply to Shane:

C:\Program Files\MySQL\mysql-snapshot\bin>mysqld --NO-defaults --console
090913 11:49:29 [Note] PLUGIN 'FEDERATED' IS disabled.
INNODB: The LOG sequence number IN ibdata files does NOT MATCH
INNODB: the LOG sequence number IN the ib_logfiles!
090913 11:49:29  INNODB: DATABASE was NOT shut down normally!
INNODB: STARTING crash recovery.
INNODB: Reading TABLESPACE information FROM the .ibd files...
INNODB: Restoring possible half-written DATA pages FROM the doublewrite
INNODB: buffer...
090913 11:49:29  INNODB: Started; LOG sequence number 0 44363
090913 11:49:29 [Note] Event Scheduler: Loaded 0 EVENTS
090913 11:49:29 [Note] mysqld: ready FOR connections.
VERSION: '5.1.40-snapshot20090904'  SOCKET: ''  PORT: 3306  Source distribution

SHOW PLUGINS:

NAME        STATUS    TYPE            Library  License
----------  --------  --------------  -------  -------
BINLOG      ACTIVE    STORAGE ENGINE  (NULL)   GPL    
ARCHIVE     ACTIVE    STORAGE ENGINE  (NULL)   GPL    
BLACKHOLE   ACTIVE    STORAGE ENGINE  (NULL)   GPL    
CSV         ACTIVE    STORAGE ENGINE  (NULL)   GPL    
FEDERATED   DISABLED  STORAGE ENGINE  (NULL)   GPL    
MEMORY      ACTIVE    STORAGE ENGINE  (NULL)   GPL    
INNODB      ACTIVE    STORAGE ENGINE  (NULL)   GPL    
MYISAM      ACTIVE    STORAGE ENGINE  (NULL)   GPL    
MRG_MYISAM  ACTIVE    STORAGE ENGINE  (NULL)   GPL    
PARTITION   ACTIVE    STORAGE ENGINE  (NULL)   GPL    

.. so it reads some default file ... :-(
OK .. seems like I am confused. I will have to doublecheck once more (a complication: I need to have UAC on due to some other test running)

But my original problem was that I could not start the snapshot server as a service really!

I am sorry for spamming Sheeri here.  
I have now reopened http://bugs.mysql.com/bug.php?id=46870 for discussion of the failure to start as service as Sheeri is probably not interested in getting notifications mails about this.

The plugin defaut/not default discussion was a 'sidestep'. But there really is lot of confusion about InnoDB in 5.1.39.  From the beginning of this thread I can read that also Sheeri expected the new plugin as default for 5.1.39.  It has been communicated somewhere on MySQL website.  I am pretty sure about that.

(and I am not able to understand from where configuration was read in previous example.  There was no matching parallel user file in \virtualstore.  And besides I had started cmd.exe with explicit administrator option. But the only reason why I am here is the service problem. So I will concentrate on that.)

One more post: 
I found out how it could read an options file:

output of 'mysqld --verbose -- help'
"Default options are read from the following files in the given order:
C:\Windows\my.ini C:\Windows\my.cnf C:\my.ini C:\my.cnf C:\Program
Files\MySQL\MySQL Server 5.1\my.ini C:\Program Files\MySQL\MySQL Server
5.1\my.cnf"

.. there was a c:\my.ini!
 

This is not documented in documentation! I have posted Bug #47293

Closing bug, original problem was fixed.