MySQL Bugs: #42610: Dynamic plugin broken in 5.1.31

Bug #42610	Dynamic plugin broken in 5.1.31
Submitted:	4 Feb 23:11	Modified:	18 Mar 15:59
Reporter:	Calvin Sun
Status:	Closed
Category:	Server	Severity:	S4 (Feature request)
Version:	5.1.31	OS:	Any
Assigned to:	Sergey Gluhov	Target Version:	5.1.33
Triage:	Triaged: D2 (Serious)

Description:
The dynamic plugin is broken in 5.1.31, due to the "fix" of bug#29263. Before the "fix",
a plugin (engine) is not added to the plugin list. But now, it is added into the plugin
list with "DISABLED" status. When trying to load a dynamic plugin with the same name, the
server refuses with error "already exists" - even the builtin one is already disabled.
This breaks the compatibility.

The following is from the latest MySQL 5.1 tree, plus InnoDB plugin.

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.1.32    |
+-----------+
1 row in set (0.00 sec)

mysql> show variables like '%innodb%';
+---------------+----------+
| Variable_name | Value    |
+---------------+----------+
| have_innodb   | DISABLED |
+---------------+----------+
1 row in set (0.01 sec)

mysql> show engines;
+------------+---------+------------------------------------------------------------+--------------+------+------------+
| Engine     | Support | Comment                                                    |
Transactions | XA   | Savepoints |
+------------+---------+------------------------------------------------------------+--------------+------+------------+
| CSV        | YES     | CSV storage engine                                         | NO 
         | NO   | NO         |
| MyISAM     | DEFAULT | Default engine as of MySQL 3.23 with great performance     | NO 
         | NO   | NO         |
| MEMORY     | YES     | Hash based, stored in memory, useful for temporary tables  | NO 
         | NO   | NO         |
| InnoDB     | NO      | Supports transactions, row-level locking, and foreign keys |
NULL         | NULL | NULL       |
| MRG_MYISAM | YES     | Collection of identical MyISAM tables                      | NO 
         | NO   | NO         |
+------------+---------+------------------------------------------------------------+--------------+------+------------+
5 rows in set (0.00 sec)

mysql> show plugins;
+------------+----------+----------------+---------+---------+
| Name       | Status   | Type           | Library | License |
+------------+----------+----------------+---------+---------+
| binlog     | ACTIVE   | STORAGE ENGINE | NULL    | GPL     |
| CSV        | ACTIVE   | STORAGE ENGINE | NULL    | GPL     |
| MEMORY     | ACTIVE   | STORAGE ENGINE | NULL    | GPL     |
| InnoDB     | DISABLED | STORAGE ENGINE | NULL    | GPL     |
| MRG_MYISAM | ACTIVE   | STORAGE ENGINE | NULL    | GPL     |
| MyISAM     | ACTIVE   | STORAGE ENGINE | NULL    | GPL     |
+------------+----------+----------------+---------+---------+
6 rows in set (0.00 sec)

mysql> INSTALL PLUGIN INNODB SONAME 'ha_innodb.so';
ERROR 1125 (HY000): Function 'INNODB' already exists
mysql> show errors;
+-------+------+----------------------------------+
| Level | Code | Message                          |
+-------+------+----------------------------------+
| Error | 1125 | Function 'INNODB' already exists |
+-------+------+----------------------------------+
1 row in set (0.00 sec) 

How to repeat:
- use the MySQL 5.1.31 or 5.1.32
- use the InnoDB plugin
- build the server and the plugin
- try to install the plugin following
http://www.innodb.com/doc/innodb_plugin-1.0/innodb-plugin-installation.html

Thank you for the report.

Verified as described.

Problem is UNINSTALL PLUGIN does not work for built-it plugins:

CURRENT_TEST: main.bug42610
mysqltest: At line 3: query 'uninstall plugin InnoDB' failed: 1305: PLUGIN InnoDB does
not exist

The result from queries just before the failure was:
...
Warnings from just before the error:
Warning 1619 Built-in plugins cannot be deleted

It was always considered a bug that one can load a plugin with the same name as the
already installed plugin (I was quite surprised when I've heard on the conference that it
actually worked). It's fixed now.

One is supposed to use MySQL without InnoDB to load InnoDB into it.

Well, MySQL releases InnoDB as a builtin engine. And it can not be uninstalled.

By the objective standard of creating an incompatibility, the effect of fixing bug 29263
(http://bugs.mysql.com/bug.php?id=29263) has in fact created another BUG.  What was
possible before (loading the InnoDB Plugin into an existing binary mysqld, replacing the
builtin InnoDB), is no longer possible.

This issue should be re-opened as a bug, so that we can track its repair.

For those users who want to use the latest InnoDB Plugin in a binary distribution of
MySQL, this is a serious limitation.  The lack of previously available functionality is
serious, there is no workaround, and the impact is substantial.  The "effort" and "risk"
is low in "unfixing" bug 29263, which would restore the status quo ante.

Ouch, sorry. You're right.
Somehow I believed that one can download MySQL binary without InnoDB, but it is not the
case :(

I see two solutions then:
* either provide a binary without InnoDB
* or add a special hack that will allow end user to pretend that there is no InnoDB built
in, even if there is one.

Thank you Sergei ...

We would like to find a solution to this problem, and will work with MySQL to do so.

In the meantime, please re-open this bug, and consider the comments about the proper
triage values.   This bug report provides a convenient way to track progress toward
resolution of this problem.

thanks

Pushed into 5.1.32 (revid:davi.arnaut@sun.com-20090209214102-gj3sb3ujpnvpiy4c) (version
source revid:davi.arnaut@sun.com-20090209214102-gj3sb3ujpnvpiy4c) (merge vers: 5.1.32)
(pib:6)

Pushed into 6.0.10-alpha (revid:matthias.leich@sun.com-20090212211028-y72faag15q3z3szy)
(version source revid:matthias.leich@sun.com-20090212211028-y72faag15q3z3szy) (merge
vers: 6.0.10-alpha) (pib:6)

Pushed into 5.1.32-ndb-6.3.23 (revid:tomas.ulin@sun.com-20090217131017-6u8qz1edkjfiobef)
(version source revid:tomas.ulin@sun.com-20090216083408-rmvyaxjt6mk8sg1y) (merge vers:
5.1.32-ndb-6.3.23) (pib:6)

Pushed into 5.1.32-ndb-6.4.3 (revid:tomas.ulin@sun.com-20090217134419-5ha6xg4dpedrbmau)
(version source revid:tomas.ulin@sun.com-20090216083646-m8st11oj1hhfuuh5) (merge vers:
5.1.32-ndb-6.4.3) (pib:6)

Pushed into 5.1.32-ndb-6.2.17 (revid:tomas.ulin@sun.com-20090217134216-5699eq74ws4oxa0j)
(version source revid:tomas.ulin@sun.com-20090211111208-wf0acl7c1vl5653e) (merge vers:
5.1.32-ndb-6.2.17) (pib:6)

This issue is present in MySQL Cluster NDB-6.4.3, which is based on the 5.1.32 MySQL
Server. Per email discussion with Tomas, I've added the following note to the NDB-6.4.3
changelog:

        *Important Note* It is not possible in this release to install the 
        InnoDB plugin if InnoDB support is already compiled into mysqld. 
        (Bug #42610).

        This issue was introduced by the fix for Bug #29263.

I'll do the same for any other 5.1.32-based MySQL Cluster releases that get released
without the fix.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/69037

2832 Sergey Glukhov	2009-03-12
      Bug#42610 Dynamic plugin broken in 5.1.31(2nd part)
      --added ability to obtain plugin variables from my.cnf on INSTALL PLUGIN stage
      --option 'ignore-builtin-innodb' disables all InnoDB builtin plugins
      (including I_S plugins)
     @ sql/mysqld.cc
        --added ability to obtain plugin variables from my.cnf on INSTALL PLUGIN stage
     @ sql/sql_plugin.cc
        --added ability to obtain plugin variables from my.cnf on INSTALL PLUGIN stage
        --option 'ignore-builtin-innodb' disables all InnoDB builtin plugins
        (including I_S plugins)

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/69070

2832 Sergey Glukhov	2009-03-12
      Bug#42610 Dynamic plugin broken in 5.1.31
      --added ability to obtain plugin variables from my.cnf on INSTALL PLUGIN stage
      --option 'ignore-builtin-innodb' disables all InnoDB builtin plugins
      (including I_S plugins)
     @ sql/mysql_priv.h
        --added ability to obtain plugin variables from my.cnf on INSTALL PLUGIN stage
     @ sql/mysqld.cc
        --added ability to obtain plugin variables from my.cnf on INSTALL PLUGIN stage
     @ sql/sql_plugin.cc
        --added ability to obtain plugin variables from my.cnf on INSTALL PLUGIN stage
        --option 'ignore-builtin-innodb' disables all InnoDB builtin plugins
        (including I_S plugins)

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/69103

2835 Sergey Glukhov	2009-03-13
      Bug#42610 Dynamic plugin broken in 5.1.31
      --added ability to obtain plugin variables from my.cnf on INSTALL PLUGIN stage
      --option 'ignore-builtin-innodb' disables all InnoDB builtin plugins
        (including I_S plugins)
     @ sql/mysql_priv.h
        --added ability to obtain plugin variables from my.cnf on INSTALL PLUGIN stage
     @ sql/mysqld.cc
        --added ability to obtain plugin variables from my.cnf on INSTALL PLUGIN stage
     @ sql/sql_plugin.cc
        --added ability to obtain plugin variables from my.cnf on INSTALL PLUGIN stage
        --option 'ignore-builtin-innodb' disables all InnoDB builtin plugins
          (including I_S plugins)

Pushed into 5.1.33 (revid:joro@sun.com-20090313111355-7bsi1hgkvrg8pdds) (version source
revid:sergey.glukhov@sun.com-20090313085125-moautfrmj79ldxx4) (merge vers: 5.1.33)
(pib:6)

Noted in 5.1.33 changelog.

The fix for Bug#29263 resulted in the problem that the InnoDB plugin
could not be used with a server that was compiled with the built-in
InnoDB. Now the server supports an --ignore-builtin-innodb option
that causes the server to ignore the built-in InnoDB so that the
InnoDB plugin can be used.

Also updated InnoDB option/variable lists.

Paul: not only. There are two features here.

The first is --ignore-builtin-innodb option, that makes the server to behave as if innodb
is not compiled in at all (for example, command-line innodb options are not recognized),
you may want to highlight the differences between this and --skip-innodb (which simply
turns innodb off).

The second is - on INSTALL PLUGIN MySQL reads my.cnf files (exactly the same procedure as
on startup is repeated) and allows the plugin to pick up options from there. Plugin *no
longer* is started with all options set to defaults now.
So, one can add plugin options to my.cnf even before loading a plugin (using the loose-
prefix, of course). Another side-effect - one can uninstall plugin, edit my.cnf, and
install plugin again - and it'll see new option values. Server restart isn't needed, only
plugin restart.

Revised changelog entry:

An earlier bug fix resulted in the problem that the InnoDB plugin
could not be used with a server that was compiled with the built-in
InnoDB. To handle this two changes were made:

* The server now supports an --ignore-builtin-innodb option that causes
  the server to behave as if the built-in InnoDB is not present. This
  option causes other InnoDB options not to be recognized.

* For the INSTALL PLUGIN statement, the server reads option (my.cnf)
  files just as during server startup. This enables the plugin to pick
  up any relevant options from those files. Consequently, a plugin no
  longer is started with each option set to its default value.

Because of this change, it is possible to add plugin options to an
option file even before loading a plugin (if the loose prefix is
used). It is also possible to uninstall a plugin, edit my.cnf, and
install the plugin again. Restarting the plugin this way enables it
to the new option values without a server restart.

Note:
To take advantage of this bug fix, InnoDB Plugin 1.0.4 or higher is
required. 

Also updated INSTALL PLUGIN description.

Setting report to NDI pending push into 6.0.x.

Pushed into 6.0.11-alpha (revid:joro@sun.com-20090318122208-1b5kvg6zeb4hxwp9) (version
source revid:sergey.glukhov@sun.com-20090313094254-lwu4fzuap013msc8) (merge vers:
6.0.11-alpha) (pib:6)

Noted in 6.0.11 changelog.

Revised "Note" part of changelog entry:

Note:
InnoDB Plugin versions 1.0.4 and higher will take advantage of this
bug fix. Although the InnoDB Plugin is source code compatible with
multiple MySQL releases, a given binary InnoDB Plugin can be used
only with a specific MySQL release. When InnoDB Plugin 1.0.4 is
released, it is expected to be compiled for MySQL 5.1.34. For 5.1.33,
you can use InnoDB Plugin 1.0.3, but you must build from source.

Pushed into 5.1.34-ndb-6.2.18 (revid:jonas@mysql.com-20090508185236-p9b3as7qyauybefl)
(version source revid:jonas@mysql.com-20090508100057-30ote4xggi4nq14v) (merge vers:
5.1.33-ndb-6.2.18) (pib:6)

Pushed into 5.1.34-ndb-6.3.25 (revid:jonas@mysql.com-20090509063138-1u3q3v09wnn2txyt)
(version source revid:jonas@mysql.com-20090508175813-s6yele2z3oh6o99z) (merge vers:
5.1.33-ndb-6.3.25) (pib:6)

Pushed into 5.1.34-ndb-7.0.6 (revid:jonas@mysql.com-20090509154927-im9a7g846c6u1hzc)
(version source revid:jonas@mysql.com-20090509073226-09bljakh9eppogec) (merge vers:
5.1.33-ndb-7.0.6) (pib:6)