MySQL Bugs: #39053: UNISTALL PLUGIN does not allow the storage engine to cleanup open connections

Bug #39053	UNISTALL PLUGIN does not allow the storage engine to cleanup open connections
Submitted:	26 Aug 2008 18:48	Modified:	23 Oct 7:16
Reporter:	Inaam Rana
Status:	In progress
Category:	Server: SE API	Severity:	S3 (Non-critical)
Version:	5.1	OS:	Any
Assigned to:	Tatjana A. Nuernberg	Target Version:	5.1+
Triage:	Triaged: D2 (Serious) / R3 (Medium) / E3 (Medium)

Description:
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     | 
| MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL    | GPL     | 
| MyISAM     | ACTIVE | STORAGE ENGINE | NULL    | GPL     | 
+------------+--------+----------------+---------+---------+
5 rows in set (0.00 sec)

mysql> install plugin innodb soname 'ha_innodb.so';
Query OK, 0 rows affected (1.30 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     | 
| MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL         | GPL     | 
| MyISAM     | ACTIVE | STORAGE ENGINE | NULL         | GPL     | 
| InnoDB     | ACTIVE | STORAGE ENGINE | ha_innodb.so | GPL     | 
+------------+--------+----------------+--------------+---------+
6 rows in set (0.00 sec)

mysql> use test;
Database changed
mysql> create table t1 (id int primary key) engine=innodb;
Query OK, 0 rows affected (0.01 sec)

mysql> uninstall plugin innodb;

uninstall plugin innodb hangs. This happens because MySQL does not call
closecon_handlerton() for the query thread that created the table. Hence InnoDB does not
get a chance to clean up the open connection and hangs in
logs_empty_and_mark_files_at_shutdown() waiting for all open MySQL connections to close.

How to repeat:
see above.

Thank you for the report.

Verified as described using InnoDB plugin.

The fix for this is not as easy as calling closecon_handlerton() from uninstall plugin
routine. As per the existing current interface : 

Close Connection Routine :
     hton-> close_connection = innobase_close_connection; 
     This is called for each user connection when the connection is terminated.

Panic Routine :
     hton -> panic = innobase_end ;
     This is called when you shutdown the server or when you uninstall 
     the plugin.
  
  It is up to the implementation to properly close all the open connections 
associated when the plugin is unloaded. 
  This problem is specifically troublesome for innodb since it keeps a dummy
transaction (trx structure) open for all idle connections which has ever opened atleast
one innodb table in it's lifetime (though there is no active mysql
transaction involved).

Following simple scenario hangs (autocommit is on) even when there is no other
connection active :
      select * from innodb_table; uninstall plugin innodb; 

The semantics and fix for what should be done when there are multiple active transactions
using innodb table is more complicated and graceful handling of these will definitely
involve server changes. We are not likely to do major changes in 5.1 to address this.

  However, the change in innodb in innobase_end() function to atleast close the
transaction structures (which are currently not in use) will be acceptable for 5.1 fix.

The symptoms for uninstall problems have changed now.
"uninstall plugin innodb" will return immediately and "show plugins" will show
the plugin is "deleted". However, if there are any other active connections involving
innodb, will not have their close connection handlerton method called when the session is
terminated later. The problem does not seem to be limited to close connection but also
applies to few other handlerton methods.
Also, "show engine innodb status" does not work when the plugin is marked deleted but is
still in use.

These problems need to be fixed in server after a plugin is marked "deleted". 

Note: These fixes alone won't help fix the problem specific to innodb described earlier.
innobase_end should not be waiting for other open idle sessions to terminate when they
are not involved in any sort of transactions.

The following workaround can be used regarding uninstall of innodb 
(or any plugin)  :
   - Terminate all sessions which has ever touched innodb table.
   - Open a new fresh connection:
           mysql -u root 
             mysql> flush tables;
             mysql> uninstall plugin innodb;

A follow-up to last discussion with SergG about this bug (5.1 specific)...

Currently SE plugin gets locked whenever table share is created, and unlocked when table
share gets freed. We're free to uninstall this plugin whenever it is in unlocked state.

Unfortunately there is additional thing to consider. There may be a binding between
storage engine and connection (THD). It is a bad idea to remove a plugin until this
binding is destroyed.

This binding may be created by a storage engine at any time by setting THD::ha_data to
not-NULL. It gets destroyed when a connection is termintated
(THD::~THD/ha_close_connection).

What we should do is set up additional SE plugin lock, which must stay while described
above binding is alive.

A few important points:
1. thd_ha_data() - is called by a plugin to retrieve a pointer to ha_data. As we can't be
sure what is SE intention at this point, we must lock a plugin here.

2. End of statement. Here we know for sure if we need additional lock (ha_data != NULL).
If we don't need it (ha_data == NULL) and we locked a plugin in thd_ha_data(), release
lock.

3. End of connection (ha_close_connection). If we have additional lock, release it.