Bug #25721 Federated: hang with simultaneous ALTER SERVER and CREATE SERVER
Submitted: 19 Jan 2007 17:28 Modified: 30 Mar 2007 19:50
Reporter: Peter Gulutzan Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Federated storage engine Severity:S3 (Non-critical)
Version:5.1.15-beta-debug OS:Linux (SUSE 10.0 / 64-bit)
Assigned to: Antony Curtis CPU Architecture:Any

[19 Jan 2007 17:28] Peter Gulutzan
Description:
I create a procedure which repeatedly says CREATE SERVER and ALTER SERVER.
I run the procedure on two connections.
After a while, both connections hang.

How to repeat:
This is a "federated" bug, but there are no federated tables.
The hang can be repeated with one machine.

Start mysqld. Start two instances of mysql client, T1 and T2.

On T1, say:

delimiter //
drop server s//
drop table t100//
drop procedure p1//
create procedure p1 ()
begin
  DECLARE v INT DEFAULT 0;
  DECLARE v2 INT;
  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SELECT 'error';
  WHILE v < 1000000 do
    SELECT v;
    SELECT 'CREATE SERVER';
    CREATE SERVER s
      FOREIGN DATA WRAPPER mysql
      OPTIONS (USER 'Remote', HOST '192.168.1.106', DATABASE 'test');
    SELECT 'ALTER SERVER';
    ALTER SERVER s OPTIONS (USER 'Remote');
    SET v = v + 1;
    END WHILE;
  END//
CALL p1()//

On T2, say:
CALL p1();

Eventually -- usually within 10,000 iterations on my machine --
both T1 and T2 will hang. If I start a third mysql connection
and say SHOW PROCESSLIST, I get the impression that the server
is trying to handle an ALTER SERVER and a CREATE SERVER
simultaneously.
[19 Jan 2007 17:48] Miguel Solorzano
Thank you for the bug report. Verified as described.
[26 Jan 2007 2:03] 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/18821

ChangeSet@1.2410, 2007-01-25 18:02:42-08:00, acurtis@xiphis.org +4 -0
  Bug#25721
    "Concurrent ALTER/CREATE SERVER can deadlock"
    Deadlock caused by inconsistant order of locking mutexes.
    Test provided.
[8 Feb 2007 0:45] 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/19528

ChangeSet@1.2429, 2007-02-07 16:45:31-08:00, antony@ppcg5.local +4 -0
  Bug#25721
    "Concurrent ALTER/CREATE SERVER can lead to deadlock"
    Deadlock caused by inconsistant use of mutexes in sql_server.cc
    Code has been simplified and opportunity for deadlock removed.
    Test included.
[24 Mar 2007 1:31] 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/22853

ChangeSet@1.2498, 2007-03-23 17:31:27-07:00, acurtis@xiphis.org +5 -0
  Bug#25721
    "Concurrent ALTER/CREATE SERVER can lead to deadlock"
    Deadlock caused by inconsistant use of mutexes in sql_server.cc
    One mutex has been removed to resolve deadlock.
    Many functions were made private which should not be exported.
    Unused variables and function removed.
[24 Mar 2007 1:39] Antony Curtis
pushed to 5.1-engines repository
[30 Mar 2007 17:28] Bugs System
Pushed into 5.1.18-beta
[30 Mar 2007 19:50] Paul Dubois
Noted in 5.1.18 changelog.
[4 Apr 2007 21:36] 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/23839

ChangeSet@1.2542, 2007-04-04 21:35:58+00:00, acurtis@xiphis.org +2 -0
  fix test for bug#25721
  did not run properly on a fast Windows machine
[8 Apr 2007 19:01] Bugs System
Pushed into 5.1.18-beta