Bug #33899 Deadlock in mysql_real_query with shared memory connections
Submitted: 17 Jan 2008 16:28 Modified: 13 May 2009 16:26
Reporter: Armin Schöffmann (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: C API (client library) Severity:S2 (Serious)
Version:5.0.x/5.1 OS:Windows
Assigned to: Davi Arnaut CPU Architecture:Any
Tags: client, Contribution, deadlock, max_allowed_packet, shared memory

[17 Jan 2008 16:28] Armin Schöffmann
Description:
A call to mysql_real_query will block forever if:
- the connection is made through shared_memory
- and the resulting query is aborted by the server (e.g. KILL or exceeding max_allowed_packet)

How to repeat:

1)
Connect to mysql-server through shared_memory.

Create a sql-statement exceeding max_allowed_packet-size.
e.g. a long blob-insert.

Send statement through mysql_real_query.
Server will abort the query. (exspected behaviour) with errorcode "MySQL server has gone away"

mysql_real_query never returns, the executing application-thread is deadlocked.

Repeat query through a tcp/ip connection:
mysql_real_connect returns with error-code as soon as the server aborts the query.

2)
Same behaviour with mysql-cmd-client.

3)
Same problem, if a running query is aborted through "KILL conn_id" from within another connection (no matter of max_allowed_packet)
[25 Jan 2008 13:47] Susanne Ebrecht
Many thanks for writing a bug report.

Please can you provide a short test, that we can try to reproduce the issue.
[26 Jan 2008 14:23] Armin Schöffmann
test config my.ini

Attachment: my.ini (application/octet-stream, text), 1.10 KiB.

[26 Jan 2008 14:31] Armin Schöffmann
How to repeat:

Platform win32

Mysql installed as service at:
"C:\Program Files (x86)\MySQL 5.0"

Datadir at:
"C:\Program Files (x86)\MySQL 5.0\data"

using my.ini - config-file

1) Replace server-config ("my.ini") with attached sample-config and 
   restart MySQL-server .
   This config restricts max_allowed_packet-size to 1M and enables shared-memory.

2) Download this sql-dump (~9MB) from 
   http://www.aegaeon.de/downloads/mysql/dump.txt
   and save it as "C:\dump.txt"

5) Execute script with protocol "TCP"

> MYSQL -uroot --protocol=tcp -e "SOURCE c:\dump.txt"
Enter password:**********
ERROR 2006 (HY000) at line 40 in file: 'c:\dump.txt': MySQL server has gone away
>

exspected behaviour

6) repeat same script with protocol "MEMORY"
> MYSQL -uroot --protocol=memory -e "SOURCE c:\dump.txt"
Enter password:**********

mysql-client deadlocks
^C

-> [CTRL][BREAK] necessary

Best regards,
Armin.
[18 Mar 2008 20:44] Armin Schöffmann
The below patch against 'viosocket.c' fixes the deadlock-condition in function 'vio_write_shared_memory(Vio * vio, const gptr buf, int size)'.

I'd be happy if you could push this into the next 5.0 enterprise-build.

patch verified on 5.0.60 (repository) and against 5.0.54-enterprise-nt

--- viosocket.c	2008-03-18 20:54:02.125254000 +0100
+++ patch_bug#33899_viosocket.c	2008-03-18 20:50:01.584985500 +0100
@@ -479,19 +479,21 @@
   int length;
   int remain_local;
   char *current_postion;
+  HANDLE events[2];
 
   DBUG_ENTER("vio_read_shared_memory");
   DBUG_PRINT("enter", ("sd: %d  buf: 0x%lx  size: %d", vio->sd, buf, size));
 
   remain_local = size;
   current_postion=buf;
+
+  events[0]= vio->event_server_wrote;
+  events[1]= vio->event_conn_closed;
+
   do
   {
     if (vio->shared_memory_remain == 0)
     {
-      HANDLE events[2];
-      events[0]= vio->event_server_wrote;
-      events[1]= vio->event_conn_closed;
       /*
         WaitForMultipleObjects can return next values:
          WAIT_OBJECT_0+0 - event from vio->event_server_wrote
@@ -538,6 +540,7 @@
   int length;
   uint remain;
   HANDLE pos;
+  HANDLE events[2];
   int sz;
   char *current_postion;
 
@@ -546,10 +549,14 @@
 
   remain = size;
   current_postion = buf;
+
+  events[0]= vio->event_server_read;
+  events[1]= vio->event_conn_closed;
+
   while (remain != 0)
   {
-    if (WaitForSingleObject(vio->event_server_read, vio->net->write_timeout*1000) 
-                            != WAIT_OBJECT_0)
+    if (WaitForMultipleObjects(2,events,FALSE,
+							   vio->net->write_timeout*1000) != WAIT_OBJECT_0)
     {
       DBUG_RETURN(-1);
     };
[1 Jul 2008 17:55] MySQL Verification Team
Thank you for the bug report. Verified with the mysql client test case, the client remains hanging even after the server shutdown.
[8 Oct 2008 14:18] Armin Schöffmann
Gentlemen, would it be possible to push our fix into the next release?
This flaw is definately high prio.

Or are we the only ones using shared-mem ?
Regards,Armin.
[24 Mar 2009 6:20] Masood Mortazavi
Hi Armin -

Given you're interested in contribution to MySQL, please note that
MySQL is using a new contributor agreement (i.e. the "Sun Contributor Agreement"). 

Before we can handle, review and absorb your code into the main development branches for MySQL or in updates on existing releases, etc., it is required that you sign the "Sun Contributor Agreement". 

Under the Sun Contributor Agreement (SCA), the contributor retains copyrights while also granting those same rights to Sun as the project sponsor. It supersedes the previously used MySQL Contributor License Agreement (CLA).

You can get a copy of the SCA document here:
http://www.sun.com/software/opensource/sca.pdf 

Once you sign and send your copy of the SCA document as described here: 
  http://forge.mysql.com/wiki/Sun_Contributor_Agreement , 
your signatory status will be reflected, along with your interest in contributing to MySQL, here: 
   https://sca.dev.java.net/CA_signatories.htm 

Having a clear SCA status will enable and facilitate the review and interaction regarding your code contributions to MySQL (as well as to any other Sun-managed Open-Source project).

I hope this helps.

Regards,
- m.
[24 Mar 2009 16:01] Armin Schöffmann
SCA signed and approved.
[26 Mar 2009 22:47] 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/70597
[26 Mar 2009 23:17] 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/70600

2778 Davi Arnaut	2009-03-26
      Bug#33899: Deadlock in mysql_real_query with shared memory connections
      
      The problem is that the read and write methods of the shared
      memory transport (protocol) didn't react to asynchronous close
      events, which could lead to a lock up as the client would wait
      (until time out) for a server response that will never come.
      
      The solution is to also wait for close events while waiting
      for I/O from or to the server.
     @ mysql-test/r/shm.result
        Add test case result for Bug#33899
     @ mysql-test/t/shm.test
        Add test case for Bug#33899
     @ vio/viosocket.c
        Also wait for close events.
[26 Mar 2009 23:26] 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/70602
[26 Mar 2009 23:26] Davi Arnaut
Queued to 5.0-bugteam
[5 May 2009 18:54] Bugs System
Pushed into 5.0.82 (revid:davi.arnaut@sun.com-20090505184158-dvmedh8n472y8np5) (version source revid:davi.arnaut@sun.com-20090505184158-dvmedh8n472y8np5) (merge vers: 5.0.82) (pib:6)
[5 May 2009 19:37] Bugs System
Pushed into 5.1.35 (revid:davi.arnaut@sun.com-20090505190206-9xmh7dlc6kom8exp) (version source revid:davi.arnaut@sun.com-20090505190206-9xmh7dlc6kom8exp) (merge vers: 5.1.35) (pib:6)
[6 May 2009 14:09] Bugs System
Pushed into 6.0.12-alpha (revid:svoj@sun.com-20090506125450-yokcmvqf2g7jhujq) (version source revid:horst@mysql.com-20090327184517-25eq077q2beocs6y) (merge vers: 6.0.11-alpha) (pib:6)
[13 May 2009 16:26] Paul DuBois
Noted in 5.0.82, 5.1.35, 6.0.12 changelogs.

For shared-memory connections, the read and write methods did not
properly handle asynchronous close events, which could lead to the
client locking up waiting for a server response. For example, a call
to mysql_real_query() would block forever on the client side if the
executed statement was aborted on the server side.
[15 Jun 2009 8:24] Bugs System
Pushed into 5.1.35-ndb-6.3.26 (revid:jonas@mysql.com-20090615074202-0r5r2jmi83tww6sf) (version source revid:jonas@mysql.com-20090615070837-9pccutgc7repvb4d) (merge vers: 5.1.35-ndb-6.3.26) (pib:6)
[15 Jun 2009 9:04] Bugs System
Pushed into 5.1.35-ndb-7.0.7 (revid:jonas@mysql.com-20090615074335-9hcltksp5cu5fucn) (version source revid:jonas@mysql.com-20090615072714-rmfkvrbbipd9r32c) (merge vers: 5.1.35-ndb-7.0.7) (pib:6)
[15 Jun 2009 9:44] Bugs System
Pushed into 5.1.35-ndb-6.2.19 (revid:jonas@mysql.com-20090615061520-sq7ds4yw299ggugm) (version source revid:jonas@mysql.com-20090615054654-ebgpz7elwu1xj36j) (merge vers: 5.1.35-ndb-6.2.19) (pib:6)