MySQL Bugs: #33731: INSTALL/UNINSTALL PLUGIN: Inconsistent handling of identifier case

Bug #33731	INSTALL/UNINSTALL PLUGIN: Inconsistent handling of identifier case
Submitted:	8 Jan 2008 0:43	Modified:	12 Nov 2009 20:59
Reporter:	Roland Bouman	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: User-defined functions ( UDF )	Severity:	S3 (Non-critical)
Version:	5.1.22	OS:	Any
Assigned to:	Sergei Golubchik	CPU Architecture:	Any

Description:
When installing plugins, the plugin identifier is not handled consistently with respect to letter case. 

For a SE plugin for example, the addition/removal to the mysql.plugin table seems to be case sensitive, wheres the the plugin is removed from the list of SE's regardless of lettercase. Something similar is seen with information_schema plugins.

This can lead to the confusing situation that a SE for an uninstalled plugin is no longer available, whereas the plugin is still present. The workaround in this case is to remove the entry from the plugin table, or to re-install the plugin using the exact same name case that was used before.

How to repeat:
#1 demonstrate the plugin is not here yet

mysql> select * from mysql.plugin;
Empty set (0.00 sec)

mysql> show engines;
+------------+---------+----------------------------------------------------------------+--------------+-----+------------+
| Engine     | Support | Comment                                                        | Transactions | XA  | Savepoints |
+------------+---------+----------------------------------------------------------------+--------------+-----+------------+
....
+------------+---------+----------------------------------------------------------------+--------------+-----+------------+
8 rows in set (0.00 sec)

#2 install the plugin (upper case identifier)

mysql> install  plugin PBXT soname 'libpbxt.so';
Query OK, 0 rows affected (0.05 sec)

#3 demonstrate the plugin is now present both as SE and as plugin

mysql> select * from mysql.plugin;
+------+------------+
| name | dl         |
+------+------------+
| PBXT | libpbxt.so |
+------+------------+
1 row in set (0.00 sec)

mysql> show engines;
+------------+---------+----------------------------------------------------------------+--------------+-----+------------+
| Engine     | Support | Comment                                                        | Transactions | XA  | Savepoints |
+------------+---------+----------------------------------------------------------------+--------------+-----+------------+
| PBXT       | YES     | High performance, multi-versioning transactional engine        +------------+---------+----------------------------------------------------------------+--------------+-----+------------+
9 rows in set (0.00 sec)

#4 uninstall the plugin, upper case name

mysql> uninstall  plugin PBXT;
Query OK, 0 rows affected (0.01 sec)

#5 demonstrate the plugin and sE are now gone as expected

mysql> select * from mysql.plugin;
Empty set (0.00 sec)

mysql> show engines;
+------------+---------+----------------------------------------------------------------+--------------+-----+------------+
| Engine     | Support | Comment                                                        | Transactions | XA  | Savepoints |
+------------+---------+----------------------------------------------------------------+--------------+-----+------------+
...
+------------+---------+----------------------------------------------------------------+--------------+-----+------------+
8 rows in set (0.00 sec)

#6 demonstrate that the plugin can be re-installed without issues:

mysql> install  plugin PBXT soname 'libpbxt.so';
Query OK, 0 rows affected (0.04 sec)

mysql> select * from mysql.plugin;
+------+------------+
| name | dl         |
+------+------------+
| PBXT | libpbxt.so |
+------+------------+
1 row in set (0.00 sec)

mysql> show engines;
+------------+---------+----------------------------------------------------------------+--------------+-----+------------+
| Engine     | Support | Comment                                                        | Transactions | XA  | Savepoints |
+------------+---------+----------------------------------------------------------------+--------------+-----+------------+
| PBXT       | YES     | High performance, multi-versioning transactional engine        +------------+---------+----------------------------------------------------------------+--------------+-----+------------+
9 rows in set (0.00 sec)

#7 uninstall with lower case name

mysql> uninstall  plugin pbxt;
Query OK, 0 rows affected (0.00 sec)

#8 demonstrate that the plugin is still there, although the engine is not:

mysql> select * from mysql.plugin;
+------+------------+
| name | dl         |
+------+------------+
| PBXT | libpbxt.so |
+------+------------+
1 row in set (0.00 sec)

mysql> show engines;
+------------+---------+----------------------------------------------------------------+--------------+-----+------------+
| Engine     | Support | Comment                                                        | Transactions | XA  | Savepoints |
+------------+---------+----------------------------------------------------------------+--------------+-----+------------+
....
+------------+---------+----------------------------------------------------------------+--------------+-----+------------+
8 rows in set (0.00 sec)

mysql> install  plugin PBXT soname 'libpbxt.so';
ERROR 1062 (23000): Duplicate entry 'PBXT' for key 'PRIMARY'
mysql> install  plugin pbxt soname 'libpbxt.so';
Query OK, 0 rows affected (0.05 sec)

mysql> show engines;
+------------+---------+----------------------------------------------------------------+--------------+-----+------------+
| Engine     | Support | Comment                                                        | Transactions | XA  | Savepoints |
+------------+---------+----------------------------------------------------------------+--------------+-----+------------+
| PBXT       | YES     | High performance, multi-versioning transactional engine        +------------+---------+----------------------------------------------------------------+--------------+-----+------------+
9 rows in set (0.00 sec)

mysql>                                                                                            

Suggested fix:
Please treat plugins consistently with regard to lettercase.

Thank you for the report.

Verified as described.

We are hitting it too, in MySQL 5.1.29. This is not limited to storage engine plugins, but also INFORMATION_SCHEMA plugins are affected:

INSTALL PLUGIN InnoDB SONAME 'ha_innodb.so';
SELECT * FROM mysql.plugin; -- it is there
UNINSTALL PLUGIN INNODB; -- will only delete the plugin object from memory
SELECT * FROM mysql.plugin; -- InnoDB is still listed there

Suggested fix:

Refuse to uninstall the plugin object from the memory, unless the name matches case-sensitively.

Alternatively, do not ignore "record not found" errors from mysql_uninstall_plugin(), or make it scan the table mysql.plugin until a case-insensitive match is found. The column mysql.plugin.name is case-sensitive, because it has been declared COLLATE utf8_bin.

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/72408

2744 Sergei Golubchik	2009-04-17
      Bug #33731 INSTALL/UNINSTALL PLUGIN: Inconsistent handling of identifier case
      fix the table definition to match the assumptions of the code
     @ scripts/mysql_system_tables.sql
        plugin names are compared case insensitive
     @ scripts/mysql_system_tables_fix.sql
        plugin names are compared case insensitive

Ok to push.

Pushed into 6.0.11-alpha (revid:alik@sun.com-20090423070920-e5lq3vrrqi016z2c) (version source revid:alik@sun.com-20090423070920-e5lq3vrrqi016z2c) (merge vers: 6.0.11-alpha) (pib:6)

Noted in 6.0.11 changelog.

INSTALL PLUGIN and UNINSTALL PLUGIN did not handle plugin identifiers
consistently with respect to lettercase.

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/86596

2896 Sergei Golubchik	2009-10-12
      bug#33731 - INSTALL/UNINSTALL PLUGIN: Inconsistent handling of identifier case 
      
      indexed column in mysql.plugin table should use case-insensitive collation
      for index lookups to work
      
      (backport from 6.0)
     @ sql/sql_plugin.cc
        generate the key correctly using key_copy.
        field->store() stores the value in record format, not key format.

Pushed into 6.0.14-alpha (revid:alik@sun.com-20091031081410-qkxmjsdzjmj840aq) (version source revid:serg@mysql.com-20091012191335-9spk8dos6hp19ito) (merge vers: 6.0.14-alpha) (pib:13)

Already fixed in 6.0.11.

Setting report to NDI pending push to 5.5.x

Pushed into 5.5.0-beta (revid:alik@sun.com-20091110093229-0bh5hix780cyeicl) (version source revid:mikael@mysql.com-20091102100915-a2nbfxaqprpgptfw) (merge vers: 5.5.0-beta) (pib:13)

Noted in 5.5.0 changelog.