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
Triage: Triaged: D2 (Serious) / R3 (Medium) / E3 (Medium)

[26 Aug 2008 16:48] Inaam Rana
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.
[27 Aug 2008 6:14] Sveta Smirnova
Thank you for the report.

Verified as described using InnoDB plugin.
[9 Mar 2009 15:57] Thava Alagu
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.
[18 Mar 2009 23:08] Thava Alagu
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;
[10 Nov 2009 12:12] Sergey Vojtovich
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.
[31 Mar 2010 12:12] Bugs System
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.
[13 Apr 2010 11:11] Bugs System
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.
[14 Apr 2010 9:54] Bugs System
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.
[14 Apr 2010 10:07] Sergey Vojtovich
See also BUG#52814.
[14 Apr 2010 11:53] Ingo Strüwing
Approved after review requests have been executed.
[5 May 2010 15:23] Bugs System
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)
[6 May 2010 9:06] MC Brown
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.
[28 May 2010 6:05] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100524190136-egaq7e8zgkwb9aqi) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (pib:16)
[28 May 2010 6:33] Bugs System
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)
[28 May 2010 7:01] Bugs System
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)
[2 Jun 2010 8:05] MC Brown
Updated versions for 5.5.5 and 6.0.14
[17 Jun 2010 12:09] Bugs System
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)
[17 Jun 2010 12:57] Bugs System
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)
[17 Jun 2010 13:37] Bugs System
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)
[18 Jun 2010 5:24] MC Brown
No additional changelog entries required.