Bug #43477 Server hangs with table general log, statement binlog format and partitions
Submitted: 7 Mar 2009 20:09 Modified: 5 Oct 2010 14:46
Reporter: Elena Stepanova Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Partitions Severity:S2 (Serious)
Version:5.1.29, 5.1.32, 5.1.33 OS:Any
Assigned to: Assigned Account CPU Architecture:Any
Triage: Triaged: D1 (Critical)

[7 Mar 2009 20:09] Elena Stepanova
Description:
Server hangs when several connections simultaneously insert into the same table. 

dbx output from the process (Ver 5.1.33-debug for sun-solaris2.10 on sparc (Source distribution):

(dbx) where
current thread: t@1
  [1] __pollsys(0xffffffff7fff8cb0, 0x2, 0x0, 0x0, 0x0, 0x0), at 0xffffffff7ded346c
  [2] _pollsys(0xffffffff7fff8cb0, 0x2, 0x0, 0x0, 0x0, 0x0), at 0xffffffff7dec5780
  [3] _pselect(0x2, 0xffffffff7fff8cb0, 0xffffffff7dff4580, 0xffffffff7dff4580, 0x0, 0x0), at 0xffffffff7de72bd8
  [4] _select(0x9, 0xffffffff7fffaf68, 0x0, 0x0, 0x0, 0x2), at 0xffffffff7de72f7c
=>[5] handle_connections_sockets(arg = (nil)), line 4931 in "mysqld.cc"
  [6] main(argc = 34, argv = 0xffffffff7fffd188), line 4430 in "mysqld.cc"

Below are the conditions which seem to matter, as I could not reproduce the problem without having all of them:

- the table where inserts are done has partitions (at least one, tried with partition by hash);
- there is a trigger after insert on the table, which inserts into another table;
- binary log is switched on, and binlog_format is set to STATEMENT;
- logging to TABLE is switched on (log_output is TABLE or TABLE and FILE);
- general_log is switched on.

Possible workaround is to avoid using table logging if other conditions listed above are strictly necessary.

How to repeat:
 perl ./mysql-test-run.pl --mysqld=--log-output=TABLE --mysqld=--general-log --do-test=server_hangup 

The test case is attached. It works for 5.1.30 and higher; for lower version it might need some modifications, but the problem itself is reproducible on 5.1.29 as well.

Please note the variable 
let $con_number= 10;
at the start of the test. Depending on the machine, you might need to increase the value to reproduce the problem. I had a range from 4 to 30 on different machines.
[7 Mar 2009 20:12] Elena Stepanova
Test file

Attachment: server_hangup.test (application/octet-stream, text), 1.28 KiB.

[7 Mar 2009 20:16] Elena Stepanova
The minimal command to run the test is 

perl ./mysql-test-run.pl --mysqld=--log-output=TABLE --mysqld=--general-log --do-test=server_hangup

binlog_format is set to STATEMENT in the test itself.
[7 Apr 2010 14:22] Mattias Jonsson
Related to bug#51851. The problem is that partitioning holds the table_share->mutex to avoid giving the same auto_increment values when in statement based replication.

Solution would be to use the patch given in bug#51851 also in 5.1 (i.e break out the partitioning specific data from ha_data into ha_part_data and add a destroy function for that too). This will also resolve the problem that table_share->ha_data cannot be used by engines if it is partitioned.
[5 Oct 2010 14:46] Mattias Jonsson
Closing as duplicate of bug#51851. (tested after that was pushed to 51-bugteam).