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 16:48	Modified:	18 Jun 2010 5:24
Reporter:	Inaam Rana	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Storage Engine API	Severity:	S3 (Non-critical)
Version:	5.1	OS:	Any
Assigned to:	Sergey Vojtovich	CPU Architecture:	Any

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.

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

3436 Sergey Vojtovich	2010-03-31
      BUG#39053 - UNISTALL PLUGIN does not allow the storage engine
                  to cleanup open connections
      
      It was possible to UNINSTALL storage engine plugin when binding
      between THD object and storage engine is still active (e.g. in
      the middle of transaction).
      
      To avoid unclean deactivation (uninstall) of storage engine plugin
      in the middle of transaction, additional storage engine plugin
      locks are acquired by thd_set_ha_data().
      
      If ha_data is not null and storage engine plugin was not locked
      by thd_set_ha_data() in this connection before, storage engine
      plugin gets locked.
      
      If ha_data is null and storage engine plugin was locked by
      thd_set_ha_data() in this connection before, storage engine
      plugin lock gets released.
      
      If handlerton::close_connection() didn't reset ha_data, server does
      it immediately after calling handlerton::close_connection().
      
      Note that this is just a framework fix, storage engines must switch
      to thd_set_ha_data() from thd_ha_data() if they want to see fit.
     @ include/mysql/plugin.h
        As thd_{get|set}_ha_data() have some extra logic now, they
        must be implemented on server side.
     @ include/mysql/plugin.h.pp
        As thd_{get|set}_ha_data() have some extra logic now, they
        must be implemented on server side.
     @ sql/handler.cc
        Make sure ha_data is reset and ha_data_lock is released.
     @ sql/sql_class.cc
        As thd_{get|set}_ha_data() have some extra logic now, they
        must be implemented on server side.
     @ sql/sql_class.h
        Added ha_data_lock.

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

3436 Sergey Vojtovich	2010-04-13
      BUG#39053 - UNISTALL PLUGIN does not allow the storage engine
                  to cleanup open connections
      
      It was possible to UNINSTALL storage engine plugin when binding
      between THD object and storage engine is still active (e.g. in
      the middle of transaction).
      
      To avoid unclean deactivation (uninstall) of storage engine plugin
      in the middle of transaction, additional storage engine plugin
      lock is acquired by thd_set_ha_data().
      
      If ha_data is not null and storage engine plugin was not locked
      by thd_set_ha_data() in this connection before, storage engine
      plugin gets locked.
      
      If ha_data is null and storage engine plugin was locked by
      thd_set_ha_data() in this connection before, storage engine
      plugin lock gets released.
      
      If handlerton::close_connection() didn't reset ha_data, server does
      it immediately after calling handlerton::close_connection().
      
      Note that this is just a framework fix, storage engines must switch
      to thd_set_ha_data() from thd_ha_data() if they want to see fit.
     @ include/mysql/plugin.h
        As thd_{get|set}_ha_data() have some extra logic now, they
        must be implemented on server side.
     @ include/mysql/plugin.h.pp
        As thd_{get|set}_ha_data() have some extra logic now, they
        must be implemented on server side.
     @ sql/handler.cc
        Make sure ha_data is reset and ha_data lock is released.
     @ sql/sql_class.cc
        As thd_{get|set}_ha_data() have some extra logic now, they
        must be implemented on server side.
     @ sql/sql_class.h
        Added ha_data lock.

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

3436 Sergey Vojtovich	2010-04-14
      BUG#39053 - UNISTALL PLUGIN does not allow the storage engine
                  to cleanup open connections
      
      It was possible to UNINSTALL storage engine plugin when binding
      between THD object and storage engine is still active (e.g. in
      the middle of transaction).
      
      To avoid unclean deactivation (uninstall) of storage engine plugin
      in the middle of transaction, additional storage engine plugin
      lock is acquired by thd_set_ha_data().
      
      If ha_data is not null and storage engine plugin was not locked
      by thd_set_ha_data() in this connection before, storage engine
      plugin gets locked.
      
      If ha_data is null and storage engine plugin was locked by
      thd_set_ha_data() in this connection before, storage engine
      plugin lock gets released.
      
      If handlerton::close_connection() didn't reset ha_data, server does
      it immediately after calling handlerton::close_connection().
      
      Note that this is just a framework fix, storage engines must switch
      to thd_set_ha_data() from thd_ha_data() if they want to see fit.
     @ include/mysql/plugin.h
        As thd_{get|set}_ha_data() have some extra logic now, they
        must be implemented on server side.
     @ include/mysql/plugin.h.pp
        As thd_{get|set}_ha_data() have some extra logic now, they
        must be implemented on server side.
     @ sql/handler.cc
        Make sure ha_data is reset and ha_data lock is released.
     @ sql/handler.h
        hton is not supposed to be updated by ha_lock_engine(),
        make it const.
     @ sql/sql_class.cc
        As thd_{get|set}_ha_data() have some extra logic now, they
        must be implemented on server side.
     @ sql/sql_class.h
        Added ha_data lock.

See also BUG#52814.

Approved after review requests have been executed.

Pushed into 5.1.47 (revid:joro@sun.com-20100505145753-ivlt4hclbrjy8eye) (version source revid:kristofer.pettersson@sun.com-20100416145616-vdcdyz9eu7j86lp7) (merge vers: 5.1.47) (pib:16)

A note has been added to the 5.1.47 changelog: 

        When using <literal>UNINSTALL PLUGIN</literal> to remove a                                                                                         
        loaded plugin, open tables and connections could                                                                                                   
        caused <command>mysqld</command> to hang until the open                                                                                            
        connections had been closed.

Pushed into mysql-next-mr (revid:alik@sun.com-20100524190136-egaq7e8zgkwb9aqi) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (pib:16)

Pushed into 6.0.14-alpha (revid:alik@sun.com-20100524190941-nuudpx60if25wsvx) (version source revid:alik@sun.com-20100422150658-fkhgnwwkyugtxrmu) (merge vers: 6.0.14-alpha) (pib:16)

Pushed into 5.5.5-m3 (revid:alik@sun.com-20100524185725-c8k5q7v60i5nix3t) (version source revid:alexey.kopytov@sun.com-20100429203306-tg0wz4y2xyx8edrl) (merge vers: 5.5.5-m3) (pib:16)

Updated versions for 5.5.5 and 6.0.14

Pushed into 5.1.47-ndb-7.0.16 (revid:martin.skold@mysql.com-20100617114014-bva0dy24yyd67697) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

Pushed into 5.1.47-ndb-6.2.19 (revid:martin.skold@mysql.com-20100617115448-idrbic6gbki37h1c) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

Pushed into 5.1.47-ndb-6.3.35 (revid:martin.skold@mysql.com-20100617114611-61aqbb52j752y116) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

No additional changelog entries required.